Oracleは、SQLを実行するために、どのような順序で表からデータを取得したり、絞り込みをしたりしたら最も効率的に結果を返せるかを様々なパターンで計算します。
様々なパターンのうち、Oracleが最も効率的と考えた順序で、SQLを実行する流れとなります。
この時の実行の順序は、SQL実行計画として管理されていて、パフォーマンス遅延が発生した場合などに、実行計画を確認することで、どこにボトルネックが発生しているのかを確認することができます。
本記事はSQL実行計画を確認する4つの方法を解説します。
確認方法のそれぞれの特徴は以下となります。
| 確認方法 | SQL実行情報を ベースとしているか | 事前準備 | 特徴 |
|---|---|---|---|
| 共有プール | YES | 不要 | キャッシュされている間は取得可能 |
| AWR SQLレポート | YES | 不要 | 8日間以内であれば取得可能だが、全てのSQL情報が確認できるとは限らない |
| SQLトレース | YES | 必要 | 事前準備（取得設定）が必要。取得し続ける場合は、環境に掛かる負荷やトレースファイルの出力容量に注意 |
| EXPLAIN PLAN | NO | 不要 | 手軽に実行計画を確認可能だが、実際にはSQLを実行しないため、確認精度は落ちる |
本記事の前提
本記事ではサンプルSQLとして、
select * from scott.emp where empno = 7900;を実行した際の実行計画を中心に記載しています。
サンプルデータの作り方はこちらを参考にしてください。
実際のSQL文を実行すると、このような結果が返ってきます。
SQL> select * from scott.emp where empno = 7900;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ------------------------------ --------------------------- ---------- -------- ---------- ---------- ----------
7900 JAMES CLERK 7698 81-12-03 950 301. 共有プールにキャッシュされている情報から確認する
Oracleでは、SQLを実行する際に、SQLを実行するために計算した実行計画などの情報を共有プール（メモリ）上に保存しています。
これは同じSQLが複数回実行された際に、再計算することなく同じ情報を利用するためで、キャッシュとして格納しています。
共有プールは有限ですので、大量のSQLが実行されると、古い情報からキャッシュが上書きされるのですが、キャッシュ上に残っている間は、実行計画を確認することができます。
以下の流れで確認することができます。
まずは、確認したいSQLのSQL_IDをV$SQLから特定します。
この結果から、SQL_IDは「g8qp3zuuhbvf0」であることが分かります。
V$SQLを確認したSQLの情報も出力されますので、間違えないように注意しましょう。
SQL> select sql_id, hash_value, sql_text from v$sql where sql_text like '%empno = 7900%';
SQL_ID HASH_VALUE
--------------------------------------- ----------
SQL_TEXT
-------------------------------------------------------------------------------------------
gsm4qj3wkwfy6 4180556742
select sql_id, hash_value, sql_text from v$sql where sql_text like '%empno = 7900%'
g8qp3zuuhbvf0 3037064640
select * from scott.emp where empno = 7900SQL_IDが分かりましたので、dbms_xplan.display_cursorプロシージャを使って、実行計画の情報を出力することができます。
引数に特定したSQL_IDを指定します。
select * from table(dbms_xplan.display_cursor(sql_id => 'g8qp3zuuhbvf0'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------
SQL_ID g8qp3zuuhbvf0, child number 0
-------------------------------------
select * from scott.emp where empno = 7900
Plan hash value: 2949544139
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 39 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)| |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=7900)実行計画の中身までは解説しませんが、共有プール（メモリ）上の実行計画を確認することができました。
2. AWR SQLレポートを取得して確認する
共有プール上から消えてしまった情報は、1. の方法では確認することができません。
例えば、2-3日前に実行していたSQLの実行計画を確認したい場合は、AWR SQLレポートの取得が有効です。
AWRはAutomatic Workload Repositoryの略で、Oracleデータベース全体の実行統計（簡単に説明するとSQLなどの情報や待機イベントなど）を定期的に取得する機能を持っています。
デフォルトでは、1時間単位で8日間保存されています。
AWR SQLレポートは、AWRから特定のSQLのみの情報を取得することができます。
このレポートに実行計画も含まれています。
ただし、全てのSQLに対してAWR SQLレポートを出力できるわけではありません。
Oracleが自動的に判断して定期的にSQLの情報を保存しています。
まずはAWR SQLレポートを取得するために awrsqrpt.sql を実行します。
レポートの出力形式を聞かれますので、ここでは「text」を入力します。
htmlファイルとして出力させたい場合は、「html」を指定してもいいですし、デフォルトではHTML形式ですので、何も入力せずにエンターキーで大丈夫です。
$ sqlplus / as sysdba
SQL> @?/rdbms/admin/awrsqrpt
Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
Would you like an HTML report, or a plain text report?
Enter 'html' for an HTML report, or 'text' for plain text
Defaults to 'html'
report_typeに値を入力してください: text★
Type Specified: textデータベース情報が出力されます。
過去8日間のスナップショットが残っている状態ですので、何日前まで遡るかを入力します。
ここでは直近24時間前から現時点での情報を出力するため、1を指定しています。
Current Instance
~~~~~~~~~~~~~~~~
DB Id DB Name Inst Num Instance
----------- ------------ -------- ------------
286745900 C1DB 2 c1db2
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
------------ ---------- --------- ---------- ------
286745900 1 C1DB c1db1 host01m.jp.
* 286745900 2 C1DB c1db2 host02m.jp.
Using 286745900 for database Id
Using 2 for instance number
Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed. Pressing <return> without
specifying a number lists all completed snapshots.
num_daysに値を入力してください: 1 ★スナップショットの一覧が表示されますので、どの期間のスナップショットから情報を取得する指定します。
ここでは 2/22 21:00～2/22 22:00の期間を指定するため、スナップショットIDの18254と18255を入力します。
Listing the last day's Completed Snapshots
Instance DB Name Snap Id Snap Started Snap Level
------------ ------------ ---------- ------------------ ----------
c1db2 C1DB 18250 22 2月 2025 18:17 1
18251 22 2月 2025 18:58 1
18252 22 2月 2025 19:01 1
18253 22 2月 2025 20:00 1
18254 22 2月 2025 21:00 1
18256 22 2月 2025 22:00 1
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
begin_snapに値を入力してください: 18254 ★
Begin Snapshot Id specified: 18254
end_snapに値を入力してください: 18255 ★
End Snapshot Id specified: 18255SQL_IDの入力を求められますので、対象SQLのSQL_IDを指定します。
Specify the SQL Id
~~~~~~~~~~~~~~~~~~
sql_idに値を入力してください: g8qp3zuuhbvf0 ★
SQL ID specified: g8qp3zuuhbvf0
Listing all available Container DB Ids for SQL Id g8qp3zuuhbvf0
Container DB Id Container Name
----------------- --------------
* 3840497407 PDB1
Using Container DB Id 3840497407最後に出力先のファイル名を入力します。
デフォルトで「awrsqlrpt_2_18254_18256.txt」というファイル名が設定されていますので、問題なければ何も入力せずにエンターキーで大丈夫です。
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrsqlrpt_2_18254_18256.txt. To use this name,
press <return> to continue, otherwise enter an alternative.
report_nameに値を入力してください: ★
Using the report name awrsqlrpt_2_18254_18256.txt画面上に実行計画が出力されます。
同様な内容がテキストファイルにも出力され、実行計画を確認することができます。
Execution Plan
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 39 | 1 (0)| 00:00:01 |
| 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)| |
--------------------------------------------------------------------------------------対象のSQL_IDが見つからなかった場合
指定したスナップショットの期間に対象のSQL_IDが見つからなかった場合は以下のようなエラーが発生します。
その期間に対象のSQLが実行されていないか、対象SQLの情報が取得できていない可能性があります。
Specify the SQL Id
~~~~~~~~~~~~~~~~~~
sql_idに値を入力してください: g8qp3zuuhbvf0
SQL ID specified: 竍8qp3zuuhbvf0
declare
*
行1でエラーが発生しました。:
ORA-20025: SQL ID ?qp3zuuhbvf does not exist for this database/instance
ORA-06512: 行28
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.14.0.0.0との接続が切断されました。
$3. SQLトレースを取得して確認する
Oracleでは、SQL実行時の詳細な内容をトレースファイルとして取得することができます。
SQLを実行する前に、alter sessionコマンドでsql_traceをtrueにすることでSQLトレースを取得可能です。
SQL> alter session set sql_trace = true;
SQL> select * from scott.emp where empno = 7900;
SQL> alter session set sql_trace = false;sql_traceをfalseに設定する間に実行されたSQLのトレースファイルが出力されることになります。
sql_traceには様々なオプションを設定できますが、まずは一番簡単な例で説明します。
トレースファイルはdiagnostic_destパラメータで設定されたディレクトリ配下に出力されます。
SQL> show parameter diagnostic_dest
NAME TYPE VALUE
------------------------------------ --------------------------------- ------------------------------
diagnostic_dest string /u01/app/oracle具体的には、<diagnostic_dest>/diag/rdbms/<dbname>/<instance>/trace ディレクトリに出力されます。
ディレクトリ内にはトレースファイルが大量に出力されている可能性がありますので、ls -ltrコマンドでソートしてあげると、最新のトレースファイルが一番下に出力されます。
それでもバックグラウンドプロセスのトレースファイルは常に更新されていますので、ファイル名が _ora_ を含むトレースファイルを確認しましょう。
_ora_のトレースファイルは、ユーザによるサーバプロセスが出力したトレースを意味しています。
_m000_や_dbrm_などのトレースファイルは、Oracleのバックグラウンドプロセスが出力しているトレースファイルとなります。
$ ls -ltr
:
-rw-r----- 1 oracle dba 1877 2月 22 21:35 c1db2_m000_271163.trm
-rw-r----- 1 oracle dba 7113 2月 22 21:35 c1db2_m000_271163.trc
-rw-r----- 1 oracle dba 1343 2月 22 21:36 c1db2_ora_359078.trm
-rw-r----- 1 oracle dba 2567 2月 22 21:36 c1db2_ora_359078.trc ★
-rw-r----- 1 oracle dba 2145 2月 22 21:39 c1db2_dbrm_270993.trm
-rw-r----- 1 oracle dba 14687 2月 22 21:39 c1db2_dbrm_270993.trc
-rw-r----- 1 oracle dba 2054 2月 22 21:39 c1db2_lmd1_271036.trm
-rw-r----- 1 oracle dba 43669 2月 22 21:39 c1db2_lmd1_271036.trc
$ 中身を確認すると、このような情報が出力されていると思います。
:
=====================
PARSING IN CURSOR #140489035401512 len=42 dep=0 uid=0 oct=3 lid=0 tim=9688466856048 hv=3037064640 ad='191b02d10' sqlid='g8qp3zuuhbvf0'
select * from scott.emp where empno = 7900
END OF STMT
PARSE #140489035401512:c=3170,e=3946,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=2949544139,tim=9688466856047
EXEC #140489035401512:c=69,e=70,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=2949544139,tim=9688466856256
:ただし、このままだと非常に見づらいので、OracleではSQLトレースの整形ツール（tkprof）が用意されています。
第1引数に対象のSQLトレース、第2引数に出力先を指定します。
$ tkprof c1db2_ora_359078.trc /tmp/c1db2_ora_359078.prf
TKPROF: Release 19.0.0.0.0 - Development on 土 2月 22 21:49:55 2025
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
$生成されたファイルを確認すると、以下のような実行計画を確認することができます。
SQL ID: g8qp3zuuhbvf0 Plan Hash: 2949544139
select *
from
scott.emp where empno = 7900
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 2 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 2 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 TABLE ACCESS BY INDEX ROWID EMP (cr=2 pr=0 pw=0 time=50 us starts=1 cost=1 size=39 card=1)
1 1 1 INDEX UNIQUE SCAN PK_EMP (cr=1 pr=0 pw=0 time=33 us starts=1 cost=0 size=0 card=1)(object id 46458)4. EXPLAIN PLANを実行して確認する
explain plan文を実行することで実行計画を出力することができます。
ただし、こちらは実際にSQLを実行せずに実行計画を出力します。
SQL> explain plan for
2 select * from scott.emp where empno = 7900;
解析されました。
SQL> select * from table(dbms_xplan.display(format => 'all'));
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------
Plan hash value: 2949544139
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 39 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 39 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
:まとめ
本記事ではSQL実行計画を確認する4つの方法を解説しました。
実行計画を確認する状況としては、特定のSQLの性能が悪い場合などに、どこにボトルネックがあるかなどを調査するために利用するケースが多いと思います。
また、それぞれの確認方法で特徴がありますので、どの方法で情報を取得できるかなどは事前に抑えておくといいかと思います。
コメント