statspackの構成
- Report Summary
- Instance Efficiency Percentage
- Top 5 Time Evnets
- 統計情報
- 待機イベント統計
- SQL
- IO統計
- セグメント統計
- アドバイザ統計
statspack見方
report summaryで問題の切り分けを行い、統計情報に詳細の原因を追究する。
(1)Report Summaryを分析する
-
Instacnce Efficiency Percentage
→処理の効率の良さを示す
100％に近いほど、効率が良い -
Top 5 Timed Events
発生している待機イベントの割合を示す
%Total Ela Timeの割合が高いイベントを見ることで原因特定の可能性が高い
ここのトップにCPU Timeが入ることが良い性能の印
（CPU処理時間の割合が多く、無駄な待機時間が少ないということ）
→ただし、効率の悪いSQLがCPU Timeを増加させている可能性もあり、
統計情報のSQLセクションにてCPU Timeを多く使っているSQLがないか確認した方がいい
→「SQL ordered by CPU Time」を確認する
CPU Timeが上位でなくとも1回の実行時間「Elapsed Time」が多いSQLは同じように確認した方がいい -
よく出る待機イベント
db file sequencial read
索引スキャンのときに発生するI/O待機イベント
IO統計からIO状況を確認する
またバッファキャッシュヒット率が低くなっていないか確認する
バッファキャッシュヒット率は一般的には95%以上が望ましい。
→キャッシュヒット率を上げるには？：DB_CACHE_SIZEをチューニング
db file scattered read
→フルスキャンのときに発生するI/O待機イベント
対策としては、
・キャッシュヒット率をあげる
フルスキャンは索引スキャンとは異なり大きいサイズのテーブルはキャッシュされないので、
キャッシュヒット率を改善しても影響がない場合もある。
・索引スキャンするようにする
→有効な索引を作成する
・I/O性能の改善
→ストレージ構成、ディスク追加
・初期化パラメータ「DB_FILE_MULTIBLOCK_READ_COUNT」の調整
→Oracle10gより自動調整のため不要
library cache pin
→ライブラリキャッシュの競合。
共有プール上に確保するライブラリキャッシュに必要なSQLやPL/SQLが確保できていない。
結果的に必要なSQLやPL/SQLがメモリにキャッシュされているかどうかを表すライブラリヒット率が低くなる。
ライブラリヒット率の向上や競合回避のためには、キャッシュ自体のサイズを大きくする必要があるため、
SHARED_POOL_SIZEのパラメータ調整が有効
buffer busy waits
→バッファに確保されているブロックがbusy状態のため、待機したイベント。
ブロックが競合していることが考えられる。
ブロックの中のデータの更新に関しては、行ロックを使用することで整合性を確保しているが、
ブロックの管理情報（ブロック内の空き領域やサイズ情報）については、同時に変更すると不整合が発生するため、
同一のブロックの同時更新はできないようになっている。（通常はキャッシュ上で更新するので、一瞬で完了）
「セグメント統計」の「Segments by Buffer Busy Waits」で競合が発生しているセグメントを確認する。
対象セグメントのブロックサイズを小さくしたり、ハッシュパーティションを使用してアクセスするブロックサイズを分散
させることで競合を回避することが有効。
あとはPCTFREEを大きくすることで1ブロック当たりのレコード数の削減も効果的。
またBuffre wait Statisticsから何のブロックが競合しているかが確認できる
data block : 上記対策が有効
undo block / undo header : 自動UNDO管理にすればよい
segment header : パーティション化のみが効果的
→バッチ処理の場合は、処理単位毎にパーティション化
OLTP処理の場合は、主キー等、等価条件に使用している列をもとにハッシュパーティション化
(2)バッファキャッシュヒット率をあげる
Buffer Pool Advisoryにデータベースバッファキャッシュのアドバイザ情報がある。
Est Physical Read Factorが現行サイズから見たときの物理読み込み回数の比較となるため、
なるべく数値が低い、かつ効果が高いサイズに変更することが有効
基本は、バッファキャッシュを増やすかより高性能なHWへの交換対応となる。
上記が難しい場合は、「セグメント統計」（「Segments by Logical Reads」と「Segments by Physical Reads」など）を
利用してアクセスが多いセグメントを特定し、それをKEEPバッファプール（初期パラDB_KEEP_CACHE_SIZE）に指定し、
キャッシュに乗せておくか、ディスクアクセスの高速化（より性能の高いディスクへセグメントを移動）させるなどがある。
対象のセグメントサイズが大きく、とてもバッファキャッシュにKEEPしておけない場合は、ディスクアクセスの高速化を検討するとよい。（ちなみにKEEPバッファプールもアドバイザ情報がある）
また物理メモリを増やすことが難しい場合は、Database Smart Flash Cache機能にて、
SSDを2次キャッシュとして利用したバッファキャッシュ増加方法もある。
(3)ライブラリヒット率をあげる
Shared Pool Advisoryにライブラリキャッシュのアドバイザ情報がある。
Estd LC Time Saved Factrが現行サイズから見たときの解析時間の比較となるため、
なるべく数値が低い、かつ効果が高いサイズに変更することが有効
基本的には、キャッシュを増やすこととなるが、それが難しい場合は、AP側での調整を検討。
よりキャッシュを効率的に使用するために同一のSQLでカーソルを大量に生成することを避ける必要があり、
バインド変数を使用したSQLを検討するのがよい。
パッケージソフトに組み込まれているSQLで、変更するのが難しい場合はなどは、初期パラCURSOR_SHARINGの使用を検討。
デフォルトEXACT（共有しない）であるが、FORCEやSIMILARにすることで、内部的に定数部分をバインド変数に置換して、
実行してくれる。
SIMILARは、統計情報を参照したうえでカーソルを再利用し、FORCEは統計情報に関係なくカーソルを再利用する。
定数部分の値により結果に偏りがある場合は、SIMILARの方がより効率的にデータアクセスができる。
(4)IO性能を確認する
Tablespace IO Statsの読み取り回数「Reads」や平均読み取り時間「Av Rd(ms)」などからI/O状況を確認。
Readsがどこかの表領域に集中している場合は、そこがパフォーマンス低下のボトルネックになっている可能性がある。
表領域が複数のデータファイルからなる場合は、File IO Statsを確認して、データファイル単位で、IO状況を確認する。
CPU Timeの長いSQL（効率の悪いSQL)の分析の仕方
「SQL ordered by CPU Time」の上位にあるSQLや1回の実行時間Elapsed Timeが長いSQLを特定し、Old hash valueを控える
控えたSQLの実行計画を確認する
SQL> @$ORACLE_HOME/rdbms/admin/spreqsql.sql
確認したstatspackレポートのbegin_snap/end_snapおよびold hash valueを入力する
（ただし、キャッシュに残っている情報を表示するだけなので、該当SQLがキャッシュアウトされていないことが条件）
- 実行計画の分析
同じ深さのインデントのステップは、上位のステップから実行されている
インデントが一番深いステップのうち、最上位に表示されたものが一番最初に実行されている
詳しくは、こちら参照
https://www.oracle.com/technetwork/jp/database/articles/tsushima/tsm04-1598237-ja.html
テーブルに対するスキャン
- TABLE ACCESS FULL
テーブルフルスキャン
- TABLE ACCESS BY INDEX ROWID
インデックスを使用したスキャン
次の行のNAME列に使用した索引名を表示する
Rows(行数)が多いと、CPU使用時間が多くなり、コストも高くなる。
→他の索引やパーティションの使用を検討すること
オプティマイザ統計が最新化されているかも確認しておくこと