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:
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>