Trace your session

Trace your session:

Enabling trace:
Enable the SQL Trace facility for the session by using one of the following:

1)  EXEC DBMS_SESSION.SESSION_TRACE_ENABLE(waits => TRUE, binds => FALSE);

2)  ALTER SESSION SET SQL_TRACE = TRUE;

3) DBMS_SESSION.SET_SQL_TRACE 

Disabling trace:
EXEC DBMS_SESSION.SESSION_TRACE_DISABLE();

Easily identifying your trace files:
alter session set tracefile_identifier='mysession';

Session/Database Level Tracing

To enable tracing for a specific session ID and serial number, determine the values for the session to trace:

SELECT SID, SERIAL#, USERNAME FROM V$SESSION;

       SID    SERIAL# USERNAME

---------- ---------- ------------------------------

        120       23   ERP

To trace particular session:

EXEC dbms_monitor.SESSION_TRACE_ENABLE(session_id=> 120, serial_num=>23, waits=>TRUE, binds=>FALSE);

EXEC dbms_monitor.SESSION_TRACE_DISABLE(session_id =>120, serial_num=>23);

To trace all sessions in the database:

EXEC dbms_monitor.DATABASE_TRACE_ENABLE(TRUE,TRUE);

EXEC dbms_monitor.DATABASE_TRACE_DISABLE();

--AsifKhan

Service Level Tracing

The SERV_MOD_ACT_TRACE_ENABLE procedure enables SQL tracing for a given combination of service name, module, and action globally for a database.

To Trace all sessions that log in under the ERP service are traced. A trace file is created for each session that uses the service, regardless of the module and action.

exec DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE('ERP');

To Trace service along with particular module, and action:

exec DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE('ERP', 'PAYMENTS_MODULE', 'SALES_ACTION');

Trace a particular client identifier:
exec DBMS_MONITOR.CLIENT_ID_TRACE_ENABLE (client_id=>'C1', waits => TRUE, binds => FALSE);

--AsifKhan

Identify Trace File

Different methods to identify your trace file:

     1) From v$diag_info
     2) By Joining v$session, v$process, and v$parameter
     3) Set TRACEFILE_IDENTIFIER parameter

1) Identify the current trace file.
SQL> SELECT value FROM v$diag_info WHERE name = 'Default Trace File';
VALUE
-----------------------------------------------------------------------------
d:\app\diag\rdbms\obieedb\obieedb\trace\obieedb_ora_7728.trc


2)  You can get trace file using this query:

SELECT s.sid, s.serial#, pa.value || '/' || LOWER(SYS_CONTEXT('userenv','instance_name')) ||   '_ora_' || p.spid || '.trc' AS trace_file
FROM   v$session s,
           v$process p,
           v$parameter pa
WHERE  pa.name = 'user_dump_dest'
AND    s.paddr = p.addr

AND    s.audsid = SYS_CONTEXT('USERENV', 'SESSIONID');


3) You can set the TRACEFILE_IDENTIFIER parameter at session level to allow you to include some recognizable text into the trace file name:


ALTER SESSION SET TRACEFILE_IDENTIFIER = "POST_FIX_TEXT";

trcsess utility

The trcsess utility is for combining and filtering trace files to extract the statistics for a single session, service, or module across multiple trace files. 

The trcsess utility consolidates trace output from selected trace files based on several
criteria: session ID, client ID, service name, action name, and module name. After trcsess
merges the trace information into a single output file, the output file can be processed by

tkprof.

trcsess  syntax:

trcsess  [output=output_file_name]
         [session=session_id]
         [clientid=client_id]
         [service=service_name]
         [action=action_name]
         [module=module_name]
         [trace_files]
where

output=<output file name> output destination default being standard output.
session=<session Id> session to be traced.
Session id is a combination of session Index & session serial number 
clientid=<clientid> clientid to be traced.
service=<service name> service to be traced.
action=<action name> action to be traced.
module=<module name> module to be traced.
<trace_file_names> Space separated list of trace files with wild card '*' supported.

How to Trace Procedure ??

  I wanted to share procedure tracing method. Here are the steps to trace...

1. Give tracefile identifier within the procedure, so that everything will be dumped into that trace file.
2. Enable 10046 event to that session.
3. Write your code  
4. Disable 10046 event.
5. Navigate to "user_dump_dest" location
6. Search for "postfix_to_trace" trace files
7. Use tkprof to convert into human readable format.

1 to 4 )

CREATE OR REPLACE PROCEDURE SAMPLE_PROC AS
V_STRING VARCHAR2(2000);
BEGIN
V_STRING:='ALTER SESSION SET TRACEFILE_IDENTIFIER = "postfix_to_trace" ';
EXECUTE IMMEDIATE V_STRING;

V_STRING:='ALTER SESSION SET EVENTS ''10046 trace name context forever, level 8'' ';
EXECUTE IMMEDIATE V_STRING;

...

V_STRING:='ALTER SESSION SET EVENTS ''10046 trace name context off'' ';
EXECUTE IMMEDIATE V_STRING;

EXCEPTION
...
END  SAMPLE_PROC;

5) 
SYS>show parameter user_dump_dest
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
user_dump_dest                       string      /xyz/oracle/diag/rdbms/mydb/mydb/trace       

6)
bash-3.2$ cd /xyz/oracle/diag/rdbms/mydb/mydb/trace

bash-3.2$ ls *postfix_to_trace*

mydb_ora_14490_postfix_to_trace.trc

bash-3.2$

7)

bash-3.2$ tkprof mydb_ora_14490_postfix_to_trace.trc  mydb_ora_14490_postfix_to_trace.txt

TKPROF: Release 11.2.0.2.0 - Development on Sun Apr 13 01:17:40 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

bash-3.2$