Show pageOld revisionsBacklinksODT exportBack to top This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong. =====Overview===== =====Scripts===== Scripts that are specific to exadata monitoring: ====Cell Usage==== <Code:bash> select a.name, b.value/1024/1024 MB from v$sysstat a, v$mystat b where a.statistic# = b.statistic# and (a.name in ('physical read total bytes', 'physical write total bytes', 'cell IO uncompressed bytes') or a.name like 'cell phy%'); SQL> select a.name, b.value from v$sysstat a, v$mystat b 2 where a.statistic# = b.statistic# and 3 (a.name like '%flash cache read hits' 4 or a.name like 'cell phy%' 5 or a.name like 'physical read tot%' 6 or a.name like 'physical read req%'); NAME VALUE ---------------------------------------------------------------- ---------- physical read total IO requests 1067 physical read total multi block requests 20 physical read requests optimized 1013 physical read total bytes optimized 15884288 physical read total bytes 20463616 cell physical IO interconnect bytes 20463616 cell physical IO bytes saved during optimized file creation 0 cell physical IO bytes saved during optimized RMAN file restore 0 cell physical IO bytes eligible for predicate offload 0 cell physical IO bytes saved by storage index 0 cell physical IO bytes sent directly to DB node to balance CPU 0 NAME VALUE ---------------------------------------------------------------- ---------- cell physical IO interconnect bytes returned by smart scan 0 cell physical IO bytes saved by columnar cache 0 cell physical write bytes saved by smart file initialization 0 cell flash cache read hits 1013 cell physical write IO bytes eligible for offload 0 cell physical write IO host network bytes written during offloa 0 17 rows selected. </Code> ====Flash Cache Content==== To see if an object is in the flash cache, we have to know its: * object number * Tablespace number * Database name <Code:bash> SQL> @/home/oracle/labs/lab04-03-15.sql SQL> select owner, object_name, tablespace_name, ts#, dataobj#, 2 statistic_name, value 3 from v$segment_statistics 4 where owner='SH' and object_name='CUSTOMERS' 5 and statistic_name='optimized physical reads'; OWNER OBJECT_NAME TABLESPACE_NAME TS# DATAOBJ# STATISTICS VALUE ----- -------------------- ----- -- ---------- ------------------------- ----------------------- SH CUSTOMERS SH 7 20473 optimized physical reads 477 </Code> once we have that, we can check in the call using the CellCli: <Code:bash> CellCLI> list flashcachecontent where objectnumber=20473 and tablespacenumber=7 and dbuniquename=DBM detail cachedKeepSize: 0 cachedSize: 13189120 cachedWriteSize: 0 columnarCacheSize: 0 columnarKeepSize: 0 dbID: 2080757153 dbUniqueName: DBM hitCount: 160 missCount: 159 objectNumber: 20473 tableSpaceNumber: 7 CellCLI> </Code> Furthermore, we see that the hitcount in the storage cell is ony: 160, where in SQL query is 477. The reason is the fact that the load was distributed among all 3 cells. We can check that using dcli to query all cells: <Code:bash> [celladmin@qr01celadm01 ~]$ dcli -c qr01celadm01,qr01celadm02,qr01celadm03 cellcli -e list flashcachecontent where objectnumber=20473 and tablespacenumber=7 and dbuniquename=DBM detail qr01celadm01: cachedKeepSize: 0 qr01celadm01: cachedSize: 13189120 qr01celadm01: cachedWriteSize: 0 qr01celadm01: columnarCacheSize: 0 qr01celadm01: columnarKeepSize: 0 qr01celadm01: dbID: 2080757153 qr01celadm01: dbUniqueName: DBM qr01celadm01: hitCount: 160 <- Load 3 qr01celadm01: missCount: 159 qr01celadm01: objectNumber: 20473 qr01celadm01: tableSpaceNumber: 7 qr01celadm02: cachedKeepSize: 0 qr01celadm02: cachedSize: 14188544 qr01celadm02: cachedWriteSize: 0 qr01celadm02: columnarCacheSize: 0 qr01celadm02: columnarKeepSize: 0 qr01celadm02: dbID: 2080757153 qr01celadm02: dbUniqueName: DBM qr01celadm02: hitCount: 177 <- Load 2 qr01celadm02: missCount: 175 qr01celadm02: objectNumber: 20473 qr01celadm02: tableSpaceNumber: 7 qr01celadm03: cachedKeepSize: 0 qr01celadm03: cachedSize: 11378688 qr01celadm03: cachedWriteSize: 0 qr01celadm03: columnarCacheSize: 0 qr01celadm03: columnarKeepSize: 0 qr01celadm03: dbID: 2080757153 qr01celadm03: dbUniqueName: DBM qr01celadm03: hitCount: 140 <- Load 1 qr01celadm03: missCount: 139 qr01celadm03: objectNumber: 20473 qr01celadm03: tableSpaceNumber: 7 </Code> In total is 477, just like the SQL query. ====Smart Scan==== We can see if a query is using the Smart Scan capabilities of the exadata by observing the execution plan: <Code:bash|Query plan using Smart Scan> SQL> connect sh/sh Connected. SQL> set autotrace on explain SQL> SQL> set timing on SQL> select count(*) from cust_info where occupation = 'Farming'; COUNT(*) ---------- 27244 Elapsed: 00:00:02.80 Execution Plan ---------------------------------------------------------- Plan hash value: 1273666552 ---------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 7 | 8718 (1)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 7 | | | |* 2 | TABLE ACCESS STORAGE FULL| CUST_INFO | 229K| 1570K| 8718 (1)| 00:00:01 | ---------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - storage("OCCUPATION"='Farming') filter("OCCUPATION"='Farming') SQL> set autotrace off SQL> </Code> As you can see above, the query is using the: "TABLE ACCESS STORAGE FULL" which is a sign of using the Smart Scan capabilities. Other indication is the wait time statistics: <Code:bash|Wait time statistics> SQL> @/home/oracle/labs/lab09-01-07.sql SQL> SELECT DISTINCT event, total_waits, 2 time_waited/100 wait_secs, average_wait/100 avg_wait_secs 3 FROM V$SESSION_EVENT e, V$MYSTAT s 4 WHERE event LIKE 'cell%' AND e.sid = s.sid; EVENT TOTAL_WAITS WAIT_SECS AVG_WAIT_SECS ---------------------------------------------------------------- ----------- ---------- ------------- cell single block physical read 93 .3 .0033 cell smart table scan 190 2.59 .0137 Elapsed: 00:00:00.24 SQL> </Code> As you can see, the "Cell smart Table scan" takes the majority of the time. (2.59 seconds out of 2.80 seconds) <Code:bash|Smart Scan Monitoring> SQL> col event format a35 SQL> select event, total_waits, 2 time_waited/100 wait_secs, average_wait/100 avg_wait_secs 3 from v$system_event where event like 'cell%'; EVENT TOTAL_WAITS WAIT_SECS AVG_WAIT_SECS ----------------------------------- ----------- ---------- ------------- cell smart table scan 9992 164.9 .0165 cell statistics gather 384 .32 .0008 cell smart file creation 12855 78.46 .0061 cell single block physical read 13292 41.39 .0031 cell multiblock physical read 3354 38.98 .0116 cell list of blocks physical read 23 .77 .0334 6 rows selected. Elapsed: 00:00:00.06 SQL> </Code> oracle_exadata_monitoring.txt Last modified: 2020/12/04 16:20by andonovj