系统全局查看：

15分钟内最重要的等待事件：

select event,

sum(wait_time +

time_waited) total_wait_time

from v$active_session_history

where sample_time between

sysdate -180/2880 and sysdate

group by event

order by total_wait_time desc;

如果是30分钟,则是60/2880

15分钟内哪一个用户经历了最多的等待

select s.sid, s.username,

sum(a.wait_time +

a.time_waited) total_wait_time

from v$active_session_history a,

v$session s

where a.sample_time between sysdate -30/2880 and sysdate

and a.session_id=s.sid

group by s.sid, s.username

order by total_wait_time desc;

15分钟内等待时间最长的对象

select a.current_obj#, d.object_name, d.object_type, a.event,

sum(a.wait_time +

a.time_waited) total_wait_time

from v$active_session_history a,

dba_objects d

where a.sample_time between sysdate -30/2880 and sysdate

and a.current_obj# = d.object_id

group by a.current_obj#, d.object_name, d.object_type, a.event

order by total_wait_time;

15分钟内等待时间最长的SQL

select a.user_id,u.username,s.sql_text,

sum(a.wait_time + a.time_waited) total_wait_time

from v$active_session_history a,

v$sqlarea s,

dba_users u

where a.sample_time between sysdate -30/2880 and sysdate

and a.sql_id = s.sql_id

and a.user_id = u.user_id

group by a.user_id,s.sql_text, u.username;

在30分钟内查看引起等待最多的SQL：

select ash.user_id,

u.username,

s.sql_text,

sum(ash.wait_time +

ash.time_waited) ttl_wait_time

from v$active_session_history ash,

v$sqlarea s,

dba_users u

where ash.sample_time between sysdate - 60/2880 and sysdate

and ash.sql_id = s.sql_id

and ash.user_id = u.user_id

group by ash.user_id,s.sql_text, u.username

order by ttl_wait_time;

--查看各会话的等待事件（db file sequential read）：
select a.sid,
a.event,
a.time_waited,
a.time_waited / c.sum_time_waited * 100 pct_wait_time,
round((sysdate - b.logon_time) * 24) hours_connected
from v$session_event a, v$session b,
(select sid, sum(time_waited) sum_time_waited
from v$session_event
where event not in (
'Null event',
'client message',
'KXFX: Execution Message Dequeue - Slave',
'PX Deq: Execution Msg',
'KXFQ: kxfqdeq - normal deqeue',
'PX Deq: Table Q Normal',
'Wait for credit - send blocked',
'PX Deq Credit: send blkd',
'Wait for credit - need buffer to send',
'PX Deq Credit: need buffer',
'Wait for credit - free buffer',
'PX Deq Credit: free buffer',
'parallel query dequeue wait',
'PX Deque wait',
'Parallel Query Idle Wait - Slaves',
'PX Idle Wait',
'slave wait',
'dispatcher timer',
'virtual circuit status',
'pipe get',
'rdbms ipc message',
'rdbms ipc reply',
'pmon timer',
'smon timer',
'PL/SQL lock timer',
'SQL*Net message from client',
'WMON goes to sleep')
having sum(time_waited) > 0 group by sid) c
where a.sid = b.sid
and a.sid = c.sid
and a.time_waited > 0
and a.event = 'db file sequential read'
order by hours_connected desc, pct_wait_time;

select b.sid,
nvl(substr(a.object_name,1,30),
'P1='||b.p1||' P2='||b.p2||' P3='||b.p3) object_name,
a.subobject_name,
a.object_type
from dba_objects a, v$session_wait b, x$bh c
where c.obj = a.object_id(+)
and b.p1 = c.file#(+)
and b.p2 = c.dbablk(+)
and b.event = 'db file sequential read'
union
select b.sid,
nvl(substr(a.object_name,1,30),
'P1='||b.p1||' P2='||b.p2||' P3='||b.p3) object_name,
a.subobject_name,
a.object_type
from dba_objects a, v$session_wait b, x$bh c
where c.obj = a.data_object_id(+)
and b.p1 = c.file#(+)
and b.p2 = c.dbablk(+)
and b.event = 'db file sequential read'
order by 1;

ITPub博客

Oracle性能诊断思路2

最新文章