http://blog.itpub.net/16832682/viewspace-1249765/
Recently, due to the issue of PL/SQL performance on the project, it is necessary to tune the concurrency programs that have been developed. An important step in tuning is to get the trace file for the concurrent program to know which SQL has a problem.
Here's a summary of how to get the trace file for the Concurrency program:
1. Start the tracking function on the Concurrent Program registration page;
2. Commit concurrency, to get the trace ID after the concurrent run, you can use the following code to complete:
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 | | ' _anonymous.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_id
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
After SQL executes, get the value of the Trace Name column and go to the corresponding directory:
3. Use the Tkprof file to parse the TRC file:
Tkprof ERPPRD_ORA_10481_2510.TRC/TMP/2510.TRC
4. Log in to the corresponding directory using FTP tools such as FileZilla, and drop the generated TKP file to local
5. Open the downloaded TKP file with a document editing tool such as Notepad or notepad++:
The last thing, that is a sentence of the view of SQL, see the specific section of SQL consumption is larger.
EBS Gets the concurrency program trace File