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
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
- When the base tables are highly volatile ?
- When i opt for dynamic sampling ?
- when extended stats are collected ? i.,e already have given better information about the columns with the extended stats
- 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
