Trivial Research on the Cardinality Feedback on 11gR2
These are findings and dicussions on the undocumented cardinality feedback implementation on Oracle 11gR2.
This is a very fantastic but embarassing feature, so I decided to reproduce it myself and have some research on it. I focused on the following questions.
- Can I reproduce it?
- How does it work?
- Can I control it – hidden parameters and/or diagnostic event an/or hints?
And I think I got the right answers for all these questions.
1. Can I reproduce it?
I could reproduce it myself but with the different execution plans from the orignal post. Here is the result.(Sorry for the ugly indents)
UKJA@UKJA1120> select /* GG */ t.year_id, sum(f.metric1) 2 from fact f, time t, dim2 d2, dim3 d3, dim4 d4 3 where f.time_id=t.time_id 4 and f.dim2_id=d2.dim2_id 5 and f.dim3_id1=d3.dim3_id1 6 and f.dim3_id2=d3.dim3_id2 7 and f.dim4_id=d4.dim4_id 8 and d2.dim2_lib='Value 5' 9 and d3.dim3_lib='Value (2,2)' 10 and d4.dim4_l2='L2.1' 11 and attr2='ZZ4' 12 and t.time_id=trunc(t.time_id,'W') 13 group by t.year_id 14 order by t.year_id; YEAR_ID SUM(F.METRIC1) ------------------- -------------- 2009/01/01 00:00:00 38490 -- First execution -------------------------------------------------------------------------------- SQL_ID 956ypc7b6prrj, child number 0 ------------------------------------- Plan hash value: 2090619557 -----------------------------------------------------------------------=------------- | Id | Operation | Name | E-Rows | A-Rows | ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | 1 | | 1 | SORT GROUP BY | | 1 | 1 | | 2 | NESTED LOOPS | | | 42 | | 3 | NESTED LOOPS | | 1 | 56 | | 4 | NESTED LOOPS | | 1 | 56 | | 5 | NESTED LOOPS | | 8 | 560 | | 6 | MERGE JOIN CARTESIAN | | 1 | 60 | | 7 | PARTITION RANGE ALL | | 1 | 60 | |* 8 | TABLE ACCESS FULL | TIME | 1 | 1 | | 9 | BUFFER SORT | | 1 | 60 | |* 10 | TABLE ACCESS FULL | DIM3 | 1 | 1 | | 11 | PARTITION RANGE ITERATOR | | 8 | 560 | | 12 | PARTITION HASH ALL | | 8 | 560 | |* 13 | TABLE ACCESS BY LOCAL INDEX ROWID| FACT | 8 | 560 | | 14 | BITMAP CONVERSION TO ROWIDS | | | 64760 | | 15 | BITMAP AND | | | 120 | |* 16 | BITMAP INDEX SINGLE VALUE | FACT_TIME_IDX | | 120 | |* 17 | BITMAP INDEX SINGLE VALUE | FACT_DIM3_IDX | | 145 | | 18 | PARTITION HASH ITERATOR | | 1 | 56 | |* 19 | TABLE ACCESS BY LOCAL INDEX ROWID | DIM2 | 1 | 56 | |* 20 | INDEX UNIQUE SCAN | DIM2_PK | 1 | 560 | |* 21 | INDEX UNIQUE SCAN | DIM4_PK | 1 | 56 | |* 22 | TABLE ACCESS BY INDEX ROWID | DIM4 | 1 | 42 | ------------------------------------------------------------------------------------- -- Second Exectuion (Note that cardinality feedback worked like the magic!) Plan hash value: 2424032429 ------------------------------------------------------------------------------------ | Id | Operation | Name | E-Rows | A-Rows | ------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | | 1 | | 1 | SORT GROUP BY | | 1 | 1 | | 2 | NESTED LOOPS | | | 3 | | 3 | NESTED LOOPS | | 32 | 4 | | 4 | VIEW | VW_GBC_17 | 3 | 4 | | 5 | HASH GROUP BY | | 3 | 4 | |* 6 | HASH JOIN | | 2823 | 56 | | 7 | PARTITION HASH ALL | | 56 | 1 | |* 8 | TABLE ACCESS FULL | DIM2 | 56 | 1 | | 9 | NESTED LOOPS | | | 560 | | 10 | NESTED LOOPS | | 504 | 64760 | | 11 | MERGE JOIN CARTESIAN | | 60 | 60 | |* 12 | TABLE ACCESS FULL | DIM3 | 1 | 1 | | 13 | BUFFER SORT | | 60 | 60 | | 14 | PARTITION RANGE ALL | | 60 | 60 | |* 15 | TABLE ACCESS FULL | TIME | 60 | 60 | | 16 | PARTITION RANGE ITERATOR | | | 64760 | | 17 | PARTITION HASH ALL | | | 64760 | | 18 | BITMAP CONVERSION TO ROWIDS | | | 64760 | | 19 | BITMAP AND | | | 120 | |* 20 | BITMAP INDEX SINGLE VALUE | FACT_TIME_IDX | | 120 | |* 21 | BITMAP INDEX SINGLE VALUE | FACT_DIM3_IDX | | 145 | |* 22 | TABLE ACCESS BY LOCAL INDEX ROWID| FACT | 8 | 560 | |* 23 | INDEX UNIQUE SCAN | DIM4_PK | 1 | 4 | |* 24 | TABLE ACCESS BY INDEX ROWID | DIM4 | 11 | 3 | ------------------------------------------------------------------------------------- Note ----- - cardinality feedback used for this statement
Very impressing, isn’t it? Oracle introuduced the concept of the cardinality feedback on 10g – as the name of automatic query tuning. It seems that Oracle has determined to apply that technology for the normal query processing.
2. How does it work?
10053 trace shows that Oracle uses OPT_ESTIMATE hint which is the one used exactly by the automatic query tuning engine.
SELECT /*+ OPT_ESTIMATE (TABLE "D4" MIN=42.000000 ) OPT_ESTIMATE (INDEX_SCAN "D4" "DIM4_PK" MIN=56.000000 ) OPT_ESTIMATE (INDEX_FILTER "D4" "DIM4_PK" MIN=56.000000 ) OPT_ESTIMATE (TABLE "D2" MIN=56.000000 ) OPT_ESTIMATE (INDEX_SCAN "D2" "DIM2_PK" MIN=560.000000 ) OPT_ESTIMATE (INDEX_FILTER "D2" "DIM2_PK" MIN=560.000000 ) OPT_ESTIMATE (TABLE "T" ROWS=60.000000 ) OPT_ESTIMATE (TABLE "F" MIN=560.000000 ) */ "T"."YEAR_ID" "YEAR_ID",SUM("F"."METRIC1") "SUM(F.METRIC1)" FROM "UKJA"."FACT" "F","UKJA"."TIME" "T","UKJA"."DIM2" "D2","UKJA"."DIM3" "D3","UKJA"."DIM4" "D4" WHERE "F"."TIME_ID"="T"."TIME_ID" AND "F"."DIM2_ID"="D2"."DIM2_ID" AND "F"."DIM3_ID1"="D3"."DIM3_ID1" AND "F"."DIM3_ID2"="D3"."DIM3_ID2" AND "F"."DIM4_ID"="D4"."DIM4_ID" AND "D2"."DIM2_LIB"='Value 5' AND "D3"."DIM3_LIB"='Value (2,2)' AND "D4"."DIM4_L2"='L2.1' AND "F"."ATTR2"='ZZ4' AND "T"."TIME_ID"=TRUNC("T"."TIME_ID",'W') GROUP BY "T"."YEAR_ID" ORDER BY "T"."YEAR_ID"
A series of OPT_ESTIMATE hint is appended on the fly even before the query transformation is being processed.
If you analyze the numbers used in the hints carefully, you would realize that Oracle uses the actual rows as the cardinality feedback.
It seems that Oracle stores the actual rows for each table/indexe at the first execution and uses them to feedback the cardinality on the next execution. And it also seems that Oracle does so only when there is a great difference between the estimated cardinality and the actual cardinality.
3. Can I control it?
The final question is how we control it. The hidden parameter “_optimizer_use_feedback” is the answer.
By disabling this parameter, I could prevent the cardinality feedback from happening.
alter session set "_optimizer_use_feedback" = false;
This cardinality feedback implementation is quite interesting but the details are not well known. It also has a chance to make many DBAs/developers embarassed.
Let us wait and see how the things go.
Dion Cho - Oracle Performance Storyteller 
Nice! The 2 questions I’m very interested in are:
– what triggers the re-calculation?
– are the execution cardinalities accessible somehow by regular human being (ie me, not Dion or Jonathan ;-) )
Gregory
December 17, 2009 at 8:41 am
Gregory.
First of all, I’m a regular human being, but Jonathan Lewis seems to be not sometimes. :)
I didn’t get the details yet, but my guesses are
– I assume that the big difference between the estimated cardinality and the actual cardinality triggers the re-calculation.
– You can capture the the execution(real) cardinalites through the plan statistics which are populated by the GATHER_PLAN_STATISTICS hint or ALL statistics level. (Is this what you meant?)
Dion Cho
December 17, 2009 at 9:51 am
We may know more about that feature soon.
About the 2nd point, I did not really mean that:
I’ve checked and disabled everything I could, including (1) the gather_plan_statistics hint (2) the statistic level (I left it to typical), (3) the control pack parameter, (4) sql plan monitoring, etc; the feature is still triggered.
My bet (and that’s only a bet; I may be wrong) is that cardinality and some other criteria trigger the plan change. The question is: Would query execution operation cardinality somehow be accessible from outside ? v$sql_plan_statistics after the executions is empty in my case and it sounds, let’s say, “fair”…
arkzoyd
December 17, 2009 at 6:19 pm
It took less time than I had expected!
Check out the document and presentation Greg Rahn points in his comment.
arkzoyd
December 17, 2009 at 6:29 pm
I may suggest seeing the VLDB 2008 paper and presentation “Closing the Query Processing Loop in Oracle 11g”
Greg Rahn
December 17, 2009 at 6:04 pm
Nice thing, but as every automatic mechanism also this one can bring automatic instability in the system. I think it is time now to start installing 11gR2.
Joze Senegacnik
December 17, 2009 at 7:03 pm
The VLDB 2008 paper was exactly the source of this new feature.
To summarize:
– My assumption was right. The difference between the estimated cardinality and real cardinality is the key trigger of the cadinality feedback
– But it seems that there are no exposed views containing the monitoring informations used by the cardinality feedback engine.
– I was curios on how Oracle handles the repeatitive feedback, but the paper clearly says that only the first execution is the target of the feedback, which is very logical.
– Next version(maybe 12g?) would introduce the cardinality feedback on the join which would make this feature more flexible.
Dion Cho
December 18, 2009 at 6:48 am
Dion,
I don’t think the paper completely excludes the possibility of sampling being done on subsequent executions, with the possibility of further re-optimisations.
Note how is says it looks at single table access paths. When you run a query the path you use to get into any specific table may be one that doesn’t give you the “Single Table Access” data set.
If this is the case you could find that the second execution – with a change in execution plan – could give you the actual cardinality from a single table access path that you didn’t get on the first execution – which could give you another re-optimisation.
(Purely hypothetical argument at the moment, of course. When I have a little time I’ll see if I can generate a case to test the idea.)
Regards
Jonathan Lewis
Jonathan Lewis
December 20, 2009 at 6:56 pm
[…] 18-How does auto cardinality feedback works in 11GR2 Dion Cho-Trivial Research on the Cardinality Feedback on 11gR2 […]
Blogroll Report 11/12/2009-18/12/2009 « Coskan’s Approach to Oracle
January 2, 2010 at 5:50 pm