Re: Help understand raw trace data.

Jack,

Why am I sending so much data and what would cause the long delays in the 
SQL*Net more data top client.

Thanks

Fred



It's early for me, but what exactly is your question?



Jack van Zanen

------------------------- 
This e-mail and any attachments may contain confidential material for the sole 
use of the intended recipient. If you are not the intended recipient, please be 
aware that any disclosure, copying, distribution or use of this e-mail or any 
attachment is prohibited. If you have received this e-mail in error, please 
contact the sender and delete all copies.
Thank you for your cooperation 


2009/9/6 Fred Tilly <ftilly@xxxxxxxxxxxxxx>
Hi,
        
        The procedure accepts  the following parameters:
        
           ALOGID NUMBER;
           AINTREF VARCHAR2(50);
           ACLIENTTYPE NUMBER;
           ACLIENTID VARCHAR2(20);
           ACLIENTSUBID VARCHAR2(50);
           ASTARTTIME varchar2(20);
           ACHANNEL NUMBER;
           AINITUSERGROUP NUMBER;
           AINITUSERGROUPNAME VARCHAR2(50);
           AINITUSERID VARCHAR2(100);
           AINITUSERNAME VARCHAR2(50);
           AREFERENCE VARCHAR2(50);
           ADATA VARCHAR2(500);
           AVERIFIED CHAR(1);
           ACLIENTCATEGORY NUMBER;
           ACLIENTNAME VARCHAR2(255);
           AENDDATE varchar2(20);
         
        And it is being called via JDBC, but I have no access to the calling 
routine.
         
        The main problem is that this query is used by a telephone based 
application and at times takes 2 seconds or more to respond but most of the 
time it has a subsecond response. However the database is being blamed but I do 
not think this is the case, I am looking at gathering evidence and the trace 
was my starting point.
         
         
        Thanks
         
        Fred
        
        --
        http://www.freelists.org/webpage/oracle-l
        
        
        


Fred

What is the signature of the procedure ORA_INTERACTION? are the parameters 
constrained or unconstrained, CHARs or VARCHAR2s, and byte or char semantics? 

And what are the datatypes of the actual arguments? Come to that, describe the 
calling client (JDBC?)

Regards Nigel

2009/9/4 Fred Tilly <ftilly@xxxxxxxxxxxxxx>
Hi,
        
        I have a stored procedure that runs a simple select statement and 
returns a series of parameters, the total size of the parameters returned in 
bytes should be 1032 bytes. However when I trace the procedure call I see the 
following in the trace file:
        
        PARSING IN CURSOR #32 len=112 dep=0 uid=60 oct=47 lid=60 tim=2677334787 
hv=2159500523 ad='2db0cc60'
        BEGIN ORA_INTERACTION(:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, 
:12, :13, :14, :15, :16, :17); END;
        END OF STMT
        PARSE #32:c=0,e=24,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=2677334785
        EXEC #18:c=0,e=20,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,tim=2677334986
        FETCH #18:c=0,e=35,p=0,cr=4,cu=0,mis=0,r=1,dep=1,og=1,tim=2677335035
        WAIT #32: nam='SQL*Net message to client' ela= 1 driver id=1952673792 
#bytes=1 p3=0 obj#=68676 tim=2677335092
        WAIT #32: nam='SQL*Net more data to client' ela= 22 driver 
id=1952673792 #bytes=2134 p3=0 obj#=68676 tim=2677335132
        <snip> a further 15 SQL*Net more data to client 

EXEC #32:c=0,e=1233159,p=0,cr=4,cu=0,mis=0,r=1,dep=0,og=1,tim=2678568000
        WAIT #32: nam='SQL*Net message from client' ela= 3385 driver 
id=1952673792 #bytes=1 p3=0 obj#=68676 tim=2678571444
        XCTEND rlbk=0, rd_only=1
        WAIT #0: nam='SQL*Net message to client' ela= 0 driver id=1952673792 
#bytes=1 p3=0 obj#=68676 tim=2678571535
        WAIT #0: nam='SQL*Net message from client' ela= 555 driver 
id=1952673792 #bytes=1 p3=0 obj#=68676 tim=2678572105
        XCTEND rlbk=0, rd_only=1
        WAIT #0: nam='SQL*Net message to client' ela= 0 driver id=1952673792 
#bytes=1 p3=0 obj#=68676 tim=2678572143
        WAIT #0: nam='SQL*Net message from client' ela= 2682513 driver 
id=1952673792 #bytes=1 p3=0 obj#=68676 tim=2681254673
        *** 2009-09-04 11:32:44.610
        
        My question is why so many calls to " SQL*Net more data to client" if I 
am only passing what I think is 1032 bytes.
        
        Thanks
        
        Fred
        
        --
        http://www.freelists.org/webpage/oracle-l
        
        

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

Other related posts: