クエリ・オプティマイザによってビューを参照している SQL が書換えられているか確認する

Oracle Database でSQL文を実行すると、論理的に同じ意味を持たせたままSQL文自体をより効率的な文に変換されることがある。この機能を Query Transformation と呼ぶ。Query Transformation には様々な種類があるが、その中に View Merging と呼ばれる機能がある。View Merging はビューやインラインビューに問合わせているSQL文を元表に直接問合せる文に変換し、元表のインデックスが使用されるようにするといった最適化を行う。View Merging についてちょっとした検証を行ってみた。

検証ポイント

  • クエリ・オプティマイザの Vew Merging によってSQL文が書換えられることを確認する。

検証シナリオ

  • View Merging されるSQL文を実行し、クエリ・オプティマイザによってSQL文が書換えられることを確認する。
  • NO_MERGE ヒントを付与してを実行し、クエリ・オプティマイザによってがSQL文が書換えられないことを確認する。
  • 確認には EVENT 10053 を使う。

検証結果

View Merging されるSQL文を実行し、クエリ・オプティマイザが書換えたSQL文を確認する
  • 共有プールをフラッシュする。
SQL> alter system flush shared_pool;
  • EVENT 10053 を有効化する。
SQL> alter session set events '10053 trace name context forever, level 1';
  • View Merging されるSQL文を実行する。
SQL> select a.empno, a.ename  from (select ename,empno from emp where sal >= 500) a where a.empno = 7788;
  • EVENT 10053 を無効化する。
SQL> alter session set events '10053 trace name context off';
  • トレースファイルを確認する。
bash-3.00$ less orcl_ora_27597.trc

(中略)

**************************
Predicate Move-Around (PM)
**************************
PM:     PM bypassed: Outer query contains no views.
PM:     PM bypassed: Outer query contains no views.
query block SEL$F5BB74E1 (#0) unchanged
FPD: Considering simple filter push in query block SEL$F5BB74E1 (#0)
"EMP"."EMPNO"=7788 AND "EMP"."SAL">=500
try to generate transitive predicate from check constraints for query block SEL$F5BB74E1 (#0)
finally: "EMP"."EMPNO"=7788 AND "EMP"."SAL">=500

apadrv-start sqlid=15740000582826725676
  :
    call(in-use=2400, alloc=16344), compile(in-use=62680, alloc=66832), execution(in-use=3040, alloc=4032)

*******************************************
Peeked values of the binds in SQL statement
*******************************************

Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "EMP"."EMPNO" "EMPNO","EMP"."ENAME" "ENAME" FROM "SCOTT"."EMP" "EMP" WHERE "EMP"."EMPNO"=7788 AND "EMP"."SAL">=500
kkoqbc: optimizing query block SEL$F5BB74E1 (#0)

(中略)

============
Plan Table
============
-----------------------------------------------+-----------------------------------+
| Id  | Operation                    | Name    | Rows  | Bytes | Cost  | Time      |
-----------------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT             |         |       |       |     1 |           |
| 1   |  TABLE ACCESS BY INDEX ROWID | EMP     |     1 |    14 |     1 |  00:00:01 |
| 2   |   INDEX UNIQUE SCAN          | PK_EMP  |     1 |       |     0 |           |
-----------------------------------------------+-----------------------------------+
Predicate Information:
----------------------
1 - filter("SAL">=500)
2 - access("EMPNO"=7788)

想定通り View Merging されてる。

View Merging されるSQL文に NO_MERGE ヒントを付与してを実行し、クエリ・オプティマイザがSQL文を書換えてないことを確認する
  • 共有プールをフラッシュする。
SQL> alter system flush shared_pool;
  • EVENT 10053 を有効化する。
SQL> alter session set events '10053 trace name context forever, level 1';
  • View Merging されるSQL文を実行する。
SQL> select /*+ no_merge(a) */ a.empno, a.ename  from (select ename,empno from emp where sal >= 500) a where a.empno = 7788;
  • EVENT 10053 を無効化する。
SQL> alter session set events '10053 trace name context off';
  • トレースファイルを確認する。
bash-3.00$ less orcl_ora_27803.trc

(中略)

**************************
Predicate Move-Around (PM)
**************************
PM:   Passed validity checks.
PM:   Pulled up predicate ("A"."EMPNO","EMP"."SAL")>=500
 from query block SEL$2 (#0) to query block SEL$1 (#0)
PM:   Pushed down predicate "EMP"."EMPNO"=7788
 from query block SEL$1 (#0) to query block SEL$2 (#0)
PM:     PM bypassed: checking.
query block SEL$1 (#0) unchanged
FPD: Considering simple filter push in query block SEL$1 (#0)
 ?? 
FPD: Considering simple filter push in query block SEL$2 (#0)
"EMP"."SAL">=500 AND "EMP"."EMPNO"=7788
try to generate transitive predicate from check constraints for query block SEL$2 (#0)
finally: "EMP"."SAL">=500 AND "EMP"."EMPNO"=7788

apadrv-start sqlid=10867003482408133453
  :
    call(in-use=1776, alloc=16344), compile(in-use=65928, alloc=66920), execution(in-use=3120, alloc=4032)

*******************************************
Peeked values of the binds in SQL statement
*******************************************

Final query after transformations:******* UNPARSED QUERY IS *******
SELECT /*+ NO_MERGE ("A") */ "A"."EMPNO" "EMPNO","A"."ENAME" "ENAME" FROM  (SELECT "EMP"."ENAME" "ENAME","EMP"."EMPNO" "EMPNO" FROM "SCOTT"."EMP" "EMP" WHERE "EMP"."SAL">=500 AND "EMP"."EMPNO"=7788) "A"
kkoqbc: optimizing query block SEL$2 (#0)

(中略)

 
============
Plan Table
============
------------------------------------------------+-----------------------------------+
| Id  | Operation                     | Name    | Rows  | Bytes | Cost  | Time      |
------------------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT              |         |       |       |     1 |           |
| 1   |  VIEW                         |         |     1 |    20 |     1 |  00:00:01 |
| 2   |   TABLE ACCESS BY INDEX ROWID | EMP     |     1 |    14 |     1 |  00:00:01 |
| 3   |    INDEX UNIQUE SCAN          | PK_EMP  |     1 |       |     0 |           |
------------------------------------------------+-----------------------------------+
Predicate Information:
----------------------
2 - filter("SAL">=500)
3 - access("EMPNO"=7788)

View merging されないことを想定していたけどされてる。NO_MERGE ヒントをつけたことにより View merging で書換えられたSQL文がさっきと違う。続きはまた今度。