8 SQL*Plusのチューニング
この章では、パフォーマンスを向上させるためにSQL*Plusをチューニングする方法を説明します。内容は次のとおりです。
Oracle Databaseのチューニングについては、『Oracle Databaseパフォーマンス・チューニング・ガイド』を参照してください。
8.1 文のトレースについて
SQLオプティマイザが使用する実行パス、および文の実行統計に関するレポートを自動的に取得できます。このレポートは、SQLのDML(SELECT、DELETE、UPDATEおよびINSERT)文が正常に実行された後に生成されます。このレポートは、これらの文のパフォーマンスを監視し、チューニングする場合に有効です。
動的サンプリングが有効な場合、DMLに関するSQL*Plusのレポート出力が異なる場合があります。
8.1.1 自動トレース・レポートの制御
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変数を参照してください。
ノート:
SQL*Plus AUTOTRACEでは、ALTER SESSION SET CONTAINERオプションを使用したコンテナの切替えはサポートしていません。この場合に収集された統計データには一貫性がない可能性があります。
例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.
8.1.3 統計
統計は、文を実行したときにサーバーによって記録され、文の実行に必要とされたシステム・リソースを示します。結果には、次の統計が含まれます。
| データベース統計名 | 説明 |
|---|---|
|
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 Oracle Net Services to client |
フォアグラウンド・プロセスからクライアントへ送信された合計バイト数。 |
|
bytes received through Oracle Net Services from client |
Oracle Netによってクライアントから受信した合計バイト数。 |
|
Oracle Net Services round-trips to/from client |
クライアントとの間で送受信されたOracle Netメッセージの合計数。 |
|
sorts (memory) |
メモリー内で完全に実行され、ディスク書込みを必要としないソート操作数 |
|
sorts (disk) |
1回以上のディスク書込みを必要としたソート操作数 |
|
rows processed |
操作中に処理された行の数。 |
統計の中で参照されるクライアントは、SQL*Plusです。「Oracle Net」は、Oracle Netがインストールされているかどうかに関係なく、SQL*Plusとサーバー間の汎用プロセス通信を意味します。統計レポートのデフォルト書式は変更できません。
データベース統計の完全なリストについては、「統計の説明」を参照してください。統計およびその解釈方法の詳細は、「データベース統計の収集」を参照してください。
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 Oracle Net Services to client
496 bytes received via Oracle Net Services from client
2 Oracle Net Services roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
6 rows processed6 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 Oracle Net Services to client
496 bytes received via Oracle Net Services from client
2 Oracle Net Services roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
6 rows processedこのオプションは、大規模な問合せのチューニング中に、問合せレポートを表示しない場合に有効です。
ノート:
出力は、サーバーのバージョンおよび構成によって異なります。
例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 /
例8-5 問合せデータを表示せずに文をトレースする方法
問合せデータを表示せずに同じ文をトレースするには、次のように入力します。
SET AUTOTRACE TRACEONLY /
8.2 タイミング統計の収集について
SQL*PlusのTIMINGコマンドを使用すると、1つ以上のコマンドまたはブロックを実行するために使用されたコンピュータ・リソースの量に関するデータを収集して表示できます。TIMINGは、経過時間に関するデータを収集し、タイマーの時間内に実行されたコマンドに関するデータを保存します。
統計を収集するAUTOTRACEの使用については、TIMINGコマンドおよび文のトレースについてを参照してください。
すべてのタイマーを削除するには、CLEAR TIMINGを入力します。
8.3 パラレル問合せおよび分散問合せのトレース
例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 Oracle Net Services to client
496 bytes received via Oracle Net Services from client
2 Oracle Net Services roundtrips to/from client
14 sorts (memory)
0 sorts (disk)
1 rows processedconsistent getsまたはphysical readsの値が、戻されるデータ量に比例して高い場合、問合せにはコストがかかるため、再確認して最適化する必要があります。たとえば、戻される行が1,000行未満で、consistent getsが1,000,000、physical readsが10,000の場合、さらに最適化が必要です。
ノート:
V$SQLまたはTKPROFを使用して、ディスク読取りおよびバッファ取得を監視することもできます。
8.4 以前のデータベースでの実行計画の出力
Oracle Database 9iリリース2(9.2)以前では、実行計画の出力が異なります。
実行計画の各行には、連続した行番号が付きます。SQL*Plusは、親操作の行番号も表示します。
実行計画は、次の順序で表示される4つの列で構成されます。
| 列名 | 説明 |
|---|---|
|
|
各実行ステップの行番号を表示します。 |
|
|
各ステップとその親の関係を表示します。この列は、大きなレポートに有効です。 |
|
|
レポートの各ステップを表示します。 |
|
|
使用されたデータベース・リンク、またはパラレル問合せサーバーを表示します。 |
列の書式は、COLUMNコマンドで変更できます。たとえば、PARENT_ID_PLUS_EXP列を表示しないようにするには、次のように入力します。
COLUMN PARENT_ID_PLUS_EXP NOPRINT
実行計画の出力は、EXPLAIN PLANコマンドを使用して生成します。
パラレル問合せまたは分散問合せ内で文をトレースする場合、実行計画には、コストベースのオプティマイザの行数見積り(カーディナリティ)が表示されます。一般に、各ノードでのコスト、カーディナリティおよびバイト数は、累積結果を表しています。たとえば、結合ノードのコストは結合操作を実行するためのコストのみを考慮したものではなく、その結合でリレーションにアクセスするコスト全体を含んでいます。
アスタリスク(*)が付いた行は、パラレル操作またはリモート操作を表しています。それぞれの操作は、レポートの2番目の部分で説明されています。パラレル操作および分散操作の詳細は、「分散トランザクションの概念」を参照してください。
このレポートの2番目のセクションは、次の順序で表示される3つの列で構成されます。
| 列名 | 説明 |
|---|---|
|
|
各実行ステップの行番号を表示します。 |
|
|
OTHER_PLUS_EXP列内のSQL文の機能を説明します。 |
|
|
パラレル・サーバーまたはリモート・データベースについての問合せのテキストを表示します。 |
列の書式は、COLUMNコマンドで変更できます。
8.5 SQL*Plusスクリプトのチューニングについて
スクリプトで実行されるSQL問合せをチューニングすることによって、ほとんどの場合、パフォーマンス上の利点が得られます。このチューニングには、SQL*PlusのAUTOTRACEコマンドのようなツールを使用します。Oracle DatabaseのSQLオプティマイザを最大限に活用することには、問合せの再構成も含まれます。SQL文のチューニングについては、『Oracle Databaseパフォーマンス・チューニング・ガイド』を参照してください。
SQL*Plus固有のコマンドをチューニングすることで得られるパフォーマンスの向上はわずかですが、その向上が重要となるアプリケーションもあります。次のシステム変数およびコマンドは、SQL*Plusのパフォーマンスに影響を与える場合があります。