DBチューニングのための指標を整理してみた

DB 性能

以下、考えたり調べたりしたけど、やはりDBのチューニングは、インデックス、メモリ、統計情報、待機回避、ソート回避、解析再利用、そして負荷分散、という観点に落ち着きそうだ。
（※以下、基本的にOracleについての情報です）

ブレスト的に考えみた指標

  • 事前設定
    • メモリ（バッファキャッシュ、共有プールなど）の容量（物理読み込みの頻度が高くないか）
    • ファイル分割
  • 状態
    • 統計情報は古くなっていないか
      • テーブル統計情報
      • 列統計情報
      • 索引統計情報
      • システム統計情報

　　　　　　※ANALYZE文で取得
　　　　　　※DBMS_STATSパッケージが便利

  • 定義
    • テーブル
    • インデックス
      • 対象列
      • 種類（B*Tree索引、ビットマップ索引、ファンクション索引、逆キー索引）
  • アクセス方法
    • SQL
      • アクセス方法は適切か
      • テーブル結合方法は適切か
      • ハードパース率が高すぎないか
      • 競合ロックが発生しすぎていないか
    • ネットワーク構成
    • 負荷分散構成

Oracleパフォーマンス指標（※ツール軸）

  • AUTOTRACE
    • db block gets: CURRENTブロック（DML やSELECT FOR UPDATE の際）要求回数
    • consistent gets: 読み取り一貫性ブロック要求回数
    • pyhsical reads: 物理読み込みのブロック数
    • sorts(memory): メモリ内でのソート回数
    • sorts(disk): 一時表領域でのソート回数
  • SQLトレース
    • SQLの解析、実行、およびフェッチのカウント
    • CPU時間と経過時間
    • 物理読み込みと論理読み込み
    • ライブラリ・キャッシュ・ミスの回数
    • 実行計画情報
      • cr: その行について、バッファから読み取ったブロック総数
      • pr: その行について、物理読み込みブロック総数
      • pw: その行について、物理書き込みブロック総数
      • time: 処理にかかった時間（マイクロ秒）
  • STATSPACK
    • ロードプロファイル: 負荷に関する情報。下記のような項目について、1秒あたりおよび1トランザクションあたりの量を示す。
    • インスタンス効率: SGAメモリ領域の状態に関する情報。100%に近いほど好ましい
    • TOP5待機イベント: プロセスがCPUを使わずに待っている状態である待機イベント
      • db file sequential read: ディスクからのランダムデータ読み込み待ちイベント
    • リソース使用率の高いSQL: 設定した閾値を越えたSQLの情報。設定する閾値は下記
      • CPU時間
      • 総処理時間
      • 論理ブロックアクセス数
      • 物理ブロックアクセス数
      • 実行回数
      • 解析回数

Oracleパフォーマンス関連ツール（CUIベース）

  • EXPLAIN PLAN: 実行計画の取得
    • PLAN_TABLEテーブルが必要
  • AUTOTRACE: 実行計画、CPU使用時間、物理読み込み・論理読み込み、などの取得
    • PLAN_TABLE, plustraceロール が必要
    • SET AUTOTRACE ON で開始
    • SET AUTOTRACE OFF で終了
  • SQLトレース: セッション単位、インスタンス単位で発行されたSQLのパフォーマンス情報の取得（※インスタンス単位のトレースを取得するには、Oracleの再起動が必要）
    • 初期化パラメータ TIMED_STATISTICS, USER_DUMP_DEST, MAX_DUMP_FILE_SIZE の設定
    • 開始（セッション単位）：ALTER SESSION SET SQL_TRACE=TRUE
      • PL/SQLから開始したい場合は、 DBMS_SESSION.SET_SQL_TRACE(TRUE);
      • 特定のセッションに対しては〜・・・
    • 開始（インスタンス単位）： SQL_TRACE=TRUE
  • TKPROF: SQLトレースの出力ファイルの整形
  • STATSPACK: 任意の瞬間でパフォーマンス統計のスナップショットを取得し、任意の2時点間の差分をレポート
  • ライブラリキャッシュ内のSQL

参考文献

プロとしてのSQLチューニング入門

プロとしてのSQLチューニング入門