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:
- 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).
- 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.