記事一覧へ戻る

掲載元 2013年5月/6月

テクノロジー：Ask Tom

効率性、索引の回避、パフォーマンスについて

オラクルの技術者が、非常に効率的なDDLを使用し、トリック質問に回答し、スカラー副問合せについてアドバイスします。

数百万のレコードを含む表があります。このうちの特定の数のレコード、たとえば10,000件を更新してコミットしたいのですが、ロールバック・セグメントの問題に陥る可能性があるため、一気に処理したくありません。最適な方法について教えていただけますか。

よくこのような質問を受けます。答えは、UPDATEを使用しないこと（または、更新ではなく消去の場合は、DELETEを使用しないこと）です。UPDATEとDELETEは、一部のレコード（一度に1レコード、あるいは10レコード）を変更するようなオンライン・トランザクション処理（OLTP）システムではたしかに優れていますが、大規模な処理には適していません。UNDOを生成する必要があり、その結果生成されるREDOは大規模なバッチ処理には不要なオーバーヘッドとなります。

このような場合に、データ定義言語（DDL）が役に立ちます（データ操作言語（DML）ではありません）。DDLではUNDO生成はすべて省略されます。また、（NOLOGGINGを使用して）REDO生成も適宜スキップできます。少なくとも、生成するREDOのサイズは最小限に抑えられます。さらに、この大規模な処理の実行中は、非効率的なバッファ・キャッシュも使用しません。そう、非効率的なバッファ・キャッシュです。

ここで、数百万件の行の更新について計算してみましょう。例として、ブロック数を100万（約7.5GBの表データ）とします。この場合、ありとあらゆるブロックをバッファ・キャッシュに読み込む必要があります（ここでは、全表スキャンを実行すると仮定します）。そのため、この時点で表全体（つまり100万ブロック）を少なくとも1回読み取っています。

この後、変更するすべての行に対して、"変更前イメージ"をUNDOセグメントに書き込んで、バッファ・キャッシュ内で（データベースにはまだ書き込まずに）表ブロックを変更する必要があります。このUNDOセグメントには、ありとあらゆる行について、UNDOセグメント内で追跡する付加的なブックキーピング情報も書き込まれます。

そのため、おそらくは表ブロック数の2倍以上のUNDOブロックがUNDOセグメントに記録されます。これにより、一度に300万ブロック（100万の表ブロックと200万のUNDOブロック）をバッファ・キャッシュに書き込むことになります。

ほとんどのケースで、この書込みのサイズはバッファ・キャッシュのサイズを上回るでしょう。また、上回らない場合でも、チェックポイントやその他のキャッシュ内で空きブロックを必要とするセッションなどが発生すれば、バッファ・キャッシュのブロックがディスクに書き込まれることになります。

この時点で、100万ブロックの読取りと300万ブロックの書込みが実行されました。さらに、生成したREDOについてはまだカウントされていません。REDOは表のブロックに加え、生成したUNDOブロックも保護することから、この処理のREDOは非常に大きなものになります。最後に、これまでのすべてのブロックを、コミット前にディスクに書き込む必要もあります。

では、この方法を、CREATE TABLE AS SELECT文を使用して変更する行を取得して書き出す方法と比較してみます。行を更新するのではなく、変更する行を選択してそれらを新しい表に配置する問合せを作成します。この方法では完全にUNDO生成をスキップして、さらにオプションでREDO生成もスキップできます。変更時にバッファ・キャッシュの使用を省略して、変更を直接ディスクに書き込みます。キャッシュ内のブロックを取得するために他のセッションと競合することはありません。

そのため、全体的なI/Oは大幅に減少し、CPU時間の使用も通常は大幅に短縮されます。これは、バッファ・キャッシュ内のデータ構造を維持する必要がなくなるためです。最後に、新しい表に索引を設定し、制約を追加し、古い表を削除して、この新しい表の名前を変更します。

表のごく一部の更新または削除を行う場合でも、通常はこのDDLアプローチを使用した方法が有効です。たとえば、100万行の表の1%を更新するとしましょう。この表にはブロックあたり約100行が格納されており、更新する必要のある行は、表全体でほぼ均等に分布しています。この場合、更新する行がすべてのブロックに約1行ずつ存在することから、結局は表全体を読み取ることになります。そのため、すべてのUNDOが生成され、バッファ・キャッシュ内に再びダーティ・ブロックが多数生成されます。

さらに、これらのブロックは1つずつディスクに書き込む必要があります。
「いや、表全体のわずか1%の行しか読み取らないので、当然ながら索引を使用すべきではないのか」
と言う人もいるでしょうが、これも正解ではありません。結局、表内のほぼすべてのブロックを読み取る必要があるので、全表スキャンが適切なアプローチとなります。

データが全体的に拡散している場合に、索引を介して数百万の行を取得することは、実際には実現不可能です。簡単な計算をしてみるだけで、それを立証できます。典型的な1ブロックのI/Oにかかる時間は、平均で約5ミリ秒です。100万個のブロックを読み取る場合、500万ミリ秒（5,000秒）、実に約1時間半もかかることになります。しかし、代わりに全表スキャンを使用して同様のI/Oを実行すると、そのデータは数秒で読み取ることができます。仮に転送レートを100MB/秒という低い値に設定した場合でも、そのデータは約75秒で読み取ることができるのです。

したがって、今回の質問のような処理については、バッファ・キャッシュを介して表の全ブロックの読取り/書込みを実行して不要な大量のUNDO（さらに場合によってはREDO）を生成する方法ではなく、DDLを使用する方法（私はこれを"残酷なほど効率的な"DDLと呼んでいます）を使用してください。

このアプローチの事例については、bit.ly/WDML5Yを参照してください。"How to Update Millions of Records in a Table（数百万のレコードを含む表を更新する方法）"に関する質問を最初に受けたのは10年以上前になりますが、その回答は数年にわたって追加されており、最終更新は数日前になっています。

パーティション表に適しているのはグローバル索引かローカル索引か

日付（startdateとします）に基づいたパーティション表と、日別のインターバル・パーティションがあります。また、日数の範囲に基づいてレポートを生成する問合せを使用しています（過去5日間のレポートなど）。さらに、時間の範囲に基づいてレポートを生成する問合せも使用しています（過去5時間のレポートなど）。パーティション内のデータや、複数のパーティションにまたがったデータにアクセスする問合せもあります。このstartdateに対してグローバル索引とローカル索引のどちらを使用すべきかについてアドバイスしてください。

これはひっかけ問題的な質問ですね。どのケースにおいても、索引を使用することを望んでないように思えます。5日間分のデータにアクセスするなど、複数のパーティションをまたがって操作するような場合、幸いにも索引はまったく使用しません。

この場合、結局はすべての行にアクセスすることになるため、5つのパーティションに対して全表スキャンを使用するのが理想的です。データをパーティション化する目的のひとつは、非効率な索引の使用を回避することです。索引は、表から数行のデータを取得する場合は有効ですが、大量の行を取得するとなれば、恐ろしいほど非効率になります。これは、前の質問で示した計算のとおりです。

すべての問合せの条件にstartdateやその他の列が含まれており、かつ通常は一部（この例では最大で4個以下）のパーティションにしかアクセスしないと思われる場合は、おそらく、ほぼすべての索引について、ローカル・パーティション索引を採用することになるでしょう。

また、これらすべての索引でstartdateが必要になるわけではないので、索引の接頭辞にstartdateを付ける必要はありません。（startdateで始まる索引は、過去N時間の問合せを行う場合のみ作成してください）。

たとえば、以下の問合せを使用するとします。

select .... 
from t
where startdate 
between sysdate and sysdate-5
and x > 100;

select .... 
from t
where startdate 
between sysdate and sysdate-2
and x > 100;

これらの問合せでは、xに対してローカル・パーティション索引を使用するとよい場合があります。ただし、それはxのみの場合です。日別のパーティション化スキームを使用しているため、1つ目の問合せでは、ちょうど5つのパーティションにアクセスすることが分かります。これら5つのパーティションのそれぞれで、x > 100に当てはまる行が非常に少ない場合は、xへの索引（ただしxのみ）は適切です。

この問合せによって、数行の検索のために索引レンジ・スキャンが5回実行されます（これは許容できる回数です）。startdateの条件によってレンジ・スキャン対象の索引パーティションが決まり、さらにその5つのパーティションのそれぞれに対してx > 100のレンジ・スキャンが実行されます。2つ目の問合せでは、索引レンジ・スキャンが2回のみ実行されます（これも許容できる回数です）。

以下のような問合せの場合は、xに対してグローバル・パーティション索引を使用してください。

select .... 
from t
where startdate 
between sysdate and sysdate-50
and x > 100;

select .... 
from t
where x > 100;

ここでも、x > 100によって候補のデータ・セットから少数のデータ行が返されると仮定します。この場合は、xに対してグローバル索引を設定すべきです。索引レンジ・スキャンを50回実行することは許容できないためです。50回のレンジ・スキャンでは処理量が多すぎます。また、2つ目の問合せでは、N回のレンジ・スキャンが実行されます（Nは表内のパーティション数）。これもおそらくは許容できません。

最後に、以下のような問合せを使用します。

select .... 
from t
where startdate 
between sysdate and sysdate-5;

select .... 
from t
where startdate 
between sysdate and sysdate-2;

この場合は、索引は必要ありません。パーティションの全表スキャンを実行して、それで終了します。索引は、大規模なデータ・セットから数行のデータを取得する場合に便利ですが、データ・セット内のすべての行を検索する場合は、索引の使用は避けるべきです。

過去5時間のデータを問い合わせる場合でも、索引は使用しないほうがよいでしょう。1日の始まりには、前日のデータの約20%が返されますが、これは索引により取得するデータとしてはあまりにも多すぎます。また、午前中の場合、現在のパーティションのすべての行が返されるため、やはり索引の意味がありません。正午には、そのパーティション内のデータの約50%が返されます。また、1日の最後には、データの約20%が返されます。これらのデータ量は索引を使用するには多すぎます。結局、使いやすい全表スキャンがもっとも適しています。

startdateに対する索引を使用するのは、多数の行から数行だけを返す問合せを実行する場合に限られます。たとえば、5分間分のデータを問い合わせる場合は、startdateに索引を作成することには意味がありますが、それ以外ではおそらく意味はありません。

一時領域の使用状況

DBAは開発者に対して、クライアント側で手の込んだ独自の結合を実装するのではなく、結合処理をデータベース・サーバーに任せるべきだと主張します。ほとんどの場合にデータベースの方がジョブを適切に実行できるからです。

しかし、Oracle Databaseはデータの処理時にTEMP表領域にすべてのデータを展開しているようです。この動作は、ディスク上での不要なソートにつながることがあり、実行速度が非常に遅くなります。

私が知る限りでは、ポインタに似たような仕組みで、ソートに使用するメモリ・サイズを最小化するアルゴリズムがあるようです。私は長い間、Oracle Databaseでもこの種のTEMPの最適化が実装されるだろうと思っていました（このような最適化がOracle Net Servicesで実装されたことは知っています。このことは、ネットワークのSnifferを使用して容易に確認できます）。

この問題に対してOracle Databaseでの解決策はありますか。TEMPが溢れないように、ディテール・レコードをソートし、その後に関連するマスター・レコードを取得するような方法はありますか。

DBAではありませんが、私も開発者にはクライアント側での結合を行わないように伝えています。しかし、開発者が取りうるアプローチはあります。それは、スカラー副問合せを使用することです。

まず、「このような最適化がOracle Net Servicesで実装されたことは知っています。このことは、ネットワークのSnifferを使用して容易に確認できます」という部分についてコメントします。

これは、Oracle Magazineの2012年1月/2月号で私が説明したことです。この最適化については、"Oracle Net Services compression"のサブセクションを参照してください。

では、目下の質問について考えましょう。この問題のデモに使用できるマスター/ディテール関係を設定することから始めます。まずは、リスト1のとおりにマスター表を作成します。

コード・リスト1：各行のサイズが大きいマスター表の作成

SQL> create table master
  2  (
3  id  number primary key,
4  c1  varchar2(4000) default rpad('*',4000,'*'),
5  c2  varchar2(4000) default rpad('*',4000,'*'),
6  c3  varchar2(4000) default rpad('*',4000,'*'),
7  c4  varchar2(4000) default rpad('*',4000,'*'),
8  c5  varchar2(4000) default rpad('*',4000,'*'),
9  c6  varchar2(4000) default rpad('*',4000,'*'),
10  c7  varchar2(4000) default rpad('*',4000,'*'),
11  c8  varchar2(4000) default rpad('*',4000,'*'),
12  c9  varchar2(4000) default rpad('*',4000,'*')
 13  );
Table created.

ご覧のとおり、マスター表の各行のサイズは非常に大きく、1行は約36KBです。次に、このマスターに関連付けるディテール表を定義します。

SQL> create table detail
  2  (
3  id         number  primary key,
4  master_id  number references master,
5  data       varchar2(40)
  6  );
Table created.

この表の各行は非常に小さく、1行は約40バイトです。マスター表と比較すると数桁違います。

次に、それぞれの表にデータを挿入します。マスター表には1行のみを配置します。これは、1件～数件のみのマスター・レコードと、数千の関連するディテール・レコードを返す問合せについて、一時領域の使用状況を調査するためです。リスト2のコードにより、この両方の表にデータを挿入します。

コード・リスト2：マスター表とディテール表への値の挿入

SQL> insert into master( id ) values ( 1 );
1 row created.

SQL> insert into detail (id, master_id, data )
2  select rownum, 1, object_name
3    from all_objects;
72900 rows created.

SQL> exec dbms_stats.gather_table_stats( user, 'MASTER', cascade=>true );
PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats( user, 'DETAIL', cascade=>true );
PL/SQL procedure successfully completed.

これで、このマスター表の1件のマスター・レコードについてディテール表と結合して、TEMP領域の問題を実証できるようになりました。実証では、ディテール表の特定の属性でソートします（リスト3を参照）。

コード・リスト3：マスター表とディテール表を結合し、ディテール表の属性を基準としてソート

SQL> set autotrace traceonly explain

SQL> select *
2    from master m ,detail d
3   where m.id = d.master_id
4     and m.id = 1
5   order by d.data;

Execution Plan
————————————————————————————————————
Plan hash value: 3160665378

———————————————————————————————————————————————————————————————————————————————
|Id|Operation                  |Name  |Rows |Bytes |TempSpc|Cost(%CPU)|Time    |
———————————————————————————————————————————————————————————————————————————————
| 0|SELECT STATEMENT           |      |72900| 2505M|       | 546K  (1)|01:49:15|
| 1| SORT ORDER BY             |      |72900| 2505M|   569M| 546K  (1)|01:49:15|
| 2|  NESTED LOOPS             |      |72900| 2505M|       | 138   (1)|00:00:02|
| 3|   TABLE ACCESS BY INDEX...|MASTER|    1|36012 |       |   1   (0)|00:00:01|
|*4|    INDEX UNIQUE SCAN      | ...  |    1|      |       |   0   (0)|00:00:01|
|*5|   TABLE ACCESS FULL       |DETAIL|72900| 2278K|       | 137   (1)|00:00:02|
———————————————————————————————————————————————————————————————————————————————

Predicate Information (identified by operation id):
——————————————————————————————————————————————————————

4 - access("M"."ID"=1)
5 – filter("D"."MASTER_ID"=1)

ご覧のとおり、約569MBの一時領域が必要になることをオプティマイザが示しています。この理由として、マスター表にある約36KBの1行を、72,900件のディテール・レコードに結合しています。ディテール表の1レコードは約40バイトです。この36KBと72,900件とを乗算した結果は約2,505MBになります。そして、オプティマイザの推測によれば、このorder by（ソート手順）の実行中に一部のデータをTEMPに格納するため、ディスク上で500MB程度のTEMP領域が必要になるということです。

この極端な例では、数十万件の細身のディテール・レコードが1つのサイズの大きいマスター・レコードに結合されるため、ディテール・レコードをソートした後に、関連するマスター・レコードを選択した方が良いでしょう。このために、まずはマスター表と同様のオブジェクト・タイプを作成します。

SQL> create or replace 
type myScalarType as object
  2  (
3  id  number ,
4  c1  varchar2(4000),
5  c2  varchar2(4000),
6  c3  varchar2(4000),
7  c4  varchar2(4000),
8  c5  varchar2(4000),
9  c6  varchar2(4000),
10  c7  varchar2(4000),
11  c8  varchar2(4000),
12  c9  varchar2(4000)
 13  );
 14  /

Type created.

次に、以下のようなSQLを使用して、マスター表のすべての列を1つの"列"として選択できます。

select myScalarType(id,c1,c2, .. c9),
from master

これで、ディテール表への問合せの際に、マスター表に対するスカラー副問合せを使用できるようになります。スカラー副問合せに関する以前の記事（"On Caching and Evangelizing SQL"）で説明したとおり、スカラー副問合せでは最大1つの行と1つの列を返す必要があります。そのため、スカラー副問合せから複数の列を取得するには、先ほど定義したオブジェクト・タイプのように、複数の属性を持つタイプを使用する必要があります。

この複数の属性を持つタイプを作成してから、リスト4に示すように、上記のデータを取得するための問合せを記述します。

コード・リスト4： スカラー副問合せを使用する方法

SQL> select x.master_data.id,
2         id detail_id,
3         data,
4         x.master_data.c1,
5         x.master_data.c2,
6         x.master_data.c3,
7         x.master_data.c4,
8         x.master_data.c5,
9         x.master_data.c6,
10         x.master_data.c7,
11         x.master_data.c8,
12         x.master_data.c9
13    from (
14  select d.*,
15         (select myScalarType( id, c1, c2, c3, c4, c5, c6, c7, c8, c9)
16            from master
17           where master.id = d.master_id) master_data
18    from detail d
19   where d.master_id = 1
20         ) x
21    order by data
 22  /

Execution Plan
————————————————————————————————
Plan hash value: 1647374361

——————————————————————————————————————————————————————————————————————————————
|Id| Operation                |Name  |Rows |Bytes |TempSpc|Cost(%CPU)|Time    |
——————————————————————————————————————————————————————————————————————————————
| 0| SELECT STATEMENT         |      |72900| 2278K|       |776    (1)|00:00:10|
| 1|  TABLE ACCESS BY INDEX...|MASTER|    1|36012 |       |  1    (0)|00:00:01|
|*2|   INDEX UNIQUE SCAN      |  ... |    1|      |       |  0    (0)|00:00:01|
| 3|  SORT ORDER BY           |      |72900| 2278K|  3168K|776    (1)|00:00:10|
|*4|   TABLE ACCESS FULL      |DETAIL|72900| 2278K|       |137    (1)|00:00:02|
———————————————————————————————————————————————————————————————————————————————

Predicate Information (identified by operation id):
—————————————————————————————————————————————————————

2 - access("MASTER"."ID"=:B1)
4 – filter("D"."MASTER_ID"=1)

リスト4の14～19行目では、ディテール表を問い合わせた後、スカラー副問合せを使用して、ディテール・レコードのそれぞれに関連するマスター・レコードを取得しています。スカラー副問合せはキャッシュされます（詳細については、上記に示したスカラー副問合せに関する以前の記事を参照してください）。

そのため、マスター表に対する問合せを76,000回以上実行しているように見えますが、実際には1回しか実行しません。問合せ計画では、まずディテール表を読み取ってソートした後に、マスター・レコードをソート後に選択して行を返していることが示されます。この計画では、TEMP領域は約3MB必要になると示されています。まったく同じ結果を返す先ほどの問合せでは約500MBと示されており、大きく異なります。

SQLトレース（sql_trace）を有効にしてこれらの問合せを実行すると、この例でのスカラー副問合せの効果を確認できます。リスト5に、それぞれの問合せのTKPROFレポートを示します（結合を使用する問合せ、スカラー副問合せの順）。

コード・リスト5： 結合を使用する問合せとスカラー副問合せのTKPROFレポートの比較

call     count       cpu    elapsed       disk     query   current        rows
—————— ———————— ————————— —————————— —————————— ————————— ————————— ——————————
Parse        1      0.00       0.00          0         0         0           0
Execute      1      0.00       0.00          0         0         0           0
Fetch     4861     14.55      29.34     323542       426        55       72900
—————— ———————— ————————— —————————— —————————— ————————— ————————— ——————————
total     4863     14.55      29.34     323542       426        55       72900

Row Source Operation
————————————————————————————————————————————————————————————————————————————
SORT ORDER BY (cr=426 pr=323542 pw=323542 time=28592898 us cost=546194 
size=2627607600 card=72900) 
NESTED LOOPS  (cr=426 pr=0 pw=0 time=97803 us cost=138 size=2627607600 
card=72900) 
TABLE ACCESS BY INDEX ROWID MASTER (cr=10 pr=0 pw=0 time=99 us cost=1 
size=36012 card=1) 
INDEX UNIQUE SCAN SYS_C0047319 (cr=1 pr=0 pw=0 time=19 us cost=0 size=0 
card=1)(object id 143020) 
TABLE ACCESS FULL DETAIL (cr=416 pr=0 pw=0 time=41772 us cost=137 size=2332800 
card=72900)

call     count       cpu    elapsed       disk     query   current        rows
——————— ——————— ————————— —————————— —————————— ————————— —————————— —————————
Parse        1      0.00       0.00          0         0         0           0
Execute      1      0.00       0.00          0         0         0           0
Fetch     4861      0.38       0.56          0       429         1       72900
——————— ——————— ————————— —————————— —————————— ————————— ————————— ——————————
total     4863      0.38       0.56          0       429         1       72900

Row Source Operation
————————————————————————————————————————————————————————————————————————————
TABLE ACCESS BY INDEX ROWID MASTER (cr=13 pr=0 pw=0 time=185514 us cost=1 
size=36012 card=1) 
INDEX UNIQUE SCAN SYS_C0047319 (cr=4 pr=0 pw=0 time=72345 us cost=0 size=0 
card=1)(object id 143020) 
SORT ORDER BY (cr=429 pr=0 pw=0 time=439523 us cost=776 size=2332800 
card=72900) 
TABLE ACCESS FULL DETAIL (cr=416 pr=0 pw=0 time=29926 us cost=137 size=2332800 
card=72900)

リスト5のレポートで分かるように、1つ目の問合せでは数十万件のI/Oが実行されています。このI/Oには、物理書込み（Row Source Operationのpw）と物理読取り（pr）が含まれます。これらは、TEMPに対する読取りと書込みを表します。また、この問合せでのCPU使用量は非常に大きく、14 CPU秒を超えています。これはメモリとディスクとの間で、40バイトのレコードではなく約36KBのレコードを入れ替える必要があったためです。

一方、2つ目の問合せではスカラー副問合せを利用しており、この例では物理I/Oは発生していません。実環境では、部分的に物理I/Oを実行しなければならないこともありますが、その回数は非常に少なくなります。また、1件40バイトのレコード72,000件をソートするためのCPU使用量は、36KBのレコードをソートするためのCPU使用量よりも大幅に少なくなっています。全体的に、問合せにかかった時間は30秒から0.5秒までに短縮されました。

では、スカラー副問合せのキャッシュが、すべてのパフォーマンス問題を解決する答えとなるのでしょうか。もちろん、違います。問合せが数千のマスター・レコードを返す場合（各マスター・レコードにディテール・レコードがほとんど関連付けられないような、より一般的な状況）では、索引を介して各レコードを検索する方が、ディテール表自体をソートする利点を上回ります。それでも、スカラー副問合せは、以前のスカラー副問合せに関する記事で詳細に説明した一部のケースなどで使用できる手段のひとつです。そのような特殊なケースで使用することを検討してください。

▲ ページTOPに戻る

記事一覧へ戻る