津島博士のパフォーマンス講座 indexページ▶▶

津島博士のパフォーマンス講座 
第20回 パラレル実行について

■津島博士による解説が動画でも! セミナー動画公開中です。
津島博士のパフォーマンス講座「パフォーマンス問題はなぜ起きるのか」  【WMV】 【MP4】 【PDF

皆さんこんにちは、今年の夏も非常に暑いですが体調はいかがでしょうか。 。私は夏休みを頂いたのでリフレッシュでき多少元気です。 今回は、このコラムの様々なところで高速化するには並列化が一般的な手法ですと説明していますが、Oracleデータベースの基本機能であるパラレル問合せ、パラレルDML、パラレルDDLについて説明していなかったように思いますので、このようなパラレル実行について説明しようと思いますので、参考にして下さい。

■ 1. パラレル実行とは
まずは、パラレル実行についての概要を説明しましょう。
Oracleデータベースは、パラレル問合せ、パラレルDML、パラレルDDLをパラレル実行することができます。PARALLEL句(テーブルの属性、索引の属性など)を指定されているか、またはPARALLELヒントを指定している場合に動作します。また、PARALLELを指定していない場合でも、以下のALTER SESSION FORCE PARALLEL文で強制的にパラレル化することも可能です。ただし、DMLについてはデフォルトがDISABLE(シリアルで実行)になっていますので、このSQL文でDISABLE以外にしないとパラレルで動作しませんので注意して下さい。

SQL> ALTER SESSION {ENABLE|DISABLE|FORCE} PARALLEL {DML|DDL|QUERY} [PARALLEL <パラレル度>];

ENABLE :PARALLEL句またはPARALLELヒントのパラレル度で実行する
DISABLE:シリアルで実行する
FORCE  :PARALLEL句またはPARALLELヒントが指定されていない場合でもパラレルで実行する(この文でPARALLEL <パラレル度>を指定していない場合はデフォルト・パラレル度になります)

このパラレル実行は、パラレル・スレーブ・プロセス(PQプロセス)を使用して最大2セット(2つの処理)までを同時に行います。例えば、以下のSQL文を実行すると3つの処理(スキャン、Group-by 、Order-by)が動作しますが、以下の右図のように最初にスキャンとGroup-byを同時に行って、その後にGroup-byとOrder-byを行うことになります(これを操作間パラレル化と呼びます。これに対してパラレル度のことを操作内パラレル化と呼びます)。そして、最終的にコーディネータ・プロセス(QCプロセス)が結果をまとめます。

img_tsushima_110119_01.jpg

以下のようなUNION ALLなどを行うと、それぞれのSELECT毎にパラレル実行されますが、同時に行うことはありません(実行計画のTQ列にQ1とQ2が出力されているので、2つのパラレル実行が動作していることを意味します)。パラレル実行されると実行計画には、以下のようにTQ(テーブルキュー:プロセス間通信するための構造の総称です) 、IN-OUT(プロセス間通信の入力と出力)、PQ Distrib(データ分散処理方法)の列とOperationにパラレル操作(PX SEND HASH、PX RECEIVEなど)が追加されます。これの説明は後で行います。

 


img_tsushima_110119_01.jpg

ただし、PQプロセスの最大起動数は、初期化パラメータPARALLEL_MAX_SERVERS(デフォルト値は、CPU_COUNT×PARALLEL_THREADS_PER_CPU×10です。初期化パラメータPGA_AGGREGATE_TARGETが未設定のときは、CPU_COUNT×PARALLEL_THREADS_PER_CPU×5です)までになりますので注意して下さい。このとき要求した数のPQプロセスが利用できない場合は、初期化パラメータPARALLELE_MIN_PERCENTの指定(0~100)により以下のように動作します。
・0:獲得できた数でパラレル実行するが、1つも確保できなければシリアル実行する(これがデフォルトです)
・1-100:要求した数に対して、指定した割合が確保できない場合はエラー(ORA-12827: 使用可能なパラレル問合せスレーブが足りません。)とする(50の場合は、要求した数の半分が確保できればパラレル実行することになります)
これが、パラレル実行の基本的な動作になります。次に、それぞれのパラレル実行について説明します。

(1)パラレル問合せ
パラレル問合せ(SELECT文)では、以下のような処理がパラレルで実行されます。
・全表スキャン(FULL TABLE SCAN)
対象テーブルのパラレル度が2以上に設定されていると動作します。
・索引高速スキャン(INDEX FAST FULL SCAN)
対象索引のパラレル度が2以上に設定されていて、必要な列がすべて索引に含まれているときに動作します(テーブルにアクセスする必要がないときです)。
・パラレル索引スキャン
第10回で説明したようにパーティション索引で2以上のパーティションがアクセスされると動作します。
・結合
すべての結合で動作します。
・ソート
ORDER BYなどのソートもパラレルで動作しますが、第9回で説明したようにレンジ分割で行うため、データが偏り易いです。
・グループ関数の集計
以前は、重複値を求めるためにソート処理を使用していましが、Oracle Database 10gR2からはハッシュ集計機能により、データもハッシュ分割で行っています。

(2)パラレルDML
パラレルDMLの対象はSELECT以外のDML文(INSERT,DELETE,UPDATE,MERGE)です。動作するには多少制約があります(同じテーブルに対するパラレルDMLは、トランザクションの最初でかつ唯一である必要があります)ので注意して下さい。トランザクション内でパラレルDMLが実行されたテーブルに後から再度アクセスすると以下のようにエラー(ORA-12838)になります。また、シリアルDMLされたテーブルに後からパラレルDMLを行う場合もエラー(ORA-12839: オブジェクトは、変更された後はパラレルで変更できません。)になります。

SQL> ALTER SESSION FORCE PARALLEL DML PARALLEL 4 ;
SQL> INSERT INTO tab02 SELECT * FROM tab01;
SQL> SELECT * FROM tab02;
ORA-12838: オブジェクトは、パラレルで変更された後は読取り/変更できません。

Oracle9iR2からはUPDATE,DELETE,MERGEについてもパーティション内パラレルDMLが可能になっています(パーティション化していないテーブルやパーティション・プルーニングでアクセスするパーティションが1つになってもパラレルDMLが動作するようになっています)ので、使いやすくなりました。それ以前は、パーティション化していて、アクセスするパーティションが2以上でないと動作しませんでしたので(最大パラレル度がアクセスするパーティション数になっていました)、使用するのが限られていたと思います(パラレル索引スキャンと同じでした)。

(3)パラレルDDL
パラレルDDLが動作するのは、データ・ディクショナリの操作以外にデータもアクセスするようなDDL文(CREATE INDEX、ALTER INDEX、CREATE TABLE AS SELECT(CTAS)など)です。このようなSQLはダイレクト操作で(ダイレクト・パス・インサートと同様に)実行されますので、NOLOGGINGモードで行うとREDOログやUNDOデータを生成しないで動作することができます。ただし、以下のようにNOLOGGINGを付けないとREDOログにブロック・イメージを出力しますので注意して下さい。

SQL> CREATE INDEX <索引名> ON <テーブル名> (列名,…) NOLOGGING PARALLEL [<パラレル度>] ;

パーティション表のローカル索引をパラレルで作成する場合は、最大パラレル度はパーティション数になりますので(以下の実行計画のように1つのPQプロセスが1パーティションの索引を作成します)、パーティション間にデータの偏りがある場合には注意が必要です(グローバル索引も最大パラレル度は索引パーティション数になりますが、索引パーティションへの分散処理が発生するため、ローカル索引のように偏ることはあまりないと思います)。

img_tsushima_110119_01.jpg

偏っている場合は、以下のようにUNUSABLE句を付けて作成して、ALTER INDEX文でパーティション毎にローカル索引をパラレルで作成して下さい。

SQL> CREATE INDEX <索引名> ON <テーブル名> (列名,…) NOLOGGING LOCAL UNUSABLE ;
SQL> ALTER INDEX <索引名> REBUILD PARTITION <パーティション名> PARALLEL [<パラレル度>] ;

■ 2. パラレル実行の問題点

パラレル実行は効率良く動作するとリニアに性能が向上しますが、何かの問題により常にそうなるとは限りません。ここからは、パラレル実行するときに気にする(注意する)必要があることについて説明します。
パラレル化の注意点としては、各PQプロセス間でのデータ転送の詰りによる性能ダウンです(RAC環境ではインターコネクトの帯域幅の限界による性能ダウンも発生する可能性があります)。基本はオプティマイザが最適なものを決定するため気にする必要はありませんが、いざという時に役に立つと思いますので、考えられる原因を以下にまとめてみました。このような場合は”PX Deq”で始まる待機イベントが発生すると思います。この待機イベントについては別の機会に説明しようと思います。
・データ分割の偏り
・プロセス間通信(転送のオーバーヘッド)
・同時実行(多重実行)
・パラレル度
それでは、それぞれについて説明します。

(1)データ分割の偏り
処理プロセスから次の処理プロセスにデータを移行するときに、それぞれの処理によって分散処理方法を変えて行います。この分割をするときには、データが偏らないようにする必要があります。このデータの偏りが発生するような場合には、パラレル度を上げても性能はリニアに向上しませんので注意が必要です(どれかのプロセスが処理するデータ量が多くなると、そのプロセスが終了するまで次の処理に移れないからです)。
この分割の方法には、ハッシュ(ハッシュ関数で分割)、レンジ(データの範囲で分割)、ブロードキャスト(すべてのデータを各プロセスに転送する)などがあります(ブロードキャストは、データが小さい場合に分割して偏るより、すべてのデータを各プロセスに与えた方が効率が良いという判断によって実行されます)。第9回でパラレルソート処理(SORT ORDER BY)はレンジ分割なので偏り易いですと説明しましたが、その他の分割でも起きる場合がありますので注意が必要です。この偏りは、以下のSQL文のNUM_ROWS、BYTES(各プロセスが処理したサイズ)で確認できます(以下は2パラレルで実行した例です。TQ_IDが実行計画のTQに対応し、SERVER_TYPEのProducerが送信側、Consumerが受信側になります)。第4回で説明したリアルタイムSQL監視を使用するともっと簡単に調べられます。

img_tsushima_110119_01.jpg

どのように分割しているかは、以下の実行計画の「PQ Distrib」で確認できます。PX SEND操作のときに出力されます(これは、次のプロセスへのデータ送信を意味し、その分割方法を出力します。P->Pはパラレルからパラレルを意味します)。

img_tsushima_110119_01.jpg

このデータ分割は、テーブル結合またはインサート(INSERT...SELECT, CTAS)の場合だけPQ_DISTRIBUTEヒントで指定することができます。PQ_DISTRIBUTEヒント(テーブル結合の場合)は、PQ_DISTRIBUTE(<内部表>,<外部表の分散処理>,<内部表の分散処理>)と指定します。以下は、上記に対してテーブル"TAB01"のデータ分割をBROADCASTにしている例です(PX SEND BROADCAST操作に変わっています)。

img_tsushima_110119_01.jpg

外部表と内部表の分散処理に以下の組合せで指定が可能です。この外部表とは、結合のときに最初にアクセスされるテーブルのことで、第10回で説明したデータロードなどで使用する外部表(External Table)機能ではありませんので注意して下さい。
・HASH-HASH:ハッシュ関数で分割する(一般的な結合時に行う分割です)
・BROADCAST-NONE、NONE-BROADCAST:小さいテーブルがブロードキャストされるため、もう一つのテーブルはデータ分割する必要がない(上記実行計画のようにスキャンと結合はデータ分散処理がなくなる)
・NONE-PARTITION、PARTITION-NONE:パーシャル・パーティション・ワイズ結合を行う
・NONE-NONE:フル・パーティション・ワイズ結合を行う

PQ_DISTRIBUTEヒント(インサートの場合)は、PQ_DISTRIBUTE(<インサート表>,<インサート表の分散処理>)と指定します。例えば、パーティション表にインサートするときでもデータ分割しないので、データの偏りが大きい場合があります(1つのPQプロセスが特定のパーティションだけにインサートするなどです)。以下のようにインサートの実行計画は、データ分割をしないためTQが1つしか使用していません(そのため、1つのPQプロセスはスキャンとインサートを行います)。

img_tsushima_110119_01.jpg

偏っている場合は、以下のようにインサートのデータ分割をRANDOMにすることでデータの偏りを改善することができます(データ分散処理をするためにTQが2セットになりPX SEND ROUND-ROBIN操作が出力されます)。

img_tsushima_110119_01.jpg

インサート表の分散処理には以下が指定できます。
・NONE:何もしない(SELECTしたデータを分散せずにインサートする。これがデフォルトです)
・PARTITION:インサート表のパーティション条件で分割する(パーティションに偏りがないときに使用する)
・RANDOM:ランドロビン法で(値に関係なく)分割する(パーティション表などに対してNONEでデータに偏りがある場合に使用する)
・RANDOM_LOCAL:パーティションとPQプロセスを複数のグループに分割してインサートするため、PQプロセスがアクセスするパーティション数を削減します(RANDOMだと全パーティションにアクセスするため、使用メモリを削減したいときに使用します)

パーティション・ワイズ結合について
ご存知ない方のために、ここでパーティション・ワイズ結合(PWJ)について簡単に説明します。
PWJは、結合列でパーティション化している場合に行うパラレル結合です。以下の図のように2つのテーブルの結合列が同じパーティションになっている(パーティション方法とパーティション数が同じ)場合はフルPWJ(左図)が動作し、そうでない場合はパーシャルPWJ(右図)が動作します。このとき、スキャンから結合はデータ分割する必要がないため、1つのPQプロセスで行います(フルPWJは2つのテーブルとも、パーシャルPWJは片方のテーブルのみ行います)。そのため、オーバーヘッドが少なく結合を行うことができます。パラレル度とアクセスするパーティション数が同じならば更に効果的に行うことが可能です。

img_tsushima_110119_01.jpg

(2)プロセス間通信
偏りを無くした後は、そのデータ通信量をできるだけ少なくすることです。Oracleデータベースは、内部的にデータ通信量を削減するようなことを行います。例えば、スキャンとGroup-byを行う処理があったとします。このときに、スキャンする件数が多くてGroup-byに渡す必要のあるデータが多い場合に、Group-by処理する列(GROUP BY句に指定されている列)の重複度が高いと、スキャンの段階でもGroup-byを行って件数を削減して通信量を少なくしてくれます(これは、Group-byを2回行うコストよりデータ通信のコストが高いと判断されたからです。特にRAC環境では効果が大きいです。これはGroup-byの前の処理が結合などでも行われます)。これをGroup-by pushdown(GPD)といいます。これが行われると実行計画は以下のように出力されます(以下の右の実行計画でGPDが行われているため、HASH GROUP BYが2回出力されています)。

img_tsushima_110119_01.jpg

この動作は自動的に行われますので(内部的に持っている閾値より重複度が大きい場合に自動的に実行されます)、気にする必要はありません。ただし、重複値を排除するためにDISTINCT句を使用しているSQLなどでは、Group-by に置き換えることで性能が向上する場合がありますので検討してみて下さい。これは、以下のようにHASH UNIQUEはGPDされないが(左側)、HASH GROUP BYはGPDされるからです(右側)。

img_tsushima_110119_01.jpg

(3)同時実行
次に、悩むのが同時実行のときにどのようにパラレル実行するかです。単独でパラレル実行を行えば処理時間を短縮できるのは分かるが、同時に実行する必要がある場合はパラレル度をどのように設定するかが悩むところです。同時実行するときは、プロセスが多いことによるオーバーヘッドとI/Oの競合が発生して効率が低下する場合がありますので注意が必要です。そうはいってもパラレル度を負荷状況によって調整するのは大変ですし、パラレル度を固定化するのも効率が悪いです。理想としては、負荷が多いときと少ないときで、効率良くリソースを調整することです。そのため、Oracleデータベースによる自動調整機能を使用すると良いです。初期化パラメータPARALLEL_ADAPTIVE_MULTI_USERをTRUEにするとアクティブ・セッション数によってパラレル度が自動的に調整されます(指定されたパラレル度を最大性能になると判断して、同時実行数によってパラレル度を減少して行きます。これによって、1実行当たりが使用するPQプロセス数を少なくして、利用しているPQプロセス数が初期化パラメータPARALLEL_MAX_SERVERSを超えてシリアル実行やORA-12827エラーになることを抑えてくれます)。つまり、負荷状況によってパラレル度を調整してくれます。

(4)パラレル度
PARALLEL_ADAPTIVE_MULTI_USERを使用する場合でも、個々のSQLのパラレル度(テーブルのPARALLEL句など)は設定する必要があるためベストにするのは大変です(これは、同じテーブルをアクセスするSQLでも負荷が高いものと低いものがあり、SQL毎に調整するにはヒントなどを指定する必要があり、ベストな実行は難しいからです)。
そのため、Oracle Database 11gR2からは、より効果的にパラレル度を設定してくれる自動並列度設定機能が提供されましたので、これを使用することをお奨めします。自動並列度設定は、初期化パラメータPARALLEL_DEGREE_POLICYをMANUAL以外(AUTOまたはLIMITED)にしたときに動作します。LIMITEDはパラレル度がデフォルト(パラレル度を明示的に指定しないSQLに対してのみ)の場合に有効になります。それ他は以下の初期化パラメータで調整されます。
・PARALLEL_DEGREE_LIMIT
SQL文の最大並列度を制御します(デフォルトはCPUです)。
“CPU”の場合は、PARALLEL_THREADS_PER_CPU×CPU_COUNT×<起動インスタンス数(RAC環境のみ)>が最大並列度になります。
“IO”の場合は、DBMS_RESOURCE_MANAGER.CALIBRATE_IOプロシージャにより計算されたI/O 統計情報を元に最大の並列度を決定します(並列度を上げるとCPUよりI/O性能の影響が大きい場合に指定します)。
“数値”を指定すると、指定した数値を自動並列度設定の最大並列度とします。
・PARALLEL_FORCE_LOCAL
RAC環境でのパラレル実行を制御します。TRUEを指定するとローカル・インスタンスでのみ実行されます(デフォルトはFALSEです)。
・PARALLEL_MIN_TIME_THRESHOLD
パラレル実行される閾値を設定します(デフォルトは10秒です)。シリアル実行の見積時間が、この時間以上になる場合にパラレル実行になります。これによって、NO_PARALLELヒントなどを使用せずに、シリアルとパラレルの自動判断を効果的に行うことが可能です。パラレル実行は時間の掛る処理には効果的ですが、短いものにはあまり効果がありません(逆にオーバーヘッドが大きいです)。このように自動判断することで管理を簡単にしてくれます。

自動並列度設定を使用した場合には、それぞれのSQLのパラレル度は以下のように実行計画の”Note”部に出力されます(例はパラレル度が12で動作したことを示しています)。

img_tsushima_110119_01.jpg

初期化パラメータPARALLEL_DEGREE_POLICYをAUTOにすると、第13回で少し触れたOracle Database 11gR2からのIn-Memory PX(In-Memory Parallel Execution)も動作しますので、ここでIn-Memory PXを少し説明しようと思います。パラレル実行ではダイレクト・リードを行っていましたが、サーバマシンに搭載できる物理メモリの増加と圧縮機能によりDBバッファ・キャッシュ上に大容量のテーブルも載せることが可能になってきました。そのため、パラレル実行するような大容量なテーブルもDBバッファ・キャッシュ上に読込んで処理を高速化する方が効率的な場合があります。そこで、DBバッファ・キャッシュに載せる閾値(小さなテーブル)とは別に、In-Memory PX が動作するときはセグメント・サイズの閾値もDBバッファ・キャッシュの80%未満とすることで、多くのテーブルを載せることが可能になっています。
そして、初期化パラメータPARALLEL_DEGREE_POLICYをAUTOすることでOracle Database 11gR2からのパラレル・ステートメント・キューイング機能も利用が可能になり、初期化パラメータPARALLEL_SERVERS_TARGETを指定することで動作します。これは、利用されているPQプロセスがPARALLEL_SERVERS_TARGETの値を超えた場合に、パラレル実行のSQLがキューイングされます。このパラレル・ステートメント・キューイング機能を使用することで、PQプロセスが不足してもORA-12827エラーの発生やシリアル実行させずにSQLをキューイングさせることが可能です(時間が掛るSQLは、シリアル実行するより少し待ってパラレル実行した方が時間が短い場合があるからです)。キューイングされているSQLはv$sql_monitorのstatus列(EXECUTING,QUEUED)で確認できます(以下が出力例です)。

img_tsushima_110119_01.jpg

■ 3. おわりに

今回はパラレル実行について説明しました。すべてを詳細に説明することはできませんでしたので、また機会があれば説明しようと思います。次回も頑張りますのでよろしくお願いします。質問をお待ちしています。
それでは、次回まで、ごきげんよう。

~第20回目を迎えて津島博士からひとこと~
昨年の1月から始まった連載ですが、ついに第20回を迎えることが出来ました。
これも多くの皆様に読んでいただいたからと感謝しております。
最初は、軽い気持ちで始めた連載なのですが、自分でもよくここまで続けられたと 驚いています(本音を言うと、いつも原稿の締め切りが大変なのですが、 皆様が記事を待っていると思って頑張ってきました)。
このような連載を書いていると、Oracleデータベースは昔に比べて、使い易くなった なと改めて実感します。そうはいっても、まだパフォーマンス問題に悩まれている方 も多いと思いますので、これからもそのような方のお役に立てればと思っております。
それから、皆様からの問い合わせがないと終ってしまうかもしれませんので、 これからもよろしくお願いいたします。

津島博士

img_tsushima.gif ■津島博士より
長年に渡りデータベースの構築やパフォーマンスチューニングなどに従事し、最近では若手エンジニアの育成および大規模データベース案件などの支援に従事しております。今までの経験が少しでもお役に立てればと思い、この連載を始めることにいたしました。できるだけ長く続けたいと思いますのでよろしくお願いいたします。

Oracle Databaseは、技術の進化により非常に扱いやすくなったと思います。私自身も昔のバージョンを使用したころに比べると非常に楽になったと感じています。いろいろと進化したとはいえパフォーマンス問題が発生しなくなった訳ではありません。今でも多くの担当者が色々と苦労していると思います。その中でスキルや機能を知らずに苦労している場合もあるように思いますので、ここで紹介していけたらと考えています。

この連載では、このようなOracle技術者(データベース技術者)の方へのアドバイスとして様々なパフォーマンス問題を題材に解説していこうと考えています。既にデータベース運用を行っている管理者、これから管理者を目指す方までを対象に、様々な疑問に対して少しでも何かの手助けになればと願っています。できるだけ読者の皆様からの疑問に答えていきたいと思っておりますので(問合せなどの具体例を使用して説明した方が分かりやすいと思いますので)、パフォーマンス問題に関する様々な質問をお願いいたします。

津島博士の記事についてのご質問はこちらまでお願いいたします。

津島博士のパフォーマンス講座 indexページ▶▶