This content has been marked as final. Show 5 replies
If you have access to the source code, it should be possible to execute a SQL statement like this to enable a 10046 extended trace at level 12 (waits and binds):
ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 12';If you do not have access to the source code, you can remotely enable an extended trace for another session with:
* Logon trigger that executes the above SQL statement for a specific user/application.
* System wide 10046 extended trace (change SESSION in the above SQL statement to SYSTEM).
* Session specific 10046 trace remotely enabled using:
EXEC SYS.DBMS_SYSTEM.SET_EV(SID,SERIAL#,10046,12,'');The SID and SERIAL# may be retrieved from V$SESSION.
The SYS.DBMS_SYSTEM.SET_EV method is unsupported, but it works fine on older versions of Oracle where the other methods do not exist.
Oracle will create the trace files in the server's udump directory.
Before executing any of the above (especially the system wide method), determine how to disable a 10046 trace, depending on how it was started:
ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT OFF';Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.
One method I like so that I don't have to modify code but get the trace remote as a DBA is to use the DBMS_SYSTEM supplied package provided by Oracle.
First you have to get the SID and the SERIAL of the session from v$session.
Then do "execute dbms_system.set_sql_trace_in_session(&1,&2,TRUE);"
do what ever process in the app you want to trace....
and then "execute dbms_system.set_sql_trace_in_session(&1,&2,FALSE);"
This may not help if the application connects and disconnects..............
Thank you for your help!
I'm having a little performance problems and I must find about what would be the exact best database design tackling for these problems.
We have a three tier architecture...
Database connections are pooled in the application server and whenever I need connection the application server gives it to me. Our pool is established at the morning, so all the sessions logon_time is at morning.
So i get the SID, from connection pools connection and trace that SID?
Is it possible to set the above tracing statements to Connection Pools initialization parameters?
How do I handle tracing of the XA-transactions to multible databases?? Is it possible to trace them??? I have understood that the XA-driver controls the overall connection and there are multiple Oracle database connections involved and multiple sessions???
How the XA-performance should be validated? Should I check every single database design how they fit to part- of overall Query???
Please help me!
Does anyone have experience about these kind of problems??
Does anyone know how to set TRACEFILE_IDENTIFIER in another session.
SYS.DBMS_SYSTEM - has only 2 procedures for setting init.ora parameters in session level. One is SET_BOOL_PARAM_IN_SESSION and other is SET_INT_PARAM_IN_SESSION.
Tracefile_identifier is character or string parameter, how do I set that?
In my own session I can set it like this. ALTER SESSION SET TRACEFILE_IDENTIFIER, but how to set it in another session??