Trace the object's/ query's execution Path

For sql / procedure / function trace (more effective way to tuning the execution path)

1. for seeing the trace file path , execute the following query

SELECT s.sid, s.serial#, pa.value '\' LOWER(SYS_CONTEXT('userenv','instance_name')) '_ora_' p.spid '.trc' AS trace_fileFROM v$session s, v$process p, v$parameter paWHERE pa.name = 'user_dump_dest'AND s.paddr = p.addrAND s.audsid = SYS_CONTEXT('USERENV', 'SESSIONID');

2. for trace file identifier(optional)

alter session set tracefile_identifier = 'LONG_RUNNING_QUERY';

3. for anable the session lebel trace file generate

ALTER SESSION SET EVENTS '10046 trace name context forever, level 8';

4. execute your tuning able objects or query

5.execute the following command for better readable format

$ tkprof trc_file_name Output_file_name.txt

example:-
$ tkprof F:\ORACLE\product\10.2.0 \admin\DBA\udump\dba_ora_3920.trc c:\transte.txt

6. Turn off the Trace

ALTER SESSION SET EVENTS '10046 trace name context off';

----------end-------------------------------------