Oracle EBS - Setup: 如何對并發請求做Trace

profile ： Concurrent: Allow Debugging

set to  ： Yes

1. 設定profile，允許并發程式調試

2. 設定并發程式，勾上Enable Trace

3. 送出請求時，設定Debug Option，在彈出來的調試選項頁面裡勾選選擇SQL Trace，然後送出請求

4.擷取Trace檔案

SELECT 'Request id: ' || request_id
      ,'Trace id: ' || oracle_process_id
      ,'Trace Flag: ' || req.enable_trace
      , 'Trace Name: 
' || dest.value || '/' || lower(dbnm.value) || '_ora_' ||
        oracle_process_id || '.trc'
      ,'Prog. Name: ' || prog.user_concurrent_program_name
      ,'File Name: ' || execname.execution_file_name ||
       execname.subroutine_name
      ,'Status : ' || decode(phase_code, 'R', 'Running') || '-' ||
       decode(status_code, 'R', 'Normal')
      ,'SID Serial: ' || ses.sid || ',' || ses.serial#
      ,'Module : ' || ses.module
  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_application_id = execname.application_id
   AND prog.executable_id = execname.executable_id;
           

5.使用tkprof指令轉換trc檔案為txt檔案

tkprof test_ora_14218.trc test_ora_14218.txt

$tkprof raw_trace_file.trc output_file explain=apps/apps sort=(exeela,fchela) sys=no

Where:

raw_trace_file.trc: Name of trace file
output_file: tkprof out file
explain: This option provides the explain plan for the sql 
statements
sort: This provides the sort criteria in which all sql 
statements will be sorted. This will bring the bad sql at 
the top of the outputfile.
sys=no: Disables sql statements issued by user SYS