Tracing is invaluable for DBAs who want to solve performance problems. With trace data, you can follow all database calls and track the execution of a given set of SQL statements of a session. To gather trace at your own session, you can issue the following commands:
|
1 2 3 |
ALTER SESSION SET timed_statistics = TRUE; ALTER SESSION SET statistics_level=ALL; ALTER SESSION SET EVENTS '10046 trace name context forever,level 12'; |
If you exit from your session or issue the following command, tracing will stop:
|
1 |
ALTER SESSION SET EVENTS '10046 trace name context off'; |
Trace files will be located in user_dump_dest:
|
1 |
SHOW PARAMETER user_dump_dest |
If you use parallel queries then some of your trace files will be located in background_dump_dest:
|
1 |
SHOW PARAMETER background_dump_dest |
Name of the trace files will be generated by Oracle. You can use tracefile identifier to be able to find your trace files easier:
|
1 |
ALTER SESSION SET tracefile_identifier='MYTRACE'; |
So your trace files will be look like this:
eystest_ora_4333_MYTRACE.trc
eystest_ora_25000.trc
eystest_ora_4352.trc
You are not limited to trace your own sessions, you can trace any sessions if you have permission to execute DBMS_MONITOR package. You enable tracing with issuing the following command:
|
1 2 3 |
DBMS_MONITOR.SESSION_TRACE_ENABLE( session_id => x, serial_num => y, waits=>(TRUE|FALSE), binds=>(TRUE|FALSE) ); |
The default of waits is TRUE and the default of binds is FALSE. You can disable tracing by issuing the following command:
|
1 |
DBMS_MONITOR.SESSION_TRACE_DISABLE( session_id => x, serial_num => y ); |
You can also use oradebug to trace a session. First, you need to identify the OS process ID of the session:
|
1 2 3 4 5 6 7 |
SELECT p.PID, p.SPID, s.SID FROM v$process p, v$session s WHERE s.paddr = p.addr AND s.SID = &session_id; PID SPID SID ---------- ------------ ---------- 115 29716 1202 |
After the OS process id has been determined then the trace can be started:
|
1 2 3 4 5 6 |
CONNECT / AS SYSDBA ORADEBUG SETOSPID 29716 ORADEBUG UNLIMIT ORADEBUG EVENT 10046 TRACE NAME CONTEXT FOREVER,LEVEL 12 Oracle pid: 115, Unix process pid: 29716, image: oracle@xyzserver |
We can use SETOSPID or SETORAPID. Please note that 29716 is a sample value. If we’ll use SETOSPID, then we should type value of p.SPID. If we’ll use SETORAPID, then we need to type the value of p.PID (i.e. 115).
You can issue the following command to disable oradebug tracing:
|
1 |
ORADEBUG EVENT 10046 TRACE NAME CONTEXT |
gram
Gokhan Atil
Pingback: Watch out for optimizer_adaptive_features as It may have a huge negative impact | bdt's oracle blog
Preguntón Cojonero Cabróncete
Gokhan Atil
Janet C