oracle_exadata_monitoring

Differences

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

Link to this comparison view

Both sides previous revision Previous revision
Next revision
Previous revision
oracle_exadata_monitoring [2020/12/02 12:17] 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:
  
 ====Cell Usage==== ====Cell Usage====
-<sxh>+<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 45: Line 45:
  
 17 rows selected. 17 rows selected.
-</sxh>+</Code>
  
 ====Flash Cache Content==== ====Flash Cache Content====
Line 54: Line 54:
   * Database name   * Database name
  
-<sxh>+<Code:bash>
 SQL> @/home/oracle/labs/lab04-03-15.sql SQL> @/home/oracle/labs/lab04-03-15.sql
 SQL> select owner, object_name, tablespace_name, ts#, dataobj#, SQL> select owner, object_name, tablespace_name, ts#, dataobj#,
Line 65: Line 65:
 -----  --------------------    -----         --  ----------  -------------------------  ----------------------- -----  --------------------    -----         --  ----------  -------------------------  -----------------------
 SH        CUSTOMERS             SH            20473   optimized physical reads 477 SH        CUSTOMERS             SH            20473   optimized physical reads 477
-</sxh>+</Code>
  
 once we have that, we can check in the call using the CellCli: once we have that, we can check in the call using the CellCli:
  
-<sxh>+<Code:bash>
 CellCLI> list flashcachecontent where objectnumber=20473 and tablespacenumber=7 and dbuniquename=DBM detail CellCLI> list flashcachecontent where objectnumber=20473 and tablespacenumber=7 and dbuniquename=DBM detail
  cachedKeepSize:    0  cachedKeepSize:    0
Line 83: Line 83:
  tableSpaceNumber:  7  tableSpaceNumber:  7
 CellCLI>  CellCLI> 
-</sxh>+</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: 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:
  
-<sxh>+<Code:bash>
 [celladmin@qr01celadm01 ~]$ dcli -c qr01celadm01,qr01celadm02,qr01celadm03 cellcli -e  list flashcachecontent where objectnumber=20473 and tablespacenumber=7 and dbuniquename=DBM detail [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: cachedKeepSize:    0
Line 122: Line 122:
 qr01celadm03: objectNumber:      20473 qr01celadm03: objectNumber:      20473
 qr01celadm03: tableSpaceNumber:  7 qr01celadm03: tableSpaceNumber:  7
-</sxh>+</Code>
  
 In total is 477, just like the SQL query. 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.1606911469.txt.gz
  • Last modified: 2020/12/02 12:17
  • by andonovj