OraFAQ Forum: Performance Tuning » More "SQL*Net message to/from client" than expected

Home » RDBMS Server » Performance Tuning » More "SQL*Net message to/from client" than expected

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

More "SQL*Net message to/from client" than expected [message #177613]

Thu, 15 June 2006 15:09

YodaBoy
Messages: 2
Registered: June 2006
Location: UK

Junior Member

Here's an excerpt from a level 8, 10046 event trace (with SQL changed for security reasons):
:
FETCH #4:c=0,e=0,p=0,cr=1,cu=0,mis=0,r=0,dep=1,og=4,tim=27242431
FETCH #6:c=0,e=0,p=0,cr=1,cu=0,mis=0,r=0,dep=1,og=4,tim=27242431
=====================
PARSING IN CURSOR #1 len=94 dep=0 uid=64 oct=3 lid=64 tim=27242431 hv=1394343879 ad='1140e68c'
Select X.Y from X where X.Z= 'Names changed to protect the guilty!'
END OF STMT
PARSE #1:c=5,e=10,p=17,cr=101,cu=1,mis=1,r=0,dep=0,og=4,tim=27242431
WAIT #1: nam='SQL*Net message to client' ela= 0 p1=1413697536 p2=1 p3=0
WAIT #1: nam='SQL*Net message from client' ela= 1 p1=1413697536 p2=1 p3=0
WAIT #1: nam='SQL*Net message to client' ela= 0 p1=1413697536 p2=1 p3=0
WAIT #1: nam='SQL*Net message from client' ela= 0 p1=1413697536 p2=1 p3=0
EXEC #1:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=27242432
WAIT #1: nam='SQL*Net message to client' ela= 0 p1=1413697536 p2=1 p3=0
WAIT #1: nam='SQL*Net message from client' ela= 0 p1=1413697536 p2=1 p3=0
WAIT #1: nam='db file sequential read' ela= 0 p1=46 p2=138 p3=1
WAIT #1: nam='SQL*Net message to client' ela= 0 p1=1413697536 p2=1 p3=0
FETCH #1:c=0,e=0,p=1,cr=1,cu=4,mis=0,r=1,dep=0,og=4,tim=27242432
WAIT #1: nam='SQL*Net message from client' ela= 0 p1=1413697536 p2=1 p3=0
STAT #1 id=1 cnt=1 pid=0 pos=0 obj=13415 op='TABLE ACCESS FULL X '
WAIT #1: nam='SQL*Net message to client' ela= 0 p1=1413697536 p2=1 p3=0
WAIT #1: nam='SQL*Net message from client' ela= 0 p1=1413697536 p2=1 p3=0
WAIT #1: nam='SQL*Net message to client' ela= 0 p1=1413697536 p2=1 p3=0
WAIT #1: nam='SQL*Net message from client' ela= 0 p1=1413697536 p2=1 p3=0
=====================
PARSING IN CURSOR #4 len=283 dep=1 uid=0 oct=3 lid=0 tim=27242432 hv=955191413 ad='11c6ba1c'
select obj#,type#,ctime,mtime,stime
:
:

The application which accessed the database (8.1.7) is written in VB using MDAC 2.6 (i.e. ADO) using the "MS Data Provider for Oracle".

I have trouble with this application in that its developers, being pretty inexperienced with databases, saw fit to write layers of code that "hide the complexity" of the nasty thing.
This means plenty of, what I call "Noddy" SQL, being executed and the results being "glued-together" within VB Recordsets: completely missing the opportunity to perform joins and running real risks with producing inconsistent results because of running all this "bitty" SQL in read committed isolation mode.

Anyway, putting that aside, for a minute, looking between the PARSE and EXECUTE phase there is more than one "SQL*Net message to/from client" pair of wait events.

Does anyone know why I'm seeing this? I kind of expected to see one pair: i.e. PARSE - DB tells App, "I've Parsed" - App tells DB "OK, thanks a lot, get on with it..." - EXEC. The application is a lot more "chatty" than I would expect.

Help!!

Report message to a moderator

Re: More "SQL*Net message to/from client" than expected [message #177617 is a reply to message #177613]

Thu, 15 June 2006 15:39

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

I will admint it looks strange, but given the fact that ela=0 I would not worry about them.

Report message to a moderator

Re: More "SQL*Net message to/from client" than expected [message #177625 is a reply to message #177617]

Thu, 15 June 2006 17:09

YodaBoy
Messages: 2
Registered: June 2006
Location: UK

Junior Member

Ah! There's more to tell in this sad sorry tale...

This little excerpt is taken from a trace file over 1 Gb in size. It's actually from a batch job that is implemented using the same techniques as the OLTP parts of the application: i.e. poorly.

At the risk of being proved wrong, I can say that there's really no true zero cost events and ela=0's are there due to quantization errors within Oracle's clock (i.e. rounding down to the nearest 1/100th sec in Oracle 8i : 0 sec > true ela < 0.005 sec).

I use the Hotsos Profiler and my resource profile is showing 330,000 SQL*Net message from client wait events taking 270 seconds on the wall clock (I get plenty of ela=1 for these events as well, and a fairly large amount of unaccounted time - I think that the majority of my SQL*Net take place in less than 0.005 sec, leaving a large gap in the accounted time).

(I hope that Cary Millsap finds this post!)

The obvious thing to do is to reduce the number of SQL calls but that will involve extensive re-write of this part of the app. That's not going to happen overnight but if there is something not quite set up in the Data Access Layer of the app (e.g. in ADO) that is creating even more unnecessary round trips over the network, I'd like to know what they are and hopefully reduce them with some easy, quick fix along set lines of set ADO.Chatter=false - I can live in hope anyway...

Thanks for the advice, "anacedent": you didn't get the full picture though and I really DO have to worry about these events!!!

Report message to a moderator

Re: More "SQL*Net message to/from client" than expected [message #177628 is a reply to message #177613]

Thu, 15 June 2006 17:26

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Cary frequently monitors the ORACLE-L list

Report message to a moderator

Previous Topic:	Latch Contention
Next Topic:	Automatic Shared Memory Management (ASMM) !!

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Apr 30 20:16:31 CDT 2024