実行に6秒以上かかっているSQLを特定する
忘れないようにメモ
select
a.username
, a.target
, a.opname AS "Opearation"
, min(a.start_time) AS "最古開始時間"
, max(a.start_time) AS "最新開始時間"
, to_char(count(a.username), '999,999') || ' 回' AS "累積発生回数"
, to_char(sum(a.elapsed_seconds), '999,999') || ' Sec' AS "合計実行時間"
, to_char(avg(a.elapsed_seconds), '999,999.99') || ' Sec' AS "平均実行時間"
, to_char(min(a.elapsed_seconds), '999,999') || ' Sec' AS "最小実行時間"
, to_char(max(a.elapsed_seconds), '999,999') || ' Sec' AS "最大実行時間"
, to_char(sum(a.totalwork), '999,999,999,999') || ' ' || units AS "合計ブロック数"
, to_char(avg(a.totalwork), '999,999,999,999') || ' ' || units AS "平均ブロック数"
, to_char(min(a.totalwork), '999,999,999,999') || ' ' || units AS "最小ブロック数"
, to_char(max(a.totalwork), '999,999,999,999') || ' ' || units AS "最大ブロック数"
, b.SQL_TEXT
from
v$session_longops a
, v$sqlarea b
where
a.sql_address = b.address
AND a.sql_hash_value = b.hash_value
group by
a.username
, a.target
, a.opname
, a.units
, b.SQL_TEXT
order by
a.target
, a.opname
, a.units
, b.SQL_TEXT