{"id":899,"date":"2010-10-25T21:53:38","date_gmt":"2010-10-25T19:53:38","guid":{"rendered":"http:\/\/www.ora-solutions.net\/web\/?p=899"},"modified":"2011-10-06T13:55:06","modified_gmt":"2011-10-06T11:55:06","slug":"suboptimal-prorated-density-calculation-with-desc-index-cardinality-feedback-feature","status":"publish","type":"post","link":"https:\/\/www.ora-solutions.net\/web\/2010\/10\/25\/suboptimal-prorated-density-calculation-with-desc-index-cardinality-feedback-feature\/","title":{"rendered":"suboptimal prorated density calculation with DESC index – cardinality feedback feature"},"content":{"rendered":"

At a customer site, I came across a problem of the Oracle cost based optimizer with selectivity calculation with a composite index with one column sorted descending. The following testcase describes the issue. Currently Oracle development is working on the issue. During testing on 11.2.0.1, I realized that the new cardinality feedback issue takes care of the problem and subsequent executions are using the correct plan.<\/p>\n

-- drop table if exists\r\ndrop table ADMDECKER.DUMMY purge;\r\n\r\n-- create table\r\ncreate table ADMDECKER.DUMMY (\r\n      a number not null,\r\n      B number not null,\r\n      C number not null,\r\n      D number not null,\r\n      E number ) NOLOGGING;\r\n\r\n-- fill table\r\ninsert \/*+ APPEND *\/  into ADMDECKER.DUMMY\r\nselect  rownum as a,\r\n        MOD(rownum,100) as B,\r\n        MOD(rownum,10000) as C,\r\n        1 as D,\r\n        234 as E\r\n        from (\r\n               select rownum r\r\n               from\r\n                       (select rownum r from dual connect by rownum <= 100) a,\r\n                       (select rownum r from dual connect by rownum <= 100) b,\r\n                       (select rownum r from dual connect by rownum <= 100) c\r\n               where rownum <= 1000000\r\n       );\r\n\r\ncommit;\r\n\r\n-- create indexes\r\ncreate index ADMDECKER.DUMMY_IDX1 on ADMDECKER.DUMMY(C,B) nologging;\r\ncreate index ADMDECKER.DUMMY_IDX2 on ADMDECKER.DUMMY(B desc,a) nologging;\r\ncreate index ADMDECKER.DUMMY_IDX3 on ADMDECKER.DUMMY(C,B,D) nologging;\r\ncreate index ADMDECKER.DUMMY_IDX4 on ADMDECKER.DUMMY(B,a) nologging;\r\n\r\n-- gather statistics (including tab stats for virtual hidden column for \"B desc\" column.\r\nbegin DBMS_STATS.GATHER_TABLE_STATS(\r\n      OWNNAME=>'ADMDECKER',\r\n      TABNAME=>'DUMMY',\r\n      METHOD_OPT=>'FOR ALL COLUMNS SIZE 1',\r\n      cascade=>true,\r\n      ESTIMATE_PERCENT=>100);\r\nEND;\r\n\/\r\n\r\nselect * from DBA_TAB_STATISTICS where TABLE_NAME = 'DUMMY';\r\nselect * from DBA_TAB_COL_STATISTICS where TABLE_NAME = 'DUMMY';\r\n\r\n-- note min\/max\/density values for system generated virtual hidden column\r\nselect * from DBA_TAB_COLS where TABLE_NAME = 'DUMMY';\r\nselect * from dba_ind_statistics  where TABLE_NAME = 'DUMMY';<\/pre>\n
I enable 10053 tracing and execute the query. Afterwards I display the execution plan.<\/p>\n
SQL> ALTER SESSION SET EVENTS='10053 trace name context forever, level 1';\r\n\r\nSession altered.\r\n\r\nselect \/*+ GATHER_PLAN_STATISTICS RUN2 *\/ COUNT(E) from ADMDECKER.DUMMY\r\nwhere C = 9592 and B >= 93 and D=1;\r\n\r\n  COUNT(E)\r\n----------\r\n         0\r\n\r\nselect * from table(DBMS_XPLAN.DISPLAY_CURSOR(null,null,'ALLSTATS LAST +COST'));\r\n\r\nPLAN_TABLE_OUTPUT\r\n---------------------\r\nSQL_ID  f5mkaprk7fzt8, child number 0\r\n-------------------------------------\r\nselect \/*+ GATHER_PLAN_STATISTICS RUN2 *\/ COUNT(E) from ADMDECKER.DUMMY\r\nwhere C = 9592 and B >= 93 and D=1\r\n\r\nPlan hash value: 1903509519\r\n\r\n------------------------------------------------------------------------------------------------\r\n| Id |Operation                    |Name      |E-Rows| Cost (%CPU)|A-Rows|   A-Time   | Buffers |\r\n------------------------------------------------------------------------------------------------\r\n|   0|SELECT STATEMENT             |          |      |     4 (100)|     1|00:00:00.07 |   23437 |\r\n|   1| SORT AGGREGATE              |          |     1|            |     1|00:00:00.07 |   23437 |\r\n|*  2|  TABLE ACCESS BY INDEX ROWID|DUMMY     |     7|     4   (0)|     0|00:00:00.07 |   23437 |\r\n|*  3|   INDEX RANGE SCAN          |DUMMY_IDX2|     1|     3   (0)| 70000|00:00:00.03 |     197 |\r\n------------------------------------------------------------------------------------------------\r\n\r\nPredicate Information (identified by operation id):\r\n---------------------------------------------------\r\n\r\n   2 - filter((\"C\"=9592 AND \"D\"=1))\r\n   3 - access(\"DUMMY\".\"SYS_NC00006$\"<=HEXTORAW('3EA1FF') )\r\n       filter(SYS_OP_UNDESCEND(\"DUMMY\".\"SYS_NC00006$\")>=93)<\/pre>\n
The complete 10053 can be viewed here: 10053 Trace of bad plan.<\/a><\/p>\n
It can be seen that the execution plan contains the following statement and uses a very, very low density.<\/p>\n
\n
  Using prorated density: 0.000002 of col #6 as selectvity of out-of-range\/non-existent value pred<\/pre>\n<\/blockquote>\n
So, instead of using Index DUMMY_IDX3, which contains all three predicate columns, especially more selective equality predicate “C = 9592”, it uses quite unselective index DUMMY_IDX2, only because density is calculated wrongly.<\/p>\n
If the statement is executed a second time, the right plan is used. Cardinality feedback feature kicks in here. Please note the higher costs and higher cardinality for index range scan at row source operation id 3.<\/p>\n
connect ADmdecker\r\nEnter password:\r\nConnected.\r\nALTER SESSION SET EVENTS='10053 trace name context forever, level 1';\r\n\r\nSession altered.\r\n\r\nselect \/*+ GATHER_PLAN_STATISTICS RUN2 *\/ COUNT(E) from ADMDECKER.DUMMY\r\nwhere C = 9592 and B >= 93 and D=1;\r\n\r\n  COUNT(E)\r\n----------\r\n         0\r\n\r\nselect * from table(DBMS_XPLAN.DISPLAY_CURSOR(null,null,'ALLSTATS LAST +COST'));\r\n\r\nPLAN_TABLE_OUTPUT\r\n----------------------\r\nSQL_ID  f5mkaprk7fzt8, child number 1\r\n-------------------------------------\r\nselect \/*+ GATHER_PLAN_STATISTICS RUN2 *\/ COUNT(E) from MDECKER.DUMMY\r\nwhere C = 9592 and B >= 93 and D=1\r\n\r\nPlan hash value: 1171978535\r\n\r\n-------------------------------------------------------------------------------------------------\r\n| Id |Operation                    |Name       |E-Rows| Cost (%CPU)|A-Rows |   A-Time   |Buffers |\r\n-------------------------------------------------------------------------------------------------\r\n|   0|SELECT STATEMENT             |           |      |    11 (100)|     1 |00:00:00.01 |      3 |\r\n|   1| SORT AGGREGATE              |           |    1 |            |     1 |00:00:00.01 |      3 |\r\n|   2|  TABLE ACCESS BY INDEX ROWID|DUMMY      |    7 |    11   (0)|     0 |00:00:00.01 |      3 |\r\n|*  3|   INDEX RANGE SCAN          |DUMMY_IDX3 |    7 |     3   (0)|     0 |00:00:00.01 |      3 |\r\n-------------------------------------------------------------------------------------------------\r\n\r\nPredicate Information (identified by operation id):\r\n---------------------------------------------------\r\n\r\n   3 - access(\"C\"=9592 AND \"B\">=93 AND \"D\"=1)\r\n       filter(\"D\"=1)\r\n\r\nNote\r\n-----\r\n - cardinality feedback used for this statement<\/pre>\n
The complete 10053 of the new plan (using cardinality feedback feature) can be viewed here: 10053 Trace of good plan using cardinality feedback.<\/a> More information about this little documented new feature can be found at Dion Cho\u00b4s blog <\/a> and from this Oracle paper<\/a>.<\/p>\n
Update: 06.10.2011
\n<\/strong><\/p>\n
Oracle Support has provided Patch 11072246, which fixes a bug in DBMS_STATS on how min\/max values are set for hidden desc columns. After installing the patch, the bug is gone and the execution plan is optimal.\n<\/p><\/blockquote>\n","protected":false},"excerpt":{"rendered":"
At a customer site, I came across a problem of the Oracle cost based optimizer with selectivity calculation with a composite index with one column sorted descending. The following testcase describes the issue. Currently Oracle development is working on the issue. During testing on 11.2.0.1, I realized that the new cardinality feedback issue takes care […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[14,57,47,19,5],"tags":[],"class_list":["post-899","post","type-post","status-publish","format-standard","hentry","category-11g","category-11gr2","category-bugs","category-metalink","category-oracle-database"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/posts\/899","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/comments?post=899"}],"version-history":[{"count":21,"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/posts\/899\/revisions"}],"predecessor-version":[{"id":915,"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/posts\/899\/revisions\/915"}],"wp:attachment":[{"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/media?parent=899"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/categories?post=899"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/tags?post=899"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}