On 8/2/05, Brian Wisniewski <brian_wisniewski@xxxxxxxxx> wrote: > RHAS 3.0/ 2 node 9.2.0.6 <http://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) > (overflow) 1793 bytes sent via fast-path > Dump of memory from 0x0ABAD20A to 0x0ABAD90B > ABAD200 20FF2020 20202020 [ . ] > ABAD210 20202020 20202020 20202020 20202020 [ ] > Repeat 14 times > ... > > __________________________________________________ > Do You Yahoo!? > Tired of spam? Yahoo! Mail has the best spam protection around > http://mail.yahoo.com > -- Niall Litchfield Oracle DBA http://www.niall.litchfield.dial.pipex.com