Check session in waiting and how much time from its waiting
COLUMN event FORMAT A30
COLUMN wait_class FORMAT A15
COLUMN sid format 9999
select sid, seq#, EVENT, WAIT_CLASS, SECONDS_IN_WAIT from v$session_wait ORDER BY seconds_in_wait DESC;
Check session wait event information with SQL type and hash value
set linesize 1000
col p1 format 9999999
col p2 format 9999999
col program format a15
col event format a20
col event form a25 trunc head "Event| Waiting For"
col command format a15
col sid format 9999
col machine format a20
col username format a20
select /*+ RULE */ a.sid,substr(b.username,1,10) username,substr(b.osuser,1,10) osuser,
substr(b.program||b.module,1,15) program,substr(b.machine,1,22) machine,
to_char(logon_time,'ddMon hh24:mi') login,decode(command,0,'None',2,'Insert',3,'Select', 6,'Update',7,'Delete',10,'Drop Index',12,'Drop Table',45,'Rollback',47,'PL/SQL',command)
command,a.event,a.p1,a.p2,a.p1raw,b.sql_hash_value
from gv$session_wait a,gV$session b where b.sid=a.sid and
a.event not in('SQL*Net message from client','SQL*Net message to client','smon timer','pmon timer')
and username is not null
order by 7;
Check session wait from session wait history table
select b.seq#, a.sid, a.username username, b.event event,
b.p1, b.p2, b.p3
from v$session a, v$session_wait_history b
where b.sid=a.sid and
b.event not in('SQL*Net message from client','SQL*Net message to client','smon timer','pmon timer')
and a.username is not null;
Check Session Wait for Wait class and how long it waits
COLUMN username FORMAT A20
COLUMN event FORMAT A30
COLUMN wait_class FORMAT A15
SELECT NVL(b.username, '(oracle)') AS username,
b.sid,
b.serial#,
a.event,
a.wait_class,
a.wait_time,
a.seconds_in_wait,
a.state
FROM v$session_wait a,
v$session b
WHERE a.sid = b.sid
ORDER BY a.seconds_in_wait DESC;
From hash value you identifed the sql id and sql statements
Select sqlid,sql_text from v$sqlarea where plan_hash_value = 'Hashvalue';