Trace a Concurrent Request And Generate TKPROF File

hei hei[@more@]

Trace a Concurrent Request And Generate TKPROF File
October 5, 2008 · 1 Comment
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 sys=no explain=apps/ sort=’(prsela,exeela,fchela)’ print=10

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/92289/viewspace-1012942/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/92289/viewspace-1012942/