| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: 10046/10079 Tracing understanding - SOLVED
I finally figured out the problem with the SQL*Net more data to client problem. The developer defined output variables as CHAR since he was only passing back a single character.
Well the max size of a CHAR field in a procedure is 32K and it's fixed length so it was returning the value back to the calling program along with another 32000+ spaces to fill it out to the max possible size. And he was doing this with 10 fields so that's a mere 320K of spaces sent back to the java pgm each and every time this pkg was called! Hence the need for Oracle to break that down into manageable pieces to send across the network.
A quick change to VARCHAR2 fixed the issue.
Initial testing showed this to only be an issue when the package was called by java - I didn't see this ...more data.. when I called it via sqlplus from the same client.
Niall Litchfield <niall.litchfield_at_gmail.com> wrote: On 8/2/05, Brian Wisniewski <brian_wisniewski_at_yahoo.com> wrote:
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.
I can't comment on why the empty packet to the client (unless its an ACK or similar), but in your 10046 trace you have a number of very quick more data to client waits (like 10-20 milliseconds) and then one rather slow one I'd be intrigued as to what data was in the 'slow' packet. It may be (though I don't think it should be a more data wait, but rather a message from wait) an indication that indeed the client is doing something slow with that particular packet.
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
snip
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
...
10046 & 10079 together
...
WAIT #1: nam='SQL*Net more data to client' ela= 14 p1=675562835 p2=2179 p3=0
SEND: (bp=0xb74209d8, bl=255, isdblink=0)
(overflow) 1537 bytes sent via fast-path
Dump of memory from 0x0ABAD2DB to 0x0ABAD8DC
ABAD2D0 FF202020 20202020 [ . ]
ABAD2E0 20202020 20202020 20202020 20202020 [ ]
Repeat 14 times
ABAD3D0 20202020 20202020 FF202020 20202020 [ ]
ABAD3E0 20202020 20202020 20202020 20202020 [ ]
Repeat 14 times
ABAD4D0 20202020 20202020 FF202020 20202020 [ . ]
ABAD4E0 20202020 20202020 20202020 20202020 [ ]
Repeat 14 times
ABAD5D0 20202020 20202020 FF202020 20202020 [ . ]
ABAD5E0 20202020 20202020 20202020 20202020 [ ]
Repeat 14 times
ABAD6D0 20202020 20202020 FF202020 20202020 [ . ]
ABAD6E0 20202020 20202020 20202020 20202020 [ ]
Repeat 14 times
ABAD7D0 20202020 20202020 FF202020 20202020 [ . ]
ABAD7E0 20202020 20202020 20202020 20202020 [ ]
Repeat 14 times
ABAD8D0 20202020 20202020 FF202020 [ .]
Dump of memory from 0xB74209D8 to 0xB7420AD7
B74209D0 20202020 20202020 [ ]
B74209E0 20202020 20202020 20202020 20202020 [ ]
Repeat 14 times
B7420AD0 20202020 20202020 [ ]
WAIT #1: nam='SQL*Net more data to client' ela= 13 p1=675562835 p2=1792 p3=0
SEND: (bp=0xb74211d0, bl=255, isdblink=0)
ABAD200 20FF2020 20202020 [ . ]
ABAD210 20202020 20202020 20202020 20202020 [ ]
Repeat 14 times
-- Niall Litchfield Oracle DBA http://www.niall.litchfield.dial.pipex.com --------------------------------- Start your day with Yahoo! - make it your home page -- http://www.freelists.org/webpage/oracle-lReceived on Thu Aug 04 2005 - 11:39:30 CDT
 |
 |