Differences

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

--- oracle_exadata_monitoring [2020/12/04 14:52] – andonovj
+++ oracle_exadata_monitoring [2020/12/04 16:20] (current) – andonovj
@@ Line 1: / Line 1: @@
 =====Overview=====
-====Scripts=====
+=====Scripts=====
 Scripts that are specific to exadata monitoring:
@@ Line 125: / Line 125: @@
 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(*)
+----------
+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):
+---------------------------------------------------
+- 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,
+  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>