Dion Cho – Oracle Performance Storyteller

We are natural born scientists

Posts Tagged ‘view merging

Small Change, Big Result – Query transformation problem

with one comment

Oracle has a quite stable optimizer, but sometimes reveals it’s vulnerability in a surprising manner.

For instance, small change(to us, not to Oracle) on the predicates results to be a disastrous result. Following is a stereotypical example.

UKJA@ukja102> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

UKJA@ukja102> create table t1(c1 varchar2(10), c2 int, c3 int, c4 int)
  2  partition by list (c1) (
  3    partition p1 values ('A'),
  4    partition p2 values ('B')
  5  )
  6  ;

Table created.

UKJA@ukja102> create table t2(c1 varchar2(10), c2 int, c3 int, c4 int)
  2  partition by list (c1) (
  3    partition p1 values ('A'),
  4    partition p2 values ('B')
  5  )
  6  ;

Table created.

UKJA@ukja102> insert into t1
  2  --select decode(mod(level,2),0,'A','B'), level, level, level
  3  select 'A', level, level, level
  4  from dual connect by level <= 1000
  5  ;

1000 rows created.

UKJA@ukja102> insert into t2
  2  --select decode(mod(level,2),0,'A','B'), level, level, level
  3  select 'A', level, level, level
  4  from dual connect by level <= 1000
  5  ;

1000 rows created.

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

PL/SQL procedure successfully completed.

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

PL/SQL procedure successfully completed.

I’m merging table t1 into t2 as following. Take a closer look at the execution plan with hash outer join which seems very reasonable.

UKJA@ukja102> explain plan for
2  merge
3  into t2
4  using (select * from t1 where c1 = 'A') x
5  on (x.c1 = t2.c1 and x.c2 = t2.c2 and x.c3 = t2.c3
6      and t2.c1 = 'A')
7  when matched then
8    update set t2.c4 = x.c4
9  when not matched then
10    insert (t2.c1, t2.c2, t2.c3, t2.c4)
11    values (x.c1, x.c2, x.c3, x.c4)
12  ;

Explained.

UKJA@ukja102> select * from table(dbms_xplan.display);
-------------------------------------------------------------------------------------------------
| Id  | Operation                | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------
|   0 | MERGE STATEMENT          |      |  1000 | 72000 |     7  (15)| 00:00:01 |       |       |
|   1 |  MERGE                   | T2   |       |       |            |          |       |       |
|   2 |   VIEW                   |      |       |       |            |          |       |       |
|*  3 |    HASH JOIN OUTER       |      |  1000 | 26000 |     7  (15)| 00:00:01 |       |       |
|   4 |     PARTITION LIST SINGLE|      |  1000 | 13000 |     3   (0)| 00:00:01 |     1 |     1 |
|   5 |      TABLE ACCESS FULL   | T1   |  1000 | 13000 |     3   (0)| 00:00:01 |     1 |     1 |
|   6 |     PARTITION LIST SINGLE|      |  1000 | 13000 |     3   (0)| 00:00:01 |     1 |     1 |
|   7 |      TABLE ACCESS FULL   | T2   |  1000 | 13000 |     3   (0)| 00:00:01 |     1 |     1 |
-------------------------------------------------------------------------------------------------

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

3 - access("T1"."C3"="T2"."C3"(+) AND "T1"."C2"="T2"."C2"(+) AND
"T1"."C1"="T2"."C1"(+))

20 rows selected.

Now I want change my predicate to just a little bit complex one. Exactly speaking from equality predicates to inlist predicates. But due to this small change, we have a very surprising effect on execution plan.

Oracle chose “nested loops outer” join over “hash outer join”.

UKJA@ukja102> explain plan for
2  merge
3  into t2
4  using (select * from t1 where c1 in ('A', 'X', 'X', 'X')) x
5  on (x.c1 = t2.c1 and x.c2 = t2.c2 and x.c3 = t2.c3
6      and t2.c1 in ('A', 'X', 'X'))
7  when matched then
8    update set t2.c4 = x.c4
9  when not matched then
10    insert (t2.c1, t2.c2, t2.c3, t2.c4)
11    values (x.c1, x.c2, x.c3, x.c4)
12  ;

Explained.

UKJA@ukja102> select * from table(dbms_xplan.display);
-------------------------------------------------------------------------------------------------
| Id  | Operation                | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------
|   0 | MERGE STATEMENT          |      |  1000 | 72000 |  3034   (2)| 00:00:37 |       |       |
|   1 |  MERGE                   | T2   |       |       |            |          |       |       |
|   2 |   VIEW                   |      |       |       |            |          |       |       |
|   3 |    NESTED LOOPS OUTER    |      |  1000 | 71000 |  3034   (2)| 00:00:37 |       |       |
|   4 |     PARTITION LIST INLIST|      |  1000 | 13000 |     3   (0)| 00:00:01 |KEY(I) |KEY(I) |
|   5 |      TABLE ACCESS FULL   | T1   |  1000 | 13000 |     3   (0)| 00:00:01 |KEY(I) |KEY(I) |
|   6 |     PARTITION LIST INLIST|      |     1 |    58 |     3   (0)| 00:00:01 |KEY(I) |KEY(I) |
|   7 |      VIEW                |      |     1 |    58 |     3   (0)| 00:00:01 |       |       |
|*  8 |       TABLE ACCESS FULL  | T2   |     1 |    13 |     3   (0)| 00:00:01 |KEY(I) |KEY(I) |
-------------------------------------------------------------------------------------------------

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

8 - filter("T1"."C2"="T2"."C2" AND "T1"."C3"="T2"."C3" AND ("T1"."C1"='A' OR
"T1"."C1"='X') AND "T1"."C1"="T2"."C1")

21 rows selected.

In this case, nested loops outer join is far more inefficient than hash outer join. For instance, cost increased to 3034 from original 7. The actual workloads would be a nightmare.

What’s happening here? 10053 trace would help. As of 10gR2, Oracle records the whole process of query transformation in addition to traditional optimizer process.

In the above queries, Oracle should transform the select part of the merge statement into outer join query(this is how merge works). From 10053 trace, I’ve found that Oracle transformed the queries in very different ways for above 2 cases.

1. Query with equality predicates (t2.c1 = ‘A’)

SELECT /*+ NO_MERGE */ "T2".ROWID "ROWID","T2"."C1" "C1","T2"."C2" "C2",
"T2"."C3" "C3","T2"."C4" "C4","T1"."C1" "C1","T1"."C2" "C2","T1"."C3" "C3",
"T1"."C4" "C4"
FROM "UKJA"."T1" "T1","UKJA"."T2" "T2"
WHERE "T1"."C3"="T2"."C3"(+) AND "T1"."C2"="T2"."C2"(+)
AND "T1"."C1"="T2"."C1"(+) AND "T2"."C1"(+)='A' AND "T1"."C1"='A'

2. Query with inlist predicates(t2.c1 in (‘A’, ‘X’, X’))

SELECT /*+ NO_MERGE */ "from$_subquery$_007"."ROWID_4" "ROWID",
"from$_subquery$_007"."C1_0" "C1","from$_subquery$_007"."C2_1" "C2",
"from$_subquery$_007"."C3_2" "C3","from$_subquery$_007"."C4_3" "C4",
"T1"."C1" "C1","T1"."C2" "C2","T1"."C3" "C3","T1"."C4" "C4"
FROM "UKJA"."T1" "T1",
 LATERAL( (SELECT "T2"."C1" "C1_0","T2"."C2" "C2_1","T2"."C3" "C3_2",
      "T2"."C4" "C4_3","T2".ROWID "ROWID_4"
      FROM "UKJA"."T2" "T2"
      WHERE "T1"."C1"="T2"."C1" AND "T1"."C2"="T2"."C2" AND
           "T1"."C3"="T2"."C3" AND
           ("T2"."C1"='A' OR "T2"."C1"='X' OR "T2"."C1"='X')))(+)
"from$_subquery$_007"
WHERE "T1"."C1"='A' OR "T1"."C1"='X' OR "T1"."C1"='X' OR "T1"."C1"='X'

See the difference?

  1. Inlist predicates are converted to OR predicates, which is quite natural.
  2. Oracle failed at merging the internal subquery due to the OR predicates. Even MERGE hint couldn’t force the view merging. It’s very common for Oracle to ignore efficient query transformation with OR predicates involved.
  3. With view merge disabled, we’re left with correlated subquery.
  4. There is no choice but to nested loops join due to the complex correlated subquery.
  5. The result is a very inefficient nested loops outer join between the 2 tables.

Aha! This is the stereotypical query transformation problem! One of the solutions is to remove redundant inlist predicate on table t2 as following:

UKJA@ukja102> explain plan for
2  merge
3  into t2
4  using (select * from t1 where c1 in ('A', 'X', 'X', 'X')) x
5  on (x.c1 = t2.c1 and x.c2 = t2.c2 and x.c3 = t2.c3)
6  when matched then
7    update set t2.c4 = x.c4
8  when not matched then
9    insert (t2.c1, t2.c2, t2.c3, t2.c4)
10    values (x.c1, x.c2, x.c3, x.c4)
11  ;

Explained.

UKJA@ukja102> select * from table(dbms_xplan.display);
------------------------------------------------------------------------------------------------
| Id  | Operation               | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------
|   0 | MERGE STATEMENT         |      |  1000 | 72000 |     7  (15)| 00:00:01 |       |       |
|   1 |  MERGE                  | T2   |       |       |            |          |       |       |
|   2 |   VIEW                  |      |       |       |            |          |       |       |
|   3 |    PARTITION LIST INLIST|      |  1000 | 26000 |     7  (15)| 00:00:01 |KEY(I) |KEY(I) |
|*  4 |     HASH JOIN OUTER     |      |  1000 | 26000 |     7  (15)| 00:00:01 |       |       |
|   5 |      TABLE ACCESS FULL  | T1   |  1000 | 13000 |     3   (0)| 00:00:01 |KEY(I) |KEY(I) |
|   6 |      TABLE ACCESS FULL  | T2   |  1000 | 13000 |     3   (0)| 00:00:01 |KEY(I) |KEY(I) |
------------------------------------------------------------------------------------------------

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

4 - access("T1"."C1"="T2"."C1"(+) AND "T1"."C3"="T2"."C3"(+) AND
"T1"."C2"="T2"."C2"(+))

19 rows selected.

And don’t forget 10053 trace. It’s an ultimate optimizer troubleshooting machine.

PS) Vist the OTN forum for the whole story.

Written by Dion Cho

March 2, 2009 at 6:23 am

The Pitfall of Cursor Expression

with one comment

Cursor expression is one of the powerful but forgotten features of Oracle query. It has following syntax.

select c1, c2,
   cursor(select * from t2 where t2 where c1 = t1.c1)
from t1;

Cursor expression is a very handy way of generating multidimensional output from RDBMS. But you should recognize following pitfalls and avoid them if necessary. The pitfalls of cursor expression are

  1. View merging is disabled(as pointed out at http://download.oracle.com/docs/cd/B10500_01/server.920/a96533/opt_ops.htm)
  2. You could have excessive calls.
  3. Some confusion with sql_trace and plan statistics.

I would demonstrate above pitfalls with very simple test cases. Looks long, but you will find it very interesting.

1. Query without cursor expression. Note that inline view is successfully merged.

select /* without_cursor */
  t1.c1, x.c2
from
  t1, (select c1, c2 from t2) x
where
  t1.c1 = x.c1
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        8      0.00       0.00          0         31          0         100
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       10      0.00       0.00          0         31          0         100

Rows     Row Source Operation
-------  ---------------------------------------------------
    100  TABLE ACCESS BY INDEX ROWID T2 (cr=31 pr=0 pw=0 time=6620 us)
    201   NESTED LOOPS  (cr=23 pr=0 pw=0 time=4782 us)
    100    INDEX FULL SCAN T1_N1 (cr=8 pr=0 pw=0 time=431 us)(object id 79292)
    100    INDEX RANGE SCAN T2_N1 (cr=15 pr=0 pw=0 time=2145 us)(object id 79293)

2. Query with cursor expression. Note that we failed at view merging. The result is increased workloads with suboptimal execution plan.

select /* with_cursor */
  t1.c1, x.c2, cursor(select * from dual)
from
  t1, (select c1, c2 from t2) x
where
  t1.c1 = x.c1

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch       51      0.03       0.05          0        154          0         100
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       53      0.03       0.05          0        154          0         100

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  TABLE ACCESS FULL DUAL (cr=0 pr=0 pw=0 time=0 us)
    100  NESTED LOOPS  (cr=154 pr=0 pw=0 time=6135 us)
    100   VIEW  (cr=53 pr=0 pw=0 time=1466 us)
    100    TABLE ACCESS FULL T2 (cr=53 pr=0 pw=0 time=754 us)
    100   INDEX RANGE SCAN T1_N1 (cr=101 pr=0 pw=0 time=3015 us)(object id 79292)

3. Query without cursor expression. Take a close look at the parse/execute/fetch values.

select
  y.*
from
  (select level from dual connect by level <= 1000) x,
  (select * from t1 where rownum <= 10) y

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch      668      0.21       0.18          0          3          0       10000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      670      0.21       0.19          0          3          0       10000

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 61  

Rows     Row Source Operation
-------  ---------------------------------------------------
  10000  MERGE JOIN CARTESIAN (cr=3 pr=0 pw=0 time=147360 us)
   1000   VIEW  (cr=0 pr=0 pw=0 time=18409 us)
   1000    CONNECT BY WITHOUT FILTERING (cr=0 pr=0 pw=0 time=11399 us)
      1     FAST DUAL  (cr=0 pr=0 pw=0 time=13 us)
  10000   BUFFER SORT (cr=3 pr=0 pw=0 time=52983 us)
     10    VIEW  (cr=3 pr=0 pw=0 time=231 us)
     10     COUNT STOPKEY (cr=3 pr=0 pw=0 time=160 us)
     10      TABLE ACCESS FULL T1 (cr=3 pr=0 pw=0 time=94 us)

4. Query with cursor expression. The output is almost same as that of previous query, but…

Oops. No logical reads? What happened with this query?

select
  cursor(select * from t1 where rownum <= 10)
from dual
connect by level <= 1000

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch      501      0.29       0.22          0          0          0        1000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      503      0.29       0.22          0          0          0        1000

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 61  

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  COUNT STOPKEY (cr=0 pr=0 pw=0 time=0 us)
      0   TABLE ACCESS FULL T1 (cr=0 pr=0 pw=0 time=0 us)
   1000  CONNECT BY WITHOUT FILTERING (cr=0 pr=0 pw=0 time=14289 us)
      1   FAST DUAL  (cr=0 pr=0 pw=0 time=12 us)

The problem is that the query in the cursor expression is executed as recursive query. Look at the parse/execute/fetch values and logical reads! This recursive query was parsed and executed 1000 times, whose value is sames as the rows fetched by main query. Very  bad, isn’t it? This is one of the main performance problem of cursor expression.

SELECT "A2"."C1" "C1","A2"."C2" "C2"
FROM
 "T1" "A2" WHERE ROWNUM<=10

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse     1000      0.00       0.02          0          0          0           0
Execute   1000      0.09       0.04          0          0          0           0
Fetch     1000      0.32       0.27          0       3000          0       10000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     3000      0.42       0.35          0       3000          0       10000

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 61  

Rows     Row Source Operation
-------  ---------------------------------------------------
     10  COUNT STOPKEY (cr=3 pr=0 pw=0 time=172 us)
     10   TABLE ACCESS FULL T1 (cr=3 pr=0 pw=0 time=98 us)

5. Another annoying problem. The main query does not show *real* execution plan. See following.

select /* with_cursor2 */
  cursor(
    select
      t1.c1, x.c2
    from t1, (select c1, c2 from t2) x
    where
      t1.c1 = x.c1
  )
from dual
Rows     Row Source Operation
-------  ---------------------------------------------------
      0  NESTED LOOPS  (cr=0 pr=0 pw=0 time=0 us)
      0   VIEW  (cr=0 pr=0 pw=0 time=0 us)
      0    TABLE ACCESS FULL T2 (cr=0 pr=0 pw=0 time=0 us)
      0   INDEX RANGE SCAN T1_N1 (cr=0 pr=0 pw=0 time=0 us)(object id 79292)
      1  FAST DUAL  (cr=0 pr=0 pw=0 time=13 us)

It looks as if the query in the cursor expression failed at view merging. But the actual execution plan from recursive query clearly says that it successfully merged the view!  Looks like a bug, doesn’t it? This annoying phenomenon is observed as of 10gR2.

SELECT "A3"."C1" "C1","A2"."C2" "C2"
FROM
 "T1" "A3", (SELECT "A4"."C1" "C1","A4"."C2" "C2" FROM "T2" "A4") "A2" WHERE
  "A3"."C1"="A2"."C1"
Rows     Row Source Operation
-------  ---------------------------------------------------
    100  TABLE ACCESS BY INDEX ROWID T2 (cr=28 pr=0 pw=0 time=7315 us)
    201   NESTED LOOPS  (cr=21 pr=0 pw=0 time=5606 us)
    100    INDEX FULL SCAN T1_N1 (cr=7 pr=0 pw=0 time=1426 us)(object id 79292)
    100    INDEX RANGE SCAN T2_N1 (cr=14 pr=0 pw=0 time=1905 us)(object id 79293)

6. The last one. Cursor expression does not go well with the plan statistics. Very disappointing.

UKJA@ukja102> select * from table                                           
  2  (dbms_xplan.display_cursor(null,null,'allstats last'));                 

PLAN_TABLE_OUTPUT                                                           
-----------------------------------------------------------------------------
SQL_ID  4um7hxd6fkkh7, child number 0                                       
-------------------------------------                                       
select /*+ gather_plan_statistics */   cursor(     select       t1.c1,      
x.c2     from t1, (select c1, c2 from t2) x     where       t1.c1 =         
x.c1   ) from dual                                                           

Plan hash value: 1702068269                                                  

-----------------------------------------------------------------------------
| Id  | Operation           | Name  | Starts | E-Rows | A-Rows |   A-Time   |
-----------------------------------------------------------------------------
|   1 |  NESTED LOOPS       |       |      0 |    100 |      0 |00:00:00.01 |
|   2 |   VIEW              |       |      0 |    100 |      0 |00:00:00.01 |
|   3 |    TABLE ACCESS FULL| T2    |      0 |    100 |      0 |00:00:00.01 |
|*  4 |   INDEX RANGE SCAN  | T1_N1 |      0 |      1 |      0 |00:00:00.01 |
|   5 |  FAST DUAL          |       |      1 |      1 |      1 |00:00:00.01 |
-----------------------------------------------------------------------------

Oops! Oracle does not report the plan statistics of the query executed in the cursor expression. Why? Not sure. Maybe some implementation limit. Looks like Oracle does not sum up the workloads of recursive query in this special case.  Another limitation of plan statistics besides the parallel execution.

Don’t forget above pitfalls when using cursor expression.





Written by Dion Cho

February 25, 2009 at 12:19 pm

Follow

Get every new post delivered to your Inbox.

Join 59 other followers