Get the Execution Plan from/using Trace files generated by DBMS_MONITOR

Tracing can be used to debug performance problems. Trace-enabling procedures have been implemented as part of the DBMS_MONITOR package. These procedures enable tracing globally for a database.

You can use the DATABASE_TRACE_ENABLE procedure to enable session level SQL tracing instance-wide. The procedure has the following parameters:

    • WAITS: Specifies whether wait information is to be traced
    • BINDS: Specifies whether bind information is to be traced
    • INSTANCE_NAME: Specifies the instance for which tracing is to be enabled. Omitting INSTANCE_NAME means that the session-level tracing is enabled for the whole database.

Use the DATABASE_TRACE_DISABLE procedure to disable SQL tracing for the whole database or a specific instance.

Similarly, you can use the SESSION_TRACE_ENABLE procedure to enable tracing for a given database session identifier on the local instance. The SID and SERIAL# information can be found from V$SESSION.

All trace files will be created in USER_DUMP_DEST (i.e. $ADR_HOME/trace) location.

Interpret the trace result using tkprof utility. This utility will generate output file which will be human readable, and which will have detailed information about execution plan.

Translate trace file: 

tkprof <trace-file> <output-file> explain=user/password@service waits=yes sys=no


Example :

• For all sessions in the database:
      EXEC dbms_monitor.DATABASE_TRACE_ENABLE(TRUE,TRUE);
      EXEC dbms_monitor.DATABASE_TRACE_DISABLE();
• For a particular session:
      EXEC dbms_monitor.SESSION_TRACE_ENABLE(session_id=>20, serial_num=>10, waits=>TRUE, binds=>FALSE);
      EXEC dbms_monitor.SESSION_TRACE_DISABLE(session_id=>20, serial_num=>10);