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

Function-based Index and Or-Expansion

with 3 comments

Let me show you a sad story I recently hit at OTN forum.

http://forums.oracle.com/forums/message.jspa?messageID=3661603

  1. They are on standard edition.
  2. Standard edition does not support 1) bitmap index and 2) it’s sister index combination.
  3. They rely on the function-based index.
  4. And query is composed of “OR” predicate.
  5. Most of all, they have no direct control on the SQL text. It’s fixed in the software.

Here let me show you why this is a sad story.

1. Create objects.

drop table t1 purge;

create table t1(c1 int, c2 int, c3 int);

insert into t1 
select level, level, level
from dual
connect by level <= 100000;

create index t1_n1 on t1(c1+1);  -- function-based index
create index t1_n2 on t1(c2+1);  -- function-based index
create index t1_n3 on t1(c1);  -- normal index
create index t1_n4 on t1(c2);  -- normal index

exec dbms_stats.gather_table_stats(user, 't1');

2.With index combination enabled, Oracle builds the most efficient execution plan imagineable.

alter session set "_b_tree_bitmap_plans" = true;

explain plan for
select *
from t1
where c1+1 = 1 or c2+1 = 1
;

-------------------------------------------------------------------------------
| Id  | Operation                        | Name  | Rows  | Bytes | Cost (%CPU)|
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |       |     2 |    48 |     2   (0)|
|   1 |  TABLE ACCESS BY INDEX ROWID     | T1    |     2 |    48 |     2   (0)|
|   2 |   BITMAP CONVERSION TO ROWIDS    |       |       |       |            |
|   3 |    BITMAP OR                     |       |       |       |            |
|   4 |     BITMAP CONVERSION FROM ROWIDS|       |       |       |            |
|*  5 |      INDEX RANGE SCAN            | T1_N1 |       |       |     1   (0)|
|   6 |     BITMAP CONVERSION FROM ROWIDS|       |       |       |            |
|*  7 |      INDEX RANGE SCAN            | T1_N2 |       |       |     1   (0)|
-------------------------------------------------------------------------------
                                                                               
Predicate Information (identified by operation id):                            
---------------------------------------------------                            
                                                                               
   5 - access("C1"+1=1)                                                        
   7 - access("C2"+1=1)                                                        

3. What if the index combination got disabled?

alter session set "_b_tree_bitmap_plans" = false; -- In standard edition, this would be fixed behavior.

explain plan for
select *
from t1
where c1+1 = 1 or c2+1 = 1
;

---------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)|
---------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     2 |    48 |    99   (6)|
|*  1 |  TABLE ACCESS FULL| T1   |     2 |    48 |    99   (6)|
---------------------------------------------------------------
                                                               
Predicate Information (identified by operation id):            
---------------------------------------------------            
                                                               
   1 - filter("C1"+1=1 OR "C2"+1=1)                            

Table full scan!

4. But with normal indexes, Oracle’s choice is Or-Expansion, which is quite natural and efficient.

explain plan for
select *
from t1
where c1 = 1 or c2 = 1
;

---------------------------------------------------------------------------
| Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)|
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |       |     2 |    30 |     4   (0)|
|   1 |  CONCATENATION               |       |       |       |            |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1    |     1 |    15 |     2   (0)|
|*  3 |    INDEX RANGE SCAN          | T1_N4 |     1 |       |     1   (0)|
|*  4 |   TABLE ACCESS BY INDEX ROWID| T1    |     1 |    15 |     2   (0)|
|*  5 |    INDEX RANGE SCAN          | T1_N3 |     1 |       |     1   (0)|
---------------------------------------------------------------------------
                                                                           
Predicate Information (identified by operation id):                        
---------------------------------------------------                        
                                                                           
   3 - access("C2"=1)                                                      
   4 - filter(LNNVL("C2"=1))                                               
   5 - access("C1"=1)                                                      

5. Okay, maybe Oracle has some logic holes with function-based indexes. Would appropriate hints force the Or-Expansion with function-based indexes?

explain plan for
select 
		/*+
				INDEX_RS_ASC(@"SEL$1_2" "T1"@"SEL$1_2" "T1_N1")
				INDEX_RS_ASC(@"SEL$1_1" "T1"@"SEL$1" "T1_N2")
				USE_CONCAT(@"SEL$1" 8) */
		*
from t1
where c1+1 = 1 or c2+1 = 1
;

----------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)|
----------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     2 |    48 |   195   (4)|
|   1 |  CONCATENATION     |      |       |       |            |
|*  2 |   TABLE ACCESS FULL| T1   |     1 |    24 |    98   (5)|
|*  3 |   TABLE ACCESS FULL| T1   |     1 |    24 |    98   (5)|
----------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("C2"+1=1)
   3 - filter("C1"+1=1 AND LNNVL("C2"+1=1))

No.

The question is why this is happening. This is well documented here.
http://download.oracle.com/docs/cd/B14117_01/appdev.101/b10795/adfns_in.htm#1006464

Yes, unfortunately, Oracle is not able to use function-based indexes with or expansion. Hence in this case, full table scan is the only option remaining.

6. There are another trick we can try when the SQL text itself is not modifiable. Stored outline. But in this case, even stored outline cannot change the execution plan to use function-based indexes. Mission impossible.

7. The last trick. Oracle 10g supports the feature called advanced query rewriting, which enables us to change the SQL text on the fly. But it has many restrictions.

  • Enterpnrise edition feature.
  • Select stateme only.
  • Bind variable not supported.

Fortunately, my simple and stupid test case can be resolved with this feature.

begin
  sys.dbms_advanced_rewrite.declare_rewrite_equivalence (
     name           => 'rewrite1',
     source_stmt =>
'select *
from t1
where c1+1 = 1 or c2+1 = 1',
    destination_stmt =>
'select *
from t1
where c1 = 0 or c2 = 0',
     validate       => false,
     rewrite_mode   => 'text_match');
end;
/

alter session set query_rewrite_integrity = trusted;

explain plan for
select *
from t1
where c1+1 = 1 or c2+1 = 1
;
        
---------------------------------------------------------------------------
| Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)|
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |       |     2 |    30 |     4   (0)|
|   1 |  CONCATENATION               |       |       |       |            |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1    |     1 |    15 |     2   (0)|
|*  3 |    INDEX RANGE SCAN          | T1_N4 |     1 |       |     1   (0)|
|*  4 |   TABLE ACCESS BY INDEX ROWID| T1    |     1 |    15 |     2   (0)|
|*  5 |    INDEX RANGE SCAN          | T1_N3 |     1 |       |     1   (0)|
---------------------------------------------------------------------------
                                                                           
Predicate Information (identified by operation id):                        
---------------------------------------------------                        
                                                                           
   3 - access("C2"=0)                                                      
   4 - filter(LNNVL("C2"=0))                                               
   5 - access("C1"=0)                                                      

But, this feature would not be easily adopted in the real life. Too many restrictions.

It was a sad story, which made me think about how to control the execution plan of the non-modifiable SQL.

Written by Dion Cho

July 31, 2009 at 8:59 am

Function based index and suspicious filter predicates

with 3 comments

One of my customers sent a very interesting question.

1. See following result, especially with attention to the filter predicate for the index range scan.

drop table t1 purge;

create table t1 (
	c1 varchar2(10),
	c2 varchar2(10),
	c3 varchar2(10),
	c4 varchar2(10),
	c5 varchar2(10)
);

insert into t1
select mod(level, 2), mod(level, 2), mod(level, 2), mod(level, 2), level
from dual 
connect by level <= 1000
;

exec dbms_stats.gather_table_stats(user, 't1');

create index t1_n1 on t1(c1, c2, nvl(c3,'x'), c4);
  
explain plan for
select /*+ index(t1 t1_n1) */
    *   
from t1 
where c1 = :b1 and c2 = :b2 and nvl(c3,'x') >= :b3 and c4 = :b4 and c5 = :b5
;

--------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |    11 |     3   (0)|
|*  1 |  TABLE ACCESS BY INDEX ROWID| T1    |     1 |    11 |     3   (0)|
|*  2 |   INDEX RANGE SCAN          | T1_N1 |     4 |       |     2   (0)|
--------------------------------------------------------------------------
                                                                           
Predicate Information (identified by operation id):                        
---------------------------------------------------                        
                                                                           
   1 - filter("C5"=:B5)                                                    
   2 - access("C1"=:B1 AND "C2"=:B2 AND NVL("C3",'x')>=:B3 AND "C4"=:B4 AND
              NVL("C3",'x') IS NOT NULL)                                   
       filter("C4"=:B4)                                                    

So far so good. Because the preceding condition is range predicate(NVL(“C3”,’x’)>=:B3), “C4″=:B4 condition is used as the filter predicate.

2. Now I create the 2nd function index. Do you notice the very suspicious filter predicate appended? – SUBSTR(“T1”.”C4″,1,3)=SUBSTR(:B4,1,3). And by virtue of this predicate, the estimated cardinality got down from 4 to 1.

create index t1_n2 on t1(c1, c2, substr(c4,1,3));  -- Note on substr(c4,1,3)!

explain plan for
select /*+ index(t1 t1_n1) */
    *   
from t1 
where c1 = :b1 and c2 = :b2 and nvl(c3,'x') >= :b3 and c4 = :b4 and c5 = :b5
;

--------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |    11 |     3   (0)|
|*  1 |  TABLE ACCESS BY INDEX ROWID| T1    |     1 |    11 |     3   (0)|
|*  2 |   INDEX RANGE SCAN          | T1_N1 |     1 |       |     2   (0)|
--------------------------------------------------------------------------
                                                                           
Predicate Information (identified by operation id):                        
---------------------------------------------------                        
                                                                           
   1 - filter("C5"=:B5)                                                    
   2 - access("C1"=:B1 AND "C2"=:B2 AND NVL("C3",'x')>=:B3 AND "C4"=:B4 AND
              NVL("C3",'x') IS NOT NULL)                                   
       filter("C4"=:B4 AND SUBSTR("T1"."C4",1,3)=SUBSTR(:B4,1,3))          

SUBSTR(“T1”.”C4″,1,3)=SUBSTR(:B4,1,3) predicate? Where does this predicate come from? Why does Oracle use the expression in the 2nd index while using the 1st index?

You might already know the answer – function based index generates hidden column!

3. Diff on the 10053 traces shows the best readibility on this problem.

-- With 1 function index				    -- With 2 function indexes
SINGLE TABLE ACCESS PATH                      | SINGLE TABLE ACCESS PATH                            
  Column (#1): C1(VARCHAR2)                   |   Column (#1): C1(VARCHAR2)                         
    AvgLen: 10.00 NDV: 0 Nulls: 0 Density: 0.0|     AvgLen: 10.00 NDV: 0 Nulls: 0 Density: 0.0000e+0
  Column (#2): C2(VARCHAR2)                   |   Column (#2): C2(VARCHAR2)                         
    AvgLen: 10.00 NDV: 0 Nulls: 0 Density: 0.0|     AvgLen: 10.00 NDV: 0 Nulls: 0 Density: 0.0000e+0
  Column (#4): C4(VARCHAR2)                   |   Column (#7): SYS_NC00007$(VARCHAR2)  NO STATISTICS
    AvgLen: 10.00 NDV: 0 Nulls: 0 Density: 0.0|     AvgLen: 6.00 NDV: 0 Nulls: 0 Density: 0.0000e+00
  Column (#5): C5(VARCHAR2)                   |   Column (#4): C4(VARCHAR2)                         
    AvgLen: 10.00 NDV: 0 Nulls: 0 Density: 0.0|     AvgLen: 10.00 NDV: 0 Nulls: 0 Density: 0.0000e+0
  Column (#6): SYS_NC00006$(VARCHAR2)  NO STAT|   Column (#5): C5(VARCHAR2)                         
    AvgLen: 10.00 NDV: 0 Nulls: 0 Density: 0.0|     AvgLen: 10.00 NDV: 0 Nulls: 0 Density: 0.0000e+0
  Table: T1  Alias: T1                        |   Column (#6): SYS_NC00006$(VARCHAR2)  NO STATISTICS
    Card: Original: 0  Rounded: 1  Computed: 0|     AvgLen: 10.00 NDV: 0 Nulls: 0 Density: 0.0000e+0
kkofmx: index filter:                         |   Table: T1  Alias: T1                              
			"T1"."C4"=:B1 AND                 |     Card: Original: 0  Rounded: 1  Computed: 0.00  N
			"T1"."C5"=:B2 AND                 | kkofmx: index filter:                               
			NVL("T1"."C3",'x')>=:B3           | 		SUBSTR("T1"."C4",1,3)=SUBSTR(:B1,1,3) AND       
  Access Path: index (IndexOnly)              | 		"T1"."C4"=:B2 AND "T1"."C5"=:B3 AND             
    Index: T1_N1                              | 		NVL("T1"."C3",'x')>=:B4                         
    resc_io: 0.00  resc_cpu: 200              | kkofmx: index filter:                               
    ix_sel: 9.0000e-007  ix_sel_with_filters: | 		"T1"."C4"=:B1 AND                               
    Cost: 0.00  Resp: 0.00  Degree: 1         | 		"T1"."C5"=:B2 AND                               
  Best:: AccessPath: IndexRange  Index: T1_N1 | 		NVL("T1"."C3",'x')>=:B3                         
         Cost: 0.00  Degree: 1  Resp: 0.00  Ca|   Access Path: index (IndexOnly)                    
                                              |     Index: T1_N1                                    
                                              |     resc_io: 0.00  resc_cpu: 200                    
                                              |     ix_sel: 9.0000e-007  ix_sel_with_filters: 9.0000
                                              |     Cost: 0.00  Resp: 0.00  Degree: 1               
                                              |   Best:: AccessPath: IndexRange  Index: T1_N1       
                                              |          Cost: 0.00  Degree: 1  Resp: 0.00  Card: 0.
                                              | 
                        

With the 2nd function index, hidden column SYS_NC00007$(SUBSTR(“T1”.”C4″,1,3)) was generated. And this hidden column is used in the predicate because the 1st index includes the real column of the hidden column.

This phenemon would have no problem in general, but the lowered cardinality would cause unwanted result under specific situation.

And Oracle has the kindness to show how exactly it generates the index filter – the power of 10053 trace.

Footnote1: Another interesting info from above 10053 trace is that hidden column doesn’t have any statistics. While Oracle 10g automatically gather statistics with index creation(rebuild), it does not gather statistics for the hidden column.

Written by Dion Cho

July 30, 2009 at 6:06 am

Stored outline bug or enhancement? – Oracle 11g

with 13 comments

One of my colleagues sent me following interesting test case on the stored outline abnormality in Oracle 11g.

1. Create objects

create table t1(c1 int, c2 int);

-- c1 = skewed, c2 = normal
insert into t1
select 1, level
from dual
connect by level <= 10000
union all
select 2, level
from dual
connect by level <= 1000
union all
select 3, level
from dual
connect by level <= 100
union all
select 4, level
from dual
connect by level <= 10
union all
select 5, level
from dual
connect by level <= 1;

create index t1_n1 on t1(c1);
create index t1_n2 on t1(c2);

exec dbms_stats.gather_table_stats(user, 't1', method_opt => 'for columns c1 size skewonly');

2. Here we have a nice execution plan. Both Oracle 10gR2(10.2.0.1) and Oracle 11g(11.1.0.6) show the same plan. Note that Oracle chose the index T1_N1.

explain plan for
select /*+ gather_plan_statistics */ count(*)
from t1
where c1 = 4
and c2 between 1 and 10;

--------------------------------------------------------------------------------------
| Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |       |     1 |     6 |     2   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE              |       |     1 |     6 |            |          |
|*  2 |   TABLE ACCESS BY INDEX ROWID| T1    |     1 |     6 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | T1_N1 |    10 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

3. Create a stored outline(test_outln4) to store the current plan(T1_N1) and let Oracle use it.

create or replace outline test_outln4
on
select /*+ gather_plan_statistics */ count(*)
from t1
where c1 = 4
and c2 between 1 and 10;

alter session set use_stored_outlines = true;

FIY, Oracle stores following hints for the stored outline TEST_OUTLN4.

select hint from user_outline_hints
where name = 'TEST_OUTLN4';

-- Oracle 10.2.0.1
HINT
--------------------------------------------------
INDEX(@"SEL$1" "T1"@"SEL$1" ("T1"."C1"))
OUTLINE_LEAF(@"SEL$1")
ALL_ROWS
OPTIMIZER_FEATURES_ENABLE('10.2.0.1')
IGNORE_OPTIM_EMBEDDED_HINTS

-- Oracle 11.1.0.6
HINT
------------------------------------------------
INDEX_RS_ASC(@"SEL$1" "T1"@"SEL$1" ("T1"."C1"))
OUTLINE_LEAF(@"SEL$1")
ALL_ROWS
DB_VERSION('11.1.0.6')
OPTIMIZER_FEATURES_ENABLE('11.1.0.6')
IGNORE_OPTIM_EMBEDDED_HINTS

4. Here comes the trick. Now I create the 3rd index T1_N3 on t1(c1, c2).

-- t1_n3 index (c1, c2) 
create index t1_n3 on t1(c1, c2);

5. Oracle 10gR2 obeys the stored outline.

explain plan for
select /*+ gather_plan_statistics */ count(*)
from t1
where c1 = 4
and c2 between 1 and 10;
--------------------------------------------------------------------------------------
| Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |       |     1 |     6 |     2   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE              |       |     1 |     6 |            |          |
|*  2 |   TABLE ACCESS BY INDEX ROWID| T1    |     1 |     6 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | T1_N1 |    10 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Note                                               
-----                                              
   - outline "TEST_OUTLN4" used for this statement 

6. But, Oracle 11g? It just ignores me and chooses another index which was created after the outline was created. But it still says that it is using the stored outline I made.

---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |     1 |     6 |     1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |       |     1 |     6 |            |          |
|*  2 |   INDEX RANGE SCAN| T1_N3 |     1 |     6 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------

Note                                               
-----                                              
   - outline "TEST_OUTLN4" used for this statement 

7. The reason? I have no idea at this time but the 10053 trace shows some hints. Oracle 10gR2 clearly says that it is using the INDEX hint given by the stored outline.

BASE STATISTICAL INFORMATION
***********************
Table Stats::
  Table: T1  Alias: T1
    #Rows: 11111  #Blks:  20  AvgRowLen:  6.00
Index Stats::
  Index: T1_N1  Col#: 1
    LVLS: 1  #LB: 22  #DK: 5  LB/K: 4.00  DB/K: 4.00  CLUF: 21.00
    User hint to use this index  -- Look at this part!
  Index: T1_N2  Col#: 2
    LVLS: 1  #LB: 24  #DK: 10000  LB/K: 1.00  DB/K: 1.00  CLUF: 2116.00
  Index: T1_N3  Col#: 1 2
    LVLS: 1  #LB: 28  #DK: 11111  LB/K: 1.00  DB/K: 1.00  CLUF: 19.00

But, Oracle 11g silently ignores the INDEX hint given by the stored outline.

BASE STATISTICAL INFORMATION
***********************
Table Stats::
  Table: T1  Alias: T1
    #Rows: 11111  #Blks:  20  AvgRowLen:  6.00
Index Stats::
  Index: T1_N1  Col#: 1
    LVLS: 1  #LB: 22  #DK: 5  LB/K: 4.00  DB/K: 4.00  CLUF: 21.00
  Index: T1_N2  Col#: 2
    LVLS: 1  #LB: 24  #DK: 10000  LB/K: 1.00  DB/K: 1.00  CLUF: 2116.00
  Index: T1_N3  Col#: 1 2
    LVLS: 1  #LB: 28  #DK: 11111  LB/K: 1.00  DB/K: 1.00  CLUF: 19.00
Access path analysis for T1

Hm… Is this just a bug or another enhancement?

Footnote1: The orignal title was [Stored outline does not work – the stupidity of Oracle 11g], but some reasonable suggestions made me rewrite the title. I did’nt mean to mention the stupdity, but just tried to make an emphasis, which turned out to be a bad trial. :( It seems that I forget my dignity as an Oracle ACE from time to time. Let me write more responsible posts next time!

Footnote2: For the same reason as footnote1, I unapproved some comments including mine to prevent unnecessary arguments from happening. Forgive me for that. It turned out that the ugly post gets the most active responses. :(

Written by Dion Cho

July 24, 2009 at 7:26 am

Posted in Optimizer

Similar cursor sharing and adative cursor sharing

with 8 comments

Similar cursor sharing and multiple child cursors are very common problems as shown here.

What makes people confused about simiar cursor sharing is this.

What the hell does “SIMILAR” mean exactly?

My opinion is…

Even with the tiny chance of the different execution plans, Oracle would deny to share the cursor.

Okay, then, when do we have any tiny chance of different execution plans with literal predicates? Many documents mention only about histogram, but as far as I know, we have more cases.

  1. Histogram predicate
  2. Range predicate
  3. Partition key predicate

Very reasonable, isn’t it?

But the stupidity of similar cursor sharing implementation drives some peolpe crazy. Oracle just spawns as many child cursors as the distinct count of literal value. This means that this SQL statment

alter session set cursor_sharing = similar;
(Assuming the existence of histogram on the column c1)
select * from t1 where c1 = 1;
select * from t1 where c1 = 2;
select * from t1 where c1 = 3;
...
select * from t1 where c1 = 100;



would have 100 child cursors, not just one shared child cursor. This would result in a disaster under certain circumstances.

Following is a simple test case that demonstrates the stupidity of similar cursor sharing.

UKJA@ukja102> create table t1(c1 int, c2 int);

Table created.

Elapsed: 00:00:00.01
UKJA@ukja102> 
UKJA@ukja102> insert into t1
  2  select level, 1
  3  from dual connect by level <= 10000
  4  ;

10000 rows created.

Elapsed: 00:00:00.04
UKJA@ukja102> 
UKJA@ukja102> insert into t1
  2  select level, 2
  3  from dual connect by level <= 1
  4  ;

1 row created.

Elapsed: 00:00:00.01
UKJA@ukja102> 
UKJA@ukja102> create index t1_n1 on t1(c1);

Index created.

Elapsed: 00:00:00.15
UKJA@ukja102> -- Gather histogram
UKJA@ukja102> exec dbms_stats.gather_table_stats(user, 't1', -
> 	  method_opt=>'for all columns size skewonly');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.23
UKJA@ukja102> -- SIMILAR cursor sharing
UKJA@ukja102> alter session set cursor_sharing = similar;

Session altered.

Elapsed: 00:00:00.00
UKJA@ukja102> -- Case1: Range predicate
UKJA@ukja102> select /* range_predicate */ count(c2) from t1 where c1 between 1 and 100;
UKJA@ukja102> select /* range_predicate */ count(c2) from t1 where c1 between 1 and 200;
UKJA@ukja102> select /* range_predicate */ count(c2) from t1 where c1 between 1 and 300;
UKJA@ukja102> select /* range_predicate */ count(c2) from t1 where c1 between 1 and 400;
UKJA@ukja102> select /* range_predicate */ count(c2) from t1 where c1 between 1 and 10000;

UKJA@ukja102> -- Version count is 5 which means no sharing happend
UKJA@ukja102> select sql_text, version_count from v$sqlarea
  2  where sql_text like 'select /* range_predicate */ count(c2) from t1%';

SQL_TEXT                                                                        
--------------------------------------------------------------------------------
VERSION_COUNT                                                                   
-------------                                                                   
select /* range_predicate */ count(c2) from t1 where c1 between :"SYS_B_0" and :
"SYS_B_1"                                                                       
            5    

UKJA@ukja102> -- Case2. Histogram predicate
UKJA@ukja102> select /* histogram */ count(c2) from t1 where c2 = 1;
UKJA@ukja102> select /* histogram */ count(c2) from t1 where c2 = 2;
UKJA@ukja102> select /* histogram */ count(c2) from t1 where c2 = 3;
UKJA@ukja102> select /* histogram */ count(c2) from t1 where c2 = 4;
UKJA@ukja102> select /* histogram */ count(c2) from t1 where c2 = 5;

UKJA@ukja102> -- Version count is 5 which means no sharing happend
UKJA@ukja102> select sql_text, version_count from v$sqlarea
  2  where sql_text like 'select /* histogram */ count(c2) from t1%';

SQL_TEXT                                                                        
--------------------------------------------------------------------------------
VERSION_COUNT                                                                   
-------------                                                                   
select /* histogram */ count(c2) from t1 where c2 = :"SYS_B_0"                  
            5                                                                   

UKJA@ukja102> create table t2(c1 int, c2 int)
  2  partition by list(c1) (
  3  	     partition p1 values (1),
  4  	     partition p2 values (2),
  5  	     partition p3 values (3)
  6  );

Table created.

Elapsed: 00:00:00.11
UKJA@ukja102> 
UKJA@ukja102> insert into t2
  2  select mod(level,3)+1, level
  3  from dual connect by level  
UKJA@ukja102> exec dbms_stats.gather_table_stats(user, 't2');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.15
UKJA@ukja102> 
UKJA@ukja102> alter session set cursor_sharing = similar;

Session altered.

Elapsed: 00:00:00.00
UKJA@ukja102> -- Case3: Partition key predicate
UKJA@ukja102> select /* partition */ count(c2) from t2 where c1 = 1;
UKJA@ukja102> select /* partition */ count(c2) from t2 where c1 = 2;
UKJA@ukja102> select /* partition */ count(c2) from t2 where c1 = 3;
UKJA@ukja102> select /* partition */ count(c2) from t2 where c1 = 4;
UKJA@ukja102> select /* partition */ count(c2) from t2 where c1 = 5;

UKJA@ukja102> -- Version count is 5 which means no sharing happend
UKJA@ukja102> select sql_text, version_count from v$sqlarea
  2  where sql_text like 'select /* partition */ count(c2) from t2%';

SQL_TEXT                                                                        
--------------------------------------------------------------------------------
VERSION_COUNT                                                                   
-------------                                                                   
select /* partition */ count(c2) from t2 where c1 = :"SYS_B_0"                  
            5                                                                   
            





Do you agree with me on the stupidity of SIMILAR cursor sharing implementation?

By the way, the only positive way to handle this problem is introduced as of 11g in the name of adaptive cursor sharing. The funny thing is we don’t need SIMILAR cursor sharing any more. FORCE sharing + adaptive cursor sharing would solve all these problems.

Following simple test case shows that SIMILAR cursor sharing is useless with adaptive cursor sharing. FORCE cursor sharing is now superior to SIMILAR cursor sharing!

select /* partition */ count(c2) from t2 where c1 = 1;
select /* partition */ count(c2) from t2 where c1 = 2;
select /* partition */ count(c2) from t2 where c1 = 3;
select /* partition */ count(c2) from t2 where c1 = 4;
select /* partition */ count(c2) from t2 where c1 = 5;

alter session set cursor_sharing = similar;

UKJA@ukja116> select sql_text, version_count from v$sqlarea
  2  where sql_text like 'select /* partition */ count(c2) from t2%';

SQL_TEXT
--------------------------------------------------------------------------------
VERSION_COUNT
-------------
select /* partition */ count(c2) from t2 where c1 = :"SYS_B_0"
            5

alter session set cursors_sharing = force;

UKJA@ukja116> select sql_text, version_count from v$sqlarea
  2  where sql_text like 'select /* partition */ count(c2) from t2%';

SQL_TEXT
--------------------------------------------------------------------------------
VERSION_COUNT
-------------
select /* partition */ count(c2) from t2 where c1 = :"SYS_B_0"
            2

UKJA@ukja116> @shared_cursor 'select /* partition */ count(c2) from t2%'
UKJA@ukja116> set echo off
old  14:           and q.sql_text like ''&1''',
new  14:           and q.sql_text like ''select /* partition */ count(c2) from t2%''',
SQL_TEXT                       = select /* partition */ count(c2) from t2 where
c1 = :"SYS_B_0"
SQL_ID                         = 5n1vgm2quammk
ADDRESS                        = 2F63F08C
CHILD_ADDRESS                  = 2B78CBE4
CHILD_NUMBER                   = 0
LOAD_OPTIMIZER_STATS           = Y
--------------------------------------------------
SQL_TEXT                       = select /* partition */ count(c2) from t2 where
c1 = :"SYS_B_0"
SQL_ID                         = 5n1vgm2quammk
ADDRESS                        = 2F63F08C
CHILD_ADDRESS                  = 262D3680
CHILD_NUMBER                   = 1
BIND_MISMATCH                  = Y
--------------------------------------------------



Adaptive cursor sharing really looks like adaptive and more powerful than any other previous implementations on the cursor sharing. Looks like prominent new feature, but let’s see what happens in the real production system.

Written by Dion Cho

June 1, 2009 at 2:41 am

Scalar Subquery and Complex View Merging Disabled

leave a comment »

One of my customers called me due to the query performance degradation. They just added simple scalar subqueries to the original query and suddenly got a problem.

It was a traditional view merging problem. Scalar subquery simply made the complex view merging disabled and the execution plan got twisted.

Simple and clear demonstration.

1. Create objects

create table t1(c1 int, c2 int);
create table t2(c1 int, c2 int);
create table t3(c1 int, c2 int);

create index t1_n1 on t1(c1);
create index t2_n1 on t2(c1);
create index t3_n1 on t3(c1);

create or replace function func1(v1 int)
return int
is
begin
  return v1+1;
end;
/

show err

insert into t1 select level, level from dual connect by level <= 10000;
insert into t2 select level, level from dual connect by level <= 10000;
insert into t3 select mod(level, 10), level from dual connect by level <= 10000;




2. This is the origial query whose complex view is successfully merged.

select /*+ gather_plan_statistics */ 
  t2.c1, v.c2+1
from 
  (select c1, sum(c2) as c2 from t1 where c1 between 1 and 100 group by c1) v,
  t2
where
  v.c1 = t2.c1
;

-----------------------------------------------------------------------------
| Id  | Operation                     | Name  | A-Rows | Buffers | Used-Mem |
-----------------------------------------------------------------------------
|   1 |  HASH GROUP BY                |       |    100 |       5 |          |
|*  2 |   HASH JOIN                   |       |    100 |       5 | 1162K (0)|
|   3 |    TABLE ACCESS BY INDEX ROWID| T1    |    100 |       3 |          |
|*  4 |     INDEX RANGE SCAN          | T1_N1 |    100 |       2 |          |
|*  5 |    INDEX RANGE SCAN           | T2_N1 |    100 |       2 |          |
-----------------------------------------------------------------------------




3. Oracle suddenly refused to merge the view and the performance degraded. All they’ve done is just to add scalar subqueries to the outer query block

select /*+ gather_plan_statistics */ 
  t2.c1, (select func1(v.c2) from dual) as c2
from 
  (select c1, sum(c2) as c2 from t1 where c1 between 1 and 100 group by c1) v,
  t2
where
  v.c1 = t2.c1
;

------------------------------------------------------------------------------
| Id  | Operation                      | Name  | A-Rows | Buffers | Used-Mem |
------------------------------------------------------------------------------
|   1 |  FAST DUAL                     |       |    100 |       0 |          |
|*  2 |  HASH JOIN                     |       |    100 |      34 | 1142K (0)|
|   3 |   VIEW                         |       |    100 |       3 |          |
|   4 |    HASH GROUP BY               |       |    100 |       3 |          |
|   5 |     TABLE ACCESS BY INDEX ROWID| T1    |    100 |       3 |          |
|*  6 |      INDEX RANGE SCAN          | T1_N1 |    100 |       2 |          |
|   7 |   INDEX FAST FULL SCAN         | T2_N1 |  10000 |      31 |          |
------------------------------------------------------------------------------





Because the view merging has failed, the efficient predicates(between 1 and 100) could not be applied to the table T2. Hence bad execution plan.

Following is excerpt from 10053 trace file. Simply saying that Oracle igonres the CVM with scalar subquery in outer query.

*******************************
Cost-Based Complex View Merging
*******************************
CVM: Finding query blocks in SEL$1 (#1) that are valid to merge.
CVM:   Checking validity of merging SEL$3 (#3)
CVM:     CVM bypassed: Outer query has select list subquery.




4. Okay, then, what can we do? Manual query transformation in need. I hate this, but there are tons of cases that we should play over the CBO. One simple way is to pull out the scalar subquery by increasing the view depth like this.

-- rewrite to enable view merge
select /*+ gather_plan_statistics */
  x.*, (select func1(x.c2) from dual) as c2
from (
  select /*+ no_merge */
    t2.c1, v.c2
  from 
    (select c1, sum(c2) as c2 from t1 where c1 between 1 and 100 group by c1) v,
    t2
  where
    v.c1 = t2.c1
) x
;

------------------------------------------------------------------------------
| Id  | Operation                      | Name  | A-Rows | Buffers | Used-Mem |
------------------------------------------------------------------------------
|   1 |  FAST DUAL                     |       |    100 |       0 |          |
|   2 |  VIEW                          |       |    100 |       5 |          |
|   3 |   HASH GROUP BY                |       |    100 |       5 |          |
|*  4 |    HASH JOIN                   |       |    100 |       5 | 1145K (0)|
|   5 |     TABLE ACCESS BY INDEX ROWID| T1    |    100 |       3 |          |
|*  6 |      INDEX RANGE SCAN          | T1_N1 |    100 |       2 |          |
|*  7 |     INDEX RANGE SCAN           | T2_N1 |    100 |       2 |          |
------------------------------------------------------------------------------



Another simple way is to replace the scalar subquery with function.

select /*+ gather_plan_statistics */ 
  t2.c1, func1(v.c2) as c2
from 
  (select c1, sum(c2) as c2 from t1 where c1 between 1 and 100 group by c1) v,
  t2
where
  v.c1 = t2.c1
;



Many of you would not like the function-version because of its performance overhead and read consistency problem.

I can make another simple test case to have performance boost by disabling complex view merging.
– Following is just an unfair and stupid test case, so do not make me nervous by pointing out that USE_NL hint should not be used. :)

-- Bad peformance with CVM enabled
select /*+ gather_plan_statistics use_nl(v t2) */ 
  t2.c1, v.c2+1
from 
  (select c1, sum(c2) as c2 from t3 group by c1) v,
  t2
where
  v.c1 = t2.c1
;

--------------------------------------------------------
| Id  | Operation           | Name  | A-Rows | Buffers |
--------------------------------------------------------
|   1 |  HASH GROUP BY      |       |      9 |   10025 |
|   2 |   NESTED LOOPS      |       |   9000 |   10025 |
|   3 |    TABLE ACCESS FULL| T3    |  10000 |      23 |
|*  4 |    INDEX RANGE SCAN | T2_N1 |   9000 |   10002 |
--------------------------------------------------------

-- But good performance with CVM disabled.                                                        
select /*+ gather_plan_statistics use_nl(v t2) */
  t2.c1, (select func1(v.c2) from dual) as c2
from 
  (select c1, sum(c2) as c2 from t3 group by c1) v,
  t2
where
  v.c1 = t2.c1
;

---------------------------------------------------------
| Id  | Operation            | Name  | A-Rows | Buffers |
---------------------------------------------------------
|   1 |  FAST DUAL           |       |      9 |       0 |
|   2 |  NESTED LOOPS        |       |      9 |      36 |
|   3 |   VIEW               |       |     10 |      23 |
|   4 |    HASH GROUP BY     |       |     10 |      23 |
|   5 |     TABLE ACCESS FULL| T3    |  10000 |      23 |
|*  6 |   INDEX RANGE SCAN   | T2_N1 |      9 |      13 |
---------------------------------------------------------





It is sometimes amazing how stupid Oracle is especially at the query transformation. Some rules and restrictions look unfair and even the proud CBQT(Cost Based Query Transformation) still makes us crazy.

Well… but what else can we do? We just need to a little bit outsmart Oracle. Good luck!

Written by Dion Cho

April 17, 2009 at 3:05 pm

Join Predicate Pushing and Group By

with 5 comments

A couple of days ago, I got asked following question.

I have following query.

select t1.c1, t1.c2, v.*
from t1, (select c1, sum(c2), sum(c3) from t2 group by c1) v
where t1.c1 = v.c1(+) and t1.c2 in (1, 2)
;



The problem is that I have join predicate pushing disabled. Thus the pagination is hard to achieve. Why and any solution?

Okay, simple but interesting question.

1. What is join predicate pushing(a.k.a JPPD)?
JPPD means literally pushing the join predicate into the view. See following example.

UKJA@ukja116> explain plan for
  2  select
  3    t1.c1, t1.c2, v.*
  4  from t1,
  5  	 (select /*+ no_merge index(t2) */ c1, c2, c3
  6  	   from t2
  7  	   --group by c1
  8  	   ) v
  9  where
 10    t1.c1 = v.c1(+)
 11    and t1.c2 in (1, 2)
 12  ;

-------------------------------------------------------
| Id  | Operation                     | Name  | Rows  |
-------------------------------------------------------
|   0 | SELECT STATEMENT              |       |  2000 |
|   1 |  NESTED LOOPS OUTER           |       |  2000 |
|*  2 |   TABLE ACCESS FULL           | T1    |    80 |
|   3 |   VIEW PUSHED PREDICATE       |       |     1 |
|   4 |    TABLE ACCESS BY INDEX ROWID| T2    |    25 |
|*  5 |     INDEX RANGE SCAN          | PK_T2 |    25 |
-------------------------------------------------------
                                                       
Predicate Information (identified by operation id):    
---------------------------------------------------    
                                                       
   2 - filter("T1"."C2"=1 OR "T1"."C2"=2)              
   5 - access("C1"="T1"."C1")                          



Here you can see that the join predicate(t1.c1 = v.c1(+)) is pushed into the inline view. This technique is extremely useful for pagination queries.

Note that JPPD works only with nested loop join. It’s very logical restriction.

2. When do we have JPPD enabled?
At the first step, Oracle tries to merge the view. This is called view merging. When the view merging is not possible, Oracle tries to push the predicates into the view. Oracle pushes not only simple predicates, but also join predicates. PUSH_PRED hint controls the join predicate pushing.

Oracle denies to push join predicate when the view has GROUP BY, DISTINCT expression or ANTI/SEMI join, just because it’s too complex to push the join predicate with those ones.

Fortunately, this restriction has gone with 11g. This is sometimes called extended JPPD and controlled by _optimizer_extend_jppd_view_types(whose default value is TRUE) parameter.

3. Your explanation is boring. Any clear test case?
For those who want live demo, not boring and dead verbal explanation(like me!), here goes a very simple demonstration.

#1. Create tables.

UKJA@ukja102> create table t1(c1, c2)
  2  as select level, mod(level, 100)
  3  from dual
  4  connect by level <= 4000
  5  ;

Table created.

UKJA@ukja102> create table t2(c1, c2, c3)
  2  as select mod(level, 4000), level, level
  3  from dual
  4  connect by level <= 100000
  5  ;

Table created.

UKJA@ukja102> alter table t1 add constraint pk_t1 primary key (c1);

Table altered.

UKJA@ukja102> alter table t2 add constraint pk_t2 primary key (c1, c2);

Table altered.

UKJA@ukja102> exec dbms_stats.gather_table_stats(user, 't1', no_invalidate=>false);

PL/SQL procedure successfully completed.

UKJA@ukja102> exec dbms_stats.gather_table_stats(user, 't2', no_invalidate=>false);



#2. Oracle 10g fails at JPPD with GROUP BY inside the view.

UKJA@ukja102> explain plan for
  2  select
  3    t1.c1, t1.c2, v.*
  4  from t1,
  5      (select /*+ no_merge index(t2) */ c1, sum(c2), max(c3)
  6        from t2
  7        group by c1) v
  8  where
  9    t1.c1 = v.c1(+)
 10    and t1.c2 in (1, 2)
 11  ;

----------------------------------------------------------------
| Id  | Operation                      | Name  | Rows  | Bytes |
----------------------------------------------------------------
|   0 | SELECT STATEMENT               |       |    80 |  3600 |
|*  1 |  HASH JOIN OUTER               |       |    80 |  3600 |
|*  2 |   TABLE ACCESS FULL            | T1    |    80 |   480 |
|   3 |   VIEW                         |       |  4020 |   153K|
|   4 |    HASH GROUP BY               |       |  4020 | 52260 |
|   5 |     TABLE ACCESS BY INDEX ROWID| T2    |   100K|  1269K|
|   6 |      INDEX FULL SCAN           | PK_T2 |   100K|       |
----------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T1"."C1"="V"."C1"(+))
   2 - filter("T1"."C2"=1 OR "T1"."C2"=2)                               



10053 trace simply says, “I cannot push join predicates because of GROUP BY!”.

JPPD:   Checking validity of push-down from SEL$1 (#1) to SEL$2 (#2)
JPPD:     JPPD bypassed: View contains a group by.


#3. But Oracle 11g has no such restriction!

UKJA@ukja116> explain plan for
  2  select
  3    t1.c1, t1.c2, v.*
  4  from t1,
  5      (select /*+ no_merge index(t2) */ c1, sum(c2), max(c3)
  6        from t2
  7        group by c1) v
  8  where
  9    t1.c1 = v.c1(+)
 10    and t1.c2 in (1, 2)
 11  ;

-----------------------------------------------------------------
| Id  | Operation                       | Name  | Rows  | Bytes |
-----------------------------------------------------------------
|   0 | SELECT STATEMENT                |       |    80 |  2560 |
|   1 |  NESTED LOOPS OUTER             |       |    80 |  2560 |
|*  2 |   TABLE ACCESS FULL             | T1    |    80 |   480 |
|   3 |   VIEW PUSHED PREDICATE         |       |     1 |    26 |
|*  4 |    FILTER                       |       |       |       |
|   5 |     SORT AGGREGATE              |       |     1 |    13 |
|   6 |      TABLE ACCESS BY INDEX ROWID| T2    |    25 |   325 |
|*  7 |       INDEX RANGE SCAN          | PK_T2 |    25 |       |
-----------------------------------------------------------------
                                                                 
Predicate Information (identified by operation id):              
---------------------------------------------------              
                                                                 
   2 - filter("T1"."C2"=1 OR "T1"."C2"=2)                        
   4 - filter(COUNT(*)>0)                                        
   7 - access("C1"="T1"."C1")                                    



Note that Oracle chose NESTED LOOP OUTER join over HASH OUTER JOIN. JPPD works only with nested loop join!

#4. In Oracle 10g, you can emulate JPPD like this.

select
  c1, c2,
  (select sum(c2) from t2 where t2.c1 = t1.c1),
  (select sum(c3) from t2 where t2.c1 = t1.c1)
from 
  t1
where
  c2 in (1, 2)
;

-- or

select
  c1, c2,
  cursor(select sum(c2), sum(c3) from t2 where t2.c1 = t1.c1)
from 
  t1
where
  c2 in (1, 2)
;






It’s sometimes amazing how Oracle transforms the query. At the same time, it’s very disappointing why Oracle didn’t do it a bit earlier. :)

Written by Dion Cho

April 12, 2009 at 2:28 pm

Follow

Get every new post delivered to your Inbox.

Join 68 other followers