Oracle Session and Process Tracing

Oracle gave various commands to trace session, processes, data files, buffers etc.  These trace files will give information about what Oracle is doing or storing information and how the work is done inside database.  These features are very helpful tools for DBAs to troubleshoot  specially performance issues.

SQL trace current session using events

Level 0 – No tracing

ALTER SESSION SET events ‘10046 trace name context forever, level 0’;

Level 1 – No bind and  no waits

ALTER SESSION SET events ‘10046 trace name context forever, level 1’;

Level 4 – Trace with Binds

ALTER SESSION SET events ‘10046 trace name context forever, level 4’;

Level 8 –  Trace with Waits

ALTER SESSION SET events ‘10046 trace name context forever, level 8’;

Level 8 – Trace with Binds and Waits

ALTER SESSION SET events ‘10046 trace name context forever, level 12’;

Turn off trace

ALTER SESSION SET events ‘10046 trace name context off’;

Remember to turn on the timed statistics when you trace with waits.   Make sure timed_statistics=TRUE or set using

SQL> ALTER SYSTEM SET TIMED_STATISTICS=TRUE;

SQL trace database level (Level 1)

Set the parameter and re-start the database.

SQL_TRACE = TRUE

Being a dynamic parameter you can use ALTER SYSTEM to enable and disable the database level tracing

SQL> ALTER SYSTEM SET SQL_TRACE=TRUE;

SQL> ALTER SYSTEM SET SQL_TRACE=FALSE;

Database level tracing using events

Set the EVENT in the parameter file to start database level tracing.

event=”10046 trace name context forever,level 1″

Where level can be 1 (Trace without waits and binds), 4 (Trace with Binds), 8 ( Trace with waits) and 12 (trace with binds and waits).

Also you can use ALTER SYSTEM to set the trace event in database level.

alter system set events=’1405 trace name context forever, level 12′ ; 

 

Using dbms_monitor.client_id_trace_enable  – 10g onwards.

Some times you need to trace part of applicaiton based on the client identifier set int the application.   You can set the identified using EXECUTE DBMS_SESSION.SET_IDENTIFIER .

Step 1. set the identifier.

EXECUTE DBMS_SESSION.SET_IDENTIFIER (‘SALE_BATCH’);

Step 2: Enable the tracing for the client identifier by any of the following SQL. 

EXECUTE dbms_monitor.client_id_trace_enable (client_id=>’SALE_BATCH’,binds=>true);

EXECUTE dbms_monitor.client_id_trace_enable (client_id=>’SALE_BATCH’,waits=>true);

EXECUTE dbms_monitor.client_id_trace_enable (client_id=>’SALE_BATCH’);

You could see the CLIENT_ID tracing enabled in  DBA_ENABLED_TRACES

SQL>  select TRACE_TYPE,PRIMARY_ID,WAITS,BINDS from DBA_ENABLED_TRACES
 TRACE_TYPE            PRIMARY_ID      WAITS BINDS
——————— ————— —– —–
CLIENT_ID             SALE_BATCH      TRUE  FALSE

Now run the SQL statements where the CLIENT_ID is set.

 

Using  dbms_monitor.SESSION_TRACE_ENABLE

We can use dbms_monitor.SESSION_TRACE_ENABLE to trace the sessions.  First identify the SID and SERIAL# and enable tracing using dbms_monitor.SESSION_TRACE_ENABLE.

exec dbms_monitor.SESSION_TRACE_ENABLE(<session_id>,<serial_num>,<Waits TRUE or FALSE>,<Binds TRUE or FALSE>);

SQL> select sid,serial# from v$session where username=’SCOTT’;

       SID    SERIAL#
———- ———-
       128      60601
SQL> exec dbms_monitor.SESSION_TRACE_ENABLE(128,60601);

PL/SQL procedure successfully completed.
SQL> exec dbms_monitor.SESSION_TRACE_ENABLE(128,60601,TRUE);

PL/SQL procedure successfully completed.

SQL> exec dbms_monitor.SESSION_TRACE_ENABLE(128,60601,TRUE,TRUE);

PL/SQL procedure successfully completed.

Once the tracing is over, either the user can quit the session or DBA can explicitly disable the trace. 

SQL> exec dbms_monitor.SESSION_TRACE_DISABLE(128,60601);

Advertisements
  1. sunil
    February 19, 2016 at 4:25 am

    i am tracing the entire database using ALTER SYSTEM command…, so how to set the trace identifier, so that i can find the trace file easily… !?

  2. February 19, 2016 at 9:46 am

    Even though, technically this is possible – doesn’t make much sense as all the trace file name come with the identifier… Useful, only when you eanble in session level tracing, so that you can identify the trace files easily, associated with the session.

    SQL> show parameter tracefile_identifier

    NAME TYPE VALUE
    ———————————— ———– ——————————
    tracefile_identifier string

    SQL> alter system set tracefile_identifier=’THOMAS’ scope=SPFILE;

    System altered.

    SQL> alter system set events=’10046 trace name context forever,level 12′;

    System altered.

    -rw-r—– 1 oracle oinstall 1251 Feb 19 04:43 xxx_mmnl_16838_THOMAS.trm
    -rw-r—– 1 oracle oinstall 14285 Feb 19 04:43 xxx_mmnl_16838_THOMAS.trc
    -rw-r—– 1 oracle oinstall 717 Feb 19 04:43 xxx_lgwr_16828_THOMAS.trm
    -rw-r—– 1 oracle oinstall 8042 Feb 19 04:43 xxx_lgwr_16828_THOMAS.trc

    Thanks
    Thomas

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: