Differences

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

--- oracle_exadata_monitoring [2020/12/04 14:55] – andonovj
+++ oracle_exadata_monitoring [2020/12/04 16:20] (current) – andonovj
@@ Line 173: / Line 173: @@
 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,
+  time_waited/100 wait_secs, average_wait/100 avg_wait_secs
+  FROM V$SESSION_EVENT e, V$MYSTAT s
+  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,
+  time_waited/100 wait_secs, average_wait/100 avg_wait_secs
+  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
+rows selected.
+Elapsed: 00:00:00.06
+SQL>
+</Code>