Cardinality Feedback in 11.2

The (undocumented) CARDINALITY Hint has been available since --10g-- 9i (correction by Timur). This allowed the Developer / DBA to "hint" to Oracle the expected number of rows that will be returned from a table that is part of a query.

11g's Adaptive Cursor Sharing for queries that use Binds uses "feedback" from previous executions to validate if an Execution Plan has been good enough (e.g. if the Execution Plan estimated 100 rows but the actual execution returned 4500 rows, then the plan was not good !) and "decide" if it should "re-peek" the bind and generate a new Execution Plan.

However, Cardinality Feedback may be used by 11g even when binds don't appear.

UPDATE 20-Feb-11 : As Timur supected, I believe, I am unable to create a consistently reproducible test case to demonstrate the "cardinality feedback used for this statement" comment from DBMS_XPLAN.DISPLAY.

Here is a simple demonstration. However, I will not explain the Cardinality Feedback and the new Execution Plan -- I might get some comments that attempt to explain these, I hope !

[Note : I have converted the "<=" and ">=" signs to "less_than_equal_to" and "greater_than_equal_to"].

In an 11.2.0.1 database, I start with a table with 100,000 rows and two single column indexes :

SQL> desc sales
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
SALE_ID                                   NOT NULL NUMBER
SALE_DATE                                          DATE
CUST_ID                                            NUMBER
REMARKS                                            VARCHAR2(50)


SQL> select num_rows from user_tables where table_name = 'SALES';

NUM_ROWS
----------
100000

SQL> select num_rows from user_tables where table_name = 'SALES';

NUM_ROWS
----------
100000

SQL> @$HOME/Scripts/which_indexes
Enter value for table_name: SALES
Enter value for table_owner: HEMANT

Index                       Ind Type     Unique?   Column            Pos
--------------------------- ------------ --------- ---------------- ----
SALES_DATES_NDX             NORMAL       NONUNIQUE SALE_DATE           1

SALES_UK                    NORMAL       UNIQUE    SALE_ID             1


SQL> 

I next run a query on two predicates :

SQL> select /*+ gather_plan_statistics */
2  cust_id, count(*)
3  from sales
4  where sale_date between
5  to_date('01-DEC-10','DD-MON-RR') and to_date('14-DEC-10','DD-MON-RR')
6  and cust_id = 4
7  group by cust_id;

CUST_ID   COUNT(*)
---------- ----------
   4        117

SQL>
SQL> select * from table(dbms_xplan.display_cursor('','','ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  65hm33unu3mcm, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ cust_id, count(*) from sales where
sale_date between to_date('01-DEC-10','DD-MON-RR') and
to_date('14-DEC-10','DD-MON-RR') and cust_id = 4 group by cust_id

Plan hash value: 1184718566

-----------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name            | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                 |      1 |        |      1 |00:00:00.04 |     149 |
|   1 |  SORT GROUP BY NOSORT         |                 |      1 |      1 |      1 |00:00:00.04 |     149 |
|*  2 |   FILTER                      |                 |      1 |        |    117 |00:00:00.01 |     149 |
|*  3 |    TABLE ACCESS BY INDEX ROWID| SALES           |      1 |    129 |    117 |00:00:00.01 |     149 |
|*  4 |     INDEX RANGE SCAN          | SALES_DATES_NDX |      1 |  13000 |  13000 |00:00:00.59 |      36 |
-----------------------------------------------------------------------------------------------------------

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

2 - filter(TO_DATE('01-DEC-10','DD-MON-RR') less_than_equal_to TO_DATE('14-DEC-10','DD-MON-RR'))
3 - filter("CUST_ID"=4)
4 - access("SALE_DATE" greater_than_equal_to TO_DATE('01-DEC-10','DD-MON-RR') AND
        "SALE_DATE" less_than_equal_to TO_DATE('14-DEC-10','DD-MON-RR'))


26 rows selected.

SQL> 

I find that Oracle actually read 13,000 ROWIDs from 36 Blocks of the SALES_DATE_NDX. However, on passing these 13,000 ROWIDs to the table, it read 113 Blocks but returned only 117 rows after filtering for CUST_ID=4. Evidently, only 117 of the 13,000 ROWIDs were relevant -- all of the rest were "throwaway".

Since I need to read only the SALE_DATE and CUST_ID columns, I create an appropriate index. (Let's ignore the possibility of a BitMap index -- maybe this is an OLTP table, maybe the Standard Edition is in use).

SQL> create index sales_cust_date_ndx on sales(cust_id, sale_date);

Index created.

SQL> 

Note : Remember that Index statistics are automatically gathered with the CREATE INDEX.
Testing the same query, I expect the Index to be used. But I find something else as well :

SQL> select /*+ gather_plan_statistics */
2  cust_id, count(*)
3  from sales
4  where sale_date between
5  to_date('01-DEC-10','DD-MON-RR') and to_date('14-DEC-10','DD-MON-RR')
6  and cust_id = 4
7  group by cust_id;

CUST_ID   COUNT(*)
---------- ----------
     4        117

SQL> select * from table(dbms_xplan.display_cursor('','','ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  65hm33unu3mcm, child number 1
-------------------------------------
select /*+ gather_plan_statistics */ cust_id, count(*) from sales where
sale_date between to_date('01-DEC-10','DD-MON-RR') and
to_date('14-DEC-10','DD-MON-RR') and cust_id = 4 group by cust_id

Plan hash value: 1839877207

------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name                | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                     |      1 |        |      1 |00:00:00.01 |       3 |
|   1 |  SORT GROUP BY NOSORT|                     |      1 |      1 |      1 |00:00:00.01 |       3 |
|*  2 |   FILTER             |                     |      1 |        |    117 |00:00:00.01 |       3 |
|*  3 |    INDEX RANGE SCAN  | SALES_CUST_DATE_NDX |      1 |    129 |    117 |00:00:00.01 |       3 |
------------------------------------------------------------------------------------------------------

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

2 - filter(TO_DATE('01-DEC-10','DD-MON-RR')less_than_equal_to TO_DATE('14-DEC-10','DD-MON-RR'))
3 - access("CUST_ID"=4 AND "SALE_DATE" greater_than_equal_to TO_DATE('01-DEC-10','DD-MON-RR') AND
          "SALE_DATE" less_than_equal_to TO_DATE('14-DEC-10','DD-MON-RR'))

Note
-----
- cardinality feedback used for this statement


28 rows selected.

SQL> 

Oracle estimated a Cardinality of 129 rows but also tells me that Cardinality Feedback was used.
Note that this SQL is exactly the same as the first one (has the same SQL_ID) but has been re-parsed and created as a new Child Cursor (CURSOR 1) with a new Plan (new PLAN_HASH_VALUE).

I then run a DDL statement to invalidate SQLs :

SQL> comment on table sales is 'Sales Data';

Comment created.

SQL> 

Retesting the query :

SQL> select /*+ gather_plan_statistics */
2  cust_id, count(*)
3  from sales
4  where sale_date between
5  to_date('01-DEC-10','DD-MON-RR') and to_date('14-DEC-10','DD-MON-RR')
6  and cust_id = 4
7  group by cust_id;

CUST_ID   COUNT(*)
---------- ----------
     4        117

SQL> select * from table(dbms_xplan.display_cursor('','','ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  65hm33unu3mcm, child number 1
-------------------------------------
select /*+ gather_plan_statistics */ cust_id, count(*) from sales where
sale_date between to_date('01-DEC-10','DD-MON-RR') and
to_date('14-DEC-10','DD-MON-RR') and cust_id = 4 group by cust_id

Plan hash value: 1839877207

------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name                | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                     |      1 |        |      1 |00:00:00.01 |       3 |
|   1 |  SORT GROUP BY NOSORT|                     |      1 |      1 |      1 |00:00:00.01 |       3 |
|*  2 |   FILTER             |                     |      1 |        |    117 |00:00:00.01 |       3 |
|*  3 |    INDEX RANGE SCAN  | SALES_CUST_DATE_NDX |      1 |    129 |    117 |00:00:00.01 |       3 |
------------------------------------------------------------------------------------------------------

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

2 - filter(TO_DATE('01-DEC-10','DD-MON-RR') less_than_equal_to TO_DATE('14-DEC-10','DD-MON-RR'))
3 - access("CUST_ID"=4 AND "SALE_DATE" greater_than_equal_to TO_DATE('01-DEC-10','DD-MON-RR') AND
          "SALE_DATE" less_than_equal_to TO_DATE('14-DEC-10','DD-MON-RR'))


24 rows selected.

SQL> 

This time, the "cardinality feedback was used for this statement" message does not appear.

(Comments on what you think are the "what" and "why" are welcome)

.

.

.