RE: 10046/10079 Tracing understanding
- From: "Post, Ethan" <Ethan.Post@xxxxxx>
- To: <brian_wisniewski@xxxxxxxxx>, <Oracle-L@xxxxxxxxxxxxx>
- Date: Tue, 2 Aug 2005 13:01:05 -0500
I have only seen this in two types of circumstances although I am sure
there are others.
1) The midtier/client is doing all the work like stuffing your 2GB table
in a java array, sorting it, storing it in some sort of Java DB and then
feeding all 2+ million rows to a grid control on the web browser. I
exaggerate a bit.
2) Network issue, most common is NICS have not all been hard set to 100
FULL. Try ftping or rcopying a file from client to DB server and midtier
to DB server, go both ways, if it is really slow you could see this wait
as the top wait. Application could be too chatty also, too much back and
forth, ie...give me a row..ok give me a row...ok give me a row...
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Brian Wisniewski
Sent: Tuesday, August 02, 2005 12:48 PM
To: Oracle-L@xxxxxxxxxxxxx
Subject: 10046/10079 Tracing understanding
RHAS 3.0/ 2 node 9.2.0.6 RAC/Web Logic front-end/JDBC thin-client
connection.
Developer is not getting response time he needs from a PL/SQL package he
calls inside his java code. Made some sql changes to the package and it
didn't improve the performance. The procedure within the package he's
calling has 3 input and 17 out variables defined.
I traced all sessions (10046) and the only significant event showing up
from the call to the package.procedure in question is SQL*Net more data
to client. For one execution of it I was walking through there were over
170 waits for "more data to client".
Each execution of this call results in multiple "more data to client"
waits with nearly all of them being 2048 bytes in size.
I then performed 10046 & 10079 tracing on a run to see what data was in
the 2048 byte wait events. It appears to me mostly spaces (hex 20) are
being transmitted to the client for some reason - broken down into 2048
byte sends. I've included snippets of the 10046 and the 10046&10079
tracing below. I'm puzzled as to why Oracle is transmitting this 'empty'
information back to the client on the package call.
Any help understanding this would be appreciated.
Thanks - Brian
10046 only:
WAIT #1: nam='SQL*Net message to client' ela= 11 p1=675562835 p2=1 p3=0
WAIT #1: nam='SQL*Net more data to client' ela= 95 p1=675562835 p2=2052
p3=0
WAIT #1: nam='SQL*Net more data to client' ela= 20 p1=675562835 p2=2048
p3=0
WAIT #1: nam='SQL*Net more data to client' ela= 24 p1=675562835 p2=2048
p3=0
WAIT #1: nam='SQL*Net more data to client' ela= 19 p1=675562835 p2=2048
p3=0
WAIT #1: nam='SQL*Net more data to client' ela= 15 p1=675562835 p2=2048
p3=0
WAIT #1: nam='SQL*Net more data to client' ela= 14 p1=675562835 p2=1792
p3=0
WAIT #1: nam='SQL*Net more data to client' ela= 15 p1=675562835 p2=2048
p3=0
WAIT #1: nam='SQL*Net more data to client' ela= 251155 p1=675562835
p2=2048 p3=0
WAIT #1: nam='SQL*Net more data to client' ela= 15 p1=675562835 p2=2048
p3=0
Other related posts:
