チューニング対象SQL文を確認する

【概要】
SQLチューニングは、SQL文のパフォーマンスを、特定の測定可能かつ達成可能な目標に
合うように向上させる反復プロセスです。
【SQL文】
/*
|| ■チューニング対象SQL文を確認する
|| 以下の要素を持つSQL文を抽出する
|| ①1実行当たりの実行時間が長いSQL
|| ②ディスク読み取りブロック数が多いSQL
|| ③バッファの読み取り数が極端に多いSQL
|| ④実行回数が極端に多いSQL
|| ※SQL文を全文出力する場合は、上記の文とv$sqltextビューを
|| addressとhash_valueで各々結合し、v$sqltextのsql_text列を表示する
|| 【項目の説明】
|| SQL_TEXT ：カレント・カーソルのSQLテキストの最初の1000文字
|| ADDRESS ：このカーソルの親に対するハンドルのアドレス
|| HASH_VALUE ：ライブラリ・キャッシュ内の親文のハッシュ値
|| PARSE_CALLS ：この子カーソルに対する解析コール数
|| EXECUTIONS ：このオブジェクトがライブラリ・キャッシュに入れられた後で行われた実行数
|| BUFFER_GETS ：この子カーソルに対するバッファ取得数
|| DISK_READS ：この子カーソルに対するディスク読取り数
|| BUFFER_PER_RUN ：1実行当たりのバッファ取得数
|| DISK_PER_RUN ：1実行当たりのディスク読取り数
|| CPU_TIME ：このカーソルが解析、実行およびフェッチのために使用するCPU時間(マイクロ秒)
|| ELAPSED_TIME ：解析、実行およびフェッチに対してこのカーソルで使用される経過時間(マイクロ秒)です。
|| 　カーソルがパラレル実行を使用する場合、ELAPSED_TIME_TOTALは、
|| 　問合せコーディネータへの時間にすべてのパラレル問合せスレーブ処理を加えた累積時間です。
*/
set echo off
set line 200
set pages 1000
clear col

spool sql_tuning_info.lis

col sql_text for a140
col buffer_per_run for 999,999,999,999
col disk_per_run for 999,999,999,999
col cpu_time for 999,999,999,999
col elapsed_time for 999,999,999,999

/*
|| ①1実行当たりの実行時間が長いSQL（ワースト10）
|| STATSPACKの【経過時間が長い順】に相当
*/
ttitle off
ttitle left ‘■■■　1実行当たりの実行時間が長いSQL　■■■’

SELECT sql_text
, buffer_per_run
, disk_per_run
, cpu_time
, elapsed_time
FROM (SELECT sql_text
, address
, hash_value
, parse_calls
, executions
, buffer_gets
, disk_reads
, buffer_gets/executions buffer_per_run
, disk_reads/executions disk_per_run
, cpu_time
, elapsed_time
FROM v$sql
WHERE executions>0
ORDER BY elapsed_time desc) –←この条件を変更する
WHERE rownum <= 10　 — ←表示件数はこの値を変更する
;
/*
|| ②ディスク読み取りブロック数が多いSQL（ワースト10）
|| STATSPACKの【物理読込みの多い順】に相当
*/
ttitle off
ttitle left ‘■■■　ディスク読み取りブロック数が多いSQL　■■■’

SELECT sql_text
, buffer_per_run
, disk_per_run
, cpu_time
, elapsed_time
FROM (SELECT sql_text
, address
, hash_value
, parse_calls
, executions
, buffer_gets
, disk_reads
, buffer_gets/executions buffer_per_run
, disk_reads/executions disk_per_run
, cpu_time
, elapsed_time
FROM v$sql
WHERE executions>0
ORDER BY disk_reads desc) 　– ←この条件を変更する
WHERE rownum <= 10 –←表示件数はこの値を変更する
;
/*
|| ③バッファの読み取り数(累積&１実行当たり)が極端に多いSQL（ワースト10）
|| STATSPACKの【バッファ取得の多い順】に相当
*/
ttitle off
ttitle left ‘■■■　バッファの読み取り数(累積&１実行当たり)が極端に多いSQL　■■■’

SELECT sql_text
, buffer_per_run
, disk_per_run
, cpu_time
, elapsed_time
FROM (SELECT sql_text
, address
, hash_value
, parse_calls
, executions
, buffer_gets
, disk_reads
, buffer_gets/executions buffer_per_run
, disk_reads/executions disk_per_run
, cpu_time
, elapsed_time
FROM v$sql
WHERE executions>0
ORDER BY buffer_gets desc) 累積
–ORDER BY buffer_gets/executions desc) –１実行当たり
WHERE rownum <= 10 — ←表示件数はこの値を変更する
;
/*
|| ④実行回数が極端に多いSQL（ワースト10）
|| STATSPACKの【実行回数が多い順】に相当
*/
ttitle off
ttitle left ‘■■■　実行回数が極端に多いSQL　■■■’

以上です (^^♪