Incorrect Cardinality Adjustment caused by Dynamic Statistics

During migration testing to Oracle 12, one of our teams complained about regression of a particular query.
Drilling into what was going on identified some a problem with Adaptive Dynamic Sampling, and certainly not the first one we have encountered.
I will detail the steps to reproduce, and my understanding of what is going on.

create table idxsel as
select rownum id,
dbms_random.value(0, 1000)  r,
rpad('X', 1000, 'X') padding
from dual connect by level <= 10000;

CREATE INDEX idxsel_id ON idxsel(id);
CREATE INDEX idxsel_r_id ON idxsel(r, id);
EXEC DBMS_STATS.GATHER_TABLE_STATS(NULL, 'IDXSEL');

OK, so we have created a table with 10000 rows.  Column id is actually unique (not that the CBO knows this for sure), and column r is a random number between 1 and 1000.

Consider the following query:

SELECT NULL FROM IDXSEL WHERE id = 1 AND r > 500;

Amongst the options available to the optimizer to perform this query are:

  1.  Use a range scan on index IDXSEL_ID to get the enties with id equal to 1 (actually there will just be one), then for every index entry, access the table to eliminate those where r is greater than 500 (fifty percent chance for each entry).
  2. Use a range scan on IDXSEL_R_ID to get the entries those rows with r > 500 (this will match about half of the index entries), then as part of the index access operation, throw away all rows where id is not 1 (which will be all except maybe one row).

Intuitively (to me at least), option 1 is the better plan, and indeed you can see this is what happens without Dynamic Sampling.

SELECT /*+ GATHER_PLAN_STATISTICS DYNAMIC_SAMPLING(0)  */ NULL FROM IDXSEL WHERE id = 1 AND r > 500;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'ALLSTATS LAST'));
-----------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |           |      1 |        |      0 |00:00:00.01 |       3 |
|*  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| IDXSEL    |      1 |      1 |      0 |00:00:00.01 |       3 |
|*  2 |   INDEX RANGE SCAN                  | IDXSEL_ID |      1 |      1 |      1 |00:00:00.01 |       2 |
-----------------------------------------------------------------------------------------------------------

However as soon as we use adaptive dynamic sampling (in the real life case this kicked in because we had a SQL Plan Directive, but for simplicity of the test case I am forcing this with a hint) you can see that option 2 is chosen.
Note, the fact that nearly 10 times the number of buffer gets (28 instead of 3) does tend to confirm that this is not the optimal plan.

SELECT /*+ GATHER_PLAN_STATISTICS DYNAMIC_SAMPLING(11)  */ NULL FROM idxsel WHERE id = 1 AND r > 500;
 SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'ALLSTATS LAST'));
 ------------------------------------------------------------------------------------------
 | Id  | Operation        | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
 ------------------------------------------------------------------------------------------
 |   0 | SELECT STATEMENT |             |      1 |        |      0 |00:00:00.01 |      28 |
 |*  1 |  INDEX RANGE SCAN| IDXSEL_R_ID |      1 |      1 |      0 |00:00:00.01 |      28 |
 ------------------------------------------------------------------------------------------

Drilling into the CBO trace (10053) gives some clues as to what is going on.  Below are some extracts from the trace en evaluating IDXSEL_R_ID.
Firstly without Dynamic Sampling.  ix_sel and ix_sel_with_filters seem correct.
(Note, for more understanding of ix_sel and ix_sel_with_filters refer to Jonathan Lewis’s book Cost-Based Oracle Fundamentals).

****** Costing Index IDXSEL_R_ID
 Access Path: index (IndexOnly)
 Index: IDXSEL_R_ID
 ix_sel: 0.499931  ix_sel_with_filters: 4.9993e-05
 Cost: 27.037320  Resp: 27.037320  Degree: 1

However with Dynamic Sampling ix_sel_with_filters remains correct, but ix_sel is way off.
(Note for full disclosure this behaviour is slightly different on the real life data where I actually saw ix_sel and ix_sel_with_filters being adjusted to the same wrong value).
This brings the cost of this access path down to a value of 2, which causes it to be chosen ahead of that using IDXSEL_ID.

****** Costing Index IDXSEL_R_ID
 >> Index Card adjusted from 4999.313010 to 1.000000 due to adaptive dynamic sampling
 Access Path: index (IndexOnly)
 Index: IDXSEL_R_ID
 ix_sel: 1.0000e-04  ix_sel_with_filters: 4.9993e-05
 Cost: 2.000452  Resp: 2.000452  Degree: 1

Update:

Oracle support have created bug 24596139 – DYNAMIC SAMPLING LEADS TO INCORRECT INDEX SELECTION DUE TO WRONG COST.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s