Skip to main content

How to Trace a Concurrent Request And Generate TKPROF File - Oracle Apps Reports Trace

How to Trace a Concurrent Request And Generate TKPROF File

1. 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 = '173753463' 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 DWORTI1_ora_156973_QXMOHAME.trc output_file explain=apps/PWD sort=(exeela,fchela) sys=no
$tkprof DWORTI2_ora_99506.trc 99506 explain=apps/PWD sort=’(exeela,fchela)’ sys=no
tkprof DWORTI2_ora_99506.trc 99506 explain=user/PWD@DB table=sys.plan_table

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 

Comments

Post a Comment

Popular posts from this blog

Oracle Forms -- Important Syntaxes

1. To get the enabled property of the item APP_ITEM_PROPERTY.GET_PROPERTY('CONTROL.FILTER_HOLD_RECORDS',ENABLED) 2. Disable Attachment for this Block in WHEN-NEW-BLOCK-INSTANCE  APP_SPECIAL.ENABLE('ATTACHMENTS', PROPERTY_OFF); 3. Default Where / Default Order By Set_block_property('XWII_MATL_DISP_HOLDS_V', Default_where, Lc_query); Set_block_property('XWII_MATL_DISP_HOLDS_V', order_by, Lc_query); 4. Visual Attribute    a. Define Visual Attribute for eg 'XWII' where you can set Colour  b. SET_ITEM_INSTANCE_PROPERTY('block.item_name',CURRENT_RECORD,VISUAL_ATTRIBUTE ,'XWII'); 4. Visual Attribute for Record APP_RECORD.HIGHLIGHT('XWII'); Where 'XWII' is the visual attribute where you can set Colour. 5. Initiliaze SPECIAL Menu app_special.instantiate ('SPECIAL1', GET_GROUP_CHAR_CELL ('SPECIAL_MENU.SPECIAL_MENU_ITEM', 1), '', TRUE, ''); 6. execute_trigger('SPECIAL25...
1 comment
Read more
Xdoxslt XML Publisher RTF Important Syntax’s     1.        To convert CASE of a tag to UPPER case      xdoxslt:convert_case(.//MATERIALTYPE,'UPPER')     2.        To convert CASE of a tag to LOWER case      xdoxslt:convert_case(.//MATERIALTYPE,'UPPER')     3.        Syntax for CONTAIN      contains(xdoxslt:convert_case(.//MATERIALTYPE,'UPPER'),’IC CREDIT MEMO’)     4.        Syntax for NOT CONTAIN      not(contains(xdoxslt:convert_case(.//REFERENCE_ID,'UPPER'),'WSC'))     5.        CHOOSE-WHEN-OTHERWISE Syntax     <?choose:?><?when: contains(xdoxslt:convert_case(.//MATERIALTYPE,'UPPER'),’IC CREDIT       MEMO’)?><?’Interco Credit Memo’?><?end when?><?otherwis...
Post a Comment
Read more

Customize New Oracle Apps Form (11i and 12i)

Create and Register of a Custom Form in Oracle Applications(11i and R12) Steps to Create a new form in oracle Apps R12   1. Check the Form builder version is compatible with your application server or not. 2. Download all the necessary .FMb and .PLL files from the server     FMB- TEMPLATE.fmb and APPSTAND.fmb files Server location FMB -$AU_TOP/forms/US Server location PLL - $AU_TOP/resource (Try to download all PLL files) First try to download below PLL files see if it works .If it does work then you don't need to download all the PLL files APPCORE.pll                   APPCORE2.pll              APPDAYPK.pll APPFLDR.pll                   CUSTOM.pll    ...
Post a Comment
Read more