Dion Cho – Oracle Performance Storyteller

We are natural born scientists

Archive for the ‘Optimizer’ Category

Trivial Research on the Cardinality Feedback on 11gR2

with 9 comments

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.

  1. Can I reproduce it?
  2. How does it work?
  3. 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.

Written by Dion Cho

December 17, 2009 at 7:45 am

Follow

Get every new post delivered to your Inbox.

Join 58 other followers