Oracle trace & tkprof

NOTE1>: There are 3 methods to manage the SQL trace facility:
      a> oradebug -- execute it using sysdba by 'sqlplus / as sysdba'
      b> alter session -- you only need alter session privilege.
      c> execute DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION
NOTE2>:Make sure the environment ORALCE_SID is set correctly.
   
Step 1> Get the Unix-PID using sysdba after one interface is run.
    select a.machine, a.username, b.spid, a.program, a.logon_time, a.serial#
    from   v$session a, v$process b
    where b.addr = a.paddr
    and a.machine = '<Main Name>'
    order by logon_time;

Step 2> Enabling the SQL Trace Facility
     execute below SQL with sysdba
       sqlplus / as sysdba
       oradebug setospid ${unix_pid}
       oradebug unlimit
       oradebug event 10046 trace name context forever, level ${trace_level}
     OR enable the SQL trace for current SQL session
       sqlplus> ALTER SESSION SET SQL_TRACE = TRUE;
     OR enable the SQL trace for other user session
       sqlplus> execute DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(<UNIX-PID>, serial#, true); --serial# is from V$SESSION
     OR You can enable the SQL Trace facility for an instance by setting the value of the SQL_TRACE initialization parameter to TRUE in the initialization file $ORACLE_HOME/admin/pfile/init<sid>.ora.     
          SQL_TRACE = TRUE
      After the instance has been restarted with the updated initialization parameter file, SQL Trace is enabled for the instance and statistics are collected for all sessions. If the SQL Trace facility has been enabled for the instance, you can disable it for the instance by setting the value of the SQL_TRACE parameter to FALSE.

Step 3> Wait for the interface is finished.

Step 4> To disable the SQL Trace facility for the session, enter:
     execute below SQL
       oradebug setospid ${unix_pid}
       oradebug event 10046 trace name context off
     OR disable the SQL trace for current SQL session
       sqlplus> ALTER SESSION SET SQL_TRACE = FALSE;
     OR disable the SQL trace for other user session
       sqlplus> execute DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(<UNIX-PID>, serial#, false); --serial# is from V$SESSION

Step 5> Get the dump folder where trace file is created.
      select u_dump.value dump_foler
        from v$parameter u_dump
       cross join v$parameter db_name
       where u_dump.name = 'user_dump_dest'
         and db_name.name = 'db_name';

Step 6> Get the trace file
The trace file for <Unix-PID> is located under:
      <Dump folder>/<DB NAME>_ora_<UNIX-PID>.trc
      E.g. /opt/app/oracle/admin/sdgmain/udump/sdgmain_ora_348.trc
It is located in the DB server.

Step 7> Formatting Trace Files with TKPROF
Usage: tkprof tracefile outputfile [explain= username/password ] [table= <DB>.<table name>]
              [print= ] [insert= ] [sys= ] [sort= ]

Example: tkprof may_ora_1069212.trc report.txt explain=monica/arbor123 table=MAY.claire_temp sort=fchela


====================================================================================
Easier way to run tkprof for a current SQL session

Step 1. Enabling the SQL Trace Facility
     sqlplus> ALTER SESSION SET SQL_TRACE = TRUE;

Step 2. Do something you want to create trace for them, such as 'select <...> from cdr_data where ....'

Step 3. disable the SQL Trace facility
      sqlplus> ALTER SESSION SET SQL_TRACE = FALSE;

Step 4. Following SQL returns the trace file fullname

select u_dump.value || '/' || db_name.value || '_ora_' || v$process.spid ||
       nvl2(v$process.traceid, '_' || v$process.traceid, null) || '.trc' "Trace File"
from v$parameter u_dump
cross join v$parameter db_name
cross join v$process
join v$session on v$process.addr = v$session.paddr
where u_dump.name = 'user_dump_dest'
   and db_name.name = 'db_name'
   and v$session.audsid = sys_context('userenv', 'sessionid');

Trace File

--------------------------------------------------------------
/oradump/cust1/udump/cust1_ora_2216704.trc

It is located in the DB server.

Step 5> Formatting Trace Files with TKPROF (Same as upper)

============================================================

An easier way to trace an application

The application should execute the following to set the client_identifier:

dbms_session.set_identifier (‘my_client_identifier’);

SQL> execute dbms_session.set_identifier ('REPORT_USAGE');

PL/SQL procedure successfully completed

The actual enabling / disabling of traces can then be done using the following calls (via sqlplus, system or sysdba):

·         To enable: exec dbms_monitor.client_id_trace_enable ('REPORT_XYZ', FALSE, FALSE) ;
·         To disable: exec dbms_monitor.client_id_trace_disable ('REPORT_XYZ') ;