How to Trace a Concurrent Request & Get the location for Trace FIle .. Generate TKPROF File form Unix

-

Step 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

Step 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
    req.request_id
    ,req.logfile_node_name node
    ,req.oracle_Process_id
    ,req.enable_trace
    ,dest.VALUE||'/'||LOWER(dbnm.VALUE)||'_ora_'||oracle_process_id||'.trc' trace_filename
    ,prog.user_concurrent_program_name
    ,execname.execution_file_name
    ,execname.subroutine_name
    ,phase_code
    ,status_code
    ,ses.SID
    ,ses.serial#
    ,ses.module
    ,ses.machine
    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 1=1
    AND req.request_id = &request --Request ID

/opt/oracle/enable/admin/ENBL1/udump  --> trace file location

Step 3:  TKPROF Trace File

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

$tkprof enbl2_ora_23852_837166_CR1814778.trc /home/vk837166/837166_CR1814778 explain=apps/nobel4pce 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

Comments

Post a Comment

Popular posts from this blog

Workflow Agent Listener Service WF_DEFERRED + Business Event not processing

-
Workflow Agent Listener Service WF_DEFERRED Will Not Start, Mailer May Be Down To implement the solution, please execute the following steps: 1.  Use this query to identify any remaining items in wf_deferred queue: SQL> select corrid, decode(state, 0, '0 = Ready', 1, '1 = Delayed', 2, '2 = Retained', 3, '3 = Exception', to_char(state)) State, count(*) COUNT from wf_deferred group by corrid, state; 2. Items with the APPS corrid will not dequeue from agent listener via service container.  This runs the deferred listener from the command lin e . Make sure that the listener and workflow service components - including the mailer - are down before you run this . This is not intended to be a substitute for the normal operation of this service container from the OAM console. Use this only during this cleanup operation. One will have to run this script manually: begin wf_log_pkg.wf_debug_flag := TRUE; wf_event...
Read more

Output Post Processor - EBS 12.2.4 Troubleshoot

-
Output Post Processor TroubleShooting Issue 1 Output Post Processor is Down with Actual Process is 0 And Target Process is 1 1. Shutdown Concurrent Managers 2. To ensure concurrent manager down; check there is no FNDLIBR process running. ps -ef | grep FNDLIBR 3. Run adadmin to relink FNDSVC executable. a. Invoke adadmin from command prompt b. Choose option 2 (2. Maintain Applications Files menu) c. Choose option 1 (1. Relink Applications programs ) d. Then type FND When prompted; ( Enter list of products to link (all for all products) [all] : FND ) e. Ensure adrelink is exiting with status 0 4. Start Concurrent Managers 5. Check the Output Post Processor Issue 2 Concurrent Processing  R12 Output Post Processor Service Not Coming Up Reason : If Service Manager for the node is not running.  Possible cause might be service manager definition is missing under Concurrent ->Manager ->Define form. If the Service Manager is not pr...
Read more

How to Diagnose Workflow Notification Mailer Issue

-
Outbound Notification Emails : 1. Check if the notification is present in the recipient/user's Worklist/Notifications page.If it does not exist     then it means that notification itself is not created and need to check the corresponding workflow     status.You may also query the notification from the wf_notifications table SQL> select recipient_role,notification_id,status,mail_status from wf_notifications where recipient_role like '&user_name';    The e-mail notification is sent only if all of following are true.                --Notification status is OPEN or CANCELED                --Notification mail_status is MAIL or INVALID 2. Check Recipient role has a valid e-mail address and notification preference MAIL% SELECT email_address, nvl(WF_PREF.get_pref(name, 'MAILT...
Read more