Newbie question - Oracle Trace Results

[tony_a...@hotmail.com]

Hi,
First time post so be gentle :)

When looking at a trace file, does the adsence of an Exec, as in the
following

PARSING IN CURSOR #1 len=3523 dep=0 uid=32 oct=3 lid=32
tim=30556916889618 hv=3499698552 ad='a6a67068'
{SQL Select Statement - Not included due to length of 3000+ chars}
END OF STMT
PARSE
#1:c=100000,e=148774,p=7,cr=425,cu=0,mis=1,r=0,dep=0,og=4,tim=30556916889610

indicate that the SQL statement was not executed?? The PARSE..
statement was the last line in the Trace file.
If so, is there anyway to find out why?

I have a Windows application that uses SQLExecDirect to execute SQL
statement on a Oracle 8i dbase.
It seems to be that when the SQL statement is of a certain size, the
application just aborts.

The issue is that we have identical Application, Oracle Client, Oracle
server and drivers on different Customer sites and on some sites the
application works and others it does not.

Regards

[bdbafh]

On Apr 9, 10:57 am, tony_auds...@hotmail.com wrote:
> Hi,
> First time post so be gentle :)
>

> When looking at a trace file, does the absence of an Exec, as in the

I would expect to see an EXEC and a FETCH line, as well as numerous
SQL*Net messages.

Is there any chance that the environment could be brought up to date
from 8i R3 to 10g R2 with the current patchsets applied?

(good luck opening a support request on 8i)

If you're using ODBC, be careful with the 10.2.0.3 patchset, or the
Oracle 10g R2 Client for MS Vista that has the 10.2.0.3 patchset built
in. There is a patch available for the issue.

-bdbafh

[tony_a...@hotmail.com]

bdbafh,

I would have expected to see the EXEC and FETCH lines as well. At the
point the application reaches the abort stage, it has already
processed a number of SQL statement successfully and also, if i move
the SQL statements to be executed around in the application, it will
run fine until it reaches a statement that is, by comparison, large.

I am presuming that due to the lack of EXEC and or FETCH lines, that
the SQL has not been executed on the server.
If this is the case, is there anything that would cause this to
happen?

Unfortunately, the application we are running will not run with Oracle
9 client or above. Already been down that route!!
Just for information, Oracle 8 client, Oracle 8.1.7.4 server, Windows
2000 SP4 and MDAC 2.7

The application connects through a ODBC DSN, using the Microsoft ODBC
driver (using the Oracle driver makes matters worse) and i've tried
the Trace option in Windows DataSources, but this gives me even less
information.

Looking at the Oracle trace file, it is making me wonder whether there
is something specific on the server that is causing the issue!!

This is making my hair grey, and my Kids are already buying 'Just for
Men' in bulk!!

Regards

[hpuxrac]

On Apr 9, 12:53 pm, tony_auds...@hotmail.com wrote:

snip

> I would have expected to see the EXEC and FETCH lines as well. At the
> point the application reaches the abort stage, it has already
> processed a number of SQL statement successfully and also, if i move
> the SQL statements to be executed around in the application, it will
> run fine until it reaches a statement that is, by comparison, large.
>
> I am presuming that due to the lack of EXEC and or FETCH lines, that
> the SQL has not been executed on the server.
> If this is the case, is there anything that would cause this to
> happen?

There's some low level tracing in the sqlnet communcations area that
you can look at enabling.

>
> Unfortunately, the application we are running will not run with Oracle
> 9 client or above. Already been down that route!!
> Just for information, Oracle 8 client, Oracle 8.1.7.4 server, Windows
> 2000 SP4 and MDAC 2.7
>
> The application connects through a ODBC DSN, using the Microsoft ODBC
> driver (using the Oracle driver makes matters worse) and i've tried
> the Trace option in Windows DataSources, but this gives me even less
> information.
>
> Looking at the Oracle trace file, it is making me wonder whether there
> is something specific on the server that is causing the issue!!
>
> This is making my hair grey, and my Kids are already buying 'Just for
> Men' in bulk!!
>
> Regards

A couple of questions ... are you seeing errors in the alert log
perhaps on the server when this is occurring? Is there a more
experienced dba in your environment that can be of assistance perhaps?

FYI I tend to pass almost all trace files thru orasrp ( a resource
profiler ) which when you execute it with a " --sort=crsid " will sort
in the order that statements actually are in a trace file ... may not
be terribly relevant to what you are doing now though.

I would expect to see a PARSE in cursor followed up by an EXEC and
perhaps an ERROR in the situation that you are describing but well
it's been a long time since I looked at a raw 8x trace file.

[mathewbutler]

Appears that the long SQL causes a failure in the app after parse. To
confirm this you could switch on debug in the application (if
supported). Otherwise you may need to confirm the messages on the
network. Investgate increasing the logging level of the listener and
the same on the client side.