はじめに
Standard Edition2環境など、Enterprise ManagerやAWRレポートが利用できないOracle環境において、パフォーマンス状況を確認するには、Statspackレポートが必要不可欠です。
Statspackがインストールされていないなどで、Statspackレポートが出力できない環境でOracleのパフォーマンス調査をする場合、まずはStatspackレポートが出力できるようにし、その後Statspackレポートを解析するようにしたい所ですが、緊急の場合は並行して調査を進めなければならない場合もあります。
今回は、Statspackレポートが利用できない場合のOracleパフォーマンス調査方法について、記載してみようと思います。
動的パフォーマンスビューで問題がありそうなSQLを調査
問題となっているSQLを調査する場合、AWRやStatspackが利用できる環境であれば、それらを利用しますが、利用できない場合は、動的パフォーマンスビューをSQLで参照して調査する事もできます。
調査には、以下の動的パフォーマンスビューを参照します。
・V$SQL(V$SQLAREA)
共有プール内で有効な(キャッシュされて残っている)SQLのパフォーマンス統計情報を参照する事ができます。
・V$SQLSTATS
インスタンス起動時からのパフォーマンス統計情報を参照する事ができます。
・V$SQL_PLAN
古いOracleバージョンで、問題がありそうなSQLの実行計画を確認する際に使用していました。
現在では、DBMS_XPLAN.DISPLAY_CURSORプロシージャを使い確認します。
V$SQL(V$SQLAREA)とV$SQLSTATSとの違い
V\$SQL(V\$SQLAREA)、V\$SQLSTATSとでは、どちらもSQLカーソルに関する基本的なパフォーマンス統計情報を参照する事ができます(列の定義もほぼ同じ)。
違いは、V\$SQLは、共有プールに存在している情報のみ参照できるのに対し、V\$SQLSTATSでは、Oracleインスタンス起動後からの全ての情報が参照できる点です。
また、V\$SQLSTATSの方が高速で負荷も少ない為、StatsPackやAWR等が利用できない時には、まずV\$SQLSTATSを参照する事をおすすめします。
※V\$SQLは、V\$SESSION、V\$LOCK等などと結合して様々な情報を参照して使う事もありますが、サーバー負荷が高い状態で使用すると、正確な情報を取得できず、調査が泥沼に陥る可能性があり、注意が必要です。
V$SQL_STATS動的パフォーマンスビューの定義
主な列情報を以下に記載します。
列名 | データ型 | 説明 |
---|---|---|
SQL_TEXT | VARCHAR2(1000) | SQL文の最初の1000バイト |
SQL_FULLTEXT | CLOB | SQL全文 |
SQL_ID | VARCHAR2(13) | ライブラリキャッシュ内のSQL識別子 |
LAST_ACTIVE_TIME | DATE | 関連カーソルの統計情報が最後に更新された時刻 |
PLAN_HASH_VALUE | NUMBER | このカーソルに対するSQLプランの数値表現。PLAN_HASH_VALUEの値を別の値と比較すると、2つのプランが同じかどうかを容易に識別できる。 |
PARSE_CALLS | NUMBER | 全SQLカーソルに対する解析コール数 |
DISK_READS | NUMBER | 全SQLカーソルに対するディスク読取り数 |
DIRECT_WRITES | NUMBER | 全SQLカーソルに対するダイレクト書込み数 |
BUFFER_GETS | NUMBER | 全カーソルに対するバッファ取得の数 |
ROWS_PROCESSED | NUMBER | SQL文が戻す行数の合計 |
EXECUTIONS | NUMBER | SQLの実行回数 |
CPU_TIME | NUMBER | SQLカーソルが解析、実行およびフェッチのために使用したCPU時間(マイクロ秒) |
ELAPSED_TIME | NUMBER | SQLカーソルが解析、実行およびフェッチにかかった時間(マイクロ秒) |
AVG_HARD_PARSE_TIME | NUMBER | ハードパースにかかった時間の平均(マイクロ秒) |
CONCURRENCY_WAIT_TIME | NUMBER | 同時実行性待機時間(マイクロ秒) |
USER_IO_WAIT_TIME | NUMBER | ユーザーI/O待機時間(マイクロ秒) |
SORTS | NUMBER | SQL子カーソルに対して実行されたソート数 |
SHARABLE_MEM | NUMBER | 全SQLカーソルによって現在占有されている共有メモリーの合計量(バイト) |
実際のV\$SQL_STATSの定義情報は、各バージョンのOracleのマニュアルを参照ください(以下は19cでの定義)
調査用の基本のSQL
調査では、SYSまたはSYSTEMユーザーで、以下のようなSQLを実行します。
-- SQL*Plusのフォーマット整形例
set lines 200 pages 100
set long 1000
col sql_id for a15
col executions for 999999999999
col cpu_time for 999999999999
col elapsed_time for 999999999999
col buffer_gets for 999999999999
col disk_reads for 999999999999
col cpu_time_per_run for 999999999999
col elapsed_time_per_run for 999999999999
col buffer_per_run for 999999999999
col disk_per_run for 999999999999
-- 調査用SQL
SELECT * FROM
(SELECT
sql_id, plan_hash_value,
to_char(last_active_time,'YYYY/MM/DD HH24:MI:SS') as last_active_time,
executions, cpu_time, elapsed_time, buffer_gets, disk_reads,
cpu_time/executions cpu_time_per_run,
elapsed_time/executions elapsed_time_per_run,
buffer_gets/executions buffer_per_run,
disk_reads/executions disk_per_run,
sql_fulltext
FROM v$sqlstats
WHERE executions > 0
AND LAST_ACTIVE_TIME between SYSDATE-(1/24) and SYSDATE -- 現在時刻から1時間前までに実行されたSQLに絞り込む
ORDER BY cpu_time desc) -- ORDER BYの条件を変える事で、実行時間、CPU時間等に問題がありそうな順番でSQLを表示する。
WHERE rownum <= 20
/
WHERE句のLAST_ACTIVE_TIME between SYSDATE-(1/24) and SYSDATE
の部分の条件を変える事で、現在時刻から、どの位前までに実行されたSQLを調査対象にするかを指定できます。
(上記例では、「現在時刻から1時間前までに実行されたSQL」を対象に)
また、ORDER BY cpu_time desc
に指定する列を変える事で、CPU時間が大きい順、Getsの回数が大きい順、Readsの回数が大きい順といったように、特定の統計情報順にSQLを表示する事ができます。
(上記例では、「CPU時間が多く使われた順にSQLを表示」)
最後に、WHERE rownum <= 20
に指定する値を変える事で、条件にマッチしたSQL情報の出力行数を指定できます。
ORDER BY句に指定する列を変える事で、StatspackレポートのSQL統計セクションに出力される情報に類似する情報を出力する事が可能です。
以下に、ORDER BY句で指定する列により、指定できる表示順をまとめます。
指定したい表示順 | ORDER BY句で指定する列 | Statspackレポートで該当するSQL統計セクション |
---|---|---|
CPU時間が多く使われた順にSQLを表示 | cpu_time | SQL ordered by CPU |
経過時間(CPU⁺待機時間)が長い順にSQLを表示 | elapsed_time | SQL ordered by Elapsed time |
Buffer Cacheを多く参照した順にSQLを表示 | buffer_gets | SQL ordered by Gets |
物理ディスクを多く参照した順にSQLを表示 | disk_reads | SQL ordered by Reads |
実行回数が多い順にSQLを表示 | executions | SQL ordered by Executions |
パース実行回数が多い順にSQLを表示 | parse_calls | SQL ordered by Parse Calls |
共有プールを多く参照した順にSQLを表示 | sharable_mem | SQL ordered by Sharable Memory |
調査用SQLで出力された結果の解析方法については、以下の記事も参考にしていただければと思います。
問題がありそうなSQLの詳細情報を調べる
動的パフォーマンスビューで、問題がありそうなSQLを調査し特定したら、そのSQLの実行計画や統計情報を確認し、具体的に問題になっている点を更に調査します。
SQLの詳細情報を取得するには、以下の3つの方法があります。
- DBMS_XPLAN.DISPLAY_CURSOR
- SQL*PlusのAutoTrace機能
- SQLトレース⁺TKPROF
■DBMS_XPLAN.DISPLAY_CURSOR
共有プールにキャッシュされている共有SQLカーソルから、実行計画を取得する際に参照します。
Oracle10gより前のバージョンでは、V$SQL_PLANを参照するしかありませんでしたが、現在では、より便利で有用なDBMS_XPLAN.DISPLAY_CURSORプロシージャを利用します。
DBMS_XPLAN.DISPLAY_CURSORの引数にSQL_IDを渡して実行する事で、SQLの実行計画を表示する事が可能です。
※共有プールに既に存在していない共有SQLカーソルから、実行計画を取得できないので注意。
実行例)
SQL> select * from TABLE(DBMS_XPLAN.DISPLAY_CURSOR('66cbyj6u5j4zv'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 66cbyj6u5j4zv, child number 0
-------------------------------------
SELECT * FROM (SELECT sql_id, last_active_time, plan_hash_value,
executions, cpu_time, elapsed_time buffer_gets, disk_reads,
cpu_time/executions cpu_time_per_run, elapsed_time/executions
elapsed_time_per_run, buffer_gets/executions buffer_per_run,
disk_reads/executions disk_per_run, sql_fulltext FROM v$sqlstats
WHERE executions > 0 -- and LAST_ACTIVE_TIME between SYSDATE-(1/24)
and SYSDATE ORDER BY elapsed_time desc) WHERE rownum <= 100
Plan hash value: 33796674
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | VIEW | | 322 | 5194K| 1 (100)| 00:00:01 |
|* 3 | SORT ORDER BY STOPKEY| | 322 | 312K| 1 (100)| 00:00:01 |
|* 4 | FIXED TABLE FULL | X$KKSSQLSTAT | 322 | 312K| 0 (0)| |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=100)
3 - filter(ROWNUM<=100)
4 - filter(("EXECUTIONS">0 AND INTERNAL_FUNCTION("CON_ID") AND
"INST_ID"=USERENV('INSTANCE')))
■SQL*PlusのAutoTrace機能
SQLの実行計画、統計情報を確認する手段として、SQL*PlusのAutoTrace機能を使う方法もあります(実行計画を確認できるEXPLAIN PLAN文という機能もありますが、ここでは説明を割愛)。
ただし、この機能を使う為には、事前に準備が必要です。
- 事前準備
SYSユーザーでOracleにログイン後、PLUSTRACEロールを作成し、AutoTrace機能を利用するOracleユーザーに、PLUSTRACEロールを付与する。
> sqlplus / as sysdba
SQL> @?/sqlplus/admin/plustrce.sql -- PLUSTRACEロールを作成するscriptを実行
SQL> grant plustrace to <plustraceを実行するOracleユーザー名>
SQL*Plusでオプションを指定する事で、実行計画、実行統計の取得が可能です。指定できるオプションと、指定する事によりできる内容は以下の通りです。
AUTOTRACEオプション | 内容 |
---|---|
set autotrace on explain | SQL実行結果と実行計画を表示する。 |
set autotrace on statistics | SQL実行結果と統計情報を表示する。 |
set autotrace on | SQL実行結果と実行計画、統計情報を表示する。 |
set autotrace traceonly | 実行計画と統計情報を表示する。SQL実行結果は表示しない。 |
set autotrace off | autotraceをoffにする。 |
SQL*Plusで、上記のAUTOTRACEオプションを有効にし、パフォーマンスに問題がありそうなSQLを実行する(調査対象SQLでバインド変数が指定されている場合は、SQL*Plusでバインド変数を指定する)と、SQLの実行計画や実行統計情報を確認する事ができます。
AutoTraceでは、実際にSQLが実行され、データ転送も行われる為、実行計画、統計情報のみが確認できればよい場合は、traceonryオプションを使用するようにします。
また、合わせて、SQL*Plusのオプションであるset timing onを有効にしておくと、SQLの実行時間も分かり、便利です。
AutoTrace機能の実行例
SQL> set pages 100 lines 120
SQL> set timing on
SQL> set autot traceonly
SQL>
SQL> select id,company from company where id = 11;
経過: 00:00:00.00
実行計画
----------------------------------------------------------
Plan hash value: 129958863
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 43 | 1 (0)| 0:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID | COMPANY | 1 | 43 | 1 (0)| 0:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_COMPANY | 1 | | 0 (0)| 0:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=11)
統計
----------------------------------------------------------
0 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
738 bytes sent via SQL*Net to client
413 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
(参考) 実行計画の統計名と意味
データベース統計名 | 意味 |
---|---|
recursive calls | 再帰的コールの回数(ユーザーおよびシステムレベル) |
db block gets | ブロックの要求回数 |
consistent gets | ブロックレベルの一貫性読込み回数 |
physical reads | 物理読込みの合計数(physical reads direct + physical reads cache) |
redo size | 生成されたREDOの合計(バイト) |
bytes sent via SQL*Net to client | Oracle*Net 経由でクライアントに送信されたバイト数 |
bytes received via SQL*Net from clien | クライアントから受信したバイト数 |
SQL*Net roundtrips to/from client | Oracle*Net の送受信のやり取りの合計数 |
sorts (memory) | 完全にメモリー内で実行されたソートの数 |
sorts (disk) | ディスク書込みを伴ったソートの数 |
rows processed | 処理を行なった行数 |
■SQLトレース⁺TKPROF
特定のSQLが遅い、バッチ処理が遅いなどのパフォーマンス低下や、障害の原因を調査する為に、SQLトレースを出力する場合があります。
SQL*PlusのAutoTrace機能などと比べ、詳細な情報が出力される為、パフォーマンス問題の原因調査に有用なツールの1つです。
SQLトレースは、実行したSQLの詳細情報をトレースファイルに出力しますが、出力量が多く、フォーマットが分かりにくい為、トレースファイルをTKPROFというツールを使って整形する事で可読性の高い情報にします。
ただし、SQLトレースの出力は、オーバーヘッドが大きく、Oracleデータベースへの負荷が高い為、使用にあたっては注意が必要です。特にデータベース操作全てのトレースを出力するのは、高負荷になる為、少なくとも本番環境での使用はさけるようにします。
SQLトレースを取得するには、以下2つの初期化パラメータの値が以下のようになっている必要があります。
- statistics_level=TYPICAL または ALL
- timed_statistics=TRUE
SQLトレースの出力先
初期化パラメータ「diagnostic_dest」に指定されているディレクトリ以下の
<diagnostic_dest>/diag/rdbms/<dbname>/<instname>/trace
に出力されます。
SQLトレースのファイル名
デフォルトのファイル名は<oracle_sid>_ora_<session_id>.trc
です。
ファイル名は、他のトレースファイルと区別がつきにくいため、初期化パラメータ「tracefile_identifier」に識別子を指定し、<oracle_sid>_ora_<session_id>_識別子.trc
のようにしておくと便利です。
例えば、初期化パラメータtracefile_identifier=’SQLTRACE’
とした場合、ファイル名は、 <oracle_sid>_ora_<session_id>_SQLTRACE.trc
になります。
トレースファイルの最大サイズ
初期化パラメータ「max_dump_file_size」の値に依存します。
SQLトレース取得の流れ
SQLトレース取得の流れは以下の通りです。
- SQLトレースを有効化(セッション単位 | DB全体)
- トレースを取得するSQLを実行
- SQLトレースを無効化
- 出力されたSQLトレースファイルの確認とTKPROFによる整形
- 整形されたトレースファイルの内容を確認し、解析
■SQLトレースの取得方法
SQLトレースの取得方法には、以下のようなものがあります。
◆SQL*Plusで取得(ALTER SESSION | DBMS_SESSION)
バインド変数が使われていない等、SQL*Plusで対象SQLが実行可能な場合は、alter sessionでSQLトレースを取得します。
SQL> conn scott/**** ※処理を実行するユーザーに接続
SQL> alter session set tracefile_identifier='SQLTRACE';
#SQLトレースを利用可能に変更(待機イベント、バインド変数、適応出力)
SQL> alter session set events 'sql_trace wait=true,bind=true,plan_stat=adaptive';
SQL> #調査対象のSQLを実行
#SQLトレースの取得を解除
SQL> alter session set events 'sql_trace off';
DBMS_SESSIONパッケージでもSQLトレースの取得が可能です。
SQL> conn scott/**** ※処理を実行するユーザーに接続
SQL> alter session set tracefile_identifier=’SQLTRACE’;
#SQLトレースを利用可能に変更(待機イベント、バインド変数、適応出力)
SQL> exec dbms_session.session_trace_enable(wait => true, binds => true, plan_stat => 'adaptive');
SQL> #調査対象のSQLを実行
#SQLトレースの取得を解除
SQL> exec dbms_session.session_trace_disable;
◆ログオントリガー
Java等のプログラムから対象のSQLを実行していて、alter sessionを埋め込むよう改修できない、あるいはDBLINKを使用しており、リモートノードのSQLトレースを取得したい場合は、ログオントリガーを利用します。
SQL> conn scott/**** ※処理を実行するユーザーに接続
#ログオン時にSQLトレースを利用可能にするログオントリガーを作成
SQL> create or replace trigger logon_sqltrc after logon on schema
SQL> begin
SQL> execute immediate 'alter session set tracefile_identifer=''SQLTRACE'' ';
SQL> execute immediate 'alter session set events ''sql_trace wait=true,bind=true,plan_stat=adaptive'' ';
SQL> end;
SQL> /
#ログオフ時にSQLトレース取得を解除するログオフトリガーを作成
SQL> create or replace trigger logoff_sqltrc after logoff on schema
SQL> begin
SQL> execute immediate ‘alter session set events ''10046 trace name context off'' ';
SQL> end;
SQL> /
#アプリケーション等から、新規にOracleに接続し調査対象のSQLを実行
※コネクションプールを使用している場合は注意
#SQLトレースが取得できたら、トリガーを使用不可に変更
SQL> alter trigger logon_sqltrc disable;
SQL> alter trigger logoff_sqltrc disable;
◆特定のSQL_IDを指定して取得(alter system)
対象SQLのSQL_IDが分かっている場合は、alter systemでSQLトレースを取得します。
SQL> conn / as sysdba ※SYSユーザーで接続
#SQL_IDを指定してSQLトレースを取得するよう設定
SQL> alter system set events 'sql_trace [sql:f4bp1uungguxb] wait=true,bind=true,plan_stat=adaptive';
#SQLトレースの取得を解除
SQL> alter system set events 'sql_trace [sql:f4bp1uungguxb] off';
◆既存のセッションに対して別セッションから取得(DBMS_MONITOR)
既存のセッションに対して、別セッションからSQLトレースを取得するには、DBMS_MONITORパッケージを使用します。
V\$SESSIONのSID,SERIAL#の値を指定する方法と、サービス名、モジュール名を指定する方法があります。
まず、SYSまたはSYSTEMユーザーでSQL\*Plusでログインし、対象セッションを確認します。
SQL> SELECT sid,serial#,module,service_name,sql_trace FROM v$session WHERE username='SCOTT';
(結果例)
SID SERIAL# MODULE SERVICE_NAME SQL_TRACE
---- ------- ----------- -------------- ---------
19 2689 Apache.exe SYS$USERS DISABLED
<V$SESSIONのSID,SERIAL#の値を指定し、SQLトレースを設定する場合>
##SIDとSERIAL#を指定して設定
SQL> EXECUTE DBMS_MONITOR.SESSION_TRACE_ENABLE(session_id => 19, serial_num => 2689, waits => true, binds => true, plan_stat=>'ALL_EXECUTIONS');
##設定されていることを確認
SQL> SELECT sid,serial#,module,service_name,sql_trace FROM v$session WHERE username='SCOTT';
SID SERIAL# MODULE SERVICE_NAME SQL_TRACE
---- ------- ----------- -------------- ---------
19 2689 Apache.exe SYS$USERS ENABLED
##SIDとSERIAL#を指定して解除
SQL> EXECUTE DBMS_MONITOR.SESSION_TRACE_DISABLE(session_id =>19, serial_num => 2689);
<V$SESSIONのサービス名、モジュール名の値を指定し、SQLトレースを設定する場合>
##サービス名、モジュール名を指定して設定
SQL> EXECUTE DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE(service_name => 'SYS$USERS', module_name => 'Apache.exe', waits => true, binds => true, plan_stat=>'ALL_EXECUTIONS');
##設定されていることを確認
SQL> SELECT primary_id, qualifier_id1, waits, binds ,plan_stats FROM dba_enabled_traces WHERE trace_type = 'SERVICE_MODULE';
PRIMARY_ID QUALIFIER_ID1 WAITS BINDS PLAN_STATS
------------ -------------- ------ ------ ----------
SYS$USERS Apache.exe TRUE TRUE ALL_EXEC
##サービス名、モジュール名を指定して解除
SQL> EXECUTE DBMS_MONITOR.SERV_MOD_ACT_TRACE_DISABLE(service_name => 'SYS$USERS', module_name => 'Apache.exe');
■SQLトレースの整形方法(TKPROF)
SQLトレースを取得した後、SQLトレース出力先ディレクトリ<diagnostic_dest>/diag/rdbms/<dbname>/<instname>/trace
でTKPROF(複数のオプションがあります)を使い、SQLトレースの内容を整形します。
例)
D:\oracle\diag\rdbms\orcl\orcl\trace> tkprof orcl_ora_2872_SQLTRACE.trc orcl_ora_2872_SQLTRACE.txt
SQLトレースの解析方法については割愛しますが、SQL実行時の詳細な統計情報(オプティマイザの予測と実際の値)、SQL実行ステップ毎の処理時間、ブロックの読み書き回数等が詳細に確認できます。
解析には慣れも必要ですが、SQLのチューニングに役立つ有用なツールです。
なお、SQLトレースをMy Oracle SupportなどのOracleサポートに共有する場合は、整形前、整形後の両方を送付するようにしてください。
動的パフォーマンスビューで、SQL以外の統計情報を確認する
SQL以外の統計情報を確認する方法も、簡単ではありますが、参考までに記載します。
ただし面倒なので、やはりStatspackレポートで確認するようにしたいところです。
なお、V\$SYSTEM_EVENTやV\$〇〇STATビューには、インスタンス起動時からのイベント情報の累積値が記録されているので、ある期間の状態を把握するには、定期的に情報を取得して差分を確認する必要がある点に注意が必要です。
確認したい統計情報と、関連する動的パフォーマンスビューを以下に記載します。
確認したい統計情報 | 関連する動的パフォーマンスビュー |
---|---|
待機イベント | V\$SYSTEM_EVENT |
File I/O | V\$FILESTAT |
PGAに関する統計、アドバイス | V\$PGASTAT、V\$PGA_TARGET_ADVICE |
SGAに関する統計、アドバイス | V\$SGAINFO、V\$SGA_DYNAMIC_COMPONENTS、V\$SGA_TARGET_ADVICE |
メモリ使用状況 | V\$MEMORY_DYNAMIC_COMPONENTS |
ヒット率など、Statspackレポートに出力される他の情報についてもSQLで取得可能ですが、ここでは割愛します。
おわりに
今回は、Statspackレポートが利用できない場合の、Oracleパフォーマンス調査方法について記載してみました。
しかし、調査方法が面倒であるため参考程度にし、基本的にはStatspackをインストール設定し、Statspackレポートを解析してパフォーマンス調査をする事をお勧めします。