Re: 10046/10079 Tracing understanding

On 8/2/05, Brian Wisniewski <brian_wisniewski_at_yahoo.com> 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

--
http://www.freelists.org/webpage/oracle-l