当前位置: 首页 > BASIC, SQL > 正文

常用脚本1:统计当前会话的等待事件

脚本来至于www.htz.pw

用于10G以上版本

SET ECHO OFF
SET PAGESIZE 2000
SET LINESIZE 200
SET HEADING ON
COL event FORMAT a25
COL program FORMAT a23
COL os_sess FOR a25 heading ‘SESS_SERIAL|OSPID’
col u_s for a22 heading ‘USERNMAE|LAST_CALL|SEQ#’
COL client FOR a31
col sql_id for a18
COL row_wait for a22 heading ‘ROW_WAIT|FILE#:OBJ#:BLOCK#:ROW#’
col logon_time for a12
col status for a20 heading ‘STATUS|STATE’
col command for a15
col block_s for a15 heading ‘BLOCK_SESS|INST:SESS’
col inst_id for 9 heading ‘I’
break on inst_id
SELECT b.inst_id,SUBSTR(b.event, 1, 25) event,
SUBSTR(b.program, 1, 22) program,
b.username||’:’||last_call_et||’:’||b.seq# u_s,
b.sid || ‘:’ || b.serial# || ‘:’ || c.spid os_sess,
substr(b.status || ‘:’ || b.state,1,19) status,
a.name command,
DECODE(b.sql_id, ‘0′, b.prev_sql_id, ”,b.prev_sql_id,b.sql_id) || ‘:’ ||
sql_child_number sql_id,
b.BLOCKING_SESSION_STATUS || ‘:’ || b.BLOCKING_INSTANCE || ‘:’ ||
b.BLOCKING_SESSION block_s,
row_wait_file# || ‘:’ || row_wait_obj# || ‘:’ || row_wait_block# || ‘:’ ||
row_wait_row# row_wait
FROM gv$session b, gv$process c, gv$session_wait s, sys.audit_actions a
WHERE b.paddr = c.addr
AND s.SID = b.SID
and b.inst_id=c.inst_id
and c.inst_id=s.inst_id
and a.action = b.command
and b.status = ‘ACTIVE’
and b.username is not null
order by inst_id,sql_id
/

select b.inst_id,DECODE(b.sql_id, ‘0′, b.prev_sql_id, b.sql_id) sql_id, a.name command, count(*)
from gv$session b, sys.audit_actions a
where username is not null
and status = ‘ACTIVE’
and a.action = b.command
group by inst_id,DECODE(b.sql_id, ‘0′, b.prev_sql_id, b.sql_id), a.name
order by inst_id,4 desc;

col event for a40

select b.inst_id,event,
DECODE(b.sql_id, ‘0′, b.prev_sql_id, b.sql_id) sql_id,
count(*)
from gv$session b
where b.status = ‘ACTIVE’
and b.username is not null
group by inst_id,event, DECODE(b.sql_id, ‘0′, b.prev_sql_id, b.sql_id)
order by inst_id;

select inst_id,DECODE(STATE,
‘ON CPU’,
DECODE(TYPE, ‘BACKGROUND’, ‘BCPU’, ‘CPU’),
EVENT) EVENT,
count(*)
from gv$session
where username is not null
and status = ‘ACTIVE’
group by inst_id,DECODE(STATE,
‘ON CPU’,
DECODE(TYPE, ‘BACKGROUND’, ‘BCPU’, ‘CPU’),
EVENT)
order by inst_id,3 desc;

本文固定链接: http://www.htz.pw/2014/05/28/%e6%89%8b%e5%8a%a8%e6%b8%85%e9%99%a4dba_datapum_jobs%e4%b8%ad%e5%bc%82%e5%b8%b8%e7%9a%84%e4%bd%9c%e4%b8%9a.html | 认真就输

该日志由 huangtingzhong 于2014年05月28日发表在 BASIC, SQL 分类下, 你可以发表评论,并在保留原文地址及作者的情况下引用到你的网站或博客。
原创文章转载请注明: 常用脚本1:统计当前会话的等待事件 | 认真就输