| SQL*Plusユーザーズ・ガイドおよびリファレンス リリース11.1 E05784-01 |
|
 戻る |
 次へ |
この章では、パフォーマンスを向上させるためにSQL*Plusをチューニングする方法を説明します。 ここでは、次の項目について説明します。
Oracle Databaseのチューニングについては、『Oracle Databaseパフォーマンス・チューニング・ガイド』を参照してください。
SQLオプティマイザが使用する実行パス、および文の実行統計に関するレポートを自動的に取得できます。 このレポートは、SQLのDML（SELECT、DELETE、UPDATEおよびINSERT）文が正常に実行された後に生成されます。 このレポートは、これらの文のパフォーマンスを監視し、チューニングする場合に有効です。
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 パフォーマンス統計および問合せ実行パス用の文のトレース
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
|
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を入力します。
パラレル問合せ（PQ）または分散問合せ内で文をトレースした場合は、使用する文によって実行計画の出力が異なります。
例8-6 パラレル問合せ（PQ）オプションによる文のトレース
パラレル問合せ（PQ）オプションを実行してパラレル問合せをトレースするには、次のようにします。
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
|
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 |
使用されたデータベース・リンク、またはパラレル問合せ（PQ）サーバーを表示します。 |
列の書式は、COLUMNコマンドで変更できます。 たとえば、PARENT_ID_PLUS_EXP列を表示しないようにするには、次のように入力します。
COLUMN PARENT_ID_PLUS_EXP NOPRINT
実行計画の出力は、EXPLAIN PLANコマンドを使用して生成します。
パラレル問合せ（PQ）または分散問合せ内で文をトレースする場合、実行計画には、コストベースのオプティマイザの行数見積り（カーディナリティ）が表示されます。 一般に、各ノードでのコスト、カーディナリティおよびバイト数は、累積結果を表しています。 たとえば、結合ノードのコストは結合操作を実行するためのコストのみを考慮したものではなく、その結合でリレーションにアクセスするコスト全体を含んでいます。
アスタリスク（*）が付いた行は、パラレル操作またはリモート操作を表しています。 それぞれの操作は、レポートの2番目の部分で説明されています。 パラレル操作および分散操作の詳細は、『Oracle Databaseパフォーマンス・チューニング・ガイド』を参照してください。
このレポートの2番目のセクションは、次の順序で表示される3つの列で構成されます。
| 列名 | 説明 |
|---|---|
ID_PLUS_EXP |
各実行ステップの行番号を表示します。 |
OTHER_TAG_PLUS_EXP |
OTHER_PLUS_EXP列内のSQL文の機能を説明します。 |
OTHER_PLUS_EXP |
Real Application Clustersまたはリモート・データベースについての問合せのテキストを表示します。 |
列の書式は、COLUMNコマンドで変更できます。
スクリプトで実行されるSQL問合せをチューニングすることによって、ほとんどの場合、パフォーマンス上の利点が得られます。 このチューニングには、SQL*PlusのAUTOTRACEコマンドのようなツールを使用します。 Oracle DatabaseのSQLオプティマイザを最大限に活用することには、問合せの再構成も含まれます。 SQL文のチューニングについては、『Oracle Databaseパフォーマンス・チューニング・ガイド』を参照してください。
SQL*Plus固有のコマンドをチューニングすることで得られるパフォーマンスの向上はわずかですが、その向上が重要となるアプリケーションもあります。 次のシステム変数およびコマンドは、SQL*Plusのパフォーマンスに影響を与える場合があります。
COLUMN NOPRINTを設定すると、列ヘッダーおよび列で選択されるすべての値の画面表示および印刷は実行されません。
SELECTから必要のない列を削除する方が、その列を表示しないようにCOLUMN NOPRINTを使用するよりも効率的です。 問合せから列を削除すると、SQLエンジンによるその列の処理もSQL*Plusへの列データの送信も必要ありません。
DBMS_APPLICATION_INFOパッケージを使用して、スクリプトの自動登録を設定します。 APPINFO OFFを設定すると、管理者はスクリプトのパフォーマンスおよびリソースの使用率を監視できなくなります。
多数のSQLスクリプトがコールされている場合、APPINFO OFFをチューニングすると、内部SQL*PlusによるデータベースのDBMS_APPLICATION_INFOパッケージへのコールが停止します。
SQL*Plusがデータベースから一度にフェッチする行数を設定します。 有効な値は1から5000です。
ARRAYSIZEの設定による効果は、Oracle Databaseによるネットワーク・パケットへの書込み効率、およびネットワークの待機時間とスループットに基づきます。 SQL*PlusおよびOracle Databaseの最近のバージョンでは、ARRAYSIZEによる効果はほとんどありません。 サイズが非常に大きくなると、より多くのSQL*Plusメモリーが使用され、全体のパフォーマンスが低下する場合があります。
SET FLUSH OFFを設定すると、オペレーティング・システムは出力をバッファに格納できます。 ONを設定すると、バッファを使用せず、画面への出力をフラッシュします。 FLUSHをONまたはOFFに設定して得られる効果は、オペレーティング・システムおよびデータによって異なります。 また、その効果はわずかである場合があります。
ユーザーとの対話が不要で、完了するまで出力結果を確認する必要のないスクリプトを実行する場合のみ、OFFを使用します。
SET LINESIZEでは、SQL*Plusが新しい行を開始する前に、1行に表示する文字の合計数を設定します。
LINESIZEをできるだけ小さくして、余分なメモリー割当ておよびメモリーのコピーを回避します。
ただし、LINESIZEを小さくしすぎると、隣りの列とサイズが合ない列には別の線が表示されます。 これによって、パフォーマンスが大幅に低下する場合があります。
SET LONGCHUNKSIZEでは、SQL*PlusがBLOB、BFILE、CLOB、LONG、NCLOBまたはXMLType値を取得するときの増分値のサイズを設定します。
LONGまたはLOBがフェッチされている場合は、別のサイズで試してください。
SET SERVEROUTPUT OFFを設定すると、SQL*Plusのストアド・プロシージャまたはPL/SQLブロックの出力（DBMS_OUTPUT.PUT_LINE）は表示されません。
SERVEROUTPUT OFFを設定すると、内部SQL*Plusによる、ユーザーSQL文の後に実行されたDBMS_OUTPUTパッケージへのコールが停止します。
SQL*Plusが空白をどのように端末に出力するかを指定します。
TAB ONを設定すると、複数の空白が端末の出力で圧縮されます。 この設定では、書き込まれているデータが大幅に縮小しないかぎり、得られる効果より必要となる処理の方がわずかに上回ります。
SET TERMOUT OFFを指定すると非表示になるため、スクリプトからの出力を画面に表示せずに、出力をスプールできます。
ファイルへのスプールおよび端末への書込みが両方とも不要な場合は、SQLスクリプトでSET TERMOUT OFFを使用して端末の出力を無効にします。
