sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 运维DBA >

【学习笔记】Oracle数据库统计当前会话的等待事件脚本

时间:2016-10-24 20:39   来源:Oracle研究中心   作者:HTZ   点击:

天萃荷净 Oracle研究中心学习笔记:分享一篇关于Oracle性能优化之统计当前会话session中所有等待事件的脚本。


用于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 =Oracle?о????? 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 | 认真就输

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【学习笔记】Oracle数据库统计当前会话的等待事件脚本

本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/1053.html

Oracle研究中心

关键词:

Oracle统计当前会话的等待事件脚本

如何统计Oracle当前会话的等待事件