Trace a Concurrent Request And Generate TKPROF File


Hello Friends, As in Oracle Application, there always need to tune programs , procedure and it is always difficult to make a perfect program, here i am trying to put a “How to” on tkprof and surely like to know if you find it useful.

How to Trace a Concurrent Request And Generate TKPROF File

 Enable Tracing For The Concurrent Manager  Program 

  • Responsibility: System Administrator
  • Navigate: Concurrent > Program > Define
  • Query Concurrent Program
  • Select the Enable Trace Checkbox 

Turn On Tracing

  • Responsibility: System Administrator
  • Navigate: Profiles > System
  • Query Profile Option Concurrent: Allow Debugging
  • Set profile to Yes

 Run Concurrent Program With Tracing Turned On

  • Logon to the Responsibility that runs the Concurrent Program 
  •  In the Submit Request Screen click on Debug Options (B)
  • Select the Checkbox for SQL Trace

 2. Find Trace File Name

  Run the following SQL to find out the Raw trace name and location for the concurrent program.  The SQL prompts the user for the request id

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_id=execname.executable_id; 

 

 

 

 

3. TKPROF Trace File

Once you have obtained the Raw trace file you need to format the file using TKPROF.

$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: 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 

Another example: To get (TKPROF) sorted by longest running queries first and limits the results to the “Top 10″ long running queries

 

 

$ tkprof <filename.trc> <output_filename> sys=no explain=apps/<password> sort='(prsela,exeela,fchela)’ print=10 

 

 

 

 

Thanks – Shivmohan Purohit 

 

About these ads

10 responses to “Trace a Concurrent Request And Generate TKPROF File

  1. Pingback: Top Referred Articles on KnowOracle | Shivmohan Purohit's Oracle Applications Blog

  2. Pingback: Top Articles in Dec – 2010 | Shivmohan Purohit's Oracle Applications Blog

  3. Pingback: KnowOracle – Top Documents – Most Read articles « Oracle Applications – Business & Technology

  4. I had to change the following character single quote:

    ’/’
    to
    ’/’

  5. Sir
    What u have given query is not running in10g.
    could u explain brifly

  6. It is Helpful.

    Regards,
    Harish

  7. hi babu
    i have checked the query and it is wrk fine..
    let me know the erro msg..
    u might need to change the single codes….and then run it ..
    it will wrk…

  8. The script to be run in the unix to get the top 10 long running queries is

    $ tkprof sys=no explain=apps/ sort sort='(prsela,exeela,fchela)’

  9. Hello,

    Your sql query not running in 10g. Please doubel check this..

    babu

  10. sir, It is fine but this script which you have given is to typical to under stand is there any another way to find the long running queries

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s