Thursday, December 16, 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 |
----------------------------------------------------------------------------

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

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 |
---------------------------------------------------------------

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