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$