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.<\/p>\n
Create the table with two partitions:<\/p>\n
CREATE TABLE T\r\n(\r\nid number,\r\nvalue varchar2(10)\r\n)\r\nPARTITION BY RANGE (id)\r\n(\r\nPARTITION t_id_1 VALUES LESS THAN (2) ,\r\nPARTITION t_id_2 VALUES LESS THAN (3)\r\n);<\/code><\/pre>\nFirstly, fill the second partition with both rare and popular data:<\/p>\n
INSERT INTO T\r\nSELECT 2,'RARE_IN_2' value\r\nFROM dual\r\nCONNECT BY level <= 1;\r\nINSERT INTO T\r\nSELECT 2,'POPULAR' value\r\nFROM dual\r\nCONNECT BY level <= 1000000 ;<\/code><\/pre>\nSecondly, insert the\u00a0rare values\u00a0from the second partition\u00a0into the first partition:<\/p>\n
INSERT INTO T\r\nSELECT 1,'RARE_IN_2' value\r\nFROM dual\r\nCONNECT BY level <= 1000;<\/pre>\nFinally,\u00a0gather statistics:<\/p>\n
EXEC DBMS_STATS.gather_table_stats('SYS', 'T');<\/code><\/pre>\nSQL plan directives<\/h4>\n
The following query selects the rare values from the second partition:<\/p>\n
SELECT \/*+ GATHER_PLAN_STATISTICS *\/ *\r\nFROM t\r\nWHERE id = 2 and value = 'RARE_IN_2' ;<\/code><\/pre>\nAs 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\u00a0of the records back (500K):<\/p>\n
-----------------------------------------------------------------------------------------\r\n| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers \r\n-----------------------------------------------------------------------------------------\r\n| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.02 | 2211 \r\n| 1 | PARTITION RANGE SINGLE| | 1 | 500K| 1 |00:00:00.02 | 2211 \r\n|* 2 | TABLE ACCESS FULL | T | 1 | 500K<\/span>| 1<\/span> |00:00:00.02 | 2211 \r\n----------------------------------------------------------------------------------------- \r\nPredicate Information (identified by operation id):\r\n---------------------------------------------------\r\n 2 - filter((\"VALUE\"='RARE_IN_2' AND \"ID\"=2))<\/code><\/pre>\nHowever, because of the \u201cAND\u201d clause in the filter predicate, the statistics feedback kicked in and as a result, the SQL plan directive was created:<\/p>\n
EXEC DBMS_SPD.flush_sql_plan_directive;\r\n\r\nselect type,state,reason,notes\r\n from dba_sql_plan_directives\r\n where directive_id in(\r\n select directive_id from dba_sql_plan_dir_objects\r\n where owner='SYS' and object_name='T'\r\n);\r\n\r\nTYPE STATE REASON \r\n-------------------- ------ ------------------------------------------------------------\r\nNOTES \r\n----------------------------------------------------------------------------------------\r\nDYNAMIC_SAMPLING USABLE SINGLE TABLE CARDINALITY MISESTIMATE\r\n<spd_note>\r\n <internal_state>NEW<\/internal_state>\r\n <redundant>NO<\/redundant>\r\n <spd_text>{EC(SYS.T)[ID, VALUE]}<\/spd_text>\r\n<\/spd_note><\/code><\/pre>\nThanks to dynamic sampling the cardinality estimate for the second execution is correct:<\/p>\n
SELECT \/*+ GATHER_PLAN_STATISTICS *\/ * \r\n FROM t t2 WHERE id = 2\r\nand value = 'RARE_IN_2' ;\r\n-----------------------------------------------------------------------------------------\r\n| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |\r\n-----------------------------------------------------------------------------------------\r\n| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.02 | 2211 |\r\n| 1 | PARTITION RANGE SINGLE| | 1 | 1 | 1 |00:00:00.02 | 2211 |\r\n|* 2 | TABLE ACCESS FULL | T | 1<\/span> | 1<\/span> | 1 |00:00:00.02 | 2211 |\r\n-----------------------------------------------------------------------------------------\r\nPredicate Information (identified by operation id):\r\n--------------------------------------------------- \r\n 2 - filter((\"VALUE\"='RARE_IN_2' AND \"ID\"=2))\r\nNote\r\n----- \r\n - dynamic statistics used: dynamic sampling (level=2)\r\n - 1 Sql Plan Directive used for this statement<\/span><\/code><\/pre>\nNow, we are selecting all the data from the first partition. No surprise there, the estimate is correct:<\/p>\n
SELECT \/*+ GATHER_PLAN_STATISTICS *\/ * \r\n FROM t WHERE id = 1\r\n----------------------------------------------------------------------------------------- \r\n| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers \r\n-----------------------------------------------------------------------------------------\r\n| 0 | SELECT STATEMENT | | 1 | | 1000 |00:00:00.01 | 72 \r\n| 1 | PARTITION RANGE SINGLE| | 1 | 1000 | 1000 |00:00:00.01 | 72 \r\n|* 2 | TABLE ACCESS FULL | T | 1 | 1000<\/span> | 1000<\/span> |00:00:00.01 | 72 \r\n-----------------------------------------------------------------------------------------\r\nPredicate Information (identified by operation id):\r\n---------------------------------------------------\r\n 2 - filter(\"ID\"=1)<\/code><\/pre>\nTo stir up we will add a conjunctive predicate:<\/p>\n
SELECT \/*+ GATHER_PLAN_STATISTICS *\/ *\r\n FROM t WHERE id = 1 and value = 'RARE_IN_2'<\/span> ;\r\n-----------------------------------------------------------------------------------------\r\n| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers \r\n-----------------------------------------------------------------------------------------\r\n| 0 | SELECT STATEMENT | | 1 | | 1000 |00:00:00.01 | 72 \r\n| 1 | PARTITION RANGE SINGLE| | 1 | 2760 | 1000 |00:00:00.01 | 72 \r\n|* 2 | TABLE ACCESS FULL | T | 1 | 2760<\/span> | 1000<\/span> |00:00:00.01 | 72 \r\n----------------------------------------------------------------------------------------- \r\nPredicate Information (identified by operation id):\r\n---------------------------------------------------\r\n 2 - filter((\"ID\"=1 AND \"VALUE\"='RARE_IN_2')) \r\nNote \r\n----- \r\n - dynamic statistics used: dynamic sampling (level=2)\r\n - 1 Sql Plan Directive used for this statement<\/span><\/code><\/pre>\nThe cardinality estimate soared which is counterintuitive\u00a0because\u00a0an additional “AND”\u00a0filter predicate can only\u00a0reduce the number of the returned rows.\u00a0What 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\u00a0led to\u00a0a far better outcome:<\/p>\n
SELECT \/*+ GATHER_PLAN_STATISTICS dynamic_sampling(0)<\/span> *\/ *\r\n FROM t WHERE id = 1 and value = 'RARE_IN_2' ;\r\n-----------------------------------------------------------------------------------------\r\n| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers \r\n-----------------------------------------------------------------------------------------\r\n| 0 | SELECT STATEMENT | | 1 | | 1000 |00:00:00.01 | 72\r\n| 1 | PARTITION RANGE SINGLE| | 1 | 1000 | 1000 |00:00:00.01 | 72\r\n|* 2 | TABLE ACCESS FULL | T | 1 | 1000<\/span> | 1000<\/span> |00:00:00.01 | 72\r\n-----------------------------------------------------------------------------------------\r\nPredicate Information (identified by operation id):\r\n---------------------------------------------------\r\n 2 - filter((\"ID\"=1 AND \"VALUE\"='RARE_IN_2'))\r\n<\/code><\/pre>\nAs a workaround, you might permanently disable the misleading\u00a0sql plan directive\u00a0by\u00a0using the procedure\u00a0described in the post<\/a>\u00a0by Franck Pachot and create the histogram on the skewed column.<\/p>\nUpdate 18.11.2015: The Oracle Support filed a bug for the problem – Bug 22229118 : WRONG CARDINALITY ESTIMATE WITH SQL PLAN DIRECTIVE .<\/p>\n","protected":false},"excerpt":{"rendered":"
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. Continue Reading