最耗时的10大会话

最新推荐文章于 2025-08-05 23:38:40 发布

jiawancai

最新推荐文章于 2025-08-05 23:38:40 发布

阅读量531

点赞数

CC 4.0 BY-SA版权

文章标签： events session join path null io

本文链接：https://blog.youkuaiyun.com/jiawancai/article/details/1069938

本文介绍了一个使用复杂SQL查询来分析会话事件的具体案例。通过详细展示如何从Oracle数据库的动态性能视图中提取数据，并利用这些数据进行进一步的分析，以此来识别导致性能瓶颈的主要SQL语句和事件。

set linesize 130 pagesize 1000
column sid format 999999 truncated
column program format A40 truncated
column top_events format A50 truncated

with events as (
select sid,time,sys_connect_by_path(text,' + ') top_events
from (
select sid,e.event,sum(time) over (partition by sid) time,
dense_rank() over (partition by sid order by e.time desc) rank,
time/sum(time) over (partition by sid) pct,
count(*) over (partition by sid) cnt,
to_char(round(100*e.time/sum(e.time) over (partition by sid)))||'% '||e.event text
from
(
select sid,event event,total_waits waits,time_waited/100 time from v$session_event
union all
select sid,'CPU',null,value/100 from v$statname join v$sesstat using (statistic#)where name = 'CPU used by this session'
) e
where time > 0
) where rank=cnt
connect by prior rank=rank-1 and prior sid=sid start with rank=1
)
select sid,v$session.program,substr(top_events,4,instr(top_events||'+ 0%','+ 0%')-4) top_events,
(block_gets+consistent_gets) logical_reads,round(pga_max_mem/1024) pga_kb
from events join v$session using(sid) join v$sess_io using (sid) join v$process on (paddr=v$process.addr)
order by block_gets+consistent_gets desc
/