Dion Cho - Oracle Performance Storyteller: December 2010

次はグローバルヒントで結合順序を制御できないテストケースです。

1. 表T1, T2, T3を作ります。


SQL> create table t1(c1 number, c2 number);

Table created.

SQL> create table t2(c1 number, c2 number);

Table created.

SQL> create table t3(c1 number, c2 number);

Table created.

2. グローバルヒントを使って、結合順序をT1->T2->T3にしますが、なぜか予想通りに動作しません。


SQL> explain plan for
  2  select * from
  3  (
  4  select
  5        /*+ leading(v.t1 v.t2 t3) */
  6        v.c1 as v_c1,
  7        v.c2 as v_c2,
  8        t3.c2 as t3_c2
  9  from
 10        (select
 11         t1.c1,
 12         t2.c2
 13        from
 14         t1, t2
 15        where
 16         t1.c1 = t2.c1) v,
 17        t3
 18  where
 19        v.c1 = t3.c1
 20  ) x
 21  ;

------------------------------------------------------------------------------
| Id  | Operation             | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |     1 |    65 |     7  (15)| 00:00:01 |
|*  1 |  HASH JOIN            |      |     1 |    65 |     7  (15)| 00:00:01 |
|   2 |   MERGE JOIN CARTESIAN|      |     1 |    52 |     4   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL  | T2   |     1 |    26 |     2   (0)| 00:00:01 |
|   4 |    BUFFER SORT        |      |     1 |    26 |     2   (0)| 00:00:01 |
|   5 |     TABLE ACCESS FULL | T3   |     1 |    26 |     2   (0)| 00:00:01 |
|   6 |   TABLE ACCESS FULL   | T1   |     1 |    13 |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------

テストの結果から、Optimizerはグローバルヒントがグローバルではないヒントとは正常的に動作しないことがわります。インラインビューでヒントを与える方法もありますが、この例ではグローバルヒントだけで制御するのが目的です。

3. ここで適用できるのがOracleの内部的なグローバルヒントの表記法です。DBMS_XPLAN.DISPLAY関数にADVANCEDオプションを使えばOracleの内部的なヒント表記法が出力されます。


select * from table(dbms_xplan.display(null, null, 'advanced'));
...

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$5C160134
   3 - SEL$5C160134 / T1@SEL$3
   4 - SEL$5C160134 / T2@SEL$3
   5 - SEL$5C160134 / T3@SEL$2

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      USE_HASH(@"SEL$5C160134" "T3"@"SEL$2")
      USE_HASH(@"SEL$5C160134" "T2"@"SEL$3")
      LEADING(@"SEL$5C160134" "T1"@"SEL$3" "T2"@"SEL$3" "T3"@"SEL$2")
      FULL(@"SEL$5C160134" "T3"@"SEL$2")
      FULL(@"SEL$5C160134" "T2"@"SEL$3")
      FULL(@"SEL$5C160134" "T1"@"SEL$3")
      OUTLINE(@"SEL$3")
      OUTLINE(@"SEL$2")
      MERGE(@"SEL$3")
      OUTLINE(@"SEL$335DD26A")
      OUTLINE(@"SEL$1")
      MERGE(@"SEL$335DD26A")
      OUTLINE_LEAF(@"SEL$5C160134")
      ALL_ROWS
      DB_VERSION('11.2.0.1')
      OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

4. その結果を応用してグローバルヒントを与えれば、結合順序を完全に制御できます。


SQL> explain plan for
  2  select * from
  3  (
  4  select
  5        /*+ LEADING(@"SEL$5C160134" "T1"@"SEL$3" "T2"@"SEL$3" "T3"@"SEL$2" ) */
  6        v.c1 as v_c1,
  7        v.c2 as v_c2,
  8        t3.c2 as t3_c2
  9  from
 10        (select
 11         t1.c1,
 12         t2.c2
 13        from
 14         t1, t2
 15        where
 16         t1.c1 = t2.c1) v,
 17        t3
 18  where
 19        v.c1 = t3.c1
 20  ) x
 21  ;

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     1 |    65 |     7  (15)| 00:00:01 |
|*  1 |  HASH JOIN          |      |     1 |    65 |     7  (15)| 00:00:01 |
|*  2 |   HASH JOIN         |      |     1 |    39 |     5  (20)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| T1   |     1 |    13 |     2   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| T2   |     1 |    26 |     2   (0)| 00:00:01 |
|   5 |   TABLE ACCESS FULL | T3   |     1 |    26 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------

5. もっと良い方法はQB_NAMEヒントでしょう。このヒントを使用すればもっと読みやすくて変更しやしはずです。


SQL> explain plan for
  2  select * from
  3  (
  4  select
  5        /*+ leading(t1@inline t2@inline t3) */
  6        v.c1 as v_c1,
  7        v.c2 as v_c2,
  8        t3.c2 as t3_c2
  9  from
 10        (select /*+ qb_name(inline) */
 11         t1.c1,
 12         t2.c2
 13        from
 14         t1, t2
 15        where
 16         t1.c1 = t2.c1) v,
 17        t3
 18  where
 19        v.c1 = t3.c1
 20  ) x
 21  ;

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     1 |    65 |     7  (15)| 00:00:01 |
|*  1 |  HASH JOIN          |      |     1 |    65 |     7  (15)| 00:00:01 |
|*  2 |   HASH JOIN         |      |     1 |    39 |     5  (20)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| T1   |     1 |    13 |     2   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| T2   |     1 |    26 |     2   (0)| 00:00:01 |
|   5 |   TABLE ACCESS FULL | T3   |     1 |    26 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------

グローバルヒントの使い方を理解するに役に立つ面白いテストケースですね。

数日前に同僚の一人が並列MERGE文章の実行計画の解析に困っていることを見て、次のようにステップーバイーステップの説明をしました。

1. まずテーブルを作ります。


SQL> create table t1
  2  as
  3  select
  4         level as c1,
  5         level as c2,
  6         rpad('x',100) as c3
  7  from
  8         dual
  9  connect by level <= 10000
 10  ;

Table created.

SQL> create table t2
  2  as
  3  select
  4         level as c1,
  5         level as c2,
  6         rpad('x', 100) as c3
  7  from
  8         dual
  9  connect by level <= 10000
 10  ;

Table created.

2. 次のSQL文は並列に実行されるでしょうか。


SQL> explain plan for
  2  merge /*+ parallel */ into t1
  3   using (select c1, c2 from t2) t2
  4   on (t1.c1 = t2.c1)
  5  when matched then
  6         update set t1.c2 = t1.c2
  7  when not matched then
  8         insert(c1, c2) values(t2.c1, t2.c2)
  9  ;

Explained.

-------------------------------------------------------
| Id  | Operation                  | Name     | Rows  |
-------------------------------------------------------
|   0 | MERGE STATEMENT            |          |  9356 |
|   1 |  MERGE                     | T1       |       |
|   2 |   PX COORDINATOR           |          |       |
|   3 |    PX SEND QC (RANDOM)     | :TQ10001 |  9356 |
|   4 |     VIEW                   |          |       |
|*  5 |      HASH JOIN OUTER       |          |  9356 |
|   6 |       PX BLOCK ITERATOR    |          |  9356 |
|   7 |        TABLE ACCESS FULL   | T2       |  9356 |
|   8 |       BUFFER SORT          |          |       |
|   9 |        PX RECEIVE          |          | 11234 |
|  10 |         PX SEND BROADCAST  | :TQ10000 | 11234 |
|  11 |          PX BLOCK ITERATOR |          | 11234 |
|  12 |           TABLE ACCESS FULL| T1       | 11234 |
-------------------------------------------------------

答えはNOです。段階２番から分かるように、MERGEパート自体は直列に実行されます。

3. 理由はたぶんPARALLEL　DMLが活性化されていないからでしょう。PARALLEL　DMLを活性化したらどうなれるか確認してみます。


SQL> alter session enable parallel dml;

Session altered.

SQL> explain plan for
  2  merge /*+ parallel */ into t1
  3   using (select c1, c2 from t2) t2
  4   on (t1.c1 = t2.c1)
  5  when matched then
  6         update set t1.c2 = t1.c2
  7  when not matched then
  8         insert(c1, c2) values(t2.c1, t2.c2)
  9  ;

-------------------------------------------------------
| Id  | Operation                  | Name     | Rows  |
-------------------------------------------------------
|   0 | MERGE STATEMENT            |          |  9356 |
|   1 |  MERGE                     | T1       |       |
|   2 |   PX COORDINATOR           |          |       |
|   3 |    PX SEND QC (RANDOM)     | :TQ10001 |  9356 |
|   4 |     VIEW                   |          |       |
|*  5 |      HASH JOIN OUTER       |          |  9356 |
|   6 |       PX BLOCK ITERATOR    |          |  9356 |
|   7 |        TABLE ACCESS FULL   | T2       |  9356 |
|   8 |       BUFFER SORT          |          |       |
|   9 |        PX RECEIVE          |          | 11234 |
|  10 |         PX SEND BROADCAST  | :TQ10000 | 11234 |
|  11 |          PX BLOCK ITERATOR |          | 11234 |
|  12 |           TABLE ACCESS FULL| T1       | 11234 |
-------------------------------------------------------

また、MERGEパートが直列に実行されています。

4. たぶん、並列MERGEはもっと正確なヒントを必要にするみたいです。これは当たり前でしょう。MERGE文章は２つ以上のテーブルを使用するから。PARALLELヒントにエイリアスを追加してみましょう。


SQL> explain plan for
  2  merge /*+ parallel(t1) */ into t1
  3   using (select c1, c2 from t2) t2
  4   on (t1.c1 = t2.c1)
  5  when matched then
  6         update set t1.c2 = t1.c2
  7  when not matched then
  8         insert(c1, c2) values(t2.c1, t2.c2)
  9  ;

----------------------------------------------------
| Id  | Operation                       | Name     |
----------------------------------------------------
|   0 | MERGE STATEMENT                 |          |
|   1 |  PX COORDINATOR                 |          |
|   2 |   PX SEND QC (RANDOM)           | :TQ10003 |
|   3 |    MERGE                        | T1       |
|   4 |     PX RECEIVE                  |          |
|   5 |      PX SEND HYBRID (ROWID PKEY)| :TQ10002 |
|   6 |       VIEW                      |          |
|*  7 |        HASH JOIN OUTER BUFFERED |          |
|   8 |         BUFFER SORT             |          |
|   9 |          PX RECEIVE             |          |
|  10 |           PX SEND HASH          | :TQ10000 |
|  11 |            TABLE ACCESS FULL    | T2       |
|  12 |         PX RECEIVE              |          |
|  13 |          PX SEND HASH           | :TQ10001 |
|  14 |           PX BLOCK ITERATOR     |          |
|  15 |            TABLE ACCESS FULL    | T1       |
----------------------------------------------------

今度こそ、MERGEパートがちゃんと並列に実行されています。

5. 注意すべきのことがもう１つあります。対象テーブルT1に新しいインデックスをつけ、どんな変化が起こるか確認してみましょう。


SQL> create index t1_n1 on t1(c1);

Index created.

SQL> explain plan for
  2  merge /*+ parallel(t1) */ into t1
  3   using (select c1, c2 from t2) t2
  4   on (t1.c1 = t2.c1)
  5  when matched then
  6         update set t1.c2 = t1.c2
  7  when not matched then
  8         insert(c1, c2) values(t2.c1, t2.c2)
  9  ;

---------------------------------------------------------------
| Id  | Operation                          | Name     | Rows  |
---------------------------------------------------------------
|   0 | MERGE STATEMENT                    |          |  9356 |
|   1 |  PX COORDINATOR                    |          |       |
|   2 |   PX SEND QC (RANDOM)              | :TQ10004 |  9356 |
|   3 |    INDEX MAINTENANCE               | T1       |       |
|   4 |     PX RECEIVE                     |          |  9356 |
|   5 |      PX SEND RANGE                 | :TQ10003 |  9356 |
|   6 |       MERGE                        | T1       |       |
|   7 |        PX RECEIVE                  |          |  9356 |
|   8 |         PX SEND HYBRID (ROWID PKEY)| :TQ10002 |  9356 |
|   9 |          VIEW                      |          |       |
|* 10 |           HASH JOIN OUTER BUFFERED |          |  9356 |
|  11 |            BUFFER SORT             |          |       |
|  12 |             PX RECEIVE             |          |  9356 |
|  13 |              PX SEND HASH          | :TQ10000 |  9356 |
|  14 |               TABLE ACCESS FULL    | T2       |  9356 |
|  15 |            PX RECEIVE              |          | 11234 |
|  16 |             PX SEND HASH           | :TQ10001 | 11234 |
|  17 |              PX BLOCK ITERATOR     |          | 11234 |
|  18 |               TABLE ACCESS FULL    | T1       | 11234 |
---------------------------------------------------------------

INDEX MAINTENANCEと言う新しい段階が現れます。インデックスメンテナンスのためにもう一つのテーブルキューTQ10004が使用されることも分かります。問題は、なぜここでインデックスメンテナンスをするのかということです。

並列MERGEは並列UPDATEと並列INSERTで構成されます。
並列INSERTとはダイレクトパスINSERTです。
ダイレクトパスINSERTが終わったら、インデックスに変更内容を反映しなければなりません。

OracleはダイレクトパスINSERTの後で、インデックスのメンテナンスをするようになっています。この機能は_idl_conventional_index_maintenanceという隠しパラメーターで制御されます。


SQL> select * from table(tpack.param('_idl_conventional_index_maintenance'));

NAME                                VALUE
----------------------------------- ----------------------------------------
Name #1                             _idl_conventional_index_maintenance
  Value                             TRUE
  Is Default                        TRUE
  Sess Modifiable                   false
  Sys Modifiable                    false
  Description                       enable conventional index maintenance fo
                                    r insert direct load

INDEX MAINTENANCEの段階はインデックスをUNUSABLEさせても消えません。でも、実際に実行する際にはUNUSABLE状態のインデックスはメンテナンスされないはずです。


SQL> alter index t1_n1 unusable;

Index altered.

SQL> explain plan for
  2  merge /*+ parallel(t1) */ into t1
  3   using (select c1, c2 from t2) t2
  4   on (t1.c1 = t2.c1)
  5  when matched then
  6         update set t1.c2 = t1.c2
  7  when not matched then
  8         insert(c1, c2) values(t2.c1, t2.c2)
  9  ;

---------------------------------------------------------------
| Id  | Operation                          | Name     | Rows  |
---------------------------------------------------------------
|   0 | MERGE STATEMENT                    |          |  9356 |
|   1 |  PX COORDINATOR                    |          |       |
|   2 |   PX SEND QC (RANDOM)              | :TQ10004 |  9356 |
|   3 |    INDEX MAINTENANCE               | T1       |       |
|   4 |     PX RECEIVE                     |          |  9356 |
|   5 |      PX SEND RANGE                 | :TQ10003 |  9356 |
|   6 |       MERGE                        | T1       |       |
|   7 |        PX RECEIVE                  |          |  9356 |
|   8 |         PX SEND HYBRID (ROWID PKEY)| :TQ10002 |  9356 |
|   9 |          VIEW                      |          |       |
|* 10 |           HASH JOIN OUTER BUFFERED |          |  9356 |
|  11 |            BUFFER SORT             |          |       |
|  12 |             PX RECEIVE             |          |  9356 |
|  13 |              PX SEND HASH          | :TQ10000 |  9356 |
|  14 |               TABLE ACCESS FULL    | T2       |  9356 |
|  15 |            PX RECEIVE              |          | 11234 |
|  16 |             PX SEND HASH           | :TQ10001 | 11234 |
|  17 |              PX BLOCK ITERATOR     |          | 11234 |
|  18 |               TABLE ACCESS FULL    | T1       | 11234 |
---------------------------------------------------------------

私の説明が役に立ったのか分かりませんが、ともかくこのようにブログに残します。

Dion Cho - Oracle Performance Storyteller

Thursday, December 16, 2010

グローバルヒントの制約の面白いケース

Sunday, December 12, 2010

並列MERGE文章の実行計画の解析

紹介

ティパック

Blog Archive

好きなブログ

私の本

訪問者