oracle_exadata_monitoring

Scripts that are specific to exadata monitoring:

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.

To see if an object is in the flash cache, we have to know its:

  • object number
  • Tablespace number
  • Database name
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

once we have that, we can check in the call using the CellCli:

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> 

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:

[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

In total is 477, just like the SQL query.

We can see if a query is using the Smart Scan capabilities of the exadata by observing the execution plan:

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>

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:

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> 

As you can see, the “Cell smart Table scan” takes the majority of the time. (2.59 seconds out of 2.80 seconds)

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> 
  • oracle_exadata_monitoring.txt
  • Last modified: 2020/12/04 16:20
  • by andonovj