8 SQL*Plusのチューニング

この章では、パフォーマンスを向上させるためにSQL*Plusをチューニングする方法を説明します。ここでは、次の項目について説明します。

文のトレース
タイミング統計の収集
パラレル問合せおよび分散問合せのトレース
以前のデータベースでの実行計画の出力
SQL*Plusスクリプトのチューニング

Oracle Databaseのチューニングについては、『Oracle Databaseパフォーマンス・チューニング・ガイド』を参照してください。

文のトレース

SQLオプティマイザが使用する実行パス、および文の実行統計に関するレポートを自動的に取得できます。このレポートは、SQLのDML(SELECT、DELETE、UPDATEおよびINSERT)文が正常に実行された後に生成されます。このレポートは、これらの文のパフォーマンスを監視し、チューニングする場合に有効です。

動的サンプリングが有効な場合、DMLに関するSQL*Plusのレポート出力が異なる場合があります。

自動トレース・レポートの制御

AUTOTRACEシステム変数を設定することによって、レポートを制御できます。

自動トレースの設定	結果
SET AUTOTRACE OFF	AUTOTRACEレポートは生成されません。これがデフォルトです。
SET AUTOTRACE ON EXPLAIN	AUTOTRACEレポートには、オプティマイザの実行パスのみ示されます。
SET AUTOTRACE ON STATISTICS	AUTOTRACEレポートには、SQL文の実行統計のみ示されます。
SET AUTOTRACE ON	AUTOTRACEレポートには、オプティマイザの実行パスとSQL文の実行統計の両方が含まれます。
SET AUTOTRACE TRACEONLY	SET AUTOTRACE ONに似ていますが、ユーザーの問合せの出力は行われません。STATISTICSが使用可能な場合、問合せデータはフェッチされますが、出力は行われません。

この機能を使用するには、自分のスキーマにPLAN_TABLE表を作成する必要があります。また、PLUSTRACEロールが必要です。DBA権限に、PLUSTRACEロールを付与する必要があります。ロールの付与方法およびPLAN_TABLE表の作成方法については、『Oracle Database SQL言語リファレンス』を参照してください。

ロールおよびPLAN_TABLE表の詳細は、『Oracle Database SQL言語リファレンス』および「SET」コマンドのAUTOTRACE変数を参照してください。

例8-1 PLAN_TABLEの作成

SQL*Plusセッションから次のコマンドを実行して、HRスキーマにPLAN_TABLEを作成します。

CONNECT HR 
@$ORACLE_HOME/rdbms/admin/utlxplan.sql

Table created.

例8-2 PLUSTRACEロールの作成

SQL*Plusセッションから次のコマンドを実行して、PLUSTRACEロールを作成し、DBAにそのロールを付与します。

CONNECT / AS SYSDBA 
@$ORACLE_HOME/sqlplus/admin/plustrce.sql 

drop role plustrace;

Role dropped.

create role plustrace;

Role created.

grant plustrace to dba with admin option;

Grant succeeded.

例8-3 PLUSTRACEロールの付与

SQL*Plusセッションから次のコマンドを実行して、HRユーザーにPLUSTRACEロールを付与します。

CONNECT / AS SYSDBA 
GRANT PLUSTRACE TO HR;

Grant succeeded.

実行計画

実行計画は、SQLオプティマイザの問合せ実行パスを示します。実行計画の出力は、EXPLAIN PLANおよびDBMS_XPLANを使用して生成します。

DBMS_XPLANの出力の解釈については、『Oracle Databaseパフォーマンス・チューニング・ガイド』を参照してください。

統計

統計は、文を実行したときにサーバーによって記録され、文の実行に必要とされたシステム・リソースを示します。結果には、次の統計が含まれます。

データベース統計名	説明
recursive calls	ユーザーおよびシステム・レベルで生成された再帰的コールの数。Oracle Databaseでは、内部処理に使用した表を保持します。これらの表を変更する必要がある場合、Oracle Databaseでは内部SQL文を内部的に生成し、この内部SQL文がかわりに再帰的コールを生成します。
db block gets	CURRENTブロックが要求された回数。
consistent gets	ブロックに対して読取り一貫性が要求された回数。
physical reads	ディスクからのデータ・ブロック読取りの合計数。「physical reads direct」とバッファ・キャッシュへの読込み合計数を足した数と同じ値になります。
redo size	生成されたREDOの合計(バイト単位)。
bytes sent through SQL*Net to client	フォアグラウンド・プロセスからクライアントへ送信された合計バイト数。
bytes received through SQL*Net from client	Oracle Netによってクライアントから受信した合計バイト数。
SQL*Net round-trips to/from client	クライアントとの間で送受信されたOracle Netメッセージの合計数。
sorts (memory)	完全にメモリー内で実行され、ディスク書込みを必要としなかったソート操作の数。
sorts (disk)	1回以上のディスク書込みを必要としたソート操作の数。
rows processed	操作中に処理された行の数。

統計の中で参照されるクライアントは、SQL*Plusです。「Oracle Net」は、Oracle Netがインストールされているかどうかに関係なく、SQL*Plusとサーバー間の汎用プロセス通信を意味します。統計レポートのデフォルト書式は変更できません。

データベース統計の完全なリストについては、『Oracle Databaseリファレンス』を参照してください。統計およびその解釈方法の詳細は、『Oracle Databaseパフォーマンス・チューニング・ガイド』の第3章「オプティマイザ統計の収集」を参照してください。

例8-4 パフォーマンス統計および問合せ実行パス用の文のトレース

SQLバッファに次の文が含まれているとします。

SELECT E.LAST_NAME, E.SALARY, J.JOB_TITLE
FROM EMPLOYEES E, JOBS J
WHERE E.JOB_ID=J.JOB_ID AND E.SALARY>12000;

この文を実行すると、次のように文のトレースが自動的に行われます。

SET AUTOTRACE ON
/

LAST_NAME                     SALARY JOB_TITLE
------------------------- ---------- -----------------------------------
King                           24000 President
De Haan                        17000 Administration Vice President
Kochhar                        17000 Administration Vice President
Partners                       13500 Sales Manager
Russell                        14000 Sales Manager
Hartstein                      13000 Marketing Manager
6 rows selected.
 
Execution Plan
----------------------------------------------------------
Plan hash value: 2988506077
------------------------------------------------------------------------------
| Id  | Operation          | Name     | Rows | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |    6 |   360 |     6  (17)| 00:00:01 |
|*  1 |  HASH JOIN         |          |    6 |   360 |     6  (17)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| EMPLOYEES|    6 |   204 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| JOBS     |   19 |   494 |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("E"."JOB_ID"="J"."JOB_ID")
   2 - filter("E"."SALARY">12000)
 
Note
-----
   - dynamic sampling used for this statement
 
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         10  consistent gets
          0  physical reads
          0  redo size
        706  bytes sent via SQL*Net to client
        496  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          6  rows processed

例8-5 問合せデータを表示せずに文をトレースする方法

問合せデータを表示せずに同じ文をトレースするには、次のように入力します。

SET AUTOTRACE TRACEONLY
/

6 rows selected.
 
Execution Plan
----------------------------------------------------------
Plan hash value: 2988506077
 
------------------------------------------------------------------------------
| Id  | Operation          | Name     | Rows | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |    6 |   360 |     6  (17)| 00:00:01 |
|*  1 |  HASH JOIN         |          |    6 |   360 |     6  (17)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| EMPLOYEES|    6 |   204 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| JOBS     |   19 |   494 |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("E"."JOB_ID"="J"."JOB_ID")
   2 - filter("E"."SALARY">12000)
 
Note
-----
   - dynamic sampling used for this statement
 
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         10  consistent gets
          0  physical reads
          0  redo size
        706  bytes sent via SQL*Net to client
        496  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          6  rows processed

このオプションは、大規模な問合せのチューニング中に、問合せレポートを表示しない場合に有効です。

注意:

出力は、サーバーのバージョンおよび構成によって異なります。

タイミング統計の収集

SQL*PlusのTIMINGコマンドを使用すると、1つ以上のコマンドまたはブロックを実行するために使用されたコンピュータ・リソースの量に関するデータを収集して表示できます。TIMINGは、経過時間に関するデータを収集し、タイマーの時間内に実行されたコマンドに関するデータを保存します。

統計を収集するAUTOTRACEの使用については、「TIMING」コマンドおよび「文のトレース」を参照してください。

すべてのタイマーを削除するには、CLEAR TIMINGを入力します。

パラレル問合せおよび分散問合せのトレース

パラレル問合せまたは分散問合せ内で文をトレースした場合は、使用する文によって実行計画の出力が異なります。

例8-6 パラレル問合せオプションによる文のトレース

パラレル問合せオプションを実行してパラレル問合せをトレースするには、次のようにします。

create table D2_t1 (unique1 number) parallel -
(degree 6);

Table created.

create table D2_t2 (unique1 number) parallel -
(degree 6);

Table created.

create unique index d2_i_unique1 on d2_t1(unique1);

Index created.

set long 500 longchunksize 500
SET AUTOTRACE ON EXPLAIN
SELECT /*+ INDEX(B,D2_I_UNIQUE1) USE_NL(B) ORDERED -
*/ COUNT (A.UNIQUE1)
FROM D2_T2 A, D2_T1 B
WHERE A.UNIQUE1 = B.UNIQUE1;

Execution Plan
----------------------------------------------------------
Plan hash value: 107954098
 
--------------------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name    | Rows |Bytes| Cost(%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |         |    1 |  26 |    1   (0)| 00:00:01 |        |      |            |
|   1 |  SORT AGGREGATE           |         |    1 |  26 |           |          |        |      |            |
|   2 |   PX COORDINATOR          |         |      |     |           |          |        |      |            |
|   3 |    PX SEND QC (RANDOM)    | :TQ10001|    1 |  26 |           |          |  Q1,01 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE        |         |    1 |  26 |           |          |  Q1,01 | PCWP |            |
|   5 |      NESTED LOOPS         |         |    1 |  26 |    1   (0)| 00:00:01 |  Q1,01 | PCWP |            |
|   6 |       PX RECEIVE          |         |      |     |           |          |  Q1,01 | PCWP |            |
|   7 |        PX SEND BROADCAST  | :TQ10000|      |     |           |          |  Q1,00 | P->P | BROADCAST  |
|   8 |         PX BLOCK ITERATOR |         |    1 |  13 |    0   (0)| 00:00:01 |  Q1,00 | PCWC |            |
|   9 |          TABLE ACCESS FULL| D2_T2   |    1 |  13 |    0   (0)| 00:00:01 |  Q1,00 | PCWP |            |
|  10 |       PX BLOCK ITERATOR   |         |    1 |  13 |    2   (0)| 00:00:01 |  Q1,01 | PCWC |            |
|* 11 |        TABLE ACCESS FULL  | D2_T1   |    1 |  13 |    2   (0)| 00:00:01 |  Q1,01 | PCWP |            |
--------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
  11 - filter("A"."UNIQUE1"="B"."UNIQUE1")
 
Note
-----
   - dynamic sampling used for this statement

例8-7 ディスク読取りおよびバッファ取得の監視

SET AUTOTRACE TRACEONLY STATISTICS

結果は次のようになります。

Statistics
----------------------------------------------------------
        467  recursive calls
         27  db block gets
        147  consistent gets
         20  physical reads
       4548  redo size
        502  bytes sent via SQL*Net to client
        496  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
         14  sorts (memory)
          0  sorts (disk)
          1  rows processed

consistent getsまたはphysical readsの値が、戻されるデータ量に比例して高い場合、問合せにはコストがかかるため、再確認して最適化する必要があります。たとえば、戻される行が1,000行未満で、consistent getsが1,000,000、physical readsが10,000の場合、さらに最適化が必要です。

注意:

V$SQLまたはTKPROFを使用して、ディスク読取りおよびバッファ取得を監視することもできます。

以前のデータベースでの実行計画の出力

Oracle Database 9iリリース2(9.2)以前では、実行計画の出力が異なります。

実行計画の各行には、連続した行番号が付きます。SQL*Plusは、親操作の行番号も表示します。

実行計画は、次の順序で表示される4つの列で構成されます。

列名	説明
`ID_PLUS_EXP`	各実行ステップの行番号を表示します。
`PARENT_ID_PLUS_EXP`	各ステップとその親の関係を表示します。この列は、大きなレポートに有効です。
`PLAN_PLUS_EXP`	レポートの各ステップを表示します。
`OBJECT_NODE_PLUS_EXP`	使用されたデータベース・リンク、またはパラレル問合せサーバーを表示します。

列の書式は、COLUMNコマンドで変更できます。たとえば、PARENT_ID_PLUS_EXP列を表示しないようにするには、次のように入力します。

COLUMN PARENT_ID_PLUS_EXP NOPRINT

実行計画の出力は、EXPLAIN PLANコマンドを使用して生成します。

パラレル問合せまたは分散問合せ内で文をトレースする場合、実行計画には、コストベースのオプティマイザの行数見積り(カーディナリティ)が表示されます。一般に、各ノードでのコスト、カーディナリティおよびバイト数は、累積結果を表しています。たとえば、結合ノードのコストは結合操作を実行するためのコストのみを考慮したものではなく、その結合でリレーションにアクセスするコスト全体を含んでいます。

アスタリスク(*)が付いた行は、パラレル操作またはリモート操作を表しています。それぞれの操作は、レポートの2番目の部分で説明されています。パラレル操作および分散操作の詳細は、『Oracle Databaseパフォーマンス・チューニング・ガイド』を参照してください。

このレポートの2番目のセクションは、次の順序で表示される3つの列で構成されます。

列名	説明
`ID_PLUS_EXP`	各実行ステップの行番号を表示します。
`OTHER_TAG_PLUS_EXP`	OTHER_PLUS_EXP列内のSQL文の機能を説明します。
`OTHER_PLUS_EXP`	パラレル・サーバーまたはリモート・データベースについての問合せのテキストを表示します。

列の書式は、COLUMNコマンドで変更できます。

SQL*Plusスクリプトのチューニング

スクリプトで実行されるSQL問合せをチューニングすることによって、ほとんどの場合、パフォーマンス上の利点が得られます。このチューニングには、SQL*PlusのAUTOTRACEコマンドのようなツールを使用します。Oracle DatabaseのSQLオプティマイザを最大限に活用することには、問合せの再構成も含まれます。SQL文のチューニングについては、『Oracle Databaseパフォーマンス・チューニング・ガイド』を参照してください。

SQL*Plus固有のコマンドをチューニングすることで得られるパフォーマンスの向上はわずかですが、その向上が重要となるアプリケーションもあります。次のシステム変数およびコマンドは、SQL*Plusのパフォーマンスに影響を与える場合があります。

COLUMN NOPRINT

COLUMN NOPRINTを設定すると、列ヘッダーおよび列で選択されるすべての値の画面表示および印刷は実行されません。

SELECTから必要のない列を削除する方が、その列を表示しないようにCOLUMN NOPRINTを使用するよりも効率的です。問合せから列を削除すると、SQLエンジンによるその列の処理もSQL*Plusへの列データの送信も必要ありません。

SET APPINFO OFF

DBMS_APPLICATION_INFOパッケージを使用して、スクリプトの自動登録を設定します。APPINFO OFFを設定すると、管理者はスクリプトのパフォーマンスおよびリソースの使用率を監視できなくなります。

多数のSQLスクリプトがコールされている場合、APPINFO OFFをチューニングすると、内部SQL*PlusによるデータベースのDBMS_APPLICATION_INFOパッケージへのコールが停止します。

SET ARRAYSIZE

SQL*Plusがデータベースから一度にフェッチする行数を設定します。有効な値は1から5000です。

ARRAYSIZEの設定による効果は、Oracle Databaseによるネットワーク・パケットへの書込み効率、およびネットワークの待機時間とスループットに基づきます。SQL*PlusおよびOracle Databaseの最近のバージョンでは、ARRAYSIZEによる効果はほとんどありません。サイズが非常に大きくなると、より多くのSQL*Plusメモリーが使用され、全体のパフォーマンスが低下する場合があります。

SET DEFINE OFF

SET DEFINE OFFを設定すると、置換変数をそれらの値に置換するコマンドの解析は実行されません。

SET FLUSH OFF

SET FLUSH OFFを設定すると、オペレーティング・システムは出力をバッファに格納できます。ONを設定すると、バッファを使用せず、画面への出力をフラッシュします。FLUSHをONまたはOFFに設定して得られる効果は、オペレーティング・システムおよびデータによって異なります。また、その効果はわずかである場合があります。

ユーザーとの対話が不要で、完了するまで出力結果を確認する必要のないスクリプトを実行する場合のみ、OFFを使用します。

SET LINESIZE

SET LINESIZEでは、SQL*Plusが新しい行を開始する前に、1行に表示する文字の合計数を設定します。

LINESIZEをできるだけ小さくして、余分なメモリー割当ておよびメモリーのコピーを回避します。

ただし、LINESIZEを小さくしすぎると、隣りの列とサイズが合ない列には別の線が表示されます。これによって、パフォーマンスが大幅に低下する場合があります。

SET LONGCHUNKSIZE

SET LONGCHUNKSIZEでは、SQL*PlusがBLOB、BFILE、CLOB、LONG、NCLOBまたはXMLType値を取得するときの増分値のサイズを設定します。

LONGまたはLOBがフェッチされている場合は、別のサイズで試してください。

SET PAGESIZE

各出力ページの行数を設定します。

PAGESIZEを大きくしてヘッダーを頻繁に表示しないようにするか、0に設定してヘッダーを表示しないようにします。

SET SERVEROUTPUT

SET SERVEROUTPUT OFFを設定すると、SQL*Plusのストアド・プロシージャまたはPL/SQLブロックの出力(DBMS_OUTPUT.PUT_LINE)は表示されません。

SERVEROUTPUT OFFを設定すると、内部SQL*Plusによる、ユーザーSQL文の後に実行されたDBMS_OUTPUTパッケージへのコールが停止します。

SET SQLPROMPT

SQL*Plusコマンド・プロンプトを設定します。

デフォルトのプロンプト「SQL>」を使用して、変数の置換がプロンプトの表示ごとに発生するのを停止します。

SET TAB

SQL*Plusが空白をどのように端末に出力するかを指定します。

TAB ONを設定すると、複数の空白が端末の出力で圧縮されます。この設定では、書き込まれているデータが大幅に縮小しないかぎり、得られる効果より必要となる処理の方がわずかに上回ります。

SET TERMOUT

SET TERMOUT OFFを指定すると非表示になるため、スクリプトからの出力を画面に表示せずに、出力をスプールできます。

ファイルへのスプールおよび端末への書込みが両方とも不要な場合は、SQLスクリプトでSET TERMOUT OFFを使用して端末の出力を無効にします。

SET TRIMOUT ON SET TRIMSPOOL ON

SET TRIMOUT ONまたはSET TRIMSPOOL ONを指定すると、表示またはスプールされる各行の末尾で後続の空白を削除します。

これらの変数をONに設定すると、書き込まれるデータ量を減らすことができます。ただし、LINESIZEが最適な場合は、変数をOFFに設定した方がより速い場合があります。問合せがルーチンを処理している間、SQL*Plusの出力行に空白が書き込まれるため、空白の削除には余計な手間がかかります。

UNDEFINE

明示的に(DEFINEコマンドを使用して)定義したか、暗黙的に(STARTコマンドまたはCOLUMN NEW_VAL|OLD_VALへの引数によって)定義した置換変数を削除します。

UNDEFINEを使用すると、不要な置換変数を削除できます。これによって、&変数、new_value変数またはold_value変数を使用する操作に費やす時間を短縮できます。