查看Oracle等待事件

查询Oracle等待事件

一、查看当前某个SQL_ID会话情况

set linesize 500
set pagesize 500
col sid format 9999
col spid for a5
col serial# format 999999
col event format a23
col p123 format a15
col wait_time format 999
col sql_id for a15
col username format a12
col machine format a12
col program format a12
alter session set cursor_sharing=force;
select /*XJ LEADING(S) FIRST_ROWS */ s.sid,s.serial#,p.spid,s.username,s.machine,s.program,s.event,S.P1 || '/'||S.P2||'/'||S.P3 P123,S.wait_time,NVL(SQL_ID,S.PREV_SQL_ID) SQL_ID from v$session s,v$process p where s.username is not null and s.paddr=p.addr and s.status='ACTIVE' and s.sid<>(select sid from v$mystat where rownum=1) and sql_id='31g8jw692q4gz' order by p.pid desc;

二、查看Oracle等待事件

col event for a60
select decode(wait_time, 0, event, 'ON CPU') event, count(*)
  from gv$session
 where wait_class <> 'Idle'
   and STATUS = 'ACTIVE'
 group by decode(wait_time, 0, event, 'ON CPU')
 order by 2 asc;
 
 select sql_id, count(*)
  from v$session s, v$process p
 where s.username is not null
   and s.paddr = p.addr
   and s.status = 'ACTIVE'
   and s.sid <> (select sid from v$mystat where rownum = 1)
   and event = 'gc buffer busy'
   and s.username = 'DPHIS'
 group by sql_id;

三、查询Oracle历史等待事件

select  sql_id,event,count(*) from (
 select user_id,
       session_id,
       session_serial#,
       sql_id,
       event,
       session_state,
       blocking_session,
       blocking_session_serial#
  from dba_hist_active_sess_history
 where SAMPLE_TIME between
       TO_TIMESTAMP('2025-04-25 08:30:00', 'YYYY-MM-DD HH24:MI:SS') and
       TO_TIMESTAMP('2025-04-25 09:10:00', 'YYYY-MM-DD HH24:MI:SS')
)  group by  sql_id,event  having count(*) >100 order by 3 desc;

四、查询过去具体的某个等待事件情况

select  sql_id,event,count(*) from (
 select user_id,
       session_id,
       session_serial#,
       sql_id,
       event,
       session_state,
       blocking_session,
       blocking_session_serial#
  from dba_hist_active_sess_history
 where SAMPLE_TIME between
       TO_TIMESTAMP('2025-04-22 10:30:00', 'YYYY-MM-DD HH24:MI:SS') and
       TO_TIMESTAMP('2025-04-22 11:30:00', 'YYYY-MM-DD HH24:MI:SS')
   and event = 'gc buffer busy acquire'
)  group by  sql_id,event order by 3;

五、根据 sql_id查询过去时间等待事件

select  sql_id,event,count(*) from (
 select user_id,
       session_id,
       session_serial#,
       sql_id,
       event,
       session_state,
       blocking_session,
       blocking_session_serial#
  from dba_hist_active_sess_history
 where SAMPLE_TIME between
       TO_TIMESTAMP('2025-04-21 10:30:00', 'YYYY-MM-DD HH24:MI:SS') and
       TO_TIMESTAMP('2025-04-21 11:30:00', 'YYYY-MM-DD HH24:MI:SS')
   and sql_id = '3km4xrs386nqq'
)  group by  sql_id,event order by 3;