topic Re: sql trace in Operating System - HP-UX

sql trace

william_39 — Thu, 31 Jul 2003 10:39:02 GMT

Hi guys,

I have oracle server on HP box, and one of my dba has turned on sql trace on one database, now when he is runing a application (its inhouse application ) he could not see the sql trace file, what could be the reason ?

NOTE : application is written in C, using OCI

Thanks in advance,
william

Re: sql trace

Steven E. Protter — Thu, 31 Jul 2003 11:05:44 GMT

Few possibilities:

1) The app is not reading/writing any oracle data.
2) Trace file location defined in control files/init.ora is not where you expect it to be.
3) Trace for the db is turned on in init.ora. Did you bouce the database after making the change?

Note that what the appliction is written in is irrelavent. If you are doing transactions and trace is properly turned on, you will get trace files.

sEP

Re: sql trace

william_39 — Thu, 31 Jul 2003 11:49:03 GMT

Hi SEP,
If the application is using only OCI functions to interact with Oracle database, will generate sql trace for it ?

Thanks in adv,
william

Re: sql trace

Stan_17 — Thu, 31 Jul 2003 16:58:22 GMT

William,

Quick test to check the settings of sql_trace and user_dump_dest parameters.

logon to the database via sqlplus. the user should have access to v$parameter. At the prompt, type

sql> show parameter sql_trace

This should return 'TRUE' if sql_trace turned on otherwise 'FALSE'

also, check the location of the user_dump_dest. thats the location where oracle dumps sql trace files. At the prompt, type

sql> show parameter user_dump_dest

let us know what you find, then we should be able to help you more.

Stan

Re: sql trace

william_39 — Fri, 01 Aug 2003 05:02:11 GMT

Hi,
Sql_trace is TRUE and path is also set correct, infact if i run any sql from sql prompt, it is able to generate sql trace, but when i run my application it doesnt generates any trace, even though it is updating database.
So what my concern is, if application is written with the help of OCI function to interact with database, will oracle generate sql trace for OCI functions also ?

thanks
william

Re: sql trace

Yogeeraj_1 — Fri, 01 Aug 2003 05:10:50 GMT

hi william,

If you environment has been properly configured, as mentioned above, you can determine the name of your trace file by running the following query:

select c.value || '/' || instance || '_ora_' ||
ltrim(to_char(a.spid,'fm99999')) || '.trc'
from v$process a, v$session b, v$parameter c, v$thread c
where a.addr = b.paddr
and b.audsid = userenv('sessionid')
and c.name = 'user_dump_dest'

hope this helps!

regards
Yogeeraj

Re: sql trace

Graham Cameron_1 — Fri, 01 Aug 2003 06:21:30 GMT

William
I have never used OCI, only the Oracle precompilers (ie Pro*C), so I don't have direct experience.
However, I found the following on metalink, which may mean something to you or to your application developer....

<metalink>

NO TRACE FILE GENERATED WHEN ALTER SESSION SET SQL_TRACE=TRUE ISSUED IN OCI

You are doing an oparse on the following statement: "alter session set sql_trace=true".
However, when you run the program, you do not get an error, nor do you get the trace file.
Why is this?

Solution Description:
=====================
The reason you get this error is that you do not have an OEXEC after the OPARSE call.
In the Programmer's Guide to the Oracle Call Interfaces, the description for the OPARSE
function says that you do not need an OXEC when executing DDL statements.
ALTER SESSION is NOT a DDL statement. It is a SESSION CONTROL COMMAND.
The SESSION CONTROL COMMANDs do not implicitly COMMIT the current transaction.
.

Hope this helps
-- Graham