SQLチューニング(1) --- 診断ユーティリティ

1Z0-033

診断ユーティリティ

  • STATSPACKのSQLレポートで怪しいSQLを確認
    • 取得順（SQL ordered by Gets）
    • 物理読み込み順（SQL ordered by Reads）
    • 実行順（SQL ordered by Executions）
    • 解析コール順（SQL ordered by Parse）
  • EXPLAIN PLANコマンドで怪しいSQL文の実行計画を確認
    • 事前にPLAN_TABLE表を作成(utlxplan.sqlを実行)
    • EXPLAIN PLANコマンドを実行
      • Ex)EXPLAIN PLAN FOR SELECT * FROM hr.employees;
    • PLAN_TABLE表を検索し、実行計画を表示
  • SQLトレースとTKPROFで詳細な実行計画を確認
    • 初期化パラメータの設定
      • USER_DUMP_DEST
      • MAX_DUMP_FILE_SIZE
      • TIMED_STATISTICS
    • SQL_TRACE初期化パラメータをTRUEに設定する
      • インスタンスレベルであれば、初期化パラメータファイルでSQL_TRACE=TRUEを設定して再起動（すべてのセッションでトレースするので非推奨）
      • ALTER SESSION SET sql_trace = TRUE ;
      • EXEC dbms_session.set_sql_trace(TRUE)
      • EXEC dbms_system.set_sql_trace_in_session(sid, serial#, TRUE)
    • アプリケーションを実行する
    • SQL_TRACEを終了する
      • SQL_TRACE初期化パラメータをFALSEにする
      • セッションを切断する
    • TKPROFユーティリティでトレースファイルを変換する
  • SQL*PlusのAUTOTRACE機能（いちばんお手軽）
    • PLAN_TABLE表の作成（utlxplan.sql）
    • PLUSTRACEロールの作成（plustrce.sql）
    • SET AUTOTRACE {ON | OFF | TRACEONLY}
SQL> @?/rdbms/admin/utlxplan

SQL> @?/sqlplus/admin/plustrce

SQL> set autot on

SQL> select sysdate from dual ;

-- SQLを実行するたびに、実行計画や統計結果が表示される

SQL> set autot off
  • ライブラリキャッシュ上にある実行計画の確認方法
    • SQLを実行した直後（ライブラリキャッシュから破棄される前）
    • v$sqlから該当SQL_TEXTのADDRESS値とHASH_VALUE値を見つける
    • v$sql_planからそのADDRESSとHASH_VALUEの実行計画を確認

※v$sqlではPLAN_HASH_VALUE値が同じものはカーソルの実行計画が同じ