A fairly important question, and a little surprise, appeared on Oracle-L a couple of days ago. Running 11.2.0.3 a query completed quickly on the first execution then ran very slowly on the second execution because Oracle had used cardinality feedback to change the plan. This shouldn’t really be entirely surprising – if you read all the notes that Oracle has published about cardinality feedback – but it’s certainly a little counter-intuitive.
Of course there are several known bugs related to cardinality feedback that could cause this anomaly to appear – a common complaint seems to relate to views on the right-hand (inner table) side of nested loop joins, and cardinality feedback being used on a table inside the view; but there’s an inherent limitation to cardinality feedback that makes it fairly easy to produce an example of a query doing more work on the second execution.
The limitation is that cardinality feedback generally can’t be used (sensibly) on all the tables where better information is needed. This blog describes the simplest example I can come up with to demonstrate the point. Inevitably it’s a little contrived, but it captures the type of guesswork and mis-estimation that can make the problem appear in real data sets. Here’s the query I’m going to use:
select
t1.n1, t1.n2, t2.n1, t2.n2
from
t1, t2
where
t1.n1 = 0
and t1.n2 = 1000
and t2.id = t1.id
and t2.n1 = 0
and t2.n2 = 400
;
You’ll notice that I’ve got two predicates on both tables so, in the absence of “column-group” extended stats the optimizer will enable cardinality feedback as the query runs to check whether or not its “independent columns” treatment of the predicates gives a suitably accurate estimate of cardinality and a reasonable execution plan. If the estimates are bad enough the optimizer will use information it has gathered as the query ran as an input to re-optimising the query on the next execution.
So here’s the trick. I’m going to set up the data so that there seem to be only two sensible plans: (a) full scan of t1, with nested loop unique index access to t2; (b) full scan of t2, with nested loop unique index access to t1. But I’m going to make sure that the optimizer thinks that (a) is more efficient than (b) by making making the stats look as if (on average) the predicates on t1 should return 100 rows while the predicates on t2 return 200 rows.
On the other hand I’ve set the data up so that (for this specific set of values) t1 returns 1,000 rows which means Oracle will decide that its estimate was so far out that it will re-optimize with 1,000 as the estimated single table access cardinality for t1 – and that means it will decide to do the nested loop from t2 to t1. But what the optimizer doesn’t know (and hasn’t been able to find out by running the first plan) is that with this set of predicates t2 will return 20,000 rows to drive the nested loop into t1 – and the new execution plan will do more buffer gets and use more CPU (and time) than the old plan. Since cardinality feedback is applied only once, the optimizer won’t be able to take advantage of the second execution to change the plan again, or even to switch back to the first plan.
Here’s the setup so you can test the behaviour for yourselves:
rem
rem Script: cardinality_feedback_trap.sql
rem Author: Jonathan Lewis
rem Dated: Oct 2014
rem
rem Last tested
rem 12.1.0.2
rem 11.2.0.4
rem
create table t1
as
with generator as (
select --+ materialize
rownum id
from dual
connect by
level <= 1e4 -- > comment to avoid wordpress format issue
)
select
rownum id,
mod(rownum,2) n1,
mod(rownum,2000) n2, -- 200 rows for each value on average
rpad('x',100) padding
from
generator v1,
generator v2
where
rownum <= 4e5 -- > comment to avoid wordpress format issue
;
alter table t1 add constraint t1_pk primary key(id);
create table t2
as
with generator as (
select --+ materialize
rownum id
from dual
connect by
level <= 1e4 -- > comment to avoid wordpress format issue
)
select
rownum id,
mod(rownum,2) n1,
2 * mod(rownum,1000) n2, -- 400 rows for each value on average, same range as t1
rpad('x',100) padding
from
generator v1,
generator v2
where
rownum <= 4e5 -- > comment to avoid wordpress format issue
;
alter table t2 add constraint t2_pk primary key(id);
begin
dbms_stats.gather_table_stats(
ownname => user,
tabname =>'T1',
method_opt => 'for all columns size 1'
);
dbms_stats.gather_table_stats(
ownname => user,
tabname =>'T2',
method_opt => 'for all columns size 1'
);
end;
/
--
-- Now update both tables to put the data out of sync with the statistics
-- We need a skewed value in t1 that is out by a factor of at least 8 (triggers use of CF)
-- We need a skewed value in t2 that is so bad that the second plan is more resource intensive than the first
--
update t1 set n2 = 1000 where n2 between 1001 and 1019;
update t2 set n2 = 400 where n2 between 402 and 598;
commit;
Here are the execution plans for the first and second executions (with rowsource execution statistics enabled, and the “allstats last” option used in a call to dbms_xplan.display_cursor()).
----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers | Reads |
----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1049 (100)| 0 |00:00:00.36 | 11000 | 6588 |
| 1 | NESTED LOOPS | | 1 | 100 | 1049 (3)| 0 |00:00:00.36 | 11000 | 6588 |
| 2 | NESTED LOOPS | | 1 | 100 | 1049 (3)| 2000 |00:00:00.35 | 9000 | 6552 |
|* 3 | TABLE ACCESS FULL | T1 | 1 | 100 | 849 (4)| 2000 |00:00:00.30 | 6554 | 6551 |
|* 4 | INDEX UNIQUE SCAN | T2_PK | 2000 | 1 | 1 (0)| 2000 |00:00:00.02 | 2446 | 1 |
|* 5 | TABLE ACCESS BY INDEX ROWID| T2 | 2000 | 1 | 2 (0)| 0 |00:00:00.01 | 2000 | 36 |
----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(("T1"."N2"=1000 AND "T1"."N1"=0))
4 - access("T2"."ID"="T1"."ID")
5 - filter(("T2"."N2"=400 AND "T2"."N1"=0))
----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers | Reads |
----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1249 (100)| 0 |00:00:00.66 | 32268 | 1246 |
| 1 | NESTED LOOPS | | 1 | 200 | 1249 (3)| 0 |00:00:00.66 | 32268 | 1246 |
| 2 | NESTED LOOPS | | 1 | 200 | 1249 (3)| 20000 |00:00:00.56 | 12268 | 687 |
|* 3 | TABLE ACCESS FULL | T2 | 1 | 200 | 849 (4)| 20000 |00:00:00.12 | 6559 | 686 |
|* 4 | INDEX UNIQUE SCAN | T1_PK | 20000 | 1 | 1 (0)| 20000 |00:00:00.19 | 5709 | 1 |
|* 5 | TABLE ACCESS BY INDEX ROWID| T1 | 20000 | 1 | 2 (0)| 0 |00:00:00.15 | 20000 | 559 |
----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(("T2"."N2"=400 AND "T2"."N1"=0))
4 - access("T2"."ID"="T1"."ID")
5 - filter(("T1"."N2"=1000 AND "T1"."N1"=0))
Note
-----
- cardinality feedback used for this statement
The second plan does fewer reads because of the buffering side effects from the first plan – but that’s not what the optimizer is looking at. The key feature is that the first plan predicts 100 rows for t1, with 100 starts for the index probe, but discovers 2,000 rows and does 2,000 probes. Applying cardinality feedback the optimizer decides that fetching 200 rows from t2 and probing t1 200 times will be lower cost than running the join the other way round with the 2,000 rows it now knows it will get – but at runtime Oracle actually gets 20,000 rows, does three times as many buffer gets, and spends twice as much time as it did on the first plan.
Hinting
Oracle hasn’t been able to learn (in time) that t2 will supply 20,000 rows – but if you knew this would happen you could use the cardinality() hint to tell the optimizer the truth about both tables /*+ cardinality(t1 2000) cardinality(t2 20000) */ this is the plan you would get:
--------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1698 (100)| 0 |00:00:00.06 | 13109 | 13105 | | | |
|* 1 | HASH JOIN | | 1 | 2000 | 1698 (4)| 0 |00:00:00.06 | 13109 | 13105 | 1696K| 1696K| 1647K (0)|
|* 2 | TABLE ACCESS FULL| T1 | 1 | 2000 | 849 (4)| 2000 |00:00:00.05 | 6554 | 6552 | | | |
|* 3 | TABLE ACCESS FULL| T2 | 1 | 20000 | 849 (4)| 20000 |00:00:00.09 | 6555 | 6553 | | | |
--------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T2"."ID"="T1"."ID")
2 - filter(("T1"."N2"=1000 AND "T1"."N1"=0))
3 - filter(("T2"."N2"=400 AND "T2"."N1"=0))
Unfortunately, unless you have used hints, it doesn’t matter how many times you re-run the query after cardinality feedback has pushed you into the bad plan – it’s not going to change again (unless you mess around flushing the shared_pool or using dbms_shared_pool.purge() to kick out the specific statement).
Upgrade
If you upgrade to 12c the optimizer does a much better job of handling this query – it produces an adaptive execution plan (starting with the nested loop join but dynamically switching to the hash join as the query runs). Here’s the full adaptive plan pulled from memory after the first execution – as you can see both the t1/t2 nested loop and hash joins were considered, then the nested loop was discarded in mid-execution. Checking the 10053 trace file I found that Oracle has set the inflexion point (cross-over from NLJ to HJ) at 431 rows.
----------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1063 (100)| 0 |00:00:00.06 | 13113 | 13107 | | | |
| * 1 | HASH JOIN | | 1 | 100 | 1063 (3)| 0 |00:00:00.06 | 13113 | 13107 | 1519K| 1519K| 1349K (0)|
|- 2 | NESTED LOOPS | | 1 | 100 | 1063 (3)| 2000 |00:00:00.11 | 6556 | 6553 | | | |
|- 3 | NESTED LOOPS | | 1 | 100 | 1063 (3)| 2000 |00:00:00.10 | 6556 | 6553 | | | |
|- 4 | STATISTICS COLLECTOR | | 1 | | | 2000 |00:00:00.09 | 6556 | 6553 | | | |
| * 5 | TABLE ACCESS FULL | T1 | 1 | 100 | 863 (4)| 2000 |00:00:00.08 | 6556 | 6553 | | | |
|- * 6 | INDEX UNIQUE SCAN | T2_PK | 0 | 1 | 1 (0)| 0 |00:00:00.01 | 0 | 0 | | | |
|- * 7 | TABLE ACCESS BY INDEX ROWID| T2 | 0 | 1 | 2 (0)| 0 |00:00:00.01 | 0 | 0 | | | |
| * 8 | TABLE ACCESS FULL | T2 | 1 | 1 | 2 (0)| 20000 |00:00:00.07 | 6557 | 6554 | | | |
----------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T2"."ID"="T1"."ID")
5 - filter(("T1"."N2"=1000 AND "T1"."N1"=0))
6 - access("T2"."ID"="T1"."ID")
7 - filter(("T2"."N2"=400 AND "T2"."N1"=0))
8 - filter(("T2"."N2"=400 AND "T2"."N1"=0))
Note
-----
- this is an adaptive plan (rows marked '-' are inactive)
Footnote:
For reference, here are a couple of the bug (or patch) numbers associated with cardinality feedback:
- Patch 13454409: BAD CARDINALITY FROM FEEDBACK (CFB) ON VIEW RHS OF NLJ
- Bug 16837274 (fixed in 12.2): Bad cost estimate for object on RHS of NLJ
- Bug 12557401: The table that is being incorrectly adjusted is in the right hand side of a nested loops.
- Bug 8521689: Bad cardinality feedback estimate for view on right-hand side of NLJ
Oracle Scratchpad 
Jonathan,
thank you for showing these inherent limits.
it has been said (in a similar context) “Of course, an optimizer that changes its mind every five minutes because of the ongoing acitivity could be more of a threat than a benefit – I think I might favor predicatbility and stability to intermittently failing perfection” – and I have cited that sentence more than once. On the other hand I guess that around 98% of my query optimizations have been based on comparing E-rows to A-rows – so I understand Oracle’s search for adaptive strategies; though I think the currently available features are somehow limited.
Martin
Comment by Martin Preiss — November 5, 2014 @ 8:07 pm GMT Nov 5,2014 |
Fantastic illustration of the limitations of the feature.
Sudden changes in execution plan to a worse plan are one of the biggest issues for sudden erratic performance, missed SLAs, DBA intervention required with baselines and SQL profiles, etc. And cardinality feedback is a significant contributor to that.
There is a huge demand for stability and predictability but understandably we don’t tend to want to go down the route of baselining everything.
If stability is the priority (and it is for most), why not just turn it off? Perhaps with an increasingly long list of features including bind variable peeking, ACS, dynamic sampling, anything with the word adapative in it, auto method opt settings, etc.
I have heard of a few systems who have chosen to go down this route.
Comment by Dom Brooks — November 6, 2014 @ 10:26 am GMT Nov 6,2014 |
Very good example, Jonathan.
One tangental observation I have seen from the BI reporting tool world is that sometimes the slow subsequent query is not down to the database at all, but rather a BI server that serves up reports from its own query cache and is just not up to the job (when compared to the DB platform). That is first execution hits the the DB and is quick, second hits the cache and is slow.
As always when you investigate performance you need to gather the evidence, and with BI an important part of that is where the query is actually executing.
Comment by Peter Scott — November 6, 2014 @ 11:10 am GMT Nov 6,2014 |
a) thanks for this extremely clear rendering of the conundrum
b) your “contrived” case should not be treated as if that adjective is a negative thing: with a minimum of complication it presents an illustrative real case very simply that is usually buried in complicated details in the wild. By stripping it down to the pertinent details you’ve kept the focus on the actual object of consideration. To me that is a hallmark of a JL analysis: let’s not complicate the central issue.
c) I suppose the answer to this conundrum actually is MORE feedback, not less. I propose that cardinality feedback changes (and perhaps all adaptive plan changes) should entail their own “best rejected previous execution” plan and stats (and it damn well should turn on row source executions statistics for the first run of an adaptively supplied plan) so it can only step on this landmine once and revert to the better previous actual plan now rejected and list the new plan as something to not try again. This new “feedback feedback” enhancement probably should come with an opportunity to collect the example for development. And it would address automatically and surgically what otherwise needs to be manually with flush or purge. Of course this is not something we can do now in the face of this conundrum, so your interim solution of cardinality hinting is on target until something similar to what I’ve suggested exists. (Is the current status of the cardinality hint deprecated? As long as it works I don’t really care except for that hint to the current thinking of the optimizer group.)
d) thanks for this extremely clear rendering of the conundrum.
Comment by rsiz — November 6, 2014 @ 1:45 pm GMT Nov 6,2014 |
Thank you for this analysis. I ended up working around this problem with a SQL baseline, and with the hope it does not come up again.
Comment by dbakerber — November 6, 2014 @ 2:55 pm GMT Nov 6,2014 |
Jonathan
Thanks for this nice example and the model I have used to check if the statistics feedback (the new 12c cardinality feedback) will kick in or not.
I started by executing two times the query presented in this blog under the default value of the optimizer_adaptive_reporting_only parameter (FALSE). This is
what I have observed:
SQL_ID c9vqfd6x8bm0x, child number 0 ---------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | ---------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 0 | | * 1 | HASH JOIN | | 1 | 101 | 0 | |- 2 | NESTED LOOPS | | 1 | | 2000 | |- 3 | NESTED LOOPS | | 1 | 101 | 2000 | |- 4 | STATISTICS COLLECTOR | | 1 | | 2000 | | * 5 | TABLE ACCESS FULL | T1 | 1 | 101 | 2000 | |- * 6 | INDEX UNIQUE SCAN | T2_PK | 0 | 1 | 0 | |- * 7 | TABLE ACCESS BY INDEX ROWID| T2 | 0 | 1 | 0 | | * 8 | TABLE ACCESS FULL | T2 | 1 | 1 | 20000 | ---------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("T2"."ID"="T1"."ID") 5 - filter(("T1"."N2"=1000 AND "T1"."N1"=0)) 6 - access("T2"."ID"="T1"."ID") 7 - filter(("T2"."N2"=400 AND "T2"."N1"=0)) 8 - filter(("T2"."N2"=400 AND "T2"."N1"=0)) Note ----- - this is an adaptive plan (rows marked '-' are inactive) SQL> select sql_id, is_reoptimizable, is_resolved_adaptive_plan from v$sql where sql_id = 'c9vqfd6x8bm0x'; SQL_ID I I ------------- - - c9vqfd6x8bm0x Y YAnd this is for the second execution
SQL_ID c9vqfd6x8bm0x, child number 1 ---------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | ---------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 0 | | * 1 HASH JOIN | | 1 | 2000 | 0 | |- 2 | NESTED LOOPS | | 1 | | 2000 | |- 3 | NESTED LOOPS | | 1 | 2000 | 2000 | |- 4 | STATISTICS COLLECTOR | | 1 | | 2000 | | * 5 | TABLE ACCESS FULL | T1 | 1 | 2000 | 2000 | |- * 6 | INDEX UNIQUE SCAN | T2_PK | 0 | | 0 | |- * 7 | TABLE ACCESS BY INDEX ROWID| T2 | 0 | 1 | 0 | | * 8 | TABLE ACCESS FULL | T2 | 1 | 20000 | 20000 | ---------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("T2"."ID"="T1"."ID") 5 - filter(("T1"."N2"=1000 AND "T1"."N1"=0)) 6 - access("T2"."ID"="T1"."ID") 7 - filter(("T2"."N2"=400 AND "T2"."N1"=0)) 8 - filter(("T2"."N2"=400 AND "T2"."N1"=0)) Note ----- - statistics feedback used for this statement - this is an adaptive plan (rows marked '-' are inactive) SQL> select sql_id, child_number, is_reoptimizable, is_resolved_adaptive_plan, is_shareable 2 from v$sql 3 where sql_id = 'c9vqfd6x8bm0x'; SQL_ID CHILD_NUMBER I I I ------------- ------------ - - - c9vqfd6x8bm0x 0 Y Y N c9vqfd6x8bm0x 1 N Y YA new plan has been compiled certainly due to the statistics feedback but probably the 12c adaptive plan kicks in and operated a join switch too.
Then, I’ve decided to suspend the adaptive join and execute two times the same query to see what will happen in such a situation
SQL> alter session set optimizer_adaptive_reporting_only=TRUE; SQL_ID c9vqfd6x8bm0x, child number 2 ------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | ------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 0 | | 1 | NESTED LOOPS | | 1 | | 0 | | 2 | NESTED LOOPS | | 1 | 101 | 2000 | |* 3 | TABLE ACCESS FULL | T1 | 1 | 101 | 2000 | |* 4 | INDEX UNIQUE SCAN | T2_PK | 2000 | 1 | 2000 | |* 5 | TABLE ACCESS BY INDEX ROWID| T2 | 2000 | 1 | 0 | ------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter(("T1"."N2"=1000 AND "T1"."N1"=0)) 4 - access("T2"."ID"="T1"."ID") 5 - filter(("T2"."N2"=400 AND "T2"."N1"=0)) Note ----- - this is an adaptive plan Adaptive plan: ------------- This cursor has an adaptive plan, but adaptive plans are enabled for reporting mode only. The plan that would be executed if adaptive plans were enabled is displayed below. ------------------------------------------- | Id | Operation | Name | Rows | ------------------------------------------- | 0 | SELECT STATEMENT | | | |* 1 | HASH JOIN | | 101 | |* 2 | TABLE ACCESS FULL| T1 | 101 | |* 3 | TABLE ACCESS FULL| T2 | 1 | ------------------------------------------- SQL> select sql_id, child_number, is_reoptimizable, is_resolved_adaptive_plan, is_shareable 2 from v$sql 3 where sql_id = 'c9vqfd6x8bm0x'; SQL_ID CHILD_NUMBER I I I ------------- ------------ - - - c9vqfd6x8bm0x 0 Y Y N c9vqfd6x8bm0x 1 N Y Y c9vqfd6x8bm0x 2 R Y YAs far as the cursor child_number 2 is marked is_reoptimizable = ‘R’ and the child number n°1 is_reoptimizable = ‘N’, statistics feedback will not
kick in as shown below :
-- second execution SQL_ID c9vqfd6x8bm0x, child number 2 ------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | ------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 0 | | 1 | NESTED LOOPS | | 1 | | 0 | | 2 | NESTED LOOPS | | 1 | 101 | 2000 | |* 3 | TABLE ACCESS FULL | T1 | 1 | 101 | 2000 | |* 4 | INDEX UNIQUE SCAN | T2_PK | 2000 | 1 | 2000 | |* 5 | TABLE ACCESS BY INDEX ROWID| T2 | 2000 | 1 | 0 | ------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter(("T1"."N2"=1000 AND "T1"."N1"=0)) 4 - access("T2"."ID"="T1"."ID") 5 - filter(("T2"."N2"=400 AND "T2"."N1"=0)) Note ----- - this is an adaptive plan SQL_ID CHILD_NUMBER I I I ------------- ------------ - - - c9vqfd6x8bm0x 0 Y Y N c9vqfd6x8bm0x 1 N Y Y c9vqfd6x8bm0x 2 R Y YIt seems that cancelling the adaptive join (by changing the default value of optimizer_adaptive_reporting_only parameter) has a good collateral effect by cancelling the statistic feedback feature. I wrote ‘good’ without being sure that the statistics feedback works as its ancestor cardinality feedback
Best regards
Comment by hourim — November 9, 2014 @ 4:32 pm GMT Nov 9,2014 |
Mohamed,
Repeating your tests (with a slight variation) I agree that it looks as if setting optimizer_adaptive_reporting_only = true does disable cardinality (statistics) feedback.
For reference, there is a new hint in 12.1.0.2 /*+ no_adaptive_plan */ which will stop the adaptive mechanism, but still allow statistics feedback to take place so that the second execution produces the hash join with the correct estimates.
Comment by Jonathan Lewis — November 9, 2014 @ 5:21 pm GMT Nov 9,2014 |
[…] Cardinality Feedback – Oracle Scratchpad […]
Pingback by Cardinality feedback — February 5, 2015 @ 3:58 pm GMT Feb 5,2015 |