RE: 10046/10079 Tracing understanding

From: Brian Wisniewski <brian_wisniewski_at_yahoo.com>
Date: Tue, 2 Aug 2005 11:09:17 -0700 (PDT)
Message-ID: <20050802180917.17948.qmail@web32203.mail.mud.yahoo.com>

I should have mentioned the package call should only return 1 row to the client. I would have expected 'more data to client' if it was returning a lot of rows or blobs/etc were in the table but it isn't. 1 row - fairly small.

All these 'blank' sends are quite confusing.

Thanks - Brian

"Post, Ethan" <Ethan.Post_at_ps.net> wrote: I have only seen this in two types of circumstances although I am sure there are others.

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.
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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Brian Wisniewski Sent: Tuesday, August 02, 2005 12:48 PM
To: Oracle-L_at_freelists.org
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


__________________________________________________

Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com

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

Received on Tue Aug 02 2005 - 13:11:21 CDT