SQL plan directives on partitioned tables

Introduction

The purpose of this post is to warn of an edge case where the cardinality estimate might significantly increase after adding a conjunctive filter predicate. This problem might arise in Oracle 12c due to sql plan directives on partitioned tables when there is a value which is rare in one partition, but appears frequently in other partitions.

Test data

Create the table with two partitions:

CREATE TABLE T
(
id number,
value varchar2(10)
)
PARTITION BY RANGE (id)
(
PARTITION t_id_1 VALUES LESS THAN (2) ,
PARTITION t_id_2 VALUES LESS THAN (3)
);

Firstly, fill the second partition with both rare and popular data:

INSERT INTO T
SELECT 2,'RARE_IN_2' value
FROM dual
CONNECT BY level <= 1;
INSERT INTO T
SELECT 2,'POPULAR' value
FROM dual
CONNECT BY level <= 1000000 ;

Secondly, insert the rare values from the second partition into the first partition:

INSERT INTO T
SELECT 1,'RARE_IN_2' value
FROM dual
CONNECT BY level <= 1000;

Finally, gather statistics:

EXEC DBMS_STATS.gather_table_stats('SYS', 'T');

SQL plan directives

The following query selects the rare values from the second partition:

SELECT /*+ GATHER_PLAN_STATISTICS */ *
FROM t
WHERE id = 2 and value = 'RARE_IN_2' ;

As expected, the estimated cardinality is wrong. As there are only two values in the table and the histograms are missing the optimizer thinks it would get the half of the records back (500K):

-----------------------------------------------------------------------------------------
| Id  | Operation              | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers 
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |      |      1 |        |      1 |00:00:00.02 |    2211 
|   1 |  PARTITION RANGE SINGLE|      |      1 |    500K|      1 |00:00:00.02 |    2211 
|*  2 |   TABLE ACCESS FULL    | T    |      1 |    500K|      1 |00:00:00.02 |    2211 
-----------------------------------------------------------------------------------------       
Predicate Information (identified by operation id):
---------------------------------------------------
  2 - filter(("VALUE"='RARE_IN_2' AND "ID"=2))

However, because of the “AND” clause in the filter predicate, the statistics feedback kicked in and as a result, the SQL plan directive was created:

EXEC DBMS_SPD.flush_sql_plan_directive;

select type,state,reason,notes
  from dba_sql_plan_directives
  where directive_id in(
    select directive_id from dba_sql_plan_dir_objects
      where owner='SYS' and object_name='T'
);

TYPE                 STATE  REASON                                                         
-------------------- ------ ------------------------------------------------------------
NOTES       
----------------------------------------------------------------------------------------
DYNAMIC_SAMPLING     USABLE SINGLE TABLE CARDINALITY MISESTIMATE
<spd_note>
  <internal_state>NEW</internal_state>
  <redundant>NO</redundant>
  <spd_text>{EC(SYS.T)[ID, VALUE]}</spd_text>
</spd_note>

Thanks to dynamic sampling the cardinality estimate for the second execution is correct:

SELECT /*+ GATHER_PLAN_STATISTICS */ *   
  FROM   t t2   WHERE  id = 2
and value = 'RARE_IN_2' ;
-----------------------------------------------------------------------------------------
| Id  | Operation              | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |      |      1 |        |      1 |00:00:00.02 |    2211 |
|   1 |  PARTITION RANGE SINGLE|      |      1 |      1 |      1 |00:00:00.02 |    2211 |
|*  2 |   TABLE ACCESS FULL    | T    |      1 |      1 |      1 |00:00:00.02 |    2211 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------                                         
   2 - filter(("VALUE"='RARE_IN_2' AND "ID"=2))
Note
-----                                            
   - dynamic statistics used: dynamic sampling (level=2)
   - 1 Sql Plan Directive used for this statement

Now, we are selecting all the data from the first partition. No surprise there, the estimate is correct:

SELECT /*+ GATHER_PLAN_STATISTICS */ *   
  FROM t WHERE  id = 1
-----------------------------------------------------------------------------------------     
| Id  | Operation              | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers 
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |      |      1 |        |   1000 |00:00:00.01 |      72 
|   1 |  PARTITION RANGE SINGLE|      |      1 |   1000 |   1000 |00:00:00.01 |      72 
|*  2 |   TABLE ACCESS FULL    | T    |      1 |   1000 |   1000 |00:00:00.01 |      72 
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("ID"=1)

To stir up we will add a conjunctive predicate:

SELECT /*+ GATHER_PLAN_STATISTICS */ *
  FROM t WHERE  id = 1 and value = 'RARE_IN_2' ;
-----------------------------------------------------------------------------------------
| Id  | Operation              | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers        
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |      |      1 |        |   1000 |00:00:00.01 |      72 
|   1 |  PARTITION RANGE SINGLE|      |      1 |   2760 |   1000 |00:00:00.01 |      72 
|*  2 |   TABLE ACCESS FULL    | T    |      1 |   2760 |   1000 |00:00:00.01 |      72 
-----------------------------------------------------------------------------------------       
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(("ID"=1 AND "VALUE"='RARE_IN_2'))                                               
Note                                                                      
-----                                           
   - dynamic statistics used: dynamic sampling (level=2)
   - 1 Sql Plan Directive used for this statement

The cardinality estimate soared which is counterintuitive because an additional “AND” filter predicate can only reduce the number of the returned rows. What happened here? Because of the additional filter predicate the sql plan directive and dynamic sampling kicked in and produced the wrong estimate because of the skewed data. In this case, the basic column statistics would have led to a far better outcome:

SELECT /*+ GATHER_PLAN_STATISTICS dynamic_sampling(0) */ *
  FROM   t WHERE  id = 1 and value = 'RARE_IN_2' ;
-----------------------------------------------------------------------------------------
| Id  | Operation              | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers 
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |      |      1 |        |   1000 |00:00:00.01 |      72
|   1 |  PARTITION RANGE SINGLE|      |      1 |   1000 |   1000 |00:00:00.01 |      72
|*  2 |   TABLE ACCESS FULL    | T    |      1 |   1000 |   1000 |00:00:00.01 |      72
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(("ID"=1 AND "VALUE"='RARE_IN_2'))

As a workaround, you might permanently disable the misleading sql plan directive by using the procedure described in the post by Franck Pachot and create the histogram on the skewed column.

Update 18.11.2015: The Oracle Support filed a bug for the problem – Bug 22229118 : WRONG CARDINALITY ESTIMATE WITH SQL PLAN DIRECTIVE .