RE: How to make a session active with wait event of 'sql*net message FROM client'?
From: Saibabu Devabhaktuni <saibabu_d_at_yahoo.com>
Date: Fri, 19 Jun 2009 01:58:31 -0700 (PDT)
Message-ID: <144483.47246.qm_at_web56004.mail.re3.yahoo.com>
Date: Fri, 19 Jun 2009 01:58:31 -0700 (PDT)
Message-ID: <144483.47246.qm_at_web56004.mail.re3.yahoo.com>
I've reproduced it on Oracle 10.2.0.4,
- run the following from session 1
declare
a number;
begin
for i in 1..1000000 loop
select 1 into a from dual;
end loop;
end;
/
- keep running the following from session 2 connected as sysdba
select
decode(bitand(s.ksuseidl,11),1,'ACTIVE',0,decode(bitand(s.ksuseflg,4096),0,'INACTIVE','CACHED'),2,'SNIPED',3,'SNIPED', 'KILLED') status, s.ksuseopc, w.ksusehopc, w.ksusehwnum, w.ksusehwtm from x$ksuse s, x$ksuseh w
where s.indx=w.ksusehsnum and s.indx=4369 and rownum <=2
/
STATUS KSUSEOPC KSUSEHOPC KSUSEHWNUM KSUSEHWTM -------- ---------- ---------- ---------- ----------
ACTIVE 259 259 1 1252 ACTIVE 259 255 2 6
event# 259 is sqlnet message from client, but you see that the waitevent right before that was 255 (which is sqlnet message to client) when the session status was still ACTIVE. So, it is due to the dirty reads of v$/x$table behavior.
Thanks,
Sai
http://sai-oracle.blogspot.com
-- http://www.freelists.org/webpage/oracle-lReceived on Fri Jun 19 2009 - 03:58:31 CDT