To GENERATE a TKPROF from an existing trace (.trc) file

1.       Get trace file name from database – use below examples for pulling trace file names for a concurrent request or form session:

a.       Concurrent Request

SELECT  request_id "Request ID"

      ,oracle_Process_id "Process ID"

      ,dest.VALUE || '/' || LOWER (dbnm.VALUE) || '_ora_' || oracle_process_id || '.trc' "Trace File Directory Path"

      ,LOWER (dbnm.VALUE) || '_ora_' || oracle_process_id || '.trc' "Trace File Name"

      ,prog.user_concurrent_program_name "Program Name"

      ,execname.execution_file_name || execname.subroutine_name "Executable"

        FROM fnd_concurrent_requests req

      ,v$session ses

      ,v$process proc

      ,v$parameter dest

      ,v$parameter dbnm

      ,fnd_concurrent_programs_vl prog

      ,fnd_executables execname

 WHERE req.request_id = &request

   AND req.oracle_process_id = proc.spid(+)

   AND proc.addr = ses.paddr(+)

   AND dest.name = 'user_dump_dest'

   AND dbnm.name = 'db_name'

   AND req.concurrent_program_id = prog.concurrent_program_id

   AND req.program_application_id = prog.application_id

   AND prog.application_id = execname.application_id

   AND prog.executable_id = execname.executable_id;