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.