Differences

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

--- oracle_exadata_monitoring [2020/12/02 12:13] – 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:
-==Cell Usage==
+====Cell Usage====
-<sxh>
+<Code:bash>
 select a.name, b.value/1024/1024 MB
 from v$sysstat a, v$mystat b
@@ Line 45: / Line 45: @@
 rows selected.
-</sxh>
+</Code>
-==Check if table is in Flash Cache==
+====Flash Cache Content====
 To see if an object is in the flash cache, we have to know its:
@@ Line 54: / Line 54: @@
   * Database name
-<sxh>
+<Code:bash>
 SQL> @/home/oracle/labs/lab04-03-15.sql
 SQL> select owner, object_name, tablespace_name, ts#, dataobj#,
@@ Line 65: / Line 65: @@
 -----  --------------------    -----         --  ----------  -------------------------  -----------------------
 SH        CUSTOMERS             SH	     7       20473   optimized physical reads	477
-</sxh>
+</Code>
 once we have that, we can check in the call using the CellCli:
-<sxh>
+<Code:bash>
 CellCLI> list flashcachecontent where objectnumber=20473 and tablespacenumber=7 and dbuniquename=DBM detail
 	 cachedKeepSize:    	 0
@@ Line 83: / Line 83: @@
 	 tableSpaceNumber:  	 7
 CellCLI>
-</sxh>
+</Code>
+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:
+<Code:bash>
+[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
+</Code>
+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>