Cardinality Feedback in Oracle 11.2.0.3

In the previous blog we have seen Cardinality Feedback in Oracle 11.2.0.1, where the reason for hard parse during second run was not tangible. Oracle overcomes this in 11.2.0.3 with an additional column named USE_FEEDBACK_STATS in v$sql_shared_cursor.  

Have repeated the same test case in Oracle 11.2.0.3, lets review it

SQL> set linesize 100
SQL> set timing on
SQL> 
SQL> create table tblt1 (id number,id1 number,id_val varchar2(10),id_val1 varchar2(10));

Table created.

Elapsed: 00:00:00.02
SQL> 
SQL> insert into tblt1
  2  select round(dbms_random.value(4,10000)) id,
  3  rownum id1,
  4  round(dbms_random.value(-100,40)) id_val,
  5  round(dbms_random.value(4,10000)) id_val1
  6  from dual connect by level < 10000;

9999 rows created.

Elapsed: 00:00:00.54
SQL> commit;

Commit complete.

Elapsed: 00:00:00.01
SQL> begin
  2  dbms_stats.gather_table_stats('HARI','TBLT1');
  3  end;
  4  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.39

And the select returned

SQL> select * from tblt1 where id_val1 > 18 and id < 99999;

        ID        ID1 ID_VAL     ID_VAL1                                                            
---------- ---------- ---------- ----------                                                         
      9917        694 28         4162                                                               
      7489        695 -40        6788                                                               
      3705        696 -80        2162                      
..
..
.....

9983 rows selected.

SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALL'));

PLAN_TABLE_OUTPUT                                                                                   
----------------------------------------------------------------------------------------------------
SQL_ID  80tf1dj5fhu58, child number 0                                                               
-------------------------------------                                                               
select * from tblt1 where id_val1 > 18 and id < 99999                                               
                                                                                                    
Plan hash value: 1166073720                                                                         
                                                                                                    
---------------------------------------------------------------------------                         
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |                         
---------------------------------------------------------------------------                         
|   0 | SELECT STATEMENT  |       |       |       |    11 (100)|          |                         
|*  1 |  TABLE ACCESS FULL| TBLT1 |   500 |  8500 |    11   (0)| 00:00:01 |                         
---------------------------------------------------------------------------                         
                                                                                                    
Query Block Name / Object Alias (identified by operation id):                                       
-------------------------------------------------------------                                       
                                                                                                    
   1 - SEL$1 / TBLT1@SEL$1                                                                          
                                                                                                    
Predicate Information (identified by operation id):                                                 
---------------------------------------------------                                                 
1 - filter((TO_NUMBER("ID_VAL1")>18 AND "ID"<99999 data-blogger-escaped------------------------------------------------------------="" data-blogger-escaped--="" data-blogger-escaped-00:00:00.09="" data-blogger-escaped-1="" data-blogger-escaped-29="" data-blogger-escaped-by="" data-blogger-escaped-column="" data-blogger-escaped-elapsed:="" data-blogger-escaped-identified="" data-blogger-escaped-information="" data-blogger-escaped-operation="" data-blogger-escaped-pre="" data-blogger-escaped-projection="" data-blogger-escaped-rows="" data-blogger-escaped-selected.="" id="">

Got the same plan as we got in 11.2.0.1, now lets check v$sql_shared_cursor

SQL> column sql_id format a20
SQL> column child_number format 99
SQL> column use_feedback_stats format a20
SQL> 
SQL> select sql_id,child_number,use_feedback_stats from v$sql_shared_cursor where sql_id = '80tf1dj5fhu58';

SQL_ID               CHILD_NUMBER USE_FEEDBACK_STATS                                                
-------------------- ------------ --------------------                                              
80tf1dj5fhu58                   0 Y                                                                 

Elapsed: 00:00:00.02

USE_FEEDBACK_STATS indicates that feedback can be used during the next rerun so as to obtain better cardinality estimates. On rerunning the query,

SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALL'));

PLAN_TABLE_OUTPUT                                                                                   
----------------------------------------------------------------------------------------------------
SQL_ID  80tf1dj5fhu58, child number 1                                                               
-------------------------------------                                                               
select * from tblt1 where id_val1 > 18 and id < 99999                                               
                                                                                                    
Plan hash value: 1166073720                                                                         
                                                                                                    
---------------------------------------------------------------------------                         
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |                         
---------------------------------------------------------------------------                         
|   0 | SELECT STATEMENT  |       |       |       |    11 (100)|          |                         
|*  1 |  TABLE ACCESS FULL| TBLT1 |  9983 |   165K|    11   (0)| 00:00:01 |                         
---------------------------------------------------------------------------                         
                                                                                                    
Query Block Name / Object Alias (identified by operation id):                                       
-------------------------------------------------------------                                       
                                                                                                    
   1 - SEL$1 / TBLT1@SEL$1                                                                          
                                                                                                    
Predicate Information (identified by operation id):                                                 
---------------------------------------------------                                                 
   1 - filter((TO_NUMBER("ID_VAL1")>18 AND "ID"<99999))                                             
                                                                                                    
Column Projection Information (identified by operation id):                                         
-----------------------------------------------------------                                         
                                                                                                    
   1 - "ID"[NUMBER,22], "TBLT1"."ID1"[NUMBER,22],                                                   
       "TBLT1"."ID_VAL"[VARCHAR2,10], "ID_VAL1"[VARCHAR2,10]                                        
                                                                                                    
Note                                                                                                
-----                                                                                               
   - cardinality feedback used for this statement                                                   
                                                                                                    

33 rows selected.

Elapsed: 00:00:00.08

Yes Cardinality Feedback is used and the correct cardinality 9983 is obtained. And the reason for hard parse is given below

SQL> select sql_id,child_number,use_feedback_stats from v$sql_shared_cursor where sql_id = '80tf1dj5fhu58';

SQL_ID               CHILD_NUMBER USE_FEEDBACK_STATS                                                
-------------------- ------------ --------------------                                              
80tf1dj5fhu58                   0 Y                                                                 
80tf1dj5fhu58                   1 N                                                                 

Elapsed: 00:00:00.00

Isn't USE_FEEDBACK_STATS much useful to affirm the usage of Cardinality Feedback in Oracle 11.2.0.3

Actual Cardinality

We have seen Extended Stats, little about dynamic sampling and all these are targeting towards obtaining Actual Cardinality. Now in this row we have "Cardinality Feedback", lets review it in Oracle version 11.2.0.1

SQL> create table tbl_t1 (id number,id_1 number,id_val1 varchar2(10),id_val2 varchar2(10));

Table created.

Elapsed: 00:00:00.15
SQL> 
SQL> insert into tbl_t1
  2  select round(dbms_random.value(4,50000)) id
  3  ,rownum Id_1
  4  ,round(dbms_random.value(-100000,50)) id_val1
  5  ,round(dbms_random.value(4,50000)) id_val2 from dual connect by rownum < 10000;

9999 rows created.
SQL> commit;

Commit complete.

Elapsed: 00:00:00.00
SQL> exec dbms_stats.gather_table_stats('HARI','TBL_T1');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.21

Let's try it with a simple query

SQL> select * from tbl_t1 where id_val1 > 18 and id < 99999;

        ID       ID_1 ID_VAL1    ID_VAL2                                                            
---------- ---------- ---------- ----------                                                         
     43725       7536 42         9958                                                               
     14761       6433 44         17751                                                              
      5992       8624 50         18125                                                              
      1512       9577 28         40933                                                              

Elapsed: 00:00:00.15

For selecting just 4 records, lets check what was the estimated cardinality for the above query

SQL> 
SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR(null,null,'ALL'));

PLAN_TABLE_OUTPUT                                                                                   
----------------------------------------------------------------------------------------------------
SQL_ID  fk3bgjgnj0w3w, child number 0                                                               
-------------------------------------                                                               
select * from tbl_t1 where id_val1 > 18 and id < 99999                                              
                                                                                                    
Plan hash value: 3205898331                                                                         
                                                                                                    
----------------------------------------------------------------------------                        
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |                        
----------------------------------------------------------------------------                        
|   0 | SELECT STATEMENT  |        |       |       |    13 (100)|          |                        
|*  1 |  TABLE ACCESS FULL| TBL_T1 |   500 | 11000 |    13   (0)| 00:00:01 |                        

PLAN_TABLE_OUTPUT                                                                                   
----------------------------------------------------------------------------                        
                                                                                                    
Query Block Name / Object Alias (identified by operation id):                                       
-------------------------------------------------------------                                       
                                                                                                    
   1 - SEL$1 / TBL_T1@SEL$1                                                                         
                                                                                                    
Predicate Information (identified by operation id):                                                 
---------------------------------------------------                                                 
                                                                                                    
   1 - filter((TO_NUMBER("ID_VAL1")>18 AND "ID"<99999 
PLAN_TABLE_OUTPUT                                                                                   
----------------------------------------------------------------------------------------------------
                                                                                                    
Column Projection Information (identified by operation id):                                         
-----------------------------------------------------------                                         
                                                                                                    
   1 - "ID"[NUMBER,22], "TBL_T1"."ID_1"[NUMBER,22],                                                 
       "ID_VAL1"[VARCHAR2,10], "TBL_T1"."ID_VAL2"[VARCHAR2,10]                                      
                                                                                                    

29 rows selected.

Elapsed: 00:00:01.03

As expected the estimates are wrong, so what's the universal slogan "when something is running slow, run it again it would run faster " :), Let's rerun the query

SQL> select * from tbl_t1 where id_val1 > 18 and id < 99999;

        ID       ID_1 ID_VAL1    ID_VAL2                                                            
---------- ---------- ---------- ----------                                                         
     43725       7536 42         9958                                                               
     14761       6433 44         17751                                                              
      5992       8624 50         18125                                                              
      1512       9577 28         40933                                                              

Elapsed: 00:00:00.00
SQL> 
SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR(null,null,'ALL'));

PLAN_TABLE_OUTPUT                                                                                   
----------------------------------------------------------------------------------------------------
SQL_ID  fk3bgjgnj0w3w, child number 1                                                               
-------------------------------------                                                               
select * from tbl_t1 where id_val1 > 18 and id < 99999                                              
                                                                                                    
Plan hash value: 3205898331                                                                         
                                                                                                    
----------------------------------------------------------------------------                        
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |                        
----------------------------------------------------------------------------                        
|   0 | SELECT STATEMENT  |        |       |       |    13 (100)|          |                        
|*  1 |  TABLE ACCESS FULL| TBL_T1 |     4 |    88 |    13   (0)| 00:00:01 |                        

PLAN_TABLE_OUTPUT                                                                                   
----------------------------------------------------------------------------------------------------
                                                                                                    
Query Block Name / Object Alias (identified by operation id):                                       
-------------------------------------------------------------                                       
                                                                                                    
   1 - SEL$1 / TBL_T1@SEL$1                                                                         
                                                                                                    
Predicate Information (identified by operation id):                                                 
---------------------------------------------------                                                 
                                                                                                    
   1 - filter((TO_NUMBER("ID_VAL1")>18 AND "ID"<99999 
PLAN_TABLE_OUTPUT                                                                                   
----------------------------------------------------------------------------------------------------
                                                                                                    
Column Projection Information (identified by operation id):                                         
-----------------------------------------------------------                                         
                                                                                                    
   1 - "ID"[NUMBER,22], "TBL_T1"."ID_1"[NUMBER,22],                                                 
       "ID_VAL1"[VARCHAR2,10], "TBL_T1"."ID_VAL2"[VARCHAR2,10]                                      
                                                                                                    
Note                                                                                                
-----                                                                                               
   - cardinality feedback used for this statement                                                   
                                                                                                    

33 rows selected.

Elapsed: 00:00:00.08

Yes the optimizer has got the correct number of rows. Look at the last section of the DBMS_XPLAN output, its the NOTE section indicating "cardinality feedback used for this statement" 

                It is cool, but what is the cost involved in doing this ? and the cost is HARD PARSE. Through being exactly the same query it has gone for hard parse so as to use the Cardinality Feedback. Do you think this feature as a boon in all cases, will it be used under all circumstances ? if so what happens when

1. When the base tables are highly volatile ? 
2. When i opt for dynamic sampling ? 
3. when extended stats are collected ? i.,e already have given better information about the columns with the extended stats 
4. When bind variables are used ?    

Well the above conditions are wherein Cardinality Feedback is likely not being used. It is more likely to occur when you happen to run more then once the same query on a less volatile tables. When you expect oracle to identify the correct stats on the very first run, well Cardinality Feedback is not going to help and they are not persistent too. Is there a way to validate the hard parse, check CFB in Oracle 11.2.0.3