oracle_exadata_monitoring

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Next revision		 Previous revision
oracle_exadata_monitoring [2020/12/01 10:40] – created andonovjoracle_exadata_monitoring [2020/12/04 16:20] (current) – andonovj
Line 1: Line 1:
 =====Overview===== =====Overview=====
  
-====Scripts=====+=====Scripts=====
 Scripts that are specific to exadata monitoring: Scripts that are specific to exadata monitoring:
  
-<sxh>+====Cell Usage==== 
 +<Code:bash>
 select a.name, b.value/1024/1024 MB select a.name, b.value/1024/1024 MB
 from v$sysstat a, v$mystat b from v$sysstat a, v$mystat b
Line 12: Line 13:
 'cell IO uncompressed bytes') 'cell IO uncompressed bytes')
 or a.name like 'cell phy%'); or a.name like 'cell phy%');
-</sxh>+ 
 +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.1606819215.txt.gz
  • Last modified: 2020/12/01 10:40
  • by andonovj