Tracing Oracle Sessions
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 |