Extended Statistics in Oracle 11g

I was not really planning to write about extended stats, it's all started with Full table scan then was trying to replace full table scan with an index and finally ended with extended stats due to the wrong cardinality estimate :)

Before we get into oracle extended statistics let check few cardinality issues when my data is skewed.

Lets start with the below demo, 

SQL> CREATE TABLE tbl_fts_chk
  2    (
  3      id           NUMBER,
  4      id_val       VARCHAR2(10),
  5      location_val VARCHAR2(10)
  6    );

Table created.

Elapsed: 00:00:00.32
SQL> BEGIN
  2    FOR x IN 1..1000
  3    LOOP
  4      INSERT INTO tbl_fts_chk
  5      SELECT *
  6      FROM
  7        ( WITH tbl_temp AS
  8        (SELECT rownum AS id,
  9          'ORACLE'
 10          ||rownum AS id_val,
 11          'location_'
 12          ||chr(rownum+64) AS location_val
 13        FROM dual
 14          CONNECT BY level < 11
 15        ),
 16        tbl_temp1 AS
 17        (SELECT rownum AS id,
 18          'ORACLE'
 19          ||rownum AS id_val,
 20          'location_'
 21          ||chr(rownum+64) AS location_val
 22        FROM dual
 23          CONNECT BY level < 11
 24        ORDER BY id DESC
 25        )
 26      SELECT t1.id,
 27        t1.id_val,
 28        t1.location_val
 29      FROM tbl_temp t1,
 30        tbl_temp1 t2
 31      WHERE t1.id > t2.id
 32      UNION ALL
 33      SELECT t1.id,
 34        t1.id_val,
 35        t1.location_val
 36      FROM tbl_temp1 t1,
 37        tbl_temp1 t2
 38      WHERE t2.id > t1.id
 39        );
 40    END LOOP;
 41    COMMIT;
 42  END;
 43  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.77
SQL> select count(*) from tbl_fts_chk;

  COUNT(*)                                                                                                                                            
----------                                                                                                                                            
     90000                                                                                                                                            

Elapsed: 00:00:00.01
----------------
SQL> EXEC DBMS_STATS.gather_table_stats('HARI', 'TBL_FTS_CHK', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.23

    Note the estimate percent is AUTO SAMPLE SIZE. The below simple query triggers the doubt and lets check whether do we get any merit for investigation,

SQL> set autotrace traceonly
SQL> 
SQL> select * from tbl_fts_chk where id = 5 and id_val = 'ORACLE5';

9000 rows selected.

Elapsed: 00:00:00.12

Execution Plan
----------------------------------------------------------
Plan hash value: 2042300665

---------------------------------------------------------------------------------
| Id  | Operation         | Name        | Rows  | Bytes | Cost (%CPU)| Time    |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |             |   900 | 19800 |   103   (1)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| TBL_FTS_CHK |   900 | 19800 |   103   (1)| 00:00:02 |
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("ID"=5 AND "ID_VAL"='ORACLE5')


Statistics
----------------------------------------------------------
        275  recursive calls
          0  db block gets
       1005  consistent gets
        378  physical reads
          0  redo size
     121681  bytes sent via SQL*Net to client
       7008  bytes received via SQL*Net from client
        601  SQL*Net roundtrips to/from client
          5  sorts (memory)
          0  sorts (disk)
       9000  rows processed

   Why is my cardinality just 900 instead of being 9000. Which means it is calculated as 1 percent but actually it has to be 10 percent of total number of records (90,000).

The math here is

Step1 :

Column - ID         - Has 10 distinct Values

SQL> select count(*),ID from tbl_fts_chk group by ID;

  COUNT(*)         ID
---------- ----------
      9000          1
      9000          6
      9000          2
      9000          4
      9000          5
      9000          8
      9000          3
      9000          7
      9000          9
      9000         10

10 rows selected.

Elapsed: 00:00:00.23

Column - ID_Val  - Has 10 distinct Values

SQL> select count(*),ID_VAL from tbl_fts_chk group by ID_VAL;

  COUNT(*) ID_VAL
---------- ----------
      9000 ORACLE7
      9000 ORACLE8
      9000 ORACLE6
      9000 ORACLE2
      9000 ORACLE4
      9000 ORACLE10
      9000 ORACLE3
      9000 ORACLE5
      9000 ORACLE9
      9000 ORACLE1

10 rows selected.

Elapsed: 00:00:00.06

Step2

Given value for ID = 5 and for ID_Val = ORACLE5, which means 1 out of 10 is being retrieved out of total number of records

Step 3

Number Of records * Column ID selectivity * Column ID_Val Selectivity

90000 * .1 * .1 = 900

Oracle hasn't considered the actual repetition of data. I retrieve only 10 percent of the data. Can index help me here ?, lets check

SQL> create index idx_fts_chk1 on tbl_fts_chk(id,id_val);

Index created.

Elapsed: 00:00:00.25
SQL> EXEC DBMS_STATS.gather_table_stats('HARI', 'TBL_FTS_CHK', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.95

Executing the query again

SQL> variable a1 number;
SQL> exec :a1:=5;

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
SQL> variable a2 varchar2(10);
SQL> 
SQL> exec :a2:='ORACLE5';

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
SQL> 
SQL> SELECT * FROM TBL_FTS_CHK T2 WHERE T2.ID = :a1 AND T2.ID_VAL = :a2;

9000 rows selected.

Elapsed: 00:00:00.23

Execution Plan
----------------------------------------------------------                                                                                            
Plan hash value: 2042300665                                                                                                                           
                                                                                                                                                      
---------------------------------------------------------------------------------                                                                     
| Id  | Operation         | Name        | Rows  | Bytes | Cost (%CPU)| Time     |                                                                     
---------------------------------------------------------------------------------                                                                     
|   0 | SELECT STATEMENT  |             |  9000 |   193K|   103   (1)| 00:00:02 |                                                                     
|*  1 |  TABLE ACCESS FULL| TBL_FTS_CHK |  9000 |   193K|   103   (1)| 00:00:02 |                                                                     
---------------------------------------------------------------------------------                                                                     
                                                                                                                                                      
Predicate Information (identified by operation id):                                                                                                   
---------------------------------------------------                                                                                                   
                                                                                                                                                      
   1 - filter("T2"."ID_VAL"=:A2 AND "T2"."ID"=TO_NUMBER(:A1))                                                                                         


Statistics
----------------------------------------------------------                                                                                            
        367  recursive calls                                                                                                                          
          0  db block gets                                                                                                                            
       1024  consistent gets                                                                                                                          
        380  physical reads                                                                                                                           
          0  redo size                                                                                                                                
     121681  bytes sent via SQL*Net to client                                                                                                         
       7008  bytes received via SQL*Net from client                                                                                                   
        601  SQL*Net roundtrips to/from client                                                                                                        
          6  sorts (memory)                                                                                                                           
          0  sorts (disk)                                                                                                                             
       9000  rows processed

Now After adding Index, Oracle is able to provide the correct cardinality -CARD-9000 i.e 10 percent of the total number of records in the table. Even though i retrive only one tenth of records my index is not used, it is a full table scan.  This is different topic by itself,  lets get into cardinality issue in this blog. Though being a full table scan, posting adding an Index optimizer is able to provide the correct cardinality, Does this mean that the optimizer for full table scan uses the Index stats on avalibility ? If that is the case what happens if i drop the index ?         

SQL> drop index idx_fts_chk1;

Index dropped.

Elapsed: 00:00:00.46
SQL>
SQL> EXEC DBMS_STATS.gather_table_stats('HARI', 'TBL_FTS_CHK', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);

PL/SQL procedure successfully completed.

Elapsed: 00:00:03.15
SQL>
SQL> SELECT * FROM TBL_FTS_CHK T2 WHERE T2.ID = :a1 AND T2.ID_VAL = :a2;

9000 rows selected.

Elapsed: 00:00:00.07

Execution Plan
----------------------------------------------------------                                                                                            
Plan hash value: 2042300665                                                                                                                           
                                                                                                                                                      
---------------------------------------------------------------------------------                                                                     
| Id  | Operation         | Name        | Rows  | Bytes | Cost (%CPU)| Time     |                                                                     
---------------------------------------------------------------------------------                                                                     
|   0 | SELECT STATEMENT  |             |   900 | 19800 |   103   (1)| 00:00:02 |                                                                     
|*  1 |  TABLE ACCESS FULL| TBL_FTS_CHK |   900 | 19800 |   103   (1)| 00:00:02 |                                                                     
---------------------------------------------------------------------------------                                                                     
                                                                                                                                                      
Predicate Information (identified by operation id):                                                                                                   
---------------------------------------------------                                                                                                   
                                                                                                                                                      
   1 - filter("T2"."ID_VAL"=:A2 AND "T2"."ID"=TO_NUMBER(:A1))                                                                                         


Statistics
----------------------------------------------------------                                                                                            
        169  recursive calls                                                                                                                          
          0  db block gets                                                                                                                            
        990  consistent gets                                                                                                                          
          0  physical reads                                                                                                                           
          0  redo size                                                                                                                                
     121681  bytes sent via SQL*Net to client                                                                                                         
       7008  bytes received via SQL*Net from client                                                                                                   
        601  SQL*Net roundtrips to/from client                                                                                                        
          5  sorts (memory)                                                                                                                           
          0  sorts (disk)                                                                                                                             
       9000  rows processed

 

Oh, back to 900 (1 percent of records) in the cardinality, Thus it is very evident that Oracle Full table scan uses Index stats to provide a better cardinality estimate. Now the question here is should i go for Index to help the optimizer to provide the correct cardinality estimate, Can't i just provide more details about these two columns ID and ID_VAL to the optimizer so that it helps in estimating the correct cardinality ?

           How should i inform the optimizer about these two columns? And that's all about Extended Statistics in Oracle 11g. First lets create the additional stats on the required columns using DBMS_STATS 

SQL> 
SQL> SELECT dbms_stats.create_extended_stats(OWNNAME=>'HARI',TABNAME=>'TBL_FTS_CHK',EXTENSION=>'(ID,ID_VAL)') FROM DUAL;

DBMS_STATS.CREATE_EXTENDED_STATS(OWNNAME=>'HARI',TABNAME=>'TBL_FTS_CHK',EXTENSION=>'(ID,ID_VAL)')                                                     
------------------------------------------------------------------------------------------------------------------------------------------------------
SYS_STUSBSF29XATPMX0WFZ7WKDVIV                                                                                                                        

Elapsed: 00:00:00.21

Collecting stats on this table,  

SQL> EXEC DBMS_STATS.gather_table_stats('HARI', 'TBL_FTS_CHK', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.95

          Note the dbms_stats is executed with default method_opt option - FOR ALL COLUMNS SIZE AUTO and auto sampling size. Now executing the query again

SQL> SELECT * FROM TBL_FTS_CHK T2 WHERE T2.ID = :a1 AND T2.ID_VAL = :a2;

9000 rows selected.

Elapsed: 00:00:00.07

Execution Plan
----------------------------------------------------------                                                                                            
Plan hash value: 2042300665                                                                                                                           
                                                                                                                                                      
---------------------------------------------------------------------------------                                                                     
| Id  | Operation         | Name        | Rows  | Bytes | Cost (%CPU)| Time     |                                                                     
---------------------------------------------------------------------------------                                                                     
|   0 | SELECT STATEMENT  |             |   900 | 20700 |   103   (1)| 00:00:02 |                                                                     
|*  1 |  TABLE ACCESS FULL| TBL_FTS_CHK |   900 | 20700 |   103   (1)| 00:00:02 |                                                                     
---------------------------------------------------------------------------------                                                                     
                                                                                                                                                      
Predicate Information (identified by operation id):                                                                                                   
---------------------------------------------------                                                                                                   
                                                                                                                                                      
   1 - filter("T2"."ID_VAL"=:A2 AND "T2"."ID"=TO_NUMBER(:A1))                                                                                         


Statistics
----------------------------------------------------------                                                                                            
          8  recursive calls                                                                                                                          
          0  db block gets                                                                                                                            
        974  consistent gets                                                                                                                          
          0  physical reads                                                                                                                           
          0  redo size                                                                                                                                
     121681  bytes sent via SQL*Net to client                                                                                                         
       7008  bytes received via SQL*Net from client                                                                                                   
        601  SQL*Net roundtrips to/from client                                                                                                        
          0  sorts (memory)                                                                                                                           
          0  sorts (disk)                                                                                                                             
       9000  rows processed                                                                                                                           

    oops still not the correct cardinality, lets try providing the literal instead of binds,

SQL> SELECT * FROM TBL_FTS_CHK T2 WHERE T2.ID = 5 AND T2.ID_VAL = 'ORACLE5';

9000 rows selected.

Elapsed: 00:00:00.07

Execution Plan
----------------------------------------------------------                                                                                            
Plan hash value: 2042300665                                                                                                                           
                                                                                                                                                      
---------------------------------------------------------------------------------                                                                     
| Id  | Operation         | Name        | Rows  | Bytes | Cost (%CPU)| Time     |                                                                     
---------------------------------------------------------------------------------                                                                     
|   0 | SELECT STATEMENT  |             |   993 | 22839 |   103   (1)| 00:00:02 |                                                                     
|*  1 |  TABLE ACCESS FULL| TBL_FTS_CHK |   993 | 22839 |   103   (1)| 00:00:02 |                                                                     
---------------------------------------------------------------------------------                                                                     
                                                                                                                                                      
Predicate Information (identified by operation id):                                                                                                   
---------------------------------------------------                                                                                                   
                                                                                                                                                      
   1 - filter("T2"."ID"=5 AND "T2"."ID_VAL"='ORACLE5')                                                                                                


Statistics
----------------------------------------------------------                                                                                            
          8  recursive calls                                                                                                                          
          0  db block gets                                                                                                                            
        974  consistent gets                                                                                                                          
          0  physical reads                                                                                                                           
          0  redo size                                                                                                                                
     121681  bytes sent via SQL*Net to client                                                                                                         
       7008  bytes received via SQL*Net from client                                                                                                   
        601  SQL*Net roundtrips to/from client                                                                                                        
          0  sorts (memory)                                                                                                                           
          0  sorts (disk)                                                                                                                             
       9000  rows processed                                                                              

Not yet, how about collecting stats with appropriate columns stats - method_opt => for all columns size 254

SQL> EXEC DBMS_STATS.gather_table_stats('HARI', 'TBL_FTS_CHK', estimate_percent => NULL,method_opt => 'for all columns size 254');

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.27
SQL> 
SQL> 
SQL> 
SQL> 
SQL> 
SQL> exec :a1:=5;

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
SQL> exec :a2:='ORACLE5';

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
SQL> 
SQL> SELECT * FROM TBL_FTS_CHK T2 WHERE T2.ID = :a1 AND T2.ID_VAL = :a2;

9000 rows selected.

Elapsed: 00:00:00.07

Execution Plan
----------------------------------------------------------                                                                                            
Plan hash value: 2042300665                                                                                                                           
                                                                                                                                                      
---------------------------------------------------------------------------------                                                                     
| Id  | Operation         | Name        | Rows  | Bytes | Cost (%CPU)| Time     |                                                                     
---------------------------------------------------------------------------------                                                                     
|   0 | SELECT STATEMENT  |             |  9000 |   202K|   103   (1)| 00:00:02 |                                                                     
|*  1 |  TABLE ACCESS FULL| TBL_FTS_CHK |  9000 |   202K|   103   (1)| 00:00:02 |                                                                     
---------------------------------------------------------------------------------                                                                     
                                                                                                                                                      
Predicate Information (identified by operation id):                                                                                                   
---------------------------------------------------                                                                                                   
                                                                                                                                                      
   1 - filter("T2"."ID_VAL"=:A2 AND "T2"."ID"=TO_NUMBER(:A1))                                                                                         


Statistics
----------------------------------------------------------                                                                                            
          0  recursive calls                                                                                                                          
          0  db block gets                                                                                                                            
        972  consistent gets                                                                                                                          
          0  physical reads                                                                                                                           
          0  redo size                                                                                                                                
     121681  bytes sent via SQL*Net to client                                                                                                         
       7008  bytes received via SQL*Net from client                                                                                                   
        601  SQL*Net roundtrips to/from client                                                                                                        
          0  sorts (memory)                                                                                                                           
          0  sorts (disk)                                                                                                                             
       9000  rows processed                                                                                                                           

SQL> SELECT * FROM TBL_FTS_CHK T2 WHERE T2.ID = 5 AND T2.ID_VAL = 'ORACLE5';

9000 rows selected.

Elapsed: 00:00:00.07

Execution Plan
----------------------------------------------------------                                                                                            
Plan hash value: 2042300665                                                                                                                           
                                                                                                                                                      
---------------------------------------------------------------------------------                                                                     
| Id  | Operation         | Name        | Rows  | Bytes | Cost (%CPU)| Time     |                                                                     
---------------------------------------------------------------------------------                                                                     
|   0 | SELECT STATEMENT  |             |  9000 |   202K|   103   (1)| 00:00:02 |                                                                     
|*  1 |  TABLE ACCESS FULL| TBL_FTS_CHK |  9000 |   202K|   103   (1)| 00:00:02 |                                                                     
---------------------------------------------------------------------------------                                                                     
                                                                                                                                                      
Predicate Information (identified by operation id):                                                                                                   
---------------------------------------------------                                                                                                   
                                                                                                                                                      
   1 - filter("T2"."ID"=5 AND "T2"."ID_VAL"='ORACLE5')                                                                                                


Statistics
----------------------------------------------------------                                                                                            
          0  recursive calls                                                                                                                          
          0  db block gets                                                                                                                            
        972  consistent gets                                                                                                                          
          0  physical reads                                                                                                                           
          0  redo size                                                                                                                                
     121681  bytes sent via SQL*Net to client                                                                                                         
       7008  bytes received via SQL*Net from client                                                                                                   
        601  SQL*Net roundtrips to/from client                                                                                                        
          0  sorts (memory)                                                                                                                           
          0  sorts (disk)                                                                                                                             
       9000  rows processed                                             

Finally the optimizer is now able to provide the correct cardinality and has extended stats for the correlation between these two columns ID and ID_VAL.And yes Oracle uses Index (stats) for Full table scan to provide better cardinality estimates.

Result Cache in Oracle 11g - Part 2

                From 11g, Oracle has introduced a new component in SGA i.,e RESULT CACHE(RC). RC would store the result set of the queries  and not the data blocks.  Lets take a look at the oracle architecture, 

 

        This new component Result Cache is used to store the end result of a query or function.  By storing the end result of a query whose data is static, I/O(Physical Reads) are reduced to an large extent.  

Result Cache - Components

       There are two components within Result Cache, they cache the output of query and PLSQL Function calls. Does this new component has an impact over buffer cache or is this impacted when ever shared pool is flushed. Lets discuss this in detail with a small example, 

Step1 - Query using Result Cache

SQL> REM /* Query Using Result Cache */;
SQL> select /*+ RESULT_CACHE */ count(id) from tbl_rc where id >2.75;

 COUNT(ID)                                                                                        
----------                                                                                        
      6930                                                                                        

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------                                        
Plan hash value: 2864772016                                                                       
                                                                                                  
--------------------------------------------------------------------------------------------------
| Id  | Operation           | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                            |     1 |     3 |    11   (0)| 00:00:01 |
|   1 |  RESULT CACHE       | 04f8pmukbsk8tfstmbxb5pu9dx |       |       |            |          |
|   2 |   SORT AGGREGATE    |                            |     1 |     3 |            |          |
|*  3 |    TABLE ACCESS FULL| TBL_RC                     |  6432 | 19296 |    11   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
                                                                                                  
Predicate Information (identified by operation id):                                               
---------------------------------------------------                                               
   3 - filter("ID">2.75)                                                                          
                                                                                                  
Result Cache Information (identified by operation id):                                            
------------------------------------------------------                                            
   1 - column-count=1; dependencies=(HARI.TBL_RC); attributes=(single-row); 
   name="select /*+ RESULT_CACHE */ count(id) from tbl_rc where id >2.75"    

Statistics
----------------------------------------------------------                                        
          0  recursive calls                                                                      
          0  db block gets                                                                        
          0  consistent gets                                                                      
          0  physical reads                                                                       
          0  redo size                                                                            
        424  bytes sent via SQL*Net to client                                                     
        415  bytes received via SQL*Net from client                                               
          2  SQL*Net roundtrips to/from client                                                    
          0  sorts (memory)                                                                       
          0  sorts (disk)                                                                         
          1  rows processed                                                                       

Step2 -  Flushing Shared Pool

 alter system flush shared_pool;

System altered.

Elapsed: 00:00:00.28

Lets re-execute the same SQL,

Step3 -  SQL Re-Execution

 REM --/* Re-Executing the same SQL */ 
SQL> select /*+ RESULT_CACHE */ count(id) from tbl_rc where id >2.75;

 COUNT(ID)                                                                                        
----------                                                                                        
      6930                                                                                        

Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------                                        
Plan hash value: 2864772016                                                                       
--------------------------------------------------------------------------------------------------
| Id  | Operation           | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                            |     1 |     3 |    11   (0)| 00:00:01 |
|   1 |  RESULT CACHE       | 04f8pmukbsk8tfstmbxb5pu9dx |       |       |            |          |
|   2 |   SORT AGGREGATE    |                            |     1 |     3 |            |          |
|*  3 |    TABLE ACCESS FULL| TBL_RC                     |  6432 | 19296 |    11   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
                                                                                                  
Predicate Information (identified by operation id):                                               
---------------------------------------------------                                               
   3 - filter("ID">2.75)                                                                          
                                                                                                  
Result Cache Information (identified by operation id):                                            
------------------------------------------------------                                            
   1 - column-count=1; dependencies=(HARI.TBL_RC); attributes=(single-row); 
   name="select /*+ RESULT_CACHE */ count(id) from tbl_rc where id >2.75"    

Statistics
----------------------------------------------------------                                        
        235  recursive calls                                                                      
          0  db block gets                                                                        
         28  consistent gets                                                                      
          0  physical reads                                                                       
          0  redo size                                                                            
        424  bytes sent via SQL*Net to client                                                     
        415  bytes received via SQL*Net from client                                               
          2  SQL*Net roundtrips to/from client                                                    
          5  sorts (memory)                                                                       
          0  sorts (disk)                                                                         
          1  rows processed                                                                       

Thus even after flushing the shared pool, the physical reads is still ZERO, indicating the retrieval of result from the result cache and not through the physical reads

Step4 - Flushing Buffer Cache

 alter system flush buffer_cache;

System altered.

Elapsed: 00:00:00.13

Step5 - SQL Re-Execution

SQL> select /*+ RESULT_CACHE */ count(id) from tbl_rc where id >2.75;

 COUNT(ID)                                                                                        
----------                                                                                        
      6930                                                                                        

Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------                                        
Plan hash value: 2864772016                                                                       
--------------------------------------------------------------------------------------------------
| Id  | Operation           | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                            |     1 |     3 |    11   (0)| 00:00:01 |
|   1 |  RESULT CACHE       | 04f8pmukbsk8tfstmbxb5pu9dx |       |       |            |          |
|   2 |   SORT AGGREGATE    |                            |     1 |     3 |            |          |
|*  3 |    TABLE ACCESS FULL| TBL_RC                     |  6432 | 19296 |    11   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
                                                                                                  
Predicate Information (identified by operation id):                                               
---------------------------------------------------                                               
   3 - filter("ID">2.75)                                                                          
                                                                                                  
Result Cache Information (identified by operation id):                                            
------------------------------------------------------                                            
   1 - column-count=1; dependencies=(HARI.TBL_RC); attributes=(single-row); 
   name="select /*+ RESULT_CACHE */ count(id) from tbl_rc where id >2.75"    

Statistics
----------------------------------------------------------                                        
          0  recursive calls                                                                      
          0  db block gets                                                                        
          0  consistent gets                                                                      
          0  physical reads                                                                       
          0  redo size                                                                            
        424  bytes sent via SQL*Net to client                                                     
        415  bytes received via SQL*Net from client                                               
          2  SQL*Net roundtrips to/from client                                                    
          0  sorts (memory)                                                                       
          0  sorts (disk)                                                                         
          1  rows processed                                                                       

Thus even after flushing the shared pool/buffer cache, the query has used the same CACHE ID and has managed to execute in just one millisecond. Unlike the buffer cache, the server result cache holds result sets and not data blocks.

Step4 - Restarting DB

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area  778387456 bytes
Fixed Size                  1374808 bytes
Variable Size             268436904 bytes
Database Buffers          503316480 bytes
Redo Buffers                5259264 bytes
Database mounted.
Database opened.

The result set are stored in the cache, So a database restart should flush the results, lets check that too

Step5 - SQL Re-Execution

SQL> select /*+ RESULT_CACHE */ count(id) from tbl_rc where id >2.75;

 COUNT(ID)
----------
      6930

Elapsed: 00:00:00.18

Execution Plan
----------------------------------------------------------
Plan hash value: 2864772016
--------------------------------------------------------------------------------------------------
| Id  | Operation           | Name                       | Rows  | Bytes | Cost(%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                            |     1 |     3 |    11   (0)| 00:00:01 |
|   1 |  RESULT CACHE       | 04f8pmukbsk8tfstmbxb5pu9dx |       |       |            |          |
|   2 |   SORT AGGREGATE    |                            |     1 |     3 |            |          |
|*  3 |    TABLE ACCESS FULL| TBL_RC                     |  6432 | 19296 |    11   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("ID">2.75)

Result Cache Information (identified by operation id):
------------------------------------------------------
   1 - column-count=1; dependencies=(HARI.TBL_RC); attributes=(single-row); name
="select /*+ RESULT_CACHE */ count(id) from tbl_rc where id >2.75"

Statistics
----------------------------------------------------------
        235  recursive calls
          0  db block gets
         66  consistent gets
         39  physical reads
          0  redo size
        424  bytes sent via SQL*Net to client
        415  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          4  sorts (memory)
          0  sorts (disk)
          1  rows processed

Yes the elapsed time difference and the physical reads indicate that the optimizer has gone for full SQL life cycle. The cache id paired with the SQL Text remain the same. Nether shared_pool flush nor buffer cache flush, flushes my result cache. To flush the result cache DBMS_RESULT_CACHE.FLUSH has to be used.

Result Cache in Oracle 11g - Part1

Oracle 11g has now extended its cache option to queries result set.

I know the result, the query to retrieve is also very the same and no data has been changed, in this case if i re-execute the query should Oracle SQL engine proceed with the entire SQL life cycle ? can't it just retrieve the result for me ?  

            And that's what the optimizer does with Query Result Cache, Lets see Query Result Cache in detail, 

SQL> REM--JUST A NORMAL QUERY ;
SQL> select count(id_vh) from tbl_rc where id > 0;

COUNT(ID_VH)                                                                  
------------                                                                  
      100001                                                                  
Elapsed: 00:00:00.21
Execution Plan
----------------------------------------------------------                    
Plan hash value: 2864772016                                                   
----------------------------------------------------------------------------- 
| Id  | Operation          | Name   | Rows  | Bytes | Cost (%CPU)| Time     | 
----------------------------------------------------------------------------- 
|   0 | SELECT STATEMENT   |        |     1 |    13 |    73   (2)| 00:00:01 | 
|   1 |  SORT AGGREGATE    |        |     1 |    13 |            |          | 
|*  2 |   TABLE ACCESS FULL| TBL_RC | 99999 |  1269K|    73   (2)| 00:00:01 | 
----------------------------------------------------------------------------- 
Predicate Information (identified by operation id):                           
---------------------------------------------------                           
   2 - filter("ID">0)                                                         
Statistics
----------------------------------------------------------                    
        254  recursive calls
          0  db block gets
        314  consistent gets
        --289  physical reads
          0  redo size
        428  bytes sent via SQL*Net to client
        419  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          5  sorts (memory)
          0  sorts (disk)
          1  rows processed

        Now i know the result and the data is not gonna change. Under these circumstances one can leverage the result cache to minimize the load on SGA. Prior to usage of result cache, validate if it is enabled like below,

SQL> select dbms_result_cache.status() from dual;

DBMS_RESULT_CACHE.STATUS()                       
-------------------------------------------------
ENABLED                                          

As part of Query Result Cache, the optimizer executes the query and looks in the cache memory to determine whether the result exists in the cache. If the result exists, then the database retrieves the result from memory instead of executing the query. If the result is not cached, then the database executes the query, returns the result as output, and stores the result in the result cache. This is primarily based on the parameter RESULT_CACHE_MODE which instructs oracle to perform the same. They operate under two modes - MANUAL and FORCE. In MANUAL mode the result set are cached through a hint /*+RESULT_CACHE*/

Result Cache in MANUAL Mode -

SQL> 
SQL> alter session set result_cache_mode=MANUAL;

Session altered.
Elapsed: 00:00:00.00

Manual Mode - First Run

SQL> select /*+ RESULT_CACHE */ count(ID_VH) from tbl_rc where id > 0;

COUNT(ID_VH)                                                                                      
------------                                                                                      
      100001                                                                                      

Elapsed: 00:00:00.10
Execution Plan
----------------------------------------------------------                                        
Plan hash value: 2864772016                                                                       
--------------------------------------------------------------------------------------------------
| Id  | Operation           | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                            |     1 |    13 |    73   (2)| 00:00:01 |
|   1 |  RESULT CACHE       | b7f8nxtz1mvuj8q1ph0jj6quxx |       |       |            |          |
|   2 |   SORT AGGREGATE    |                            |     1 |    13 |            |          |
|*  3 |    TABLE ACCESS FULL| TBL_RC                     | 99999 |  1269K|    73   (2)| 00:00:01 |
--------------------------------------------------------------------------------------------------
                                                                                                  
Predicate Information (identified by operation id):                                               
---------------------------------------------------                                               
   3 - filter("ID">0)                                                                             
                                                                                                  
Result Cache Information (identified by operation id):                                            
------------------------------------------------------                                            
   1 - column-count=1; dependencies=(HARI.TBL_RC); attributes=(single-row); 
   name="select /*+ RESULT_CACHE */ count(ID_VH) from tbl_rc where id > 0"   

Statistics
----------------------------------------------------------                                        
          1  recursive calls                                                                      
          0  db block gets                                                                        
        284  consistent gets                                                                      
        --278  physical reads                                                                       
          0  redo size                                                                            
        428  bytes sent via SQL*Net to client                                                     
        419  bytes received via SQL*Net from client                                               
          2  SQL*Net roundtrips to/from client                                                    
          0  sorts (memory)                                                                       
          0  sorts (disk)                                                                         
          1  rows processed

                        Above is the first run with result cache hint, oracle does an hard parse and generates the cache id "b7f8nxtz1mvuj8q1ph0jj6quxx" for the given SQL. This would in-turn point to the result set stored in the result cache. On re-executing the query optimizer would pick the data from the cache without actually executing the query

Manual Mode - Second Run

SQL> select /*+ RESULT_CACHE */ count(ID_VH) from tbl_rc where id > 0;

COUNT(ID_VH)                                                                                      
------------                                                                                      
      100001                                                                                      

"Elapsed: 00:00:00.00"
Execution Plan
----------------------------------------------------------                                        
Plan hash value: 2864772016                                                                       
--------------------------------------------------------------------------------------------------
| Id  | Operation           | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                            |     1 |    13 |    73   (2)| 00:00:01 |
|   1 |  RESULT CACHE       | b7f8nxtz1mvuj8q1ph0jj6quxx |       |       |            |          |
|   2 |   SORT AGGREGATE    |                            |     1 |    13 |            |          |
|*  3 |    TABLE ACCESS FULL| TBL_RC                     | 99999 |  1269K|    73   (2)| 00:00:01 |
--------------------------------------------------------------------------------------------------
                                                                                                  
Predicate Information (identified by operation id):                                               
---------------------------------------------------                                               
   3 - filter("ID">0)                                                                             
                                                                                                  
Result Cache Information (identified by operation id):                                            
------------------------------------------------------                                            
   1 - column-count=1; dependencies=(HARI.TBL_RC); attributes=(single-row); 
   name="select /*+ RESULT_CACHE */ count(ID_VH) from tbl_rc where id > 0"   

Statistics
----------------------------------------------------------                                        
          0  recursive calls                                                                      
          0  db block gets                                                                        
          0  consistent gets                                                                      
          "0  physical reads"                                                                     
          0  redo size                                                                            
        428  bytes sent via SQL*Net to client                                                     
        419  bytes received via SQL*Net from client                                               
          2  SQL*Net roundtrips to/from client                                                    
          0  sorts (memory)                                                                       
          0  sorts (disk)                                                                         
          1  rows processed

              

              Nill Physical reads, Elapsed time as zero and the usage of Result Cache Id(b7f8nxtz1mvuj8q1ph0jj6quxx) indicates that the optimizer has taken a new route without going through the traditional SQL lifecycle.

Result Cache in FORCE Mode -

        In Force mode by default oracle would perform caching the result, even when RESULT_CACHE hint is not specified in the query. 

SQL> 
SQL> alter session set result_cache_mode=FORCE;

Session altered.
Elapsed: 00:00:00.00

Force Mode - First Run

SQL> REM /* FIRST RUN - Query WITHOUT Result Cache Hint */
SQL> select count(ID) from tbl_rc where id > 3;

 COUNT(ID)                                                                                        
----------                                                                                        
     99996                                                                                        

Elapsed: 00:00:00.22

Execution Plan
----------------------------------------------------------                                        
Plan hash value: 2864772016                                                                       
                                                                                                  
--------------------------------------------------------------------------------------------------
| Id  | Operation           | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                            |     1 |     5 |    73   (2)| 00:00:01 |
|   1 |  RESULT CACHE       | 3cc3zwwpyb7nhbkbt78kc9vpu9 |       |       |            |          |
|   2 |   SORT AGGREGATE    |                            |     1 |     5 |            |          |
|*  3 |    TABLE ACCESS FULL| TBL_RC                     | 99997 |   488K|    73   (2)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):                                               
---------------------------------------------------                                               
   3 - filter("ID">3)                                                                             
Result Cache Information (identified by operation id):                                            
------------------------------------------------------                                            
   1 - column-count=1; dependencies=(HARI.TBL_RC); attributes=(single-row); 
   name="select count(ID) from tbl_rc where id > 3"                          

Statistics
----------------------------------------------------------                                        
       1540  recursive calls                                                                      
          0  db block gets                                                                        
        509  consistent gets                                                                      
        288  physical reads                                                                       
          0  redo size                                                                            
        425  bytes sent via SQL*Net to client                                                     
        419  bytes received via SQL*Net from client                                               
          2  SQL*Net roundtrips to/from client                                                    
         10  sorts (memory)
          0  sorts (disk)
          1  rows processed

Force Mode - Second Run

SQL> REM /* SECOND RUN - Re-Execution - Now Optimizer would pick result from result cache */
SQL> 
SQL> select count(ID) from tbl_rc where id > 3;

 COUNT(ID)                                                                                        
----------                                                                                        
     99996                                                                                        

Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------                                        
Plan hash value: 2864772016                                                                       
                                                                                                  
--------------------------------------------------------------------------------------------------
| Id  | Operation           | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                            |     1 |     5 |    73   (2)| 00:00:01 |
|   1 |  RESULT CACHE       | 3cc3zwwpyb7nhbkbt78kc9vpu9 |       |       |            |          |
|   2 |   SORT AGGREGATE    |                            |     1 |     5 |            |          |
|*  3 |    TABLE ACCESS FULL| TBL_RC                     | 99997 |   488K|    73   (2)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):                                               
---------------------------------------------------                                               
   3 - filter("ID">3)                                                                             
Result Cache Information (identified by operation id):                                            
------------------------------------------------------                                            
   1 - column-count=1; dependencies=(HARI.TBL_RC); attributes=(single-row); 
   name="select count(ID) from tbl_rc where id > 3"                          

Statistics
----------------------------------------------------------                                        
          0  recursive calls                                                                      
          0  db block gets                                                                        
          0  consistent gets                                                                      
          0  physical reads                                                                       
          0  redo size                                                                            
        425  bytes sent via SQL*Net to client                                                     
        419  bytes received via SQL*Net from client                                               
          2  SQL*Net roundtrips to/from client                                                    
          0  sorts (memory)                                                                       
          0  sorts (disk)                                                                         
          1  rows processed

From the time consumed and statistics it is clear that the cache is used to retrieve the data. The physical reads has completely vanished from 288 (During First Run) to zero. Thus the performance is same as that of MANUAL mode.

Full Table Scan in Oracle 11g - Part 1

Common things which might strike your mind the moment you hear Full Table Scan (FTS) are

• Why my index is not used
• How to avoid the full table scan
• Why Oracle optimizer uses full table scan
• selecting more data hence full table scan's are good.
• Full table scan means "db file scattered read" (db file scattered read - Means that oracle reads the blocks using multiblock read count indicator, which looks in a scattered fashion)

          But today we will see a new approach taken by Full table scan's in Oracle 11g.

Lets start with very simple tables like below,

Big Table
create table tbl_fts_pr (id number,id_ch char(100),id_vh varchar2(100),id_char1 char(1000));
insert into tbl_fts_pr
Select Rownum, 'fts','fts','fts' From Dual Connect By Level < 100000;
insert into tbl_fts_pr
Select Rownum, 'fts','fts','fts' From Dual Connect By Level < 100000;
commit;

Small Table
create table tbl_fts_lr (id number,id_ch char(100),id_vh varchar2(100));
Insert Into Tbl_Fts_lr
Select Rownum, 'fts','fts' From Dual Connect By Level < 100000;
Insert Into Tbl_Fts_lr
Select Rownum, 'fts','fts' From Dual Connect By Level < 100000;
Insert Into Tbl_Fts_lr
Select Rownum, 'fts','fts' From Dual Connect By Level < 100000;
commit;

exec dbms_stats.gather_table_stats('HARI','TBL_FTS_PR');
exec dbms_stats.gather_table_stats('HARI','TBL_FTS_LR');

Now a simple query leading to full table scan like below on the above 2 tables,

Query

    

          This is just the execution plan from autotrace. Things look quite normal here at this stage, lets get into the second part of the autotrace- Statistics for both the queries

Autotrace- Statistics

Note the physical reads between them. Now you can see a clear differentiation in the FTS - strategy for data retrivial. Lets see further to check the actual volume of data in units of blocks to be processed to perform this FTS,                                                                                                                                  

Block Details

 *_PR table's average row length is ten times that of *_LR table and blocks are also seven times bigger.
                                   





                       
                                  Lets dig more to see the actual methodology imposed to perform block reads and the time consumed in retrieving them.

Tkprof Details- TBL_FTS_LR





                                                                                                                                                                                
_*LR behaviour is expected and the blocks are read in "db file scattered read" fashion which is what we have seen so far for FTS.  Lets see the other query's tkprof output                                                                                                                                                                                                     
                      

                                                                                                                                                               







Tkprof Details- TBL_FTS_PR


_*PR has more physical reads and elapsed time is relatively less in comparision with _*LR's stats.

Now take a look at the wait events. Where is my "db file scattered read", a full table scan without a db file scattered read is not seen so far. Oracle has chosen to go with "direct path read" and yes has proven to be faster in this case where we have more (Will see what is more in the coming post) number of blocks to be processed.  

Conclusion

            On account of FTS, Oracle 11g does its calculation on the number of blocks impacted in satisfying a query and decides the access method which can be traditional one sequential read plus n number of scattered read OR direct path read. Now with the above inference can we conclude like this 

• FTS on big tables are faster
• FTS on big tables with LESS MEMEORY are FASTER
• FTS can bypass logical reads

 Or can we also conclude like below

•  FTS on big tables with MORE MEMORY are SLOWER

Is this a bug or a feature, can this make my queries run faster in Test Env with less memory  and run slower in Production with more Memory, Yes metalink says that too "Full Table Scan May be Slower In Active Production Environment than Test [ID 1457693.1]"