Where to Find User Oracle Session Trace Files / Process Running the tkprof command to process trace file

Resolving The Problem

DUMP destination parameters are maintained in v$parameter table ( Oracle System Table / View )

To find various v$paramters, including destination for TRACE output files run the following command in sqlplus:

show parameter user_dump;

Or Here is a sample query to find various v$paramters, including destination for TRACE output files:

select substr(name,1,30) PARAM_NAME, substr(VALUE,1,40) VALUE_30
from v$parameter
where name like '%&PARAM_like%'
/

PARAM_NAME VALUE
------------------------------ ----------------------------------------
shadow_core_dump FULL
background_core_dump full
background_dump_dest ?/admin/XXXX/bdump (? refers to the Oracle Home) /u01/apps/oracle/admin/XXXX/bdump
user_dump_dest ?/admin/XXXX/udump (/u01/apps/oracle/admin/XXXX/udump)
max_dump_file_size 20240
core_dump_dest /u01/apps/oracle/admin/XXXX/cdump

For more details on how to start trace, see Solution 10702.

To use tkprof, following is a sample command line:

tkprof ora_ORCL_SESSION_PPID.trc $HOME/DEST_FILE.out explain=SQLUSER/PASSWD sort=OPTION(s)

Refer to tkprof docs for more specific details, or enter "tkprof" at the command prompt. You can ignore the sort option if you are not troubleshooting a performace issue.

For Speed / Performance / Index Utilization, one can normally use "sort=fchela ==> elapsed time fetching".

Example : tkprof ora_8923.trc $HOME/support/ora_8923.out explain=USER/PASSWD sort=fchela