Oracle Wait info

Oracle Wait information

w1.sql



prompt Report Current Wait Events.

set linesize 150
set verify off
set serveroutput on size 1000000
set feedback off

declare
cursor c_waits is
select w.sid
,decode (s.username,s.schemaname,s.username,s.username||'/'||s.schemaname) user_schema
,w.event
,w.p1, w.p1text, w.p1raw
,w.p2, w.p2text
,w.p3, w.p3text
-- ,w.wait_time
,w.seconds_in_wait
,decode (substr (w.state, 1, 7)
,'WAITING','Y'
,'WAITED ','N'
,w.state) as state
from v$session_wait w
,v$session s
where w.event <> 'SQL*Net message from client'
and w.event <> 'SQL*Net message to client'
and w.event <> 'rdbms ipc message'
and w.event <> 'smon timer'
and w.event <> 'pmon timer'
and w.sid = s.sid
order by w.p1text, w.p1, w.sid;
str varchar2(500);
s_desc varchar2(500);
n_db_files number default 1024;
begin
dbms_output.put_line ('Sid User Event W Scnds Details');
dbms_output.put_line ('--- ---------- ------------------------------- - ----- -----------------------------');
for r_wait in c_waits
loop
str := rpad (to_char (r_wait.sid),4);
str := str|| rpad (substr (r_wait.user_schema,1,10),11);
str := str|| rpad (substr (r_wait.event,1,30),31);
str := str|| ' '||r_wait.state;
str := str|| lpad (r_wait.seconds_in_wait,6);
str := str|| lpad (substr (r_wait.p1text,1,15),16)||': ';
str := str|| '('||r_wait.p1raw||')';
-- decode parameter 1 of wait event if we know how
if (r_wait.p1text = 'files') then
str := str||' Files:'||r_wait.p1||' Blks:'||r_wait.p2||' Reqs:'||r_wait.p3;
elsif (r_wait.p1text = 'file#')
or (r_wait.p1text = 'file number') then
begin
select to_number (value)
into n_db_files
from v$parameter
where name = 'db_files';
exception
when others then null;
end;
if r_wait.p1 > n_db_files then
begin /* temporary file */
select substr (file_name, instr (file_name,'/',-2) + 1)
into s_desc
from dba_temp_files
where to_char (file_id) = to_char(r_wait.p1 - n_db_files);
exception
when no_data_found then s_desc := to_char(null);
end;
else
begin /* data file */
select substr (file_name, instr (file_name,'/',-2) + 1)
into s_desc
from dba_data_files
where file_id = r_wait.p1;
exception
when no_data_found then s_desc := to_char(null);
end;
end if;
str := str||' '''||s_desc||'''';
elsif r_wait.p1text='name|mode' then
begin
select chr(bitand(r_wait.p1,-16777216)/16777215) ||
chr(bitand(r_wait.p1,16711680)/65535)||'|'||
bitand (r_wait.p1,65536)
into s_desc
from dual;
exception
when no_data_found then s_desc := to_char(null);
end;
str := str||' ('||s_desc||')';
end if;
-- decode parameter 2 of wait event if we know how
if (r_wait.event = 'latch free')
or (r_wait.event = 'latch activity') then
begin
select name
into s_desc
from v$latch
where latch# = r_wait.p2;
exception
when no_data_found then s_desc := to_char(null);
end;
str := str||' '||s_desc;
end if;
dbms_output.put_line (str);
end loop;
end;
/

set feedback 6







Sid User Event W Scnds Details
--- ---------- ------------------------------- - ----- -----------------------------
49 /SYS Space Manager: slave idle wait Y 1 Slave ID: (00)
28 /SYS Streams AQ: qmn slave idle wai Y 24 Type: (0000000000000001)
5 /SYS DIAG idle wait Y 0 component: (0000000000000005)
8 /SYS DIAG idle wait Y 1 component: (0000000000000005)
3 /SYS VKTM Logical Idle Wait Y713694: (00)
26 /SYS Streams AQ: qmn coordinator id Y 24: (00)
30 /SYS Streams AQ: waiting for time m Y 11350: (00)
51 /SYS VKRM Idle Y 42483: (00)