How to tune long running concurrent request in oracle apps
Here we will be discuss about performance tuning of the long running concurrent requests in oracle apps. When our concurrent Program is taking so much time in oracle apps to be completed then we need to work on tuning part of this program. Oracle application has provided some good mechanism to analyze the performance of these concurrent requests. This analysis helps to provide the information's about concurrent program and needs to identify which part of this request needs more tuning. Here below I will share one of the very useful oracle method to tune long running concurrent request in oracle apps.
Steps to tune long running concurrent request in oracle apps
Step1:- Go to the Concurrent Program which is taking long Time and Enable the Trace for this Concurrent Program.
System Administrator ==>Concurrent > Program > Define
Query the Concurrent Program which is taking long time.
Select the Enable Trace Checkbox
Turn On Tracing
Step3:- Go to the Oracle Profiles to Allow Debugging
System Administrator ==>Profiles > System
Query Profile "Concurrent: Allow Debugging" and set to yes.
Step4:- Go to the responsibility to run the Concurrent Program in oracle apps.
In the Submit Request Screen click on Debug Options (B)
Select the Checkbox for SQL Trace
Step5:- Copy the Concurrent Request Id for this Concurrent Program and Find the Log Trace File for this Concurrent Request
SELECT request_id,oracle_process_id 'Trace id',
req.enable_trace,
dest.VALUE
|| '/'
|| LOWER (dbnm.VALUE)
|| '_ora_'
|| oracle_process_id
|| '.trc' 'Trace Name',
prog.user_concurrent_program_name ,
execname.execution_file_name 'File Name'
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 = :p_request_id
System Administrator ==>Concurrent > Program > Define
Query the Concurrent Program which is taking long time.
Select the Enable Trace Checkbox
Turn On Tracing
Step3:- Go to the Oracle Profiles to Allow Debugging
System Administrator ==>Profiles > System
Query Profile "Concurrent: Allow Debugging" and set to yes.
Step4:- Go to the responsibility to run the Concurrent Program in oracle apps.
In the Submit Request Screen click on Debug Options (B)
Select the Checkbox for SQL Trace
Step5:- Copy the Concurrent Request Id for this Concurrent Program and Find the Log Trace File for this Concurrent Request
SELECT request_id,oracle_process_id 'Trace id',
req.enable_trace,
dest.VALUE
|| '/'
|| LOWER (dbnm.VALUE)
|| '_ora_'
|| oracle_process_id
|| '.trc' 'Trace Name',
prog.user_concurrent_program_name ,
execname.execution_file_name 'File Name'
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 = :p_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;
Step 5:- Raw trace file to format the file using TKPROF.
$tkprof raw_trace_file.trc output_file explain=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: his 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
output_file: tkprof out file
explain: This option provides the explain plan for the sql statements
sort: his 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
0 comments:
Post a Comment