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.
-- drop table if exists
DROP TABLE ADMDECKER.DUMMY purge;
-- create table
CREATE TABLE ADMDECKER.DUMMY (
a NUMBER NOT NULL,
B NUMBER NOT NULL,
C NUMBER NOT NULL,
D NUMBER NOT NULL,
E NUMBER ) NOLOGGING;
-- fill table
INSERT /*+ APPEND */ INTO ADMDECKER.DUMMY
SELECT rownum AS a,
MOD(rownum,100) AS B,
MOD(rownum,10000) AS C,
1 AS D,
234 AS E
FROM (
SELECT rownum r
FROM
(SELECT rownum r FROM dual CONNECT BY rownum <= 100) a,
(SELECT rownum r FROM dual CONNECT BY rownum <= 100) b,
(SELECT rownum r FROM dual CONNECT BY rownum <= 100) c
WHERE rownum <= 1000000
);
commit;
-- create indexes
CREATE INDEX ADMDECKER.DUMMY_IDX1 ON ADMDECKER.DUMMY(C,B) nologging;
CREATE INDEX ADMDECKER.DUMMY_IDX2 ON ADMDECKER.DUMMY(B DESC,a) nologging;
CREATE INDEX ADMDECKER.DUMMY_IDX3 ON ADMDECKER.DUMMY(C,B,D) nologging;
CREATE INDEX ADMDECKER.DUMMY_IDX4 ON ADMDECKER.DUMMY(B,a) nologging;
-- gather statistics (including tab stats for virtual hidden column for "B desc" column.
BEGIN DBMS_STATS.GATHER_TABLE_STATS(
OWNNAME=>'ADMDECKER',
TABNAME=>'DUMMY',
METHOD_OPT=>'FOR ALL COLUMNS SIZE 1',
cascade=>TRUE,
ESTIMATE_PERCENT=>100);
END;
/
SELECT * FROM DBA_TAB_STATISTICS WHERE TABLE_NAME = 'DUMMY';
SELECT * FROM DBA_TAB_COL_STATISTICS WHERE TABLE_NAME = 'DUMMY';
-- note min/max/density values for system generated virtual hidden column
SELECT * FROM DBA_TAB_COLS WHERE TABLE_NAME = 'DUMMY';
SELECT * FROM dba_ind_statistics WHERE TABLE_NAME = 'DUMMY'; |
-- drop table if exists
drop table ADMDECKER.DUMMY purge;
-- create table
create table ADMDECKER.DUMMY (
a number not null,
B number not null,
C number not null,
D number not null,
E number ) NOLOGGING;
-- fill table
insert /*+ APPEND */ into ADMDECKER.DUMMY
select rownum as a,
MOD(rownum,100) as B,
MOD(rownum,10000) as C,
1 as D,
234 as E
from (
select rownum r
from
(select rownum r from dual connect by rownum <= 100) a,
(select rownum r from dual connect by rownum <= 100) b,
(select rownum r from dual connect by rownum <= 100) c
where rownum <= 1000000
);
commit;
-- create indexes
create index ADMDECKER.DUMMY_IDX1 on ADMDECKER.DUMMY(C,B) nologging;
create index ADMDECKER.DUMMY_IDX2 on ADMDECKER.DUMMY(B desc,a) nologging;
create index ADMDECKER.DUMMY_IDX3 on ADMDECKER.DUMMY(C,B,D) nologging;
create index ADMDECKER.DUMMY_IDX4 on ADMDECKER.DUMMY(B,a) nologging;
-- gather statistics (including tab stats for virtual hidden column for "B desc" column.
begin DBMS_STATS.GATHER_TABLE_STATS(
OWNNAME=>'ADMDECKER',
TABNAME=>'DUMMY',
METHOD_OPT=>'FOR ALL COLUMNS SIZE 1',
cascade=>true,
ESTIMATE_PERCENT=>100);
END;
/
select * from DBA_TAB_STATISTICS where TABLE_NAME = 'DUMMY';
select * from DBA_TAB_COL_STATISTICS where TABLE_NAME = 'DUMMY';
-- note min/max/density values for system generated virtual hidden column
select * from DBA_TAB_COLS where TABLE_NAME = 'DUMMY';
select * from dba_ind_statistics where TABLE_NAME = 'DUMMY';
I enable 10053 tracing and execute the query. Afterwards I display the execution plan.
SQL> ALTER SESSION SET EVENTS='10053 trace name context forever, level 1';
SESSION altered.
SELECT /*+ GATHER_PLAN_STATISTICS RUN2 */ COUNT(E) FROM ADMDECKER.DUMMY
WHERE C = 9592 AND B >= 93 AND D=1;
COUNT(E)
----------
0
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST +COST'));
PLAN_TABLE_OUTPUT
---------------------
SQL_ID f5mkaprk7fzt8, child NUMBER 0
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS RUN2 */ COUNT(E) FROM ADMDECKER.DUMMY
WHERE C = 9592 AND B >= 93 AND D=1
Plan hash VALUE: 1903509519
------------------------------------------------------------------------------------------------
| Id |Operation |Name |E-ROWS| Cost (%CPU)|A-ROWS| A-TIME | Buffers |
------------------------------------------------------------------------------------------------
| 0|SELECT STATEMENT | | | 4 (100)| 1|00:00:00.07 | 23437 |
| 1| SORT AGGREGATE | | 1| | 1|00:00:00.07 | 23437 |
|* 2| TABLE ACCESS BY INDEX ROWID|DUMMY | 7| 4 (0)| 0|00:00:00.07 | 23437 |
|* 3| INDEX RANGE SCAN |DUMMY_IDX2| 1| 3 (0)| 70000|00:00:00.03 | 197 |
------------------------------------------------------------------------------------------------
Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------
2 - FILTER(("C"=9592 AND "D"=1))
3 - access("DUMMY"."SYS_NC00006$"<=HEXTORAW('3EA1FF') )
FILTER(SYS_OP_UNDESCEND("DUMMY"."SYS_NC00006$")>=93) |
SQL> ALTER SESSION SET EVENTS='10053 trace name context forever, level 1';
Session altered.
select /*+ GATHER_PLAN_STATISTICS RUN2 */ COUNT(E) from ADMDECKER.DUMMY
where C = 9592 and B >= 93 and D=1;
COUNT(E)
----------
0
select * from table(DBMS_XPLAN.DISPLAY_CURSOR(null,null,'ALLSTATS LAST +COST'));
PLAN_TABLE_OUTPUT
---------------------
SQL_ID f5mkaprk7fzt8, child number 0
-------------------------------------
select /*+ GATHER_PLAN_STATISTICS RUN2 */ COUNT(E) from ADMDECKER.DUMMY
where C = 9592 and B >= 93 and D=1
Plan hash value: 1903509519
------------------------------------------------------------------------------------------------
| Id |Operation |Name |E-Rows| Cost (%CPU)|A-Rows| A-Time | Buffers |
------------------------------------------------------------------------------------------------
| 0|SELECT STATEMENT | | | 4 (100)| 1|00:00:00.07 | 23437 |
| 1| SORT AGGREGATE | | 1| | 1|00:00:00.07 | 23437 |
|* 2| TABLE ACCESS BY INDEX ROWID|DUMMY | 7| 4 (0)| 0|00:00:00.07 | 23437 |
|* 3| INDEX RANGE SCAN |DUMMY_IDX2| 1| 3 (0)| 70000|00:00:00.03 | 197 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(("C"=9592 AND "D"=1))
3 - access("DUMMY"."SYS_NC00006$"<=HEXTORAW('3EA1FF') )
filter(SYS_OP_UNDESCEND("DUMMY"."SYS_NC00006$")>=93)
The complete 10053 can be viewed here: 10053 Trace of bad plan.
It can be seen that the execution plan contains the following statement and uses a very, very low density.
Using prorated density: 0.000002 of col #6 as selectvity of out-of-range/non-existent value pred
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.
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.
CONNECT ADmdecker
Enter password:
Connected.
ALTER SESSION SET EVENTS='10053 trace name context forever, level 1';
SESSION altered.
SELECT /*+ GATHER_PLAN_STATISTICS RUN2 */ COUNT(E) FROM ADMDECKER.DUMMY
WHERE C = 9592 AND B >= 93 AND D=1;
COUNT(E)
----------
0
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST +COST'));
PLAN_TABLE_OUTPUT
----------------------
SQL_ID f5mkaprk7fzt8, child NUMBER 1
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS RUN2 */ COUNT(E) FROM MDECKER.DUMMY
WHERE C = 9592 AND B >= 93 AND D=1
Plan hash VALUE: 1171978535
-------------------------------------------------------------------------------------------------
| Id |Operation |Name |E-ROWS| Cost (%CPU)|A-ROWS | A-TIME |Buffers |
-------------------------------------------------------------------------------------------------
| 0|SELECT STATEMENT | | | 11 (100)| 1 |00:00:00.01 | 3 |
| 1| SORT AGGREGATE | | 1 | | 1 |00:00:00.01 | 3 |
| 2| TABLE ACCESS BY INDEX ROWID|DUMMY | 7 | 11 (0)| 0 |00:00:00.01 | 3 |
|* 3| INDEX RANGE SCAN |DUMMY_IDX3 | 7 | 3 (0)| 0 |00:00:00.01 | 3 |
-------------------------------------------------------------------------------------------------
Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------
3 - access("C"=9592 AND "B">=93 AND "D"=1)
FILTER("D"=1)
Note
-----
- cardinality feedback used FOR this statement |
connect ADmdecker
Enter password:
Connected.
ALTER SESSION SET EVENTS='10053 trace name context forever, level 1';
Session altered.
select /*+ GATHER_PLAN_STATISTICS RUN2 */ COUNT(E) from ADMDECKER.DUMMY
where C = 9592 and B >= 93 and D=1;
COUNT(E)
----------
0
select * from table(DBMS_XPLAN.DISPLAY_CURSOR(null,null,'ALLSTATS LAST +COST'));
PLAN_TABLE_OUTPUT
----------------------
SQL_ID f5mkaprk7fzt8, child number 1
-------------------------------------
select /*+ GATHER_PLAN_STATISTICS RUN2 */ COUNT(E) from MDECKER.DUMMY
where C = 9592 and B >= 93 and D=1
Plan hash value: 1171978535
-------------------------------------------------------------------------------------------------
| Id |Operation |Name |E-Rows| Cost (%CPU)|A-Rows | A-Time |Buffers |
-------------------------------------------------------------------------------------------------
| 0|SELECT STATEMENT | | | 11 (100)| 1 |00:00:00.01 | 3 |
| 1| SORT AGGREGATE | | 1 | | 1 |00:00:00.01 | 3 |
| 2| TABLE ACCESS BY INDEX ROWID|DUMMY | 7 | 11 (0)| 0 |00:00:00.01 | 3 |
|* 3| INDEX RANGE SCAN |DUMMY_IDX3 | 7 | 3 (0)| 0 |00:00:00.01 | 3 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("C"=9592 AND "B">=93 AND "D"=1)
filter("D"=1)
Note
-----
- cardinality feedback used for this statement
The complete 10053 of the new plan (using cardinality feedback feature) can be viewed here: 10053 Trace of good plan using cardinality feedback. More information about this little documented new feature can be found at Dion Cho´s blog and from this Oracle paper.
Update: 06.10.2011
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.
