待機イベント情報

SELECT s.inst_id AS inst_id
, s.sid AS sid
, s.serial# AS serial
, s.event AS event
, s.p1 AS p1
, s.p2 AS p2
, s.p3 AS p3
, DECODE( s.wait_time
, 0
, TO_CHAR(s.seconds_in_wait)
, '(' || TO_CHAR(s.wait_time) || ')'
) AS wait_time
, s.module AS module
, s.action AS action
, s.machine AS machine
, s.process AS c_pid
, p.spid AS s_pid
, c.command_name AS command
, s.username AS username
, s.blocking_session_status AS blocking_session_status
, s.blocking_session AS blocking_session
, s.row_wait_obj# AS object_id
, TO_CHAR(s.logon_time, 'YYYY/MM/DD HH24:MI:SS') AS logon_time
, TO_CHAR(s.sql_exec_start, 'YYYY/MM/DD HH24:MI:SS') AS sql_exec_start
, s.sql_id AS sql_id
, s.sql_child_number AS child_number
, s.status AS status
, s.audsid AS audsid
, a.plan_hash_value AS plan_hash_value
, a.parse_calls -- 解析コールの合計数
, GREATEST(a.executions, a.px_servers_executions) AS executions -- 実行の合計数
, a.fetches -- SQL文に関連するフェッチ数
, a.cpu_time / 1000000 AS cpu_time -- CPU時間
, a.elapsed_time / 1000000 AS elapsed_time -- 経過時間
, a.disk_reads -- ディスク読取りの合計数
, a.buffer_gets -- 取得したバッファの合計数
, a.rows_processed -- 処理された行の合計数
, TO_CHAR(a.last_active_time, 'YYYY/MM/DD HH24:MI:SS') AS last_active_time
FROM gv$session s
, gv$process p
, gv$sqlcommand c
, gv$sqlarea a
WHERE 0=0
AND s.inst_id = p.inst_id
AND s.paddr = p.addr
AND s.inst_id = c.inst_id
AND s.command = c.command_type
AND s.inst_id = a.inst_id(+)
AND s.sql_id = a.sql_id(+)
;

Oracleの現場を効率化する100の技

• 作者: 鈴木 健吾
• 出版社/メーカー: 技術評論社
• 発売日: 2015/05/26
• メディア: 単行本（ソフトカバー）

タグ：待機イベント v$session SQL Oracle