Dion Cho – Oracle Performance Storyteller

We are natural born scientists

Posts Tagged ‘transformation’

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

Posted in Optimizer

Tagged with , , ,