はじめに
Oracle Databaseの性能調査をしていると、Statspackのレポートを見る機会があります。
ただ、初めてレポートを開くと、
Load Profile
Instance Efficiency Percentages
Top 5 Timed Events
SQL ordered by Gets
SQL ordered by Reads
SQL ordered by CPU
...
と大量の情報が出てきます。
私自身レポートを見た瞬間どこを見ればいいのかわからなくなりましたので、
今回は、Statspackの項目をすべて細かく説明するのではなく、性能調査をするときに、どのあたりを見ればよいのかを中心にまとめます。
1. Statspackとは
Statspackは、Oracle Databaseの性能情報を取得し、一定期間にどのような負荷が発生していたのかを確認するための機能です。
ポイントは、ある瞬間の状態を見るのではなく、2つのSnapshotの間でどのような処理が行われたのかを見ることです。
例えば、
Snapshot 100
↓
1時間
↓
Snapshot 101
というSnapshotがあった場合、
Snapshot 100 ～ 101
の間に発生したSQL実行やI/O、待機イベントなどを確認します。
そのため、
「昨日の13:00～14:00にDBが重かった」
というような事象を調査する場合に利用できます。
2. Statspackレポートを出力する
Statspackのレポートは、spreport.sqlを使用して出力できます。
通常はPERFSTATユーザーで実行します。
SQL> conn perfstat/パスワード
SQL> @?/rdbms/admin/spreport.sql
ここで、
@?
の?は、SQL*Plus上ではORACLE_HOMEを意味します。
例えば、
ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1
の場合、
@?/rdbms/admin/spreport.sql
は、実質的に、
@/u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/spreport.sql
を実行するイメージです。
@はSQLスクリプトを実行するための記号なので、
@ + ? + /rdbms/admin/spreport.sql
と考えると分かりやすいです。
Snapshotを指定する
spreport.sqlを実行すると、開始Snapshotと終了Snapshotを聞かれます。
Enter value for begin_snap:
Enter value for end_snap:
Enter value for report_name:
例えば、
begin_snap : 100
end_snap : 101
report_name: statspack_100_101
と指定します。
すると、
statspack_100_101.lst
のようなレポートファイルが作成されます。
例えば、
13:00 Snapshot 100
14:00 Snapshot 101
であれば、13:00～14:00の間にDBで何が起きていたのかを調査できます。
3. Statspackはどこを見ればいい？
Statspackのレポートには大量の情報があります。
すべてを理解する必要はなく、性能調査ではまず以下のようなセクションを押さえておくと読みやすくなります。
| 確認箇所 | 主に見るもの | 何が分かる？ |
|---|---|---|
| Snapshot | Begin Snap / End Snap | どの時間帯の情報なのか |
| Load Profile | Executes / Physical Reads / Physical Writes など | DB全体にどの程度の負荷がかかっていたか |
| Top 5 Timed Events | CPU time / I/O関連イベント / その他の待機イベント | DBが何に時間を使っていたか |
| SQL ordered by Gets | Buffer Gets | 論理I/Oの多いSQL |
| SQL ordered by Reads | Physical Reads | 物理I/Oの多いSQL |
| SQL ordered by CPU | CPU Time | CPUを多く使用したSQL |
| SQL ordered by Elapsed Time | Elapsed Time | 実行時間の長いSQL |
この表を先に確認しておくと、レポートを開いたときに、
「とりあえずこの辺を見ればいいんだな」
という感覚を持てると思います。
ここから、それぞれについて見ていきます。
4. SnapshotとLoad Profileを見る
Snapshot
まず、レポートの対象期間を確認します。
例えば、
Begin Snap:
Snap Id: 100
Snap Time: 13:00
End Snap:
Snap Id: 101
Snap Time: 14:00
となっていれば、
13:00～14:00
の1時間についてのレポートです。
StatspackはSnapshot間の統計情報を利用するため、
「このレポートはいつの性能情報なのか？」
を最初に確認しておきます。
Load Profile
次に見るのがLoad Profileです。
ここでは、対象時間帯にDBがどの程度の処理を行っていたのかを確認できます。
例えば、
Load Profile
Per Second Per Transaction
------------ --------------
DB Block Gets 50,000 1,200
Consistent Gets 80,000 1,900
Physical Reads 5,000 120
Physical Writes 1,000 24
User Calls 3,000 72
Parses 2,000 48
Executes 3,000 72
Redo size XXXXXXXX XXXXXXXX
ここでは、例えば、
Executes
を見ると、SQLがどの程度実行されていたのかが分かります。
また、
Physical Reads
Physical Writes
を見ることで、物理I/Oがどの程度発生していたのかを確認できます。
ただし、
「Physical Readsが多いから問題」
というように、数字だけで良し悪しを判断するものではありません。
Load Profileでは、
「この時間帯、DBはどのくらい仕事をしていたのか？」
という全体像を把握します。
5. Top 5 Timed EventsとSQLを見る
Top 5 Timed Events
DB全体の負荷を確認したら、次に気になるのが、
「では、その負荷は何によって発生していたのか？」
というところです。
そこでTop 5 Timed Eventsを確認します。
例えば、
Top 5 Timed Events
Event Waits Time(s)
--------------------------- ---------- --------
CPU time 3200
db file sequential read 500,000 1000
db file scattered read 100,000 400
log file sync 20,000 200
...
この部分では、DB処理で時間を使っていた主なイベントを確認できます。
例えば、
CPU time
が大きければ、CPU処理に多くの時間を使っていた可能性があります。
db file sequential read
などのI/O関連イベントが大きければ、データファイルからの読み込みに時間を使っていた可能性があります。
log file sync
が大きければ、COMMITに関連する待機などを疑います。
ここでは、
CPUが問題なのか、I/Oが問題なのか、それとも別の待機なのか
というように、問題の方向性を確認します。
SQL ordered by Gets
SQLのランキングもStatspackを見るうえで重要です。
例えば、
SQL ordered by Gets
では、Buffer Getsの多いSQLを確認できます。
Buffer Gets Executions Gets per Exec
------------ ------------ -------------
10,000,000 100 100,000
5,000,000 1,000 5,000
この場合、
Buffer Gets
が大きいSQLを確認します。
論理I/Oを大量に発生させているSQLがないか確認するために利用できます。
SQL ordered by Reads
I/Oが気になる場合は、
SQL ordered by Reads
を確認します。
例えば、
Physical Reads Executions
--------------- -----------
5,000,000 100
2,000,000 1,000
となっていれば、
「このSQLが大量の物理読み込みを発生させていないか？」
を確認します。
特にTop 5 Timed EventsでI/O関連の待機が目立っている場合は、このランキングが参考になります。
SQL ordered by CPU
CPU負荷が高い場合は、
SQL ordered by CPU
を確認します。
例えば、
CPU Time Executions
------------ ----------
3,000 sec 100
1,500 sec 1,000
となっていれば、
「CPUを多く使用しているSQLはどれか？」
を確認できます。
CPU負荷の原因を調査するときに便利です。
SQL ordered by Elapsed Time
SQLの実行時間が気になる場合は、
SQL ordered by Elapsed Time
を確認します。
例えば、
Elapsed Time Executions
-------------- ----------
5,000 sec 100
2,000 sec 1,000
となっていれば、
「実行時間が長いSQLはどれか？」
を確認できます。
ただし、Elapsed Timeが長いからといって、必ずしもSQLそのものに問題があるとは限りません。
I/O待ちなど、別の要因によって時間が長くなっている可能性もあります。
そのため、Top 5 Timed Eventsなどと合わせて確認します。
6. Statspackを見て、さらに調査する
Statspackで気になるSQLを見つけたら、そこで終わりではありません。
例えば、
SQL ordered by Reads
で大量のPhysical Readsを発生させているSQLを見つけたとします。
その場合は、そのSQLについて、
- 実行計画
- 使用しているインデックス
- テーブルのデータ量
- WHERE句
- JOIN条件
などを確認します。
例えば実行計画に、
TABLE ACCESS FULL
があれば、
「大量のデータをFULL SCANしていることが原因ではないか？」
と考えることができます。
そこから、
インデックスを作成した方がよいのか
SQLを書き換えた方がよいのか
統計情報に問題がないか
などを調査していきます。
つまり、Statspackは原因をすべて教えてくれるものではなく、調査のきっかけを作るためのものと考えると分かりやすいです。
7. まとめ
Statspackのレポートは非常に情報量が多いため、最初はどこを見ればいいのか分かりにくいです。
まずは、以下の項目を覚えておくと読みやすくなります。
| 確認箇所 | 主に見るもの | 何が分かる？ |
|---|---|---|
| Snapshot | Begin Snap / End Snap | どの時間帯の情報なのか |
| Load Profile | Executes / Physical Reads / Physical Writes など | DB全体にどの程度の負荷がかかっていたか |
| Top 5 Timed Events | CPU time / I/O関連イベント / 待機イベント | DBが何に時間を使っていたか |
| SQL ordered by Gets | Buffer Gets | 論理I/Oの多いSQL |
| SQL ordered by Reads | Physical Reads | 物理I/Oの多いSQL |
| SQL ordered by CPU | CPU Time | CPUを多く使用したSQL |
| SQL ordered by Elapsed Time | Elapsed Time | 実行時間の長いSQL |
Statspackで重要なのは、
「今調査したい問題に対して、どの情報を見れば原因に近づけるか」
だと思っています。
Statspackは、性能問題の原因を直接教えてくれるというより、「どこを詳しく調査すればよいか」を見つけるための材料として使うと分かりやすいと思います。