Database
技術記事
Ask Tom
2014年7/8月 |
テクノロジー：Ask Tom
表の更新とSQL計画ベースラインについて
Tom Kyte著
オラクルの技術者が、CTAS文による表の迅速な更新と、問合せを進化させるための最適化について説明します。
アプリケーションのアップグレードに伴い、ある表の全レコードを、一時表で得られる新しいデータに基づいて変更する必要があります（その一時表はアップグレードのためだけに存在するもので、グローバル一時表ではありません）。この目的を果たすための最善策について教えてください。変更対象の表には数百万件のレコードがあり、できる限り短い時間でアップグレードを完了する必要があります。
よくある質問ですが、その答えに驚かれるかもしれません。この表を更新するための最善策は、表を更新せず、むしろ再作成することです。表の更新はアプリケーションのアップグレードに含まれ、そのアプリケーションはアップグレード中にオフラインになる予定ですから、この目的を果たす上ではほぼ確実に、表の再作成がもっとも効率的です。
Oracle Learning Library
|
すべての行を更新すると、どうしても変更操作のたびにUNDOが生成され、表の変更ではREDOやさらに多くのUNDOが生成されます。そうではなく、単純にCREATE TABLE NEW_TABLE AS SELECT（CTAS文）を利用します。そうすれば、すべてのUNDO生成を確実にスキップでき、REDO生成のスキップも選択できます。また、アプリケーションの更新中に変更される列のすべての索引を維持しようとせずに、単純にCREATE TABLE処理の完了後に新しい索引を作成します。すべての行を更新するとなれば、変更される列の索引は、本来必要なサイズの2倍以上になるため、いずれにせよ索引の再構築の実行がほぼ確実に必要になります。
新しい更新手順は次のようになります。- CREATE TABLE NEW_TABLE AS SELECT <更新対象のデータを含むold_tableとの結合>を実行します。ほとんど（すべてではない）の行が更新対象の場合は、外部結合を使用することもあります（既存の表のすべての行に対しては、対応する行がステージ表には存在しない可能性があります）。
- DROP TABLE OLD_TABLEを実行して、古いデータを含む既存の表を削除します。
- ALTER TABLE NEW_TABLE RENAME TO OLD_TABLEを実行します。
- CREATE INDEXを実行して、索引を再作成します。
- GRANTを実行して、新しい表に適切な権限を付与します。
アプリケーションのアップグレード中にARCHIVELOGモードを使用している場合は、NOLOGGING句によってREDO生成を防ぐことを検討してください（ただし、アップグレード後はかならずNOLOGGING句を無効化してからデータベースをバックアップしてください）。NOARCHIVELOGモードを使用する場合は、CREATE TABLE NEW_TABLE AS SELECTコマンドおよびCREATE INDEXコマンドによりREDOが生成されることはありません（また、CREATE TABLEコマンドやCREATE INDEXコマンドではUNDOは生成されません）。このCREATE TABLE AS SELECTによる方法、つまりデータ操作言語（DML）の代わりにデータ定義言語（DDL）を利用する方法では、大きなオブジェクトの処理が速くなるばかりか、生成されるセグメントの再編成も不要です。この方法による更新が、おそらくはMERGE/UPDATEの操作後になるからです。
大きなデータセットでの実際の効果を確認するために、4つのパートから成るビデオ・シリーズのbit.ly/1fXdD8L、bit.ly/1idIpdK、bit.ly/1mO3dwL、bit.ly/1lIg7yOをご覧になることをお勧めします。これらのビデオでは、DDLを利用した大きなデータセットのロード、検証、変換（関心の大半はこの変換部分でしょう）について示し、このCREATE TABLE AS SELECTの方法を、同じ操作をDMLで行う場合と比較しています。
ヒントが利用可能ならベースラインも利用可能
2つのSQL文が、セマンティック上は同じ問合せを実行するのに、実行時間が大きく異なるという問題が見つかりました。この問題を、オプティマイザのカーディナリティの見積りが大きく外れる、という単純な問題にまで絞り込むことができました。統計情報は最新のもので、実際のデータを表しています。
WHERE句内に副問合せ（1つの定数値を返すもの）を含む問合せを実行した場合は、約350秒という耐えがたいほど長い時間がかかります。一方、この副問合せの値を手動で求めて（数ミリ秒かかります）その戻り値を定数値としてこの問合せに渡すと、実行時間は数ミリ秒になりますが、その際にまったく異なる計画が使用されます。
この副問合せは単純なMAX(...)問合せです。それなのに、なぜオプティマイザは同じ計画を使用できず、あるいは単一値が返されることを予測してこの問合せを最適化できないのでしょうか。
[編集部注：この質問に関するサンプルの表、実行時間の長い問合せと計画、実行時間の短い問合せと計画について、リスト1に示します。]
元の問合せを書き直さずに、この実行時間の短い問合せ計画を実現するための最善策を教えてください。
コード・リスト1：サンプルの表、実行時間の長い問合せと計画、実行時間の短い問合せと計画
サンプルの表の設定方法は次のとおりです。
CREATE TABLE t AS SELECT * FROM all_objects; ALTER TABLE t ADD CONSTRAINT t_id_pk PRIMARY KEY (object_id); CREATE INDEX t_idx_id ON t(object_id); CREATE INDEX t_idx_type ON t(object_type); exec DBMS_STATS.GATHER_TABLE_STATS(user, 'T' );
実行時間が長く、計画とあわせてチューニングしたい問合せは次のとおりです。
SQL> SELECT *
2 FROM t t1
3 WHERE t1.object_type = 'TABLE'
4 AND t1.object_id > (SELECT MAX(t2.object_id) - 500000 FROM t t2);
6115 rows selected.
Execution Plan
———————————————————————————————————————————————————————————————————————
| Id | Operation | Name | Rows | Bytes |
———————————————————————————————————————————————————————————————————————
| 0 | SELECT STATEMENT | | 413 | 39235 |
| 1 | TABLE ACCESS BY INDEX ROWID | T | 413 | 39235 |
| 2 | BITMAP CONVERSION TO ROWIDS | | | |
| 3 | BITMAP AND | | | |
| 4 | BITMAP CONVERSION FROM ROWIDS| | | |
| 5 | SORT ORDER BY | | | |
|* 6 | INDEX RANGE SCAN | T_IDX_ID | 8252 | |
| 7 | SORT AGGREGATE | | 1 | 5 |
| 8 | INDEX FULL SCAN (MIN/MAX)| T_IDX_ID | 1 | 5 |
| 9 | BITMAP CONVERSION FROM ROWIDS| | | |
|* 10 | INDEX RANGE SCAN | T_IDX_TYPE | 8252 | |
———————————————————————————————————————————————————————————————————————
Predicate Information (identified by operation id):
———————————————————————————————————————————————————————————————————————
6 - access("T1"."OBJECT_ID"> (SELECT MAX("T2"."OBJECT_ID")-500000 FROM
"T" "T2"))
filter("T1"."OBJECT_ID"> (SELECT MAX("T2"."OBJECT_ID")-500000 FROM
"T" "T2"))
10 - access("T1"."OBJECT_TYPE"='TABLE')
利用したい問合せおよび実行時間の短い計画は次のとおりです。
SQL> SELECT MAX(t2.object_id) - 500000 FROM t t2;
MAX(T2.OBJECT_ID)-500000
————————————————————————————————————
19975
SQL> SELECT *
2 FROM t t1
3 WHERE t1.object_type = 'TABLE' AND t1.object_id > 19975;
6115 rows selected.
Execution Plan
—————————————————————————————————————————————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
———————————————————————————————————————————————————————————————————————————————
| 0 | SELECT STATEMENT | | 7198 | 667K| 102 (2)|
|* 1 | TABLE ACCESS BY INDEX ROWID| T | 7198 | 667K| 102 (2)|
|* 2 | INDEX RANGE SCAN | T_IDX_TYPE | 8553 | | 6 (0)|
———————————————————————————————————————————————————————————————————————————————
Predicate Information (identified by operation id):
————————————————————————————————————————————————————————————————————————————
1 - filter("T1"."OBJECT_ID">19975)
2 – access("T1"."OBJECT_TYPE"='TABLE')
これら2つの問合せは、セマンティック上は同じですが、オプティマイザに対しては大きく異なる課題を示しています。1つ目の問合せでは、オプティマイザはまだその副問合せを実行していないため、副問合せによって返される値を"推測"しなければなりません。つまり、次の問合せがあるとします。
SQL> SELECT * 2 FROM t t1 3 WHERE t1.object_type = 'TABLE' 4 AND t1.object_id > (SELECT 5 MAX(t2.object_id) - 500000 6 FROM t t2);
この問合せは、ほぼ以下の内容に等しいのです。
SQL> SELECT * 2 FROM t t1 3 WHERE t1.object_type = 'TABLE' 4 AND t1.object_id > ???;
OBJECT_IDと比較する定数値が何になるのか、この問合せの実行時に最終的に代入される値は何なのかがオプティマイザには分かりません。そのため、オプティマイザはカーディナリティの見積り値を推測します。
一方、以下のような構成メンバーにより問合せを行うとします。
SQL> SELECT * 2 FROM t t1 3 WHERE t1.object_type = 'TABLE' 4 AND t1.object_id > 19975;
この場合は、オプティマイザが利用できる情報が大幅に増加します。条件に定数値が指定されているため、返される行数をおおむね把握することができます。したがって、質問者が取り組んでいる問合せは、オプティマイザがハード解析時に正しいカーディナリティの見積り値を取得することが本当に難しく、少なくとも最初の実行時には最適な計画を選択できない可能性も十分にあるような問合せです。質問の内容は、「コードを書き直さずに、理想的には本番コード内でヒントをまったく使用せずに修正するにはどうすればよいか」ですが、その答えは次のとおりです。望ましい計画を作成するために一時的にヒントを利用しますが、その後はOracle DatabaseのSQL計画管理機能と問合せ計画ベースラインを利用して修正を実施します。この方法により本番環境でヒントを利用する必要がなくなり、さらにオプティマイザがより適切な計画を検出するまで、望ましい計画が確実に使用されるようになります。
本番コードでヒントを利用すべきでない理由は、問合せに正しくヒントを付けることが非常に難しいことです。問合せに対して適切にヒントを付けるためには、全体的に、ヒントを冗長に付ける必要があります。たとえば、ヒントA、B、Cを利用すれば望ましい計画が作成されることがテスト環境で判明したとしても、本番環境では、統計情報の収集後やデータ変更後、あるいはデータベース・アップグレード後に、ヒントA、B、Cがその役割を果たさなくなる可能性があります。その理由として、問合せはテスト段階で"偶然にも"良い実行状態でしたが、望ましい計画をオプティマイザが確実に選択するためには実はヒントDも必要だったのに、それを事前に把握できなかったのかもしれません。もしくは、データベース・アップグレード後、ヒントが無効化されるような新しいクエリー変換/リライトをオプティマイザが取り入れる場合もあります。
後ほど、上記の問合せで望ましい計画が確実に使用されるために最低限必要になるヒントのリストを示します。このヒントのリストは大きく、皆さんはこれほど大きなリストをコーディングしたことはないでしょう。
問合せ内にヒントを配置せずに、SQL計画管理を利用すべきもう1つの理由は、オプティマイザにより、時間の経過とともに問合せ計画を改良していけることです。つまり、問合せ計画を凍結せずに、今後さらに適切な計画を利用できるようになります。問合せに対して全体的にヒントを付けると、現在の計画にとらわれ続けることになります。この問合せ計画改良の話題については、"Baselines and Better Plans"を参照してください。
これ以降は、望ましい計画を作成し、その後SQL計画管理によりその計画を確立し、問合せから透過的に利用できるようにする方法について説明します。テスト環境において、FIRST_ROWS(1)ヒントまたはOPT_PARAM( ‘_b_tree_bitmap_plans’, ‘FALSE’ )ヒントを使用することで、このSQL文でBツリー・ビットマップ計画の利用を無効化できることが分かりました。したがって、リスト2により望ましい計画を作成することにします。
コード・リスト2：ヒントを利用した計画の作成
SQL> SELECT /*+ first_rows(1) */ * 2 FROM t t1 3 WHERE t1.object_type = 'TABLE' 4 AND t1.object_id > (SELECT MAX(t2.object_id) - 500000 FROM t t2); Execution Plan ————————————————————————————————————————————————————————————————————————————— Plan hash value:1289158178 —————————————————————————————————————————————————————————————————————————————— | Id | Operation | Name | Rows | Bytes | Cost (%CPU) —————————————————————————————————————————————————————————————————————————————— | 0 | SELECT STATEMENT | | 1 | 97 | 4 (0) |* 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 97 | 2 (0) |* 2 | INDEX RANGE SCAN | T_IDX_TYPE | 1871 | | 1 (0) | 3 | SORT AGGREGATE | | 1 | 5 | | 4 | INDEX FULL SCAN (MIN/MAX)| T_ID_PK | 1 | 5 | 2 (0) —————————————————————————————————————————————————————————————————————————————— SQL> select /*+ opt_param( '_b_tree_bitmap_plans', 'FALSE' ) */ * 2 FROM t t1 3 WHERE t1.object_type = 'TABLE' 4 AND t1.object_id > (SELECT MAX(t2.object_id) - 500000 FROM t t2); Execution Plan ————————————————————————————————————————————————————————————————————————————— Plan hash value:1289158178 —————————————————————————————————————————————————————————————————————————————— | Id | Operation | Name | Rows | Bytes | Cost (%CPU) —————————————————————————————————————————————————————————————————————————————— | 0 | SELECT STATEMENT | | 94 | 9118 | 85 (0) |* 1 | TABLE ACCESS BY INDEX ROWID| T | 94 | 9118 | 83 (0) |* 2 | INDEX RANGE SCAN | T_IDX_TYPE | 1871 | | 6 (0) | 3 | SORT AGGREGATE | | 1 | 5 | | 4 | INDEX FULL SCAN (MIN/MAX)| T_ID_PK | 1 | 5 | 2 (0)
ここで、リスト2の計画がデフォルトで確実に使用されるようにしたい場合に、FIRST_ROWS(1)ヒントやOPT_PARAMヒントだけでは不足しています。実際に、DBMS_XPLANを利用して、計画の内容に加えてこの計画を確実に再実行するために必要になるすべてのヒントを表示します。その結果、リスト3の計画とアウトライン・データが得られます。
コード・リスト3：より適切なヒント付き計画の作成に必要となる計画とアウトライン情報の明確化
SQL> select *
2 from table(dbms_xplan.display_cursor (sql_id=>'4rg4kcxr83kup',
3 cursor_child_no => 0, format=>'+outline'));
PLAN_TABLE_OUTPUT
——————————————————————————————————————————
SQL_ID 4rg4kcxr83kup, child number 0
———————————————————————————————————————————————————————
select /*+ opt_param( '_b_tree_bitmap_plans', 'FALSE' )
gather_plan_statistics */ * FROM t t1 WHERE t1.object_type = 'TABLE'
AND t1.object_id > (SELECT MAX(t2.object_id) - 500000 FROM t t2)
Plan hash value:1289158178
——————————————————————————————————————————————————————————————————————————————————————
| Id | Operation | Name | Rows | Bytes| Cost (%CPU)| Time |
——————————————————————————————————————————————————————————————————————————————————————
| 0 | SELECT STATEMENT | | | | 83 (100)| |
|* 1 | TABLE ACCESS BY INDEX ROWID| T | 91| 8827| 81 (0)| 00:00:01|
|* 2 | INDEX RANGE SCAN | T_IDX_TYPE| 1823| | 6 (0)| 00:00:01|
| 3 | SORT AGGREGATE | | 1| 5| | |
| 4 | INDEX FULL SCAN (MIN/MAX)| T_ID_PK | 1| 5| 2 (0)| 00:00:01|
——————————————————————————————————————————————————————————————————————————————————————
Outline Data
———————————————————
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.2')
DB_VERSION('11.2.0.3')
OPT_PARAM('_b_tree_bitmap_plans' 'false')
ALL_ROWS
OUTLINE_LEAF(@"SEL$2")
OUTLINE_LEAF(@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "T1"@"SEL$1" ("T"."OBJECT_TYPE"))
PUSH_SUBQ(@"SEL$2")
INDEX(@"SEL$2" "T2"@"SEL$2" ("T"."OBJECT_ID"))
END_OUTLINE_DATA
*/
リスト3から分かるように、アウトライン・データ内には、必要となるすべてのヒントとしてOPT_PARAMヒントだけにとどまらず本当に多くのヒントが挙げられています。同じ計画が繰り返し選択されるように問合せに対して正しいヒントを付けることは、見た目よりもかなり難しいのです。また、この計画を永久に凍結したいとは思いません。今後のOracle Databaseのリリースでは、クエリー・リライトや、現在は存在しない最適化機能の実行によって、さらに適切な計画を作成できるようになるはずです。今後そのような新機能を利用したいのですが、問合せにヒントを付けていればそれは不可能です。
では、より適切な計画が現れるまで、オプティマイザに望ましい計画を選択させるためには、どのような手順を踏めば良いのでしょうか。
まず、既存の問合せ、それも"不適切な"計画を含む問合せ用に、SQL計画ベースラインを保存します。なぜ、不適切な計画を保存するのでしょうか。この問合せ用のSQL計画ベースライン（後で望ましい計画をロードするためベースライン）を確立するには、計画をロードする前に計画ベースラインを作成しておく必要があります。リスト4のコードを実行して、ベースラインを作成します。
コード・リスト4：ベースラインの作成
SQL> begin
2 dbms_output.put_line(
3 dbms_spm.load_plans_from_cursor_cache
4 ( sql_id => 'crvk9z6mx9n4d' )
5 );
6 end;
7 /
1
SQL> select sql_handle,
2 substr(sql_text,1,10)||'...'||
3 substr(sql_text,length(sql_text)-10) stext,
4 plan_name, enabled
5 from dba_sql_plan_baselines
6 where sql_text like
7 'SELECT%FROM t t1%(SELECT MAX(t2.object_id) - 500000 FROM t t2)';
SQL_HANDLE STEXT PLAN_NAME ENA
———————————————————— ——————————————— —————————————————————————————— ———
SQL_e738c19a5191e8fd SELECT * SQL_PLAN_fff61m98t3u7xda64b1bb YES
...FROM t t2)
これでベースラインができたため、その計画が使用されることになります。しかし、これは望ましい計画ではありません。次の手順としてこの計画を無効化します。
SQL> begin
2 dbms_output.put_line(
3 dbms_spm.alter_sql_plan_baseline
4 ( sql_handle =>
'SQL_e738c19a5191e8fd',
5 attribute_name => 'enabled',
6 attribute_value => 'NO' )
7 );
8 end;
9 /
1
データベースではこの計画が引き続き使用されますが、それは他に選択の余地がないためです。それでは、もっと適切な計画である、リスト2の1つ目の問合せ計画（SELECT /*+ first_rows(1) */ *という問合せの計画）を作成しましょう。
これこそが望ましい計画です。必要な作業は、リスト5のように、この計画を不適切な計画の代わりにロードすることだけです。
コード・リスト5：新しい適切な計画のロード
SQL> set autotrace off SQL> begin 2 dbms_output.put_line( 3 dbms_spm.load_plans_from_cursor_cache 4 ( sql_id => '5mn39tz7fpjnu', 5 plan_hash_value => 1289158178, 6 sql_handle => 'SQL_e738c19a5191e8fd' ) 7 ); 8 end; 9 / 1 SQL> select sql_handle, 2 substr(sql_text,1,10)||'...'|| 3 substr(sql_text,length(sql_text)-10) stext, 4 plan_name, enabled 5 from dba_sql_plan_baselines 6 where sql_text like 7 'SELECT%FROM t t1%(SELECT MAX(t2.object_id) - 500000 FROM t t2)'; SQL_HANDLE STEXT PLAN_NAME ENA ———————————————————— ———————— —————————————————————————————— ——— SQL_e738c19a5191e8fd SELECT * SQL_PLAN_fff61m98t3u7x971f1a3f YES ...FROM t t2) SQL_e738c19a5191e8fd SELECT * SQL_PLAN_fff61m98t3u7xda64b1bb NO ...FROM t t2)
この時点で、このSQL文には2つの計画がロードされています。1つは不適切な計画であり、無効化されています。もう1つは適切な計画であり（これが別の"異なる問合せ"に対する計画であっても、この問合せでも動作できます）、有効化されています。この問合せを再び実行すると、リスト6のように、適切な計画用の問合せ計画ベースラインが使用されます。
コード・リスト6：適切な計画が利用されることの確認
SQL> set autotrace traceonly
SQL> SELECT *
2 FROM t t1
3 WHERE t1.object_type = 'TABLE'
4 AND t1.object_id > (SELECT MAX(t2.object_id) - 500000 FROM t t2);
Execution Plan
———————————————————————————————————————————————————
Plan hash value:1289158178
—————————————————————————————————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)
—————————————————————————————————————————————————————
| 0 | SELECT STATEMENT | | 1 | 97 | 4 (0)
|* 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 97 | 2 (0)
|* 2 | INDEX RANGE SCAN | T_IDX_TYPE | 1871 | | 1 (0)
| 3 | SORT AGGREGATE | | 1 | 5 |
| 4 | INDEX FULL SCAN (MIN/MAX)| T_ID_PK | 1 | 5 | 2 (0)
—————————————————————————————————————————————————————————
Predicate Information (identified by operation id):
———————————————————————————————————————————————————————————————————————————
1 - filter("T1"."OBJECT_ID"> (SELECT /*+ PUSH_SUBQ INDEX ("T2" "T_ID_PK") *
MAX("T2"."OBJECT_ID")-500000 FROM "T" "T2"))
2 - access("T1"."OBJECT_TYPE"='TABLE')
Note
———————
- SQL plan baseline "SQL_PLAN_fff61m98t3u7x971f1a3f" used for this statement
このベースラインを使用する解決策では、永続的なヒントは存在しません。永続的なヒントの設定や利用は、思っているよりもはるかに難しいのです。本番環境で現在稼働しているヒント付き問合せの多くは時限爆弾と言えるでしょう。
また、このベースラインを使用する解決策ではアップグレードの問題は発生しません（ヒントを使用する場合、新しいクエリー・リライトやアクセス・パスなどを追加してデータベースをアップグレードした後に、すべてのヒントを完全に変更しなければならないこともあります。しかし、ベースラインを使用する解決策ではそのような変更は必要ありません）。
最後に、進化型の問合せ計画を使用している場合には、より適切な計画が現れた場合にそれを利用できます。
次のステップ
詳細
|
Tom Kyteは、オラクルのServer Technologies部門に籍を置くデータベース・エバンジェリストで、1993年からオラクルに勤務しています。Expert Oracle Database Architecture（Apress、2005年/2010年）、Effective Oracle by Design（Oracle Press、2003）などの著書があります。
Printer View