Sunday, December 12, 2010

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

数日前に同僚の一人が並列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です。段階2番から分かるように、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文章は2つ以上のテーブルを使用するから。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. 注意すべきのことがもう1つあります。対象テーブル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 |
---------------------------------------------------------------

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

No comments:

Post a Comment