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

 

皆さんこんにちは、関東も梅雨入りしましたね。雨が多くジメジメした嫌な時期ですが、めげずに頑張りましょう。
今回からOracle Database 12cR2（Oracle12cR2）で拡張された機能について説明しようと思います。今回はOracle12cR2からSQL処理がいくつか強化されていますので、その強化された機能について説明しますので、参考にしてください。

1. SQL自動変換での強化
まずは、Oracle12cR2から強化されたSQL処理で、以下のSQL自動変換（問合せ変換）について説明します。これは過去の連載でも問題点として説明しているので、私は非常に嬉しい強化だと思います。

  • OR Expansion（OR拡張）の強化
  • Partition-wise DISTINCT（パーティション・ワイズDISTINCT）の強化
  • Cursor-Duration Temporary Tables（一時表変換の強化）

（１）OR Expansion（OR拡張）の強化
第9回で説明したように、OR条件やINリストを使用すると、OR拡張によってそれぞれの条件を別問合せとすることで、索引を使用できるようにします。ただし、この問合せの分割は、これまで分かりやすいように’UNION ALL’と説明していましたが、正確には以下のようにCONCATENATION（連結演算子）が使用されていました（これはCONCATENATIONというSQLがないので、同じような’UNION ALL’と説明した方が分かりやすいからです）。実行計画を見ていただくと、CONCATENATIONと出力されているので、’UNION ALL’でないことが分かります。

SQL> SELECT c11, c12, COUNT(*) FROM tab10 WHERE c11=1 OR c12=2 GROUP BY c11,c12;

実行計画(12.1)
————————————————————
| Id  | Operation                             | Name       |
————————————————————
|   0 | SELECT STATEMENT                      |            |
|   1 |  HASH GROUP BY                        |            |
|   2 |   CONCATENATION                       |            |
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| TAB10      |
|*  4 |     INDEX RANGE SCAN                  | TAB10_IX12 |
|*  5 |    TABLE ACCESS BY INDEX ROWID BATCHED| TAB10      |
|*  6 |     INDEX RANGE SCAN                  | TAB10_IX11 |
————————————————————
Predicate Information (identified by operation id):
—————————————————
   4 – access(“C12″=2)
   5 – filter(LNNVL(“C12″=2))
   6 – access(“C11″=1)

Oracle12cR2からは、以下のように本当の’UNION ALL’（UNION-ALL演算子）が実行されるように変更されました。これにより、第52回で説明したパラレル実行で、一部がシリアル処理される問題が改善されます（第52回では、手動で’UNION ALL’のSQLを作成してくださいと説明していますが、それが必要なくなります）。パラレル実行でOR拡張が必要になるのは、OR条件で索引を使用するなどが目的のため、そんなに多くないと思います。ただし、オプティマイザが誤って変換してしまう場合もあるので、そのようなことをあまり気にする必要がなくなるのは嬉しいことです。

SQL> SELECT c11, c12, COUNT(*) FROM tab10 WHERE c11=1 OR c12=2 GROUP BY c11,c12;

実行計画(12.2)
——————————————————————
| Id  | Operation                              | Name            |
——————————————————————
|   0 | SELECT STATEMENT                       |                 |
|   1 |  HASH GROUP BY                         |                 |
|   2 |   VIEW                                 | VW_ORE_943AE5F6 |
|   3 |    UNION-ALL                           |                 |
|   4 |     TABLE ACCESS BY INDEX ROWID BATCHED| TAB10           |
|*  5 |      INDEX RANGE SCAN                  | TAB10_IX11      |
|*  6 |     TABLE ACCESS BY INDEX ROWID BATCHED| TAB10           |
|*  7 |      INDEX RANGE SCAN                  | TAB10_IX12      |
——————————————————————
Predicate Information (identified by operation id):
—————————————————
   5 – access(“C11″=1)
   6 – filter(LNNVL(“C11″=1))
   7 – access(“C12″=2)

また、この処理のための新しいヒントとして、OR_EXPAND（’UNION ALL’に変換する）とNO_OR_EXPAND（’UNION ALL’に変換しない）が追加されているので、強制的に実行させることも可能になっています（これまでのUSE_CONCATヒントを使用すると、CONCATENATIONになってしまうので注意してください）。
第9回では、OR拡張されたときの正確なSQLまでは記述していませんでしたが、INリストでないとき（異なる列のOR条件のとき）に、正確には以下のようなSQLに変換されます（上記の実行計画の述語情報からも分かると思います）。このLNNVL関数は、’c11 != 1 OR c11 IS NULL’の意味になります（すでに実行された問合せブランチの条件が、検索対象にならないようにする必要があるからです）。手動で書き換えるときには注意してください。

SQL> SELECT c11, c12, COUNT(*) FROM tab10 WHERE c11=1 AND GROUP BY c11,c12
2  UNION ALL
3  SELECT c11, c12 COUNT(*) FROM tab10 WHERE LNNVL(c11=1) AND c12=2 GROUP BY c11,c12;


（２）Partition-wise DISTINCT（パーティション・ワイズDISTINCT）の強化
第45回で説明したように、これまでパラレル実行でのパーティション・ワイズ処理はGROUP BY句だけが動作して、DISTINCT演算子では以下のように動作することができませんでした（DISTINCTするSELECTリスト列でパーティション化しても、通常のハッシュでデータ再分散を行います）。そのため、これまではできるだけGROUP BY句を使用するようにと説明してきましたが、それが改善されています。

tsushima-60-1

Oracle12cR2からは、以下のようにパラレル実行のDISTINCT演算子が、パーティション・ワイズ処理で動作するようになりました（以下の実行計画は、列c1でレンジ・パーティション化しているときに実行したもので、’HASH UNIQUE’をデータ再分散しないで処理できるようになっています）。そのため、パラレル実行時に、各パーティションをそれぞれのPXプロセスで処理することができるので、データ再分散のオーバーヘッドやPGAの使用サイズ（またはTEMP領域のサイズ）を削減することができるようになります。

tsushima-60-2

（３）Cursor-Duration Temporary Tables（一時表変換の強化）
第54回で説明したように、同じ表を何度もアクセスするような処理は、一時表変換を行って何度もアクセスしないようにしますが、これまで一時表は一時セグメントとして作成されていました（一時表変換を行うと、’TEMP TABLE TRANSFORMATION’と実行計画に出力されます）。
Oracle12cR2からは、一時表をメモリ内で行うことができるように強化されました。これをカーソルのオープン中に繰り返し使用できることから、Cursor-Duration Temporary Tables（Cursor-Duration一時表）と呼びます。そのため、この一時表変換を使用する以下のような処理で、性能が向上するようになります。

  • スター型変換
  • WITH句
  • GROUP BY句のGROUPING SETSオプション
  • 再帰問合せ（Oracle12cR2から）

以下のように、WITH句のSQLなどを実行した場合、この機能により一時表変換がメモリ上で行われると、実行計画の’LOAD AS SELECT’操作に'(CURSOR DURATION MEMORY)’と出力されます。
メモリ上にすべて格納できないときには、ソート処理などのように一時セグメントへのリード/ライトを行うのではなく、メモリに入らない残りのデータだけが一時セグメント上に格納されます。このメモリ領域は、シリアル実行だとPGA、パラレル実行だとMGA（プロセス間で共有される領域）が使用されます。

SQL> WITH w_abc AS (SELECT /*+ MATERIALIZE +/ 部門,sum(売上) 部門売上 FROM 売上 GROUP BY 部門)
2  SELECT * FROM w_abc WHERE 部門売上 < (SELECT avg(部門売上) FROM w_abc);

実行計画(12.2)
——————————————————————————
| Id  | Operation                                | Name                      |
——————————————————————————
|   0 | SELECT STATEMENT                         |                           |
|   1 |  TEMP TABLE TRANSFORMATION               |                           |
|   2 |   LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D7DCE_156F6F |
|   3 |    HASH GROUP BY                         |                           |
|   4 |     TABLE ACCESS FULL                    | 売上                      |
|*  5 |   VIEW                                   |                           |
|   6 |    TABLE ACCESS FULL                     | SYS_TEMP_0FD9D7DCE_156F6F |
|   7 |    SORT AGGREGATE                        |                           |
|   8 |     VIEW                                 |                           |
|   9 |      TABLE ACCESS FULL                   | SYS_TEMP_0FD9D7DCE_156F6F |

WITH句は、問合せにマージする場合もありますが、上記のようにMATERIALIZEヒントで一時表変換を強制させることもできます（SQLチューニング時に役に立つときもあるかと思い載せておきました）。


GROUP BY句のGROUPING SETSオプションについて
ご存知ない方のために（使用している方はあまり多くないと思うので）、ここでOracle9iからのGROUP BY句のGROUPING SETSオプションについて簡単に説明します。
GROUPING SETSは、Oracle9iから小計や総計を行うことができるように、ROLLUPやCUBEと追加されたGROUP BY句のオプションです。グループごとに集計する場合には、GROUP BY句と集計関数を使用しますが、このとき集計単位（集計したい列の組み合わせ）を一つだけしか指定できません。そのため、複数の集計単位を使用した処理を行う場合には、以下の右側のような集計単位の数だけUNION ALLを使用して、一つに結合する必要があります。しかし、この方法では、SQL文が複雑になる上、処理の対象となる表が複数回スキャンされるので、あまり効率がよくありません。GROUPING SETSオプションにより、これまで一つだけしか指定できなかった集計単位を指定することが可能になり、一つのSQL文で効果的に実行できるようになります（以下の集計単位のカッコは、複数列以外は必須ではありません。また、列名がない'()’は、全体の合計になります）。

tsushima-60-3

実行計画は、以下のように一時表に格納してから、それぞれの集計単位でGROUP BYを行うような動作になります。集計単位の数だけ一時表をアクセスする必要があるので、WHERE句の条件で行数を多く絞り込むようなときは効果がありましたが、それ以外はあまり効果的ではありませんでした。Oracle12cR2からこの一時表がメモリを使用できるようになるので、より効果的に動作できるようになります。

tsushima-60-4


2. その他のSQL処理
次に、Oracle12cR2から強化されたSQL処理で、その他のSQLで気になった以下について説明します。

  • Recursive WITH（再帰問合せ）の強化
  • Band Join（バンド結合）の強化

（１）Recursive WITH（再帰問合せ）の強化
第31回で説明したように、再帰問合せを実行すると、再帰的ブランチで実行結果をもとに何度も表と結合する必要があるので（索引を使用する必要があり、効果的にパラレル実行ができないので）、完全にパラレル実行することができませんでした（初期化ブランチだけしかパラレル実行できませんでした）。そのため、第31回では、テーブル・ファンクションを使用してくださいと説明していますが、これも必要なくなります。
Oracle12cR2からは、以下のように一時表変換を使用して、パラレル実行するように改善されました（このとき「（３）一時表変換の強化」で説明したメモリ上の一時表も使用されます）。動作としては、繰り返し求める途中結果を一時表に追加格納して、その一時表とソートバッファ上の表を結合して、それぞれのレベルの結果を求めていきます（以下の実行計画では、表’bom’をソートバッファに格納するために、’BUFFER SORT (REUSE)’を行っています）。また、一時表を検索するときに、必要ないデータを結合対象にしないため、フィルター条件（”T”.”INTERNAL_ITERS$”=LEVEL）を実行しています。このようにパラレル実行できるようになっているので、部品展開などの再帰的に実行するような処理で、性能問題になっている方は使用を検討してみてください。

tsushima-60-5

ただし、以下のような階層問合せ（CONNECT BY）では、これまでのようにパラレル実行されないので、パラレル実行する必要があるときには再帰問合せに書き換えてください。

SQL> SELECT oya, ko, cnt, LEVEL FROM bom
  2   START WITH oya = 'A01‘
  3   CONNECT BY PRIOR ko = oya  ;


（２）Band Join（バンド結合）の強化
バンド（範囲条件）を使用した結合処理は、頻繁に使用される処理ではありませんが、複雑な分析を行うときなどに使用される場合が多いようです。このときには、ソート・マージ結合を行いますが、その’SORT JOIN’でフィルター処理を行う場合に、一つの条件だけしかフィルターすることができません。そのため、二つの条件で範囲を指定するBETWEEN条件などに対して、以下の左側のようにFILTER操作を行う必要がありました。これは対象行数が多くないときには、そんなに気にする必要はありませんが、行数が多いと性能問題になる場合もありました。
Oracle12cR2からは、’SORT JOIN’で二つの条件を同時にフィルター処理することができるようになり、処理効率が改善されています（以下の右側の実行計画のように、FILTER操作が必要なくなります）。小さい強化に思えますが、このような処理を使用する方には非常に嬉しいことだと言えます。

SQL> SELECT tab1.* FROM tab1 A,tab2 B WHERE A.c1 BETWEEN B.c1-10 AND B.c1+10;

実行計画(12.1)                                               実行計画(12.2)
————————————-                        ————————————-
| Id  | Operation            | Name |                        | Id  | Operation           | Name  |
————————————-                        ————————————-
|   0 | SELECT STATEMENT     |      |                        |   0 | SELECT STATEMENT    |       |
|   1 |  MERGE JOIN          |      |                        |   1 |  MERGE JOIN         |       |
|   2 |   SORT JOIN          |      |                        |   2 |   SORT JOIN         |       |
|   3 |    TABLE ACCESS FULL | TAB1 |                        |   3 |    TABLE ACCESS FULL| TAB1  |
|*  4 |   FILTER             |      |                        |*  4 |   SORT JOIN         |       |
|*  5 |    SORT JOIN         |      |                        |   5 |    TABLE ACCESS FULL| TAB2  |
|   6 |     TABLE ACCESS FULL| TAB2 |                        ————————————-
————————————-                        Predicate Information (identified by operation id):
Predicate Information (identified by operation id):          —————————————————
—————————————————             4 – access(INTERNAL_FUNCTION(“A”.”C1″)>=”B”.”C1″-10)
   4 – filter(“A”.”C1″<=”B”.”C1″+10)                                filter(“A”.”C1″<=”B”.”C1″+10 AND
   5 – access(INTERNAL_FUNCTION(“A”.”C1″)>=”B”.”C1″-10)                    INTERNAL_FUNCTION(“A”.”C1″)>=”B”.”C1″-10)
       filter(INTERNAL_FUNCTION(“A”.”C1″)>=”B”.”C1″-10)

 

3. おわりに
今回はOracle Database 12cR2で強化されたSQL処理について説明しましたが、少しは参考になりましたでしょうか。これからも頑張りますのでよろしくお願いします。
それでは、次回まで、ごきげんよう。

ページトップへ戻る▲ 

 

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