コーソルの技術情報
KNOWLEDGE検索
コーソルでは経験豊かなエンジニアが、Oracle Databaseに関するお役立ち情報を発信しています。
データベースのチューニングや設定にお役立ていただけます。
Oracle DB ベストプラクティス
SQLのパフォーマンス分析において、実行計画は重要なキーとなります。
しかし、Oracle 8i以前では、実際に使用された実行計画を確実に特定する唯一の手段は、SQLトレースだけでした。
SQLトレースを使用するためには、SQL実行前にSQLトレースを有効化しておく必要があり、突発的なパフォーマンス問題の調査には不適切でした。この問題に対処できる方法として、Oracle 9i より導入されたV$SQL_PLANと、Oracle Database 10.1 より導入されたDBMS_XPLAN.DISPLAY_CURSORプロシージャが有効であるため、これらについて紹介します。
V$SQL_PLANを直接参照すべき状況は一般的に少ないため、DBMS_XPLAN.DISPLAY_CURSORから先に説明します。
DBMS_XPLAN.DISPLAY_CURSORは共有プールにキャッシュされた共有カーソルから実行計画を取得・表示するプロシージャであり、10.1以降で導入されました。事前設定不要で実行計画を取得でき、”Predicate Information”などのSQLトレースで表示されない情報を得られるという利点があります。
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('6377g9s3af8u6'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID 6377g9s3af8u6, child number 0
-------------------------------------
select ch, cname, pa.pa, pname from ch, pa where ch.pa = pa.pa and
pa.pa = 1
Plan hash value: 3732797803
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | NESTED LOOPS | | 1 | 1113 | 2 (0)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| PA | 1 | 85 | 2 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN | IDX_PA | 1 | | 1 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| CH | 1 | 1028 | 0 (0)| |
|* 5 | INDEX RANGE SCAN | IDX_CHPA | 1 | | 0 (0)| |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("PA"."PA"=1)
5 - access("CH"."PA"=1)
24行が選択されました。
DBMS_XPLAN.DISPLAY_CURSORプロシージャは非常に有用であり、可能であればこのプロシージャを使用すべきですが、Oracle Database 9.2, 9.0.1では、DBMS_XPLAN.DISPLAY_CURSORが導入されていません。このため、Oracle Database 9.2, 9.0.1では、V$SQL_PLANを問い合わせるカスタムSQLを実行して実行計画を確認します。SQL*Plusの変数 hash、addrには実行計画を確認したいSQLのハッシュ値とADDRESS値を指定してください。
define hash=SQLハッシュ値
define addr=SQL ADDRESS値
column id format 999 newline
column operation format a20
column operation format a20
column options format a15
column object_name format a22 trunc
column optimizer format a3 trunc
select id
, lpad (' ', depth) || operation operation
, options
, object_name
, optimizer
, cost
from v$sql_plan
where hash_value = &hash
and address = '&addr'
start with id = 0
connect by
(prior id = parent_id
and prior hash_value = hash_value
and prior child_number = child_number
)
order siblings by id, position;