Re: orasrp SQL*Net message to client as non-idle event

From: Egor Starostin <egorst_at_gmail.com>
Date: Sun, 7 Feb 2010 19:57:10 +0300
Message-ID: <f0fc91971002070857n7a777f20m6a5ebdb3babaefe3_at_mail.gmail.com>

Hi

> I wonder how can we determine if SQL*Net message to client is idle or not,
> for instance tkprof just sum them up and show as single wait event, how is
> orasrp accounting the difference?

I think you mean 'SQL*Net message from client' since OraSRP can split only a couple of events into an [idle] and [non-idle] parts: 'SQL*Net message from client' and 'SQL*Net message from dblink'.

OraSRP decides that 'SQL*Net message from client' is idle when it happened between db calls. Otherwise (within db call) it's not attributed as [idle].
If you are familiar with 10046 contents then here's an example from real trace (with my small comments inline): ***

---

PARSING IN CURSOR #1 len=43 dep=0 uid=0 oct=3 lid=0 tim=1304096237 hv=2707617103 ad='89a03e18'
select * from all_objects where rownum < 20 END OF STMT
PARSE #1:c=0,e=143,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1304096209 EXEC #1:c=0,e=744,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1304097036 WAIT #1: nam='SQL*Net message to client' ela= 3 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1304097096
FETCH #1:c=10000,e=6903,p=0,cr=9,cu=0,mis=0,r=1,dep=0,og=1,tim=1304104057 WAIT #1: nam='SQL*Net message from client' ela= 721 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1304104865 # [non-idle] WAIT #1: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1304105319
FETCH #1:c=0,e=627,p=0,cr=21,cu=0,mis=0,r=15,dep=0,og=1,tim=1304105524 WAIT #1: nam='SQL*Net message from client' ela= 253 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1304105818 # [non-idle] WAIT #1: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1304105867
FETCH #1:c=0,e=63,p=0,cr=6,cu=0,mis=0,r=3,dep=0,og=1,tim=1304105900 WAIT #1: nam='SQL*Net message from client' ela= 1960753 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1306066946 # [idle]

---

PARSING IN CURSOR #1 len=21 dep=0 uid=0 oct=3 lid=0 tim=1306069444 hv=2200891488 ad='89913b50'
select user from dual
END OF STMT
PARSE #1:c=0,e=60,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1306069440 ***

The first and the second events 'SQL*Net message from client' are happened within db call (select * from all_objects ...) and treated as non-idle.
And the third one is happened between db calls and will be attributed by OraSRP as [idle].

-- 
Egor
http://oracledba.ru
--
http://www.freelists.org/webpage/oracle-l

Received on Sun Feb 07 2010 - 10:57:10 CST