Re: Help understand raw trace data.

From: Fred Tilly <>
Date: Sun, 6 Sep 2009 07:38:14 +0000 (GMT)
Message-ID: <>


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



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 <> Hi,

        The procedure accepts  the following parameters:
           ALOGID NUMBER;
           AINTREF VARCHAR2(50);
           ACLIENTID VARCHAR2(20);
           ASTARTTIME varchar2(20);
           AINITUSERID VARCHAR2(100);
           AREFERENCE VARCHAR2(50);
           ADATA VARCHAR2(500);
           AVERIFIED CHAR(1);
           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.



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 <> 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.

Received on Sun Sep 06 2009 - 02:38:14 CDT

Original text of this message