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

 

皆さんこんにちは、大分涼しくなってきたのでもう紅葉の季節ですね。今年は、朝晩と日中の温度差があるので鮮やかな紅葉になりそうですね。
SQLのテストなどでSQL*Plusがボトルネックになっているのをときどき目にします。大量のデータを出力しているなどが原因だと思いますが、これではSQLのテストにならなくなってしまいます。そこで、今回は皆さんもよく使用していると思う（このコラムでもよく使用している）SQL*Plusのチューニングについて取り上げてみました。後半に、ハッシュ結合について参考になるようなことも載せていますので、参考にしてください。

1. SQL*Plusのチューニングについて
SQL*Plusは、テストなどでSQLを実行するときに便利なツールですが、SELECT文で大量データを出力してしまうと、以下のtopコマンドのように、SQL*Plus側のCPUがボトルネックになります（sqlplusプロセスとOracleサーバー・プロセスのCPU使用率が合計で100%までしか使用できないので、sqlplusが多いとOracleデータベースでは使用できなくなります）。これは、知ってるようで知らない方やあまり気にしていない方が多いのではないでしょうか。

tsushima-41-1

 

このようなSQLでテストを行っても、正確な実行時間を求めることができなくなってしまいます。そのため、あまり多くの結果を出力しないようなSQLにすることを検討してください。例えば、以下のように最小限の行数になるように、先頭から必要な行だけを出力する、グループ集計関数を使用して集計後のデータを出力するなどのSQLです。

tsushima-41-20

 

実際には、必要なデータをクライアント側に持ってきてから、必要な処理を行っている場合が多いように思うので、クライアント側がボトルネックになっているのが多いのではないでしょうか（特に、BIツールなどを使用するような場合に多いようです）。そのため、すべてのSQLを少ない行数にするのは難しいと思うので、大量の行数の場合でも効果的なテストを行うためのSQL*Plusのチューニングについて、以下のようなことをまとめてみました。

  • TERMOUT OFFの問題
  • データの出力
  • 列ヘッダーなどの出力

（１）TERMOUT OFFの問題
まずは、画面に出力しないTERMOUT OFFの問題から説明します。
画面の出力については、以下のようにTERMOUTコマンドに’OFF’を設定することで、STARTコマンドなどからの実行で生成される出力を抑止できるので、これで問題がなくなると思っている方も多いのではないでしょうか（SETコマンドについては、小文字の部分が省略可能を意味しています）。

SQL> SET TERMout OFF
SQL> START test.sql  -- SELECT * FROM t1 WHERE id < 20;


実は、これを設定してもファイルにスプール出力するデータの編集は行うので、SQL*PlusのCPUオーバーヘッドによって、思ったような性能にならないことがよくあります。そのため、これを設定してもSELECT文で大量データを検索しないようなSQLにする必要がありますが、それができない場合はデータを出力しないようなことを検討してください。例えば、’INSERT … SELECT’文などを行うことで、INSERT処理の負荷は増えてしまいますが、SQL*Plusのオーバーヘッドを軽減することが可能です。また、大量データの場合には、高速化のためにパラレル実行を行いますが、クライアント側のボトルネックでは効果的に動作することができません。INSERT処理は、パラレルでも実行することが可能なので、このような大量のデータでは有効なSQLになります。

（２）データの出力
次に、INSERTを使用できないような場合に、データを出力しない方法について説明します。
INSERTを使用せずに大量データを検索する場合には、以下のAUTOTRACEコマンドの使用を検討してください。検索したデータを出力せずに、SQL文の実行時間だけを求めるのに便利なコマンドです（つまり、クライアント側にデータ転送だけを行ったSQL実行時間を求めることができます）。このTRACEONLYオプションのデフォルトは、’EXPLAIN STATISTICS’になります（EXPLAINは実行計画の出力を行い、STATISTICSはSQL文統計を出力するためにSQL文の実行を行います）。

SQL> SET AUTOTrace TRACEonly [EXPlain STATistics]


以下のようにSTATISTICSオプションを指定することで、データ出力を抑止してSQL文の実行を行うことができます。これでSQL文統計は出力されますが、スプール出力するためのデータ編集は行わないので、CPUオーバーヘッドが削減されます。そのため、大量のデータを検索するような場合に有効なコマンドですが、まだ知らない方も多いようなので、今後のためにも知っておくと便利です（私もテストなどでよく使用しています）。

SQL> SET AUTOTRACE TRACEONLY STATISTICS
SQL> SELECT * FROM t1 WHERE id < 20;

19行が選択されました。

統計
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        474  consistent gets
          0  physical reads
          0  redo size
       1117  bytes sent via SQL*Net to client
        555  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         19  rows processed


（３）列ヘッダーなどの出力
次に、スプール出力を行うような場合に、オーバーヘッドを軽減する方法について説明します。
どうしてもスプール出力などを行う必要がある場合には、できるだけオーバーヘッドを軽減する必要があるので、不必要なヘッダーや空白行などを出力しないようにしてください。SELECT文を実行するとデフォルトでは、以下のようなフォーマットで列ヘッダーや結果行数が出力されるので、このような出力を制御する代表的なコマンドをいくつか説明します。

tsushima-41-2

 

空白行と列ヘッダーについては、ページ・サイズ毎に出力されてしまいます（結果行数については、実行終了後だけなので、あまり気にする必要はありません）。このページ・サイズが小さいと、列ヘッダーなどの出力がオーバーヘッドになるので、以下のPAGESIZEコマンドで適切な値に設定します（ページ・サイズのデフォルトは14行です）。結果行数が多い場合に、空白行と列ヘッダーが頻繁に出力されないように設定してください。この例は、最初だけに列ヘッダーを出力するように、ページ・サイズを22に設定しています。

tsushima-41-7

 

列ヘッダーなども必要ない場合は、出力しないようにページ・サイズを0に設定するのが簡単です。以下のように、’SET NEWPAGE NONE’コマンド（空白行を出力しない）と’SET HEADING OFF’コマンド（列ヘッダーを出力しない）でも同じことが可能です。

tsushima-41-8

 

オーバーヘッドを軽減するには、このように無駄なものを出力しないことが重要になるので、’SET LINESIZE’コマンドで1行の文字数も適切な値に設定してください。大き過ぎるとスプール行の終わりに無駄な空白が出力されるので注意してください（1行の文字数のデフォルトは80です）。以下のようにTRIMSPOOLコマンドに’ON’を設定しても各スプール行の終わりの空白を削除できますが、可能であればオーバーヘッドの少ない’SET LINESIZE’コマンドで適切な値に設定する方が効果的です。画面出力については、’SET TRIMOUT’コマンドのデフォルトが’ON’になっているので、無駄な空白は出力されないようになっています。

tsushima-41-2

 

性能に影響するコマンドについて説明してきましたが、その他にもいろいろなコマンドがあるので、目的とする出力データによって効果的に設定するようにしてください。
SQL*Plusは、SQLの実行時間などをテストするときに便利ですが、何も考えずにこのように大量データを出力すると、Oracleデータベースのテストにならなくなってしまいます。そのようなことにならないように、ここで説明したことを意識して効果的に行ってください。

2. ハッシュ結合について
ハッシュ結合は、大量データを結合するときに非常に効果的ですが、等価結合だけのような多少の制限とOracle Database 10g（Oracle10g）で拡張されていることなどを説明していなかったので、参考までに知っておくと便利そうな以下のようなことを載せておきます。

  • ハッシュ結合の拡張
  • アンチ・ハッシュ結合の制限

（１）ハッシュ結合の拡張
まずは、Oracle10gから少し拡張されている外部ハッシュ結合やセミ・ハッシュ結合などについて説明します。
外部ハッシュ結合やセミ・ハッシュ結合などは、結合するテーブルの順番が決まっているので、SQLによってはハッシュ結合が効果的でない場合があります。外部ハッシュ結合は、核になるテーブルが外部表になる必要があります（以下のSQLでは、左側のようにテーブル’tab1’が外部表として先にアクセスされます）。ただし、ハッシュ結合は、小さなテーブルでハッシュ・テーブルを作成する方（先にアクセスする方）が効率が良いので、核になるテーブルが大きい場合には効果的に動作することができませんでした。Oracle10gからは以下の右側のように、外部表と内部表を入れ替えて効果的にハッシュ結合を行うことが可能になっています。このとき実行計画には’HASH JOIN RIGHT OUTER’と出力されます。

tsushima-41-4

 

第29回で説明したセミ結合やアンチ結合もハッシュ結合の順番が決まっています。セミ・ハッシュ結合やアンチ・ハッシュ結合は、主問合せ側のテーブルが外部表になりますが（以下のSQLでは、左側のようにテーブル’tab1’が先にアクセスされます）、これも主問合せ側のテーブルが大きいと効果的に動作することができないので、右側のように入れ替えてハッシュ結合を行うことが可能になっています。このとき実行計画には’HASH JOIN RIGHT SEMI’と出力されます（アンチ・ハッシュ結合の場合は、’HASH JOIN RIGHT ANTI’と出力されます）。

tsushima-41-5

 

（２）アンチ・ハッシュ結合の制限
次に、NOT EXISTSやNOT INで動作するアンチ・ハッシュ結合の制限について説明します。
NOT IN条件は、SQL文を記述しやすいところはありますが、まだ多少の制限があるので、できるだけNOT EXISTS条件を使用するようにしてください（第38回で説明したように、SQLチューニング・アドバイザでもNOT EXISTSがアドバイスされます）。第29回で’Null-Aware(NA) ANTI JOIN’が可能になって少し制限がなくなったと説明しましたが、以下のように複数列で結合する場合などに、NOT IN条件では左側のようにハッシュ結合が動作しない場合があります（この例の場合は、アンチ・ソート・マージ結合になっています）。右側のNOT EXISTS条件は、そのような制限がないので、最適なハッシュ結合を行っています。

tsushima-41-12

 

この場合も以下のように、’IS NOT NULL’（またはNOT NULL制約）を使用するとハッシュ結合が動作しますが、そのようなことを気にする必要がないNOT EXISTS条件を使用するのが良いということです。

SQL> SELECT * FROM tab1 WHERE c1 IS NOT NULL AND c2 IS NOT NULL AND (c1,c2) NOT IN
2   (SELECT c1,c2 FROM tab2 WHERE c1 IS NOT NULL AND c2 IS NOT NULL);

———————————–
| Id  | Operation          | Name |
———————————–
|   0 | SELECT STATEMENT   |      |
|*  1 |  HASH JOIN ANTI    |      |
|   2 |   TABLE ACCESS FULL| TAB1 |
|   3 |   TABLE ACCESS FULL| TAB2 |


3. おわりに
今回はSQL*Plusやハッシュ結合について説明しましたが、少しは参考になりましたでしょうか。また機会があれば他のことについても説明したいと思います。これからもよろしくお願いします。それでは、次回まで、ごきげんよう。

ページトップへ戻る▲ 

 

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