Performance Tuning in Oracle Apps R12

Hi friends, we are going to discuss about the performance tuning in Oracle Apps R12. We will discuss, how we can do the performance tuning for long running and time taken concurrent programs in oracle apps r12. In oracle apps, we often encounters some situations when our concurrent programs running so long when we run for the longer periods and it takes lots of hours time to complete in oracle apps. In such situation's , we need to do the performance tuning  for the concurrent program to reduce the running time. In Oracle apps , performance tuning is the methodology which we need to follow for better results. In performance tuning , our end results is to fast the processing of the concurrent programs. In this post,  we will share the complete steps to do the perform tuning in oracle apps. Please find below the complete detail about Performance Tuning in Oracle Apps R12.

Performance Tuning Tool in Oracle Apps R12

1. TK PROF

TK PROF:-

TK PROF , is tool provided by oracle database to do the performance tuning of sql queries. TK PROF helps to generates the complete trace file about sql queries. This trace file have all the informations about sql which helps to do the performance tuning in oracle apps r12. TK PROF, provide sql trace file in the more readable format in oracle apps r12 to do the performance tuning about concurrent program.

4 Important Steps of Performance Tuning in Oracle Apps R12

1. Enable the trace Check box in the concurrent Program for which we want to do the performance tuning.

2. Set Profile "Concurrent: Allow Debugging" to yes.

3.Enable the SQL trace under Debug Options button in Submit Concurrent Request Window.

4. Find the Generated the SQL Trace in Oracle server.

Step by Step to perform the Performance Tuning in Oracle Apps R12

Step 1:- Go to the Concurrent Program , Registrations to enable the check box. Go to the Concurrent Program which is taking long Time and Enable the Trace for this Concurrent Program.

Navigation :-  System Administrator ==>Concurrent > Program > Define

Query the Concurrent Program which is taking long time.

Select the Enable Trace Check Box and Turn On Tracing

Step 2:- 

 

Now we need to go to the System Profiles. We need to enable the Debugging option in Oracle application using the profile as below.

Navigation : -System Administrator ==>Profiles > System

Search the  Profile option "Concurrent: Allow Debugging" and set to yes.

Step 3:- 

Now we need to run the Concurrent program. To run the concurrent program for which we want to do the performance tuning , we need to go to the submit request window.

Navigation : View: Requests : Submit Request.

When the program will be complete , then system will generate one TK prof file for this concurrent program sql query which will help us further to do the performance tuning in Oracle Apps r12.

Step 4:- 

Here below , is the sql query which helps to find the TKPROF file generated in oracle server for performance tuning.

SELECT prog.user_concurrent_program_name ,

execname.execution_file_name 'File Name',

request_id,oracle_process_id 'Trace id',

req.enable_trace,

dest.VALUE

 || '/'

 || LOWER (dbnm.VALUE)

 || '_ora_'

|| oracle_process_id

 || '.trc' 'Trace 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.concurrent_program_id = prog.concurrent_program_id

AND req.program_application_id = prog.application_id

AND prog.application_id = execname.application_id

AND proc.addr = ses.paddr(+)

AND dest.NAME = 'user_dump_dest'

AND dbnm.NAME = 'db_name'

AND prog.executable_id = execname.executable_id

AND req.oracle_process_id = proc.spid(+)

Step 5:-  Here in this step we are converting the concurrent program trace file in to the TK PROF Trace File.

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 output file.

sys=no:Disables sql statements issued by user SYS

Performance Tuning in Oracle Apps R12