簡単に言えば、CREATE TABLE文を実行しても実際にデータが追加されるまでにはセグメントが生成されない機能です。このポストではDeferred Segment CreationとINSERT ... SELECT文に関する面白い2つのバグを紹介します。
- バグ9078678: セグメントのないテーブルへの並列INSERT ... SELECT文に対しる予想実行計画（Explain Plan）は並列ではなく直列実行計画に表示されます。しかし、実際には並列で行なわれます。
- バグ9329566: セグメントのないテーブルに対してINSERT ...SELECT文を実行すれば、SELECT文を二回実行するバグです。
2つのバグはすべてセグメントかないという特徴から発生するバグです。簡単なテストケースを見ましょう。まずバグ9078678に当たる現象です。
1. オラクルバージョンは11.2.0.1です。
TPACK@ukja1120> -- version
TPACK@ukja1120> select * from v$version where rownum = 1;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
2. テーブルT1を作ります。テーブルT1はセグメントが存在しない状態です。テーブルT1に対して並列INSERT文章を行なう場合実行計画がどうなるか見ましょう。
TPACK@ukja1120> create table t1(c1 number);
Table created.
TPACK@ukja1120> alter session enable parallel dml;
Session altered.
TPACK@ukja1120> explain plan for
2 insert /*+ parallel(t1 4) */ into t1
3 select level from dual connect by level <= 10000;
Explained.
TPACK@ukja1120> select * from table(dbms_xplan.display);
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 1 | 2 (0)| 00:00:01 |
| 1 | LOAD AS SELECT | T1 | | | |
|* 2 | CONNECT BY WITHOUT FILTERING| | | | |
| 3 | FAST DUAL | | 1 | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------
ヒントを付けて並列実行を指定したが直列実行の実行計画が選択されました。
3. 予想実行計画ではなくて実際にSQL文を実行した後並列で実行するかどうかを見ましょう。
TPACK@ukja1120> insert /*+ parallel(t1 4) */ into t1
2 select level from dual connect by level <= 10000;
10000 rows created.
TPACK@ukja1120>
TPACK@ukja1120> commit;
Commit complete.
-- https://sites.google.com/site/ukja/sql-scripts-1/o-s/pqstat
TPACK@ukja1120> @pq_stat
DFO_NUMBER TQ_ID SERVER_TYP PROCESS NUM_ROWS
---------- ---------- ---------- ---------- ----------
1 0 Consumer P000 2500
1 0 Consumer P001 2500
1 0 Consumer P002 2500
1 0 Consumer P003 2500
1 0 Producer QC 10000
1 1 Consumer QC 4
1 1 Producer P000 1
1 1 Producer P001 1
1 1 Producer P002 1
1 1 Producer P003 1
10 rows selected.
STATISTIC LAST_QUERY SESSION_TOTAL
------------------------------ ---------- -------------
Queries Parallelized 0 0
DML Parallelized 1 1
DDL Parallelized 0 0
DFO Trees 1 1
Server Threads 4 0
Allocation Height 4 0
Allocation Width 1 0
Local Msgs Sent 44 44
Distr Msgs Sent 0 0
Local Msgs Recv'd 44 44
Distr Msgs Recv'd 0 0
11 rows selected.
Explain Planをよる予想実行計画では直列実行という結果がでてきたが、実際の結果からは並列に実行されたのがわかります。もっと面白いのはこの状態で（すなわち、テーブルT1にデータが追加された状態）予想実行計画を見直すと並列実行計画にかえるとくうことです。
TPACK@ukja1120> explain plan for
2 insert /*+ append parallel(t1 4) */ into t1
3 select level from dual connect by level <= 10000;
Explained.
------------------------------------------------------
| Id | Operation | Name |
------------------------------------------------------
| 0 | INSERT STATEMENT | |
| 1 | PX COORDINATOR | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 |
| 3 | LOAD AS SELECT | T1 |
| 4 | PX RECEIVE | |
| 5 | PX SEND ROUND-ROBIN | :TQ10000 |
|* 6 | CONNECT BY WITHOUT FILTERING| |
| 7 | FAST DUAL | |
------------------------------------------------------
これはバグです。セグメントがまだ生成されていないテーブルに対する並列実行文章の予想実行計画はまるで直列に実行されうように見えるが、実際には並列に実行されるバグです。すなわち、セグメントのないテーブルに対する並列実行文章の予想実行計画を正確に見せてくれないバグです。
バグ9329566に該当するテストケースも見ましょう。
1. テーブルT1とテーブルT2を同一に生成します。ただ、テーブルT2には一件の行を追加してセグメントもあらかじめ作っておきます。テーブルT1はセグメントがまだ存在しない状態です。10,000ブロックのサイズのテーブルT3も作っておきます。
TPACK@ukja1120> create table t1(c1 varchar2(2000), c2 varchar2(2000), c3 varchar2(2000), c4 varchar2(1000));
Table created.
TPACK@ukja1120> create table t2(c1 varchar2(2000), c2 varchar2(2000), c3 varchar2(2000), c4 varchar2(1000));
Table created.
TPACK@ukja1120>
TPACK@ukja1120> -- insert 1 row into table t2 to create the segment
TPACK@ukja1120> insert into t2 values('1','1','1','1');
1 row created.
TPACK@ukja1120> -- table size 10000 block
TPACK@ukja1120> create table t3
2 as
3 select rpad('x',2000) as c1, rpad('x',2000) as c2, rpad('x',2000) as c3, rpad('x',1000) as c4
4 from dual
5 connect by level <= 10000;
Table created.
2. テーブルT3から最高値を読み込んでテーブルT1（セグメントない）、テーブルT2（セグメントある）へINSERTするクエリのそれぞれのConsistent Getsを比べてみましょう。手軽なテストのためにティパックのSession Snapshot Reportを利用します。
TPACK@ukja1120> exec tpack.begin_session_snapshot;
PL/SQL procedure successfully completed.
TPACK@ukja1120> insert into t1
2 select max(c1), max(c2), max(c3), max(c4) from t3;
1 row created.
TPACK@ukja1120> exec tpack.add_session_snapshot;
PL/SQL procedure successfully completed.
TPACK@ukja1120> insert into t2
2 select max(c1), max(c2), max(c3), max(c4) from t3;
1 row created.
TPACK@ukja1120>
TPACK@ukja1120> exec tpack.add_session_snapshot;
PL/SQL procedure successfully completed.
次の結果を見たら、テーブルT1（セグメントない）に対するINSERTがテーブルT2（セグメントある）に対するINSERTに比べて2倍程度のConsistentを見せます。
TPACK@ukja1120> col item format a40
TPACK@ukja1120> col deltas format a20
TPACK@ukja1120> select item, deltas from table(tpack.session_snapshot_report)
2 where type = 'STAT';
TPACK@ukja1120> select item, deltas from table(tpack.session_snapshot_report)
2 where type = 'STAT';
ITEM DELTAS
---------------------------------------- --------------------
...
physical read bytes 164339712->82305024
consistent gets 20451->10327
...
これもやはりバグです。
- テーブルT3からデータを読んでからそのデータをテーブルT1へINSERTしようとします。
- どころが、テーブルT1はまだセグメントがありません。したがってまずセグメントを作ります。
- ここでオラクルは1番段階で獲得したデータを再活用できずまたテーブルT3からデータを読み込みます。現在のクエリはテーブルT3から最高値(MAX)を取り出しているから、クエリを実行するたびにテーブルのサイズである10,000ブロックを全部読み込まなければなりません。このために、テーブルT1に対するINSERTの際には20,000ブロック（二回読み込むから）、テーブルT2に対するINSERTは10,000ブロック（1回読み込むから）を読み込むようになるのです。
このバグたちはセグメントのないテーブルに対してのみ発生するから致命的なバグとは言いかねます。むしろ開発者がいくら基本的な罠に陥るのかをわかる良い例だといえます。オラクルで発生する多い性能問題がこのようなロジック穴から発生します。性能問題が起こった時このようなロジック穴を見破るテスト能力備えることが重要だといえます。
Posts
No comments:
Post a Comment