Dion Cho – Oracle Performance Storyteller

We are natural born scientists

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: