Where is waiting for Active sessinon


/* Oracle 9i */
SELECT DISTINCT a.SID, c.event, d.spid, a.username, a.status, a.machine,
                a.program, a.action, a.module, a.logon_time, a.sql_hash_value,
                b.sql_text, c.state
           FROM v$session a, v$sql b, v$session_wait c, v$process d
          WHERE a.paddr = d.addr
            AND a.sql_hash_value = b.hash_value(+)
            AND a.SID = c.SID(+)
            AND a.status = 'ACTIVE'
            AND c.event NOT IN
                   ('SQL*Net message to client', 'rdbms ipc message',
                    'jobq slave wait', 'pipe get',
                    'SQL*Net message from client', 'PL/SQL lock timer',
                    'wakeup time manager')
       ORDER BY event DESC;

/* Oracle 10g */
SELECT DISTINCT a.SID, a.event, d.spid, a.username, a.status, a.machine,a.program, a.action, a.module, a.logon_time, a.sql_hash_value,b.sql_text, a.state
           FROM v$session a, v$sql b, v$process d WHERE a.paddr = d.addr AND a.sql_hash_value = b.hash_value(+) AND a.status = 'ACTIVE'
            AND a.event NOT IN ('SQL*Net message to client', 'rdbms ipc message','jobq slave wait', 'pipe get','SQL*Net message from client', 'PL/SQL lock timer','wakeup time manager') ORDER BY event DESC;
/* Oracle  10g(READ MORE) */
select w.event,s.logon_time,s.sid,p.spid, w.seconds_in_wait,w.state,s.username,s.machine, s.program,s.module,s.action,v.buffer_gets,v.sql_text ,v.hash_value
from v$session_wait w , v$session  s, v$sql v ,v$process p
where v.CHILD_NUMBER=0 and w.sid=s.sid and p.addr =s.paddr and v.hash_value(+)=s.sql_hash_value and s.status='ACTIVE'   and
w.event not in ('SQL*Net message to client','rdbms ipc message','jobq slave wait','pipe get','SQL*Net message from client','PL/SQL lock timer','wakeup time manager')
order by event desc;

/* Orale 10g(READ MORE) */
select s.event,s.logon_time,s.sid,p.spid, s.seconds_in_wait,s.state,s.username,s.machine, s.program,s.module,v.buffer_gets,v.sql_text ,v.hash_value
from v$session  s, v$sql v ,v$process p where v.CHILD_NUMBER=0 and
p.addr =s.paddr and v.hash_value(+)=s.sql_hash_value and s.status='ACTIVE'   and
s.event not in ('SQL*Net message to client','rdbms ipc message','jobq slave wait','pipe get','SQL*Net message from client','PL/SQL lock timer','wakeup time manager')
order by v.buffer_gets desc nulls last;
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: