14 February, 2011

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)
.
.
.

5 comments:

Anonymous said...

>CARDINALITY Hint has been available since 10g
This is not correct:

SQL> select version from v$sql_hint where name = 'CARDINALITY';

VERSION
-------------------------
9.0.0

Regarding your test: I think that the message "cardinality feedback used for this statement" appeared there by mistake. Do you have a reproducible test case to show the same behavior at will?

Hemant K Chitale said...

Timur,
Thank you. I stand corrected on the version.
Hemant

Hemant K Chitale said...

Timur,
I had been busy these past few days.

I attempted to create a consistently reporducible test case to demonstrate the "cardinality feedback used for this statement" comment. However, currently, I am unable to reproduce it.

Hemant

Bryan Grenn said...

I have been able to create a reproducible test case.
http://bryangrenn.blogspot.com/2011/04/cardinality-feedback-example.html

What I'm wondering, and you said this, is how cardinality feedback and adaptive cursor sharing work togethor ? If Cardinality feedback changes the plan, is it ONLY for a single bind variable, or is it for any furture executions ? Do they work in concert, or independent (when there are no histograms) ?

Hemant K Chitale said...

Bryan,

In our cases, we used Literals, not Binds. (if CURSOR_SHARING is set to 'SIMILAR', then it becomes another case !.).

IMHO, Adaptive Cursor Sharing was designed to deal with Binds.
(In fact, Oracle Support Article#1169017.1 recommends moving away from CURSOR_SHARING='SIMILAR' because Adaptive Cursor Sharing is available in 11g).

ACS and Cardinality Feedback seem to be two "different" features. The latter is driven by "_optimizer_use_feedback".
Methinks that we have created "boundary" cases which may be more frequent than expected.

Hemant K Chitale