SELECT
S.STATUS, S.USERNAME,
NVL(S.SQL_EXEC_START, S.PREV_EXEC_START) "実行日時",
S.LAST_CALL_ET "状態維持秒数",
ROUND(
CASE WHEN Q.EXECUTIONS > 0 THEN Q.CPU_TIME / Q.EXECUTIONS / 1000
ELSE 0 END, 2
) "CPU時間(ミリ)",
Q.SQL_TEXT
FROM
V$SESSION S, V$SQLSTATS Q
WHERE
S.USER# <> 0
AND Q.SQL_ID(+) = NVL(S.SQL_ID, S.PREV_SQL_ID)
ORDER BYCASE STATUS WHEN 'ACTIVE' THEN 1 WHEN 'INACTIVE' THEN 2 ELSE 9 END,
CASE WHEN Q.EXECUTIONS > 0 THEN Q.CPU_TIME / Q.EXECUTIONS ELSE 0 END DESC
(注) 動的パフォーマンス・ビューの結合処理は正式にはサポートされていません。
実行結果の例 (Windows + Oracle 11g の場合)
STATUS
USERNAME
実行日時
状態維持秒数
CPU時間(ミリ)
SQL_TEXT
ACTIVE
RIVUS
2010/06/19 00:45:18
0
2.91
"SELECT S.STATUS, S.USERNAME, NVL(S.SQL_EXEC_START, S.PREV_EXEC_START) "実行日時", S.LAST_CALL_ET "状態維持秒数", ROUND(CASE WHEN Q.EXECUTIONS > 0 THEN Q.CPU_TIME / Q.EXECUTIONS / 1000 ELSE 0 END, 2) ”CPU時間(ミリ)", Q.SQL_TEXT FROM V$SESSION S, V$SQLSTATS Q WHERE S.USER# <> 0 AND Q.SQL_ID(+) = NVL(S.SQL_ID, S.PREV_SQL_ID) ORDER BY CASE STATUS WHEN 'ACTIVE' THEN 1 WHEN 'INACTIVE' THEN 2 ELSE 9 END, CASE WHEN Q.EXECUTIONS > 0 THEN Q.CPU_TIME / Q.EXECUTIONS ELSE 0 END DESC "