はじめに
Standard Edition2環境など、Enterprise ManagerやAWRレポートが利用できないOracle環境において、パフォーマンス状況を確認するには、Statspackレポートが必要不可欠です。
ただ、Statspackレポートについて、見方をまとめたドキュメント、コンテンツが意外と少ないため、レポートを出力しても、どう見たらよいか分からない事も多いのではないかと思います。
今回は、Statspackレポートの見方について書いてみたいと思います。
Statspackレポートの解析方法
Statspackは最初に「Report Summary」があり、その後に様々な「統計情報」が出力されます。
まず、「Report Summary」でざっとインスタンスの傾向・状況を確認し、「統計情報」で詳細に分析していきます。
「統計情報」には「待機イベント統計」、「SQL」、「IO統計」、「セグメント統計」、「アドバイザ統計」などがあります。
Report Summaryの確認
サマリー部分ではまず、Oracleのバージョン、OS、CPUコア数、メモリといった基本情報の確認と、セッション数の確認を行います。
Database DB Id Instance Inst Num Startup Time Release RAC
~~~~~~~~ ----------- ------------ -------- --------------- ----------- ---
346845164 xxxxxxx 1 31-12月-18 07:5 12.1.0.1.0 NO
0
Host Name Platform CPUs Cores Sockets
Memory (G)
~~~~ ---------------- ---------------------- ----- ----- ------- ------------
XXXXXXX Microsoft Windows x86 8 4 1 31.7
Snapshot Snap Id Snap Time Sessions Curs/Sess Comment
~~~~~~~~ ---------- ------------------ -------- --------- ------------------
Begin Snap: 28976 23-1月 -19 09:15:59 77 14.3
End Snap: 28977 23-1月 -19 10:15:59 77 14.4
Elapsed: 60.00 (mins) Av Act Sess: 1.8 ★この値を確認する
DB time: 106.50 (mins) DB CPU: 87.37 (mins)
Webアプリケーションであれば、コネクションプールを使う為、コネクション数に変動は無い(通常initialとmaxを同じ値にする)ので、同時アクティブセッション数の平均(Av Act Sessの値)がどの程度か確認します。
この値が10に近い値である場合、(CPUコア数にもよりますが)遅いSQLにより、処理遅延が発生している可能性があります。
Load Profileの確認
次に、Load Profileの部分を参照し、アプリケーションの傾向をざっくり確認します。
Load Profile Per Second Per Transaction Per Exec Per Call
~~~~~~~~~~~~ ------------------ ----------------- ----------- -----------
DB time(s): 1.8 0.2 0.00 0.00
DB CPU(s): 1.5 0.1 0.00 0.00
Redo size: 229,263.1 21,568.7
Logical reads: 184,975.7 17,402.2
Block changes: 1,400.5 131.8
Physical reads: 5,937.9 558.6
Physical writes: 28.7 2.7
User calls: 1,338.1 125.9
Parses: 451.9 42.5
Hard parses: 0.3 0.0
W/A MB processed: 11.4 1.1
Logons: 0.1 0.0
Executes: 460.6 43.3
Rollbacks: 0.0 0.0
Transactions: 10.6
秒あたり、トランザクションあたりの統計(値は平均値)を確認します。
また、平常時の情報も取得しておき、ベースラインとしておきます。
この情報だけでは、値が問題ないのか、異常値なのか判断が難しいですが、ベースラインと比較する事で、変化に気付きやすくなります。
インスタンス効率の確認
次に、インスタンスの効率をチェックします。
Instance Efficiency Indicators
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 99.99 Optimal W/A Exec %: 99.99
Library Hit %: 99.86 Soft Parse %: 99.94
Execute to Parse %: 1.89 Latch Hit %: 99.21
Parse CPU to Parse Elapsd %: 95.42 % Non-Parse CPU: 99.59
インスタンス効率の項目と意味は以下の通りです。
| インスタンス効率の項目 | 値の目安 | 意味 |
|---|---|---|
| Buffer Nowait % | 95%以上 | バッファに要求を出したときに、即座に使用可能だった割合 |
| Redo NoWait % | 95%以上 | redo logに要求を出したときに、即座に使用可能だった割合 |
| Buffer Hit % | 90%以上 | 必要なデータがバッファ上にあった割合 |
| Optimal W/A Exec % | 90%以上 | ソートがメモリ内で行われた割合 |
| Library Hit % | 95%以上 | 必要なSQL、PL/SQLがライブラリ・キャッシュにあった割合 |
| Soft Parse % | 90%以上 | 全ての解析のうち再利用可能なものの割合 |
| Execute to Parse % | - | SQL実行に対し解析が行われなかった割合 |
| Latch Hit % | 98%以上 | 全てのラッチのヒット率 |
| Parse CPU to Parse Elapsd % | - | 解析CPU時間/ 解析の合計時間 |
| % Non-Parse CPU | 80%以上 | 解析以外で使用されたCPU時間の割合 |
インスタンス効率については、基本全ての値を100%に近づける事が目標となります。
各項目の値が、上表の目安を下回っているような場合、メモリが足りていないか、場合によっては、アプリケーションの実装を見直す必要があります。
共有プールの使用状況
共有プールの使用状況もざっと確認します。
Shared Pool Statistics Begin End
------ ------
Memory Usage %: 90.44 90.06
% SQL with executions>1: 73.87 76.88
% Memory for SQL w/exec>1: 82.48 85.45
共有プールの使用状況についての各項目の意味は以下となります。
各項目の数値が低い場合、共有プールが不足している可能性があります。
| 共有プールの使用状況の項目 | 意味 |
|---|---|
| Memory Usage % | 使用された共有プールの割合 |
| % SQL with executions>1 | 再利用されたSQL文の割合 |
| % Memory for SQL w/exec>1 | 2回以上実行されたSQL文が使用したメモリの割合 |
Top 5 Timed Events
次に、Top 5 Timed Eventsの箇所を確認し、待機イベントの上位5つを確認します。
Top 5 Timed Events Avg %Total
~~~~~~~~~~~~~~~~~~ wait Call
Event Waits Time (s) (ms) Time
-------------------------- ------------ ----------- ------ ------
LGWR worker group idle 38,175 7,156 187 29.5
CPU time 5,179 21.3
lreg timer 1,200 3,601 3001 14.8
AQPC idle 120 3,600 30004 14.8
heartbeat redo informer 3,542 3,600 1016 14.8
-----------------------------------------------------------------
待機イベントとは、プロセスがCPUを使用せず、待っている時間の事です。
- アイドル待機イベント
- データベースリソース以外がボトルネックとなり発生した待ち時間
- その他の待機イベント
- データベースリソース(バッファ競合、I/O待ち、ラッチ競合)に関連する待ち時間
Top 5 Timed Eventsでは、待機イベントのうち、総待ち時間(Wait Time)が長い順に、上位5つまでを表示しています。
上位にきているイベントは、チューニングして効果が大きいイベントであり、手を付けるべきチューニングポイントになります。
待ち時間が少なく、CPUが効率よく使われている(CPU timeのイベント時間が長く、最上位にいる状態)場合、データベースはパフォーマンスよく稼働していると判断する事ができます。
その為、Top 5 Timed Eventsでは、CPU timeのイベントより上位に来ているイベントについて、着目して調査するようにします。
※ただし、CPU timeより下位のイベントでも、効率の悪いSQLがCPUを消費している可能性があるため、注意が必要です。
上の例では、CPU timeが2番目にあるものの、全ての待機時間に対する占有比率が21.3%となっており、他のイベントに関連するボトルネックにより、効率が悪い状態である可能性があります。
また、SQL Timeやdb file sequential read等のI/O待ち系のイベントの比率が高い場合、SQLに問題がある可能性があります。
待機イベントの種類と意味
数多くの待機イベントがありますが、ここでは主なものを記載します。
※詳細は、Oracle Databaseのマニュアル等を参照して下さい。
| 待機イベント | 概要 | 確認点 |
|---|---|---|
| db file sequential read | 単一ブロック読み込み(インデックス検索)待ち | ◆SQL ordered by GetsからGets per Execが多いSQL文を確認 ◆SQL ordered by ReadsからReads per Execが多いSQL文を確認 ◆SQL文のチューニング ◆バッファキャッシュヒット率(buffer hit %)を確認 ◆Segments by Logical Reads、Segments by Physical Readsのセクションで、Buffer Cache及び物理ディスクへのアクセスが多かったスキーマオブジェクトを確認し、当該オブジェクトを参照しているSQLを調査 ◆メモリに余裕があれば、SGA(バッファキャッシュ)のサイズを大きくすることを検討する |
| db file scattered read | マルチブロック読み込み(全表検索、索引高速スキャン)待ち | |
| buffer busy waits | 同じデータブロックへのアクセス待ち | ◆Segments by Buffer Busy Waitsからホットになっているオブジェクトを特定する(スナップレベル7以上) ◆自動セグメント領域管理、自動UNDO管理を使う(最近は使っていると思うけど。) |
| free buffer waits | サーバープロセスの空きバッファ待ち(DBWRの書き込み遅延の可能性) | ◆File IO Statsから書き込みが多いデータファイルを確認 ◆バッファキャッシュのサイズ確認、調整 ◆DBWRの設定確認 ◆チェックポイントの頻度を増やす |
| db file parallel writes | データファイルへの書き込み待ち(DBWRの書き込み遅延が原因の可能性) | ◆File IO Statsから書き込みが多いデータファイルを確認 |
| log file sync、log file parallel write | オンラインREDOログファイルへの書き込み待ち(commitやrollbackに伴うLGWRの書き込み遅延が原因) | ◆バッチ処理などでコミットのタイミングを変更できる場合は変更する(待機時間が短く回数が多い場合、コミット頻度をさげる) ◆log buffer spaceの場合は、log bufferサイズの確認、調整 |
| log buffer space | ログバッファへの書き込み待ち(commitやrollbackに伴うLGWRの書き込み遅延が原因) | |
| enqueue | 行ロック等のロック開放待ち | ◆Enqueue Activityからどのenqueueで待機が多いか確認する ■Txエンキュー(enq: Tx - contention) 行ロック開放待ち。DML文においても行ロックが取得される事も考慮し、アプリケーションのトランザクション処理の見直し(ロックの保存時間を短くするなど)が必要。 ■STエンキュー(enq: ST - contention) ローカル管理表領域を使用していない場合は使用(最近は無いはず) ■HWエンキュー(enq: HW - contention) エクステントの手動割り当てをしない(最近は無いはず) ■SQエンキュー(enq: SQ - contention) 順序を生成する頻度が高い状態。順序のキャッシュ数を増やす ■TMエンキュー(enq: TM - contention) 表ロック開放待ち。外部キーに索引を付ける |
| latch free | ラッチ(キャッシュにアクセスする際のロック機構の一種)の開放待ち | ◆CPUリソースが不足していないか確認する ◆Latch ActivityからどのLatchで待機が多いか確認する ■cache buffer chains(latch: cache buffers chains) ホットブロックが存在することが原因。採番表を使っている場合は、順序を利用。索引リーフブロックの競合により、logical readsが多く索引のサイズが小さいものが無いか調べる。 ■cache buffer lru chain(latch: cache buffers lru chain) 多くの全表検索、誤った索引の使用、DBWRの遅延などが原因 ■Library cache(latch: library cache) 過度な解析が原因。バインド変数を使っていない。不必要な解析コールの発行など。 共有プールが小さすぎる可能性。Library Hit%など共有プール関連のインスタンス効率を確認。 |
| SQL*Net message from client | アイドル待機イベント(クライアントからの処理要求待ち。DBが何もしていない状態。) |
※マニュアルに載っていない待機イベントがレポートに出力される場合があります。
※RAC環境、Exadata環境、AWSのRDS for Oracle環境では、環境特有のイベントがレポートに出力されます(RAC環境のキャッシュフュージョンに関するものや、Exadata環境のストレージアクセス関連イベントなど)。
instance CPU、Memory Statisticsの確認
「Report Summary」の最後は、ホスト及びインスタンスのCPU使用状況、ホストのメモリ、SGA、PGAの使用状況をざっと確認します。
Instance CPU
~~~~~~~~~~~~ % Time (seconds)
-------- --------------
Host: Total time (s): 28,805.0
Host: Busy CPU time (s): 5,508.5
% of time Host is Busy: 19.1
Instance: Total CPU time (s): 5,244.0
% of Busy CPU used for Instance: 95.2
Instance: Total Database time (s): 6,637.3
%DB time waiting for CPU (Resource Mgr): 0.0
Memory Statistics Begin End
~~~~~~~~~~~~~~~~~ ------------ ------------
Host Mem (MB): 32,511.6 32,511.6
SGA use (MB): 24,466.3 24,466.3
PGA use (MB): 766.6 718.5
% Host Mem used for SGA+PGA: 77.6 77.5
PGAが、実際にどの程度メモリを使っているかを確認する事で、初期化パラメータPGA_AGGREGATE_TARGETを設定する際の参考にする事ができます。
この後のセクションでは、待機イベントの詳細情報が確認できますが、状況を確認する程度として、次は、SQL文の統計情報が出力されたセクションを確認します。
SQL文の統計情報の確認
SQL文の統計情報セクションでは、以下の項目について、Statspackの設定の閾値を超えたSQLが、多い順に表示されます。
| SQL統計セクション | 概要 | 関連待機イベント等 |
|---|---|---|
| SQL ordered by CPU | CPU時間が多く使われた順にSQLを表示 | latch free、Latch Hit %が低い、CPU使用率が高い |
| SQL ordered by Elapsed time | 経過時間(CPU⁺待機時間)が長い順にSQLを表示 | 基本、ざっと確認する |
| SQL ordered by Gets | Buffer Cacheを多く参照した順にSQLを表示 | db file sequential read、db file scattered read。Cache Hit %が低い。※SQLが遅い場合、このセクションにヒントとなる情報がある事が多い。 |
| SQL ordered by Reads | 物理ディスクを多く参照した順にSQLを表示 | db file sequential read、db file scattered read。Cache Hit %が低い。※SQLが遅い場合、このセクションにヒントとなる情報がある事が多い。 |
| SQL ordered by Executions | 実行回数が多い順にSQLを表示 | CPU TimeやダイレクトI/Oが多い場合 |
| SQL ordered by Parse Calls | パース実行回数が多い順にSQLを表示 | Hard Parse%、Soft Parse %、CPU使用率 |
| SQL ordered by Sharable Memory | 共有プールを多く参照した順にSQLを表示 | Library Hit %が低い、共有プールが多く使われている |
SQL ordered by CPUセクション
CPU時間が多く使われた順にSQLが表示されます。
このセクションでは、CPU per Exec (s)の値が大きいSQLと、%Total(割合)をまず確認します。
SQL ordered by CPU DB/Inst: XXXXXXX/xxxxxxx Snaps: 28976-28977
-> Total DB CPU (s): 5,242
-> Captured SQL accounts for 95.9% of Total DB CPU
-> SQL reported below exceeded 1.0% of Total DB CPU
CPU CPU per Elapsd Old
Time (s) Executions Exec (s) %Total Time (s) Buffer Gets Hash Value
--------- ------------ --------- ------- ---------- -------------- ------------
2131.48 4,273 0.50 40.7 2160.81 130,361,455 155745860
Module: JDBC Thin Client
SELECT acl, max(id) AS id from ( SELECT acl, id, modifier, creat
or FROM ********* WHERE container = 'xxxxxxxx-xxxx-xxxx-xxxx-
xxxxxxxxxxxx' ) GROUP BY modifier, creator, acl
CPU時間が大きいSQLは、Buffer Getsの値が大きい場合が多い為、詳細はSQL ordered by Getsのセクション等で確認します。
CPU per Exec (s)の値が、0.3秒以上のものは遅めと考えてもよいと思います。
※DBMS_SCHEDULERやDynamic Sampling(Oracleが実行しているもの)で実行されているSQLについては一旦除外。
※Captured SQL accounts for XX% of Total DB CPUのパーセンテージが低い(70%～80%未満)場合、DBの負荷が高すぎて、情報が取得できていない可能性があるので注意。
SQL ordered by Elapsed timeセクション
経過時間(CPU＋待機時間)が長い順にSQLが表示されます。
このセクションでは、Elap per Exec (s)の値が大きいSQLと、%Total(割合)をまず確認します。
SQL ordered by Elapsed time for DB: XXXXXXX Instance: arieldb Snaps: 28976 -28
-> Total DB Time (s): 6,390
-> Captured SQL accounts for 96.3% of Total DB Time
-> SQL reported below exceeded 1.0% of Total DB Time
Elapsed Elap per CPU Old
Time (s) Executions Exec (s) %Total Time (s) Physical Reads Hash Value
---------- ------------ ----------- ------- ---------- --------------- -----------
348.49 75 4.65 5.5 38.23 17,666,175 3436423981
Module: JDBC Thin Client
SELECT COUNT( *** .id) FROM ***_********_********_******** ***
WHERE (******_******* = 1) AND (***.container = '********-****-****-****-************') AND (( (***.acl IN (:1 ,:2 ,:3 )) ))
AND ((***.**********_2 IN (:4 ))) AND ((***.**********_2 IN (:5
経過時間が長いSQLだけでは根本原因までは判断できない為、詳細はSQL ordered by Gets、SQL orderd by Reads等のセクションで確認します。
Elap per Exec (s)の値が、0.3秒以上のものは遅めと考えてもよいと思います。
※DBMS_SCHEDULERやDynamic Sampling(Oracleが実行しているもの)で実行されているSQLについては一旦除外。
※Captured SQL accounts for XX% of Total DB Timeのパーセンテージが低い(70%～80%未満)場合、DBの負荷が高すぎて、情報が取得できていない可能性があるので注意。
SQL ordered by Getsセクション
Buffer Cacheを多く参照した順にSQLが表示されます。
このセクションでは、Gets per Execの値が大きいSQLと、%Total(割合)をまず確認します。
SQL ordered by Gets DB/Inst: XXXXXXX/xxxxxxx Snaps: 28976-28977
-> End Buffer Gets Threshold: 10000 Total Buffer Gets: 665,912,658
-> Captured SQL accounts for 98.5% of Total Buffer Gets
-> SQL reported below exceeded 1.0% of Total Buffer Gets
CPU Elapsd Old
Buffer Gets Executions Gets per Exec %Total Time (s) Time (s) Hash Value
----------- ----------- -------------- ------- -------- --------- -----------
17,667,638 75 235,568.5 2.7 38.23 348.49 3436423981
Module: JDBC Thin Client
SELECT COUNT( *** .id) FROM ***_********_********_******** ***
WHERE (******_******* = 1) AND (***.container = '********-**
**-****-****-************') AND (( (***.acl IN (:1 ,:2 ,:3 )) ))
AND ((***.**********_2 IN (:4 ))) AND ((***.**********_2 IN (:5
ここに載ってくるSQLは、メモリを無駄に多く参照している可能性がある為、 問題のあるSQLの特定に有用です。
データをキャッシュ可能なメモリが十分ではないにせよ、ある程度確保できている場合、このセクションにパフォーマンス悪化の要因となるSQLがレポートされる事が多いです。
Getsの値は、バッファキャッシュにキャッシュされているデータブロックの参照回数です。
よって、データブロックサイズが8KBの環境で、Gets per Execが10万である場合、10万×8KB=800MBのメモリを、1回のSQL実行で参照したという意味になります(同じブロックを何度も参照している場合もあります)。
SGAサイズと同時アクティブセッション数次第ではありますが、Gets per Execの値が数万を超えるようなものは要注意です。
※DBMS_SCHEDULERやDynamic Sampling(Oracleが実行しているもの)で実行されているSQLについては一旦除外。
※Captured SQL accounts for XX% of Total Buffer Getsのパーセンテージが低い(70%～80%未満)場合、DBの負荷が高すぎて、情報が取得できていない可能性があるので注意。
SQL ordered by Readsセクション
物理ディスクを多く参照した順にSQLが表示されます。
このセクションでは、Rds per Execの値が大きいSQLと、その%Total(割合)をまず確認します。
SQL ordered by Reads DB/Inst: XXXXXXX/xxxxxxx Snaps: 28976-28977
-> End Disk Reads Threshold: 1000 Total Disk Reads: 21,376,580
-> Captured SQL accounts for 100.0% of Total Disk Reads
-> SQL reported below exceeded 1.0% of Total Disk Reads
CPU Elapsd Old
Physical Rds Executions Rds per Exec %Total Time (s) Time (s) Hash Value
------------ ------------ ------------- -------- -------- --------- ----------
17,666,175 75 235,549.0 82.6 38.23 348.49 3436423981
Module: JDBC Thin Client
SELECT COUNT( *** .id) FROM ***_********_********_******** ***
WHERE (******_******* = 1) AND (***.container = '********-**
**-****-****-************') AND (( (***.acl IN (:1 ,:2 ,:3 )) ))
AND ((***.**********_2 IN (:4 ))) AND ((***.**********_2 IN (:5
ここに載ってくるSQLは、メモリを無駄に多く参照している可能性がある為、 問題のあるSQLの特定に有用です。
このセクションに載っているSQLの、Physical Rds、Rds per Execの値が大きい場合、参照したいデータがキャッシュされておらず、物理ディスクへのアクセスが頻発している事を表しているので、このセクションにSQLが多く載っている場合、かなりパフォーマンス状況が悪い可能性があります。
パフォーマンス改善の為には、ディスクアクセスを減らすよう、メモリチューニングや、このセクションに載っているSQLの改善が必要になります。
Readsの値は、物理ディスク上のデータブロックの参照回数です。
よって、データブロックサイズが8KBの環境で、Rds per Execが10万である場合、10万×8KB=800MBのディスク領域を、1回のSQL実行で参照したという意味になります(同じブロックを何度も参照している場合もあります)。
Reads per Execの値が数万を超えるようなものは要注意です。
※DBMS_SCHEDULERやDynamic Sampling(Oracleが実行しているもの)で実行されているSQLについては一旦除外。
※Captured SQL accounts for XX% of Total Disk Readsのパーセンテージが低い(70%～80%未満)場合、DBの負荷が高すぎて、情報が取得できていない可能性があるので注意。
SQL ordered by Executionsセクション
実行回数が多い順にSQLが表示されます。
実行回数が多いSQLというだけでは、問題があるかの判断はできませんが、CPU使用率やrun queue待ち、ロードアベレージが大きい場合は、このセクションも確認しておくようにします。
SQL ordered by Executions DB/Inst: XXXXXXX/xxxxxxx Snaps: 28976-28977
-> End Executions Threshold: 100 Total Executions: 1,658,085
-> Captured SQL accounts for 97.8% of Total Executions
-> SQL reported below exceeded 1.0% of Total Executions
CPU per Elap per Old
Executions Rows Processed Rows per Exec Exec (s) Exec (s) Hash Value
---------- ---------------- --------------- ----------- ---------- -----------
256,863 93,376 0.4 0.00 0.00 384666759
Module: JDBC Thin Client
SELECT bt.resid, bt.token, bt.status, bt.parent, bt.submitter, b
t.applicant, bt.tree_id, bt.owner, bt.sub_owner, bt.bpmcomment,
bt.substitute, bt.parent, bt.priority, bt.stepid, bt.ruleid, (
SELECT max(t.token) FROM ********** t INNER JOIN **_******
Executionsの値が、数十万回、数百万回等、異常に多いものがないかを確認します。
ただし、実行回数が多くても、Elap per Exec (s)の値が0.3秒未満であれば、気にしなくてもよい場合があります(CPU使用率やrun queue待ちなどの状況次第)。
※DBMS_SCHEDULERやDynamic Sampling(Oracleが実行しているもの)で実行されているSQLについては一旦除外。
※Captured SQL accounts for XX% of Total Execusionsのパーセンテージが低い(70%～80%未満)場合、DBの負荷が高すぎて、情報が取得できていない可能性があるので注意。
SQL ordered by Parse Callsセクション
パース実行回数が多い順にSQLが表示されます。
このセクションを確認するのは、Library Hit %やSoft Parse %の値が低い場合や、待機イベントLatch Freeの占める割合が大きい場合です。
SQL ordered by Parse Calls DB/Inst: XXXXXXX/xxxxxxx Snaps: 28976-28977
-> End Parse Calls Threshold: 1000 Total Parse Calls: 1,626,807
-> Captured SQL accounts for 97.6% of Total Parse Calls
-> SQL reported below exceeded 1.0% of Total Parse Calls
% Total Old
Parse Calls Executions Parses Hash Value
----------- ------------ -------- ----------
256,862 256,863 15.79 384666759
Module: JDBC Thin Client
SELECT bt.resid, bt.token, bt.status, bt.parent, bt.submitter, b
t.applicant, bt.tree_id, bt.owner, bt.sub_owner, bt.bpmcomment,
bt.substitute, bt.parent, bt.priority, bt.stepid, bt.ruleid, (
SELECT max(t.token) FROM ********** t INNER JOIN **_******
Parse Callsの値が大きいSQLを確認し、バインド変数を使っていない等、パース実行回数が多くなる要因がないか確認します。
SQLに問題がなさそうな場合、共有メモリを増やすなどの対応が必要となる場合もあります。
※Captured SQL accounts for XX% of Total Parse Callsのパーセンテージが低い(70%～80%未満)場合、DBの負荷が高すぎて、情報が取得できていない可能性があるので注意。
SQL ordered by Sharable Memory
共有メモリの使用量が多い順にSQLが表示されます。
このセクションを確認するのは、共有メモリが不足している場合です。
SQL ordered by Sharable Memory DB/Inst: XXXXXXX/xxxxxxx Snaps: 28976-28977
-> End Sharable Memory Threshold(KB): 1024
Max End
Sharable Sharable Old
Memory (KB) Memory (KB) Parse Calls Executions % Total Hash Value
----------- ----------- ------------ ----------- -------- ----------
10,445 10,445 18,766 18,767 0.4 4278199504
Module: JDBC Thin Client
MERGE INTO **_********** USING ( SELECT :1 AS jtybng, :2 AS ki
tntjgysy_cd, :3 AS ki_cd, :4 AS kisyg, :5 AS kijusy, :6 AS k
i_dnwbg, :7 AS tyustntjgs, :8 AS tyustntbsyo, :9 AS tyustntsy
acd, :10 AS tyustntsya, :11 AS tyussksyg, :12 AS kgy, :13 AS
Parse Callsの値が大きいSQLを確認し、バインド変数を使っていない等、パース実行回数が多くなる要因がないか確認をします。
SQLに問題がなさそうな場合、共有メモリを増やすなどの対応が必要となる場合もあります。
SQLの詳細情報を出力する
SQL文の統計情報セクションで、パフォーマンスに影響がありそうなSQLを特定したら、そのSQLの実行計画や統計情報といった詳細情報を出力する事が可能です。
SQLの詳細情報は、Statspackのスナップショットをレベル6以上で取得している場合に、SQLレポートというSQL単位の詳細情報が確認できるようになります。
Statspackレポートは、DBインスタンス全体のレポートですが、SQLレポートは、Statspackレポートとは別に、SQL毎にレポートを出力します。
SQLレポートを出力するには、SQL*PlusでPERFSTATユーザーにログインし、以下のスクリプトを実行します。
SQL> @?/rdbms/admin/sprepsq.sql
このスクリプトを実行すると、プロンプトで入力を求められるので、以下を入力します。
・・・・
(画面に、snap_idの一覧が表示される)
・・・・
begin_snapに値を入力してください: ★Statspackレポート出力時に指定したbegin_snap_idを指定。
・・・・
end_snapに値を入力してください: ★Statspackレポート出力時に指定したend_snap_idを指定。
・・・・
hash_valueに値を入力してください: ★StatspackレポートのSQL文の統計情報セクションのOld_Hash_Valueの値を指定。
・・・・
report_nameに値を入力してください: ※出力するレポートファイル名を指定。
入力し、Enterキーを押下すると、レポート内容が指定したレポートファイル名に出力されます。
※レポートファイル名のデフォルトは、sp_begin_snap_idの値_end_snap_idの値_hash_valueの値.lst (例)sp_15_16_2483871355.lst
SQLレポートの出力例
STATSPACK SQL report for Old Hash Value: 2483871355 Module: JDBC Thin Client
DB Name DB Id Instance Inst Num Release RAC Host
------------ ----------- ------------ -------- ----------- --- ----------------
ORCL 1548319305 orcl 1 19.0.0.0.0 NO XXXXXX
Start Id Start Time End Id End Time Duration(mins)
--------- ------------------- --------- ------------------- --------------
267 14-5月 -20 17:15:19 268 14-5月 -20 18:15:19 60.00
SQL Statistics
~~~~~~~~~~~~~~
-> CPU and Elapsed Time are in seconds (s) for Statement Total and in
milliseconds (ms) for Per Execute
% Snap
Statement Total Per Execute Total
--------------- --------------- ------
Buffer Gets: 23,146 1,157.3 68.83
Disk Reads: 0 0.0 .00
Rows processed: 4 0.2
CPU Time(s/ms): 0 11.9
Elapsed Time(s/ms): 0 13.3
Sorts: 20 1.0
Parse Calls: 20 1.0
Invalidations: 0
Version count: 2
Sharable Mem(K): 1,039
Executions: 20
SQL Text
~~~~~~~~
SELECT NULL AS table_cat, o.owner AS table_schem, ・・・
---- (略) ----
Known Optimizer Plan(s) for this Old Hash Value
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Shows all known Optimizer Plans for this database instance, and the Snap Id's
they were first found in the shared pool. A Plan Hash Value will appear
multiple times if the cost has changed
-> ordered by Snap Id
First First Last Plan
Snap Id Snap Time Active Time Hash Value Cost
--------- ---------------- ---------------- ----------- ---------
267 14-5月 -20 17:15 14-5月 -20 17:59 608931381 297
--------------------------------------------------------------------------------
| Operation | PHV/Object Name | Rows | Bytes| Cost |
--------------------------------------------------------------------------------
|SELECT STATEMENT |----- 608931381 -----| | | 297 |
|TABLE ACCESS BY INDEX ROWID |SUM$ | 1 | 26 | 0 |
| INDEX UNIQUE SCAN |I_SUM$_1 | 1 | | 0 |
|SORT ORDER BY | | 138 | 19K| 297 |
| VIEW |ALL_OBJECTS | 138 | 19K| 296 |
| FILTER | | | | |
| HASH JOIN | | 649 | 96K| 296 |
| MERGE JOIN CARTESIAN | | 97 | 4K| 3 |
| TABLE ACCESS BY INDEX ROWID|USER$ | 1 | 18 | 2 |
| INDEX RANGE SCAN |I_USER1 | 1 | | 1 |
| BUFFER SORT | | 97 | 2K| 1 |
| INDEX FULL SCAN |I_USER2 | 97 | 2K| 1 |
| TABLE ACCESS FULL |OBJ$ | 62K| 6M| 292 |
---- (中略) ----
| NESTED LOOPS | | 1 | 15 | 2 |
| FIXED TABLE FULL |X$KZSRO | 2 | 12 | 0 |
| INDEX RANGE SCAN |I_OBJAUTH2 | 1 | 9 | 1 |
| TABLE ACCESS BY INDEX ROWID B|USER_EDITIONING$ | 1 | 6 | 2 |
| INDEX RANGE SCAN |I_USER_EDITIONING | 2 | | 1 |
| TABLE ACCESS BY INDEX ROWID B|USER_EDITIONING$ | 1 | 6 | 2 |
| INDEX RANGE SCAN |I_USER_EDITIONING | 2 | | 1 |
| NESTED LOOPS | | 1 | 29 | 2 |
| INDEX SKIP SCAN |I_USER2 | 1 | 20 | 1 |
| INDEX RANGE SCAN |I_OBJ4 | 1 | 9 | 1 |
--------------------------------------------------------------------------------
End of Report
実行計画に見方については、別記事に記載します。
I/O統計の確認
I/O統計のセクションでは、表領域やデータファイル毎のI/O統計が確認できます(下は表領域毎のI/O統計の例)。
どのスキーマオブジェクトへのRead/Writeが多いのか、参考情報として確認します。
Tablespace IO Stats DB/Inst: XXXXXXX/xxxxxxx Snaps: 28976-28977
->ordered by IOs (Reads + Writes) desc
Av Av Av Av Buffer Av Buf
Tablespace Reads Reads/s Rd(ms) Blks/Rd Writes Writes/s Waits Wt(ms)
------------ ---------- ------- ------ ------- ---------- -------- -------- -------
XXXX 184,970 51 1.1 115.1 10,669 3 428 8.4
XXXX_INDEX 40,897 11 10.1 1.0 29,828 8 849 5.3
XXXX_CLOB 48,685 14 0.1 1.0 780 0 0 0.0
PERFSTAT 630 0 6.8 1.0 4,560 1 0 0.0
UNDOTBS1 1 0 0.0 1.0 3,037 1 27 0.0
XXXX_XX 597 0 7.8 1.0 1,087 0 0 0.0
TEMP 445 0 0.0 5.8 418 0 0 0.0
SYSAUX 18 0 5.6 1.0 97 0 0 0.0
SYSTEM 11 0 39.1 1.0 18 0 0 0.0
XXXX_XXX 1 0 0.0 1.0 1 0 0 0.0
セグメント統計の確認
セグメント統計のセクションでは、バッファキャッシュ、ディスクへのアクセスが多かったスキーマオブジェクトが確認できます。
どのオブジェクトを参照するSQLに問題がありそうか判断する際の参考情報として利用でき、パフォーマンス調査の際に有用な情報です。
Segments by Logical Reads DB/Inst: XXXXXXX/xxxxxxx Snaps: 28976-28977
-> End Segment Logical Reads Threshold: 10000
-> Pct Total shows % of logical reads for each top segment compared with total
logical reads for all segments captured by the Snapshot
Subobject Obj. Logical Pct
Owner Tablespace Object Name Name Type Reads Total
------ ----------- ----------------- -------------- ---------- -------------- ------
XXXX XXXX_INDEX XX_XXXXXXXX_XXXXX INDEX 130,268,272 19.6
XXXX XXXX XX_XXXXXXXX TABLE 127,828,224 19.2
XXXX XXXX XXXXXXXXXXX TABLE 71,608,304 10.8
XXXX XXXX_INDEX XXX_XXXXX_XXXXXXX INDEX 66,426,576 10.0
XXXX XXXX XX_XXXXXXXX TABLE 62,228,368 9.4
-------------------------------------------------------------
Segments by Physical Reads DB/Inst: XXXXXXX/xxxxxxx Snaps: 28976-28977
-> End Segment Physical Reads Threshold: 1000
Subobject Obj. Physical Pct
Owner Tablespace Object Name Name Type Reads Total
------ ---------- -------------------- ------------ -------- -------------- ------
XXXX XXXX XX_XXXXXXXX TABLE 19,079,166 90.3
XXXX XXXX XX_XXXXXXXXXX TABLE 1,955,953 9.3
XXXX XXXX_CLOB SYS_LOB0000347482C00 LOB 44,263 .2
XXXX XXXX_INDEX XXX_XXXXXX_XXXXXXX_X INDEX 8,545 .0
XXXX XXXX_INDEX XX_XXXXXXXXXXX INDEX 4,703 .0
-------------------------------------------------------------
特に、Physical Readsが多いスキーマオブジェクトを参照するSQLが、パフォーマンスに悪影響を与えている場合があるので、このセクションは確認しておくことをお勧めします。
他にもRow Lock Waits、Buffer Busy Waitsが多いオブジェクト等が確認できます。
アドバイザ統計の確認
アドバイザ統計には、SGA Target Advisory、PGA Memory Advisoryなどがありますが、その情報を活用してOracleのメモリチューニングを行う方法については、以下の記事を参考にしていただければと思います。
おわりに
Statspackレポートの見方についてまとめてみました。
過去、様々な環境やレポートを見つつ、我流で得た知見も多いため、誤り等あるかもしれませんが(ご指摘等お待ちしています)、Statspackレポートを見る際の参考になれば幸いです。
参考書籍など
少し古い本ですが、Statspackについてまとめた本は、この位しか思いつきません(ただ、今では流石に古すぎるかもしれません)。
その他、書籍ではありませんが、$ORACLE_HOME/rdbms/admin配下に配置されている「spdoc.txt」が一番有用なマニュアルかもしれません。