ENABLE TRACE FOR CONCURRENT PROGRAMS IN ORACLE APPS R12

Tracing the Concurrent program is very good option to Debug any issue while running the concurrent program.

Goal :
To enable the Trace for Concurrent Programs
To Debug the issues in Concurrent Programs or its dependent objects.

Solution :
1. Goto Concurrent Program Definition

Application Developer –> Concurrent –> Program –> check Enable Trace

Query your concurrent program and check the Enable Trace check box at the bottom of the screen.

2. Set the Profile Concurrent: Allow Debugging to YES.

3. Navigate to the Responsibility from where you are running the program.

4. Before Submitting the Concurrent Program in Submit Request screen there will be an option like Debug Options click on that button.

5. In the Debug Options select the SQL Trace and enable Trace with binds.

6. Submit the Concurrent Program and note down the Request id.

7.Trace file name will always be post fixed with oracle_process_id.To get the oracle_process_id use the below SQL

Select oracle_process_id from fnd_concurrent_requests where request_id= &Request_id;

8.And the Trace file path can be derived using the below SQL
Select * from v$parameter where name=’user_dump_dest’;

9. Get the trace file to your local machine.Understanding the raw trace is very complex so use TKPROF utility to make the Trace file readable.

10. Open the Command Prompt and run the below command
TKPROF < Trace File_Name.trc> <Output_File_Name.out> SORT=fchela

11.A new outfile will be generated with the name given in the above command.Analyse the Output file to know the answers for your problem.

Sql queries to find the log files by Request id

select req.oracle_process_id tracename, req.request_id, req.requested_by,usr.user_name, prog.user_concurrent_program_name, req.request_date, req.phase_code, req.status_code, req.logfile_name , req.outfile_name, dest.value as user_dump_dest from apps. fnd_concurrent_requests req, gv$parameter dest, apps.fnd_concurrent_programs_vl prog, apps.fnd_user usr

where  req. concurrent_program_id = prog.concurrent_program_id

and req.requested_by = usr.user_id

–and request_id like ‘6013239’

and dest.name= ‘user_dump_dest’

and request_id like & Request_id;

Sql query find the log files by username

select req.oracle_process_id tracename, req.request_id, req.requested_by,usr.user_name, prog.user_concurrent_program_name, req.request_date,  req.phase_code, req.status_code, req.logfile_name , req.outfile_name, dest.value as user_dump_dest 

from apps. fnd_concurrent_requests req, gv$parameter dest, apps.fnd_concurrent_programs_vl prog, apps.fnd_user usr

where  req. concurrent_program_id = prog.concurrent_program_id

and req.requested_by = usr.user_id

–and request_id like ‘6013239’

and dest.name= ‘user_dump_dest’

and usr.user_name like ‘Ramesh%G%’

order by request_date desc

Select oracle_process_id from fnd_concurrent_requests where request_id = &Request_id;

 

select * from apps. fnd_concurrent_requests req,gv$parameter dest , apps .fnd_concurrent_programs_vl prog where  req. concurrent_program_id = prog.concurrent_program_id and request_id like ‘601300%’

and dest.name= ‘user_dump_dest’

Submit Concurrent Program From Back-end in Oracle Apps

You can submit concurrent Program from backend in oracle apps using below script. There are mandatory parameters you need to pass for the standard procedure and Concurrent program parameters can be passed with arguments.

For examples:- argument1 will be first parameter for you concurrent program, the similar way argument2 will be second parameter of your concurrent program and So On.


Script :-

DECLARE
l_conc_request_id   fnd_concurrent_requests.request_id;      -- This is OUT parameter
l_program                VARCHAR2(30);                                  -- This concurrent program short name
l_application            VARCHAR2(10);                -- Application name like AR, OM
l_description            VARCHAR2(40);     -- Description of your program
l_patameter1            VARCHAR2(20);   -- first parameter
l_parameter2            VARCHAR2(20);   --second parameter

BEGIN

l_conc_request_id := fnd_request.submit_request
            (
                application => l_application,
                program     => l_program,
                description =>l_description,
                start_time  => null,
                sub_request => false,                                   -- False as its not a subrequest
                argument1   => l_patameter1,
                argument2   => l_patameter2
            );

COMMIT;

EXCEPTION

WHEN OTHERS THEN
dbms_output.put_line ('Exception' || SQLERRM);

END;

How to create Concurrent Program in Oracle Applications

Below are simple steps to create

1. Executable
2. Concurrent program and then
3. Attach Concurrent Program with Request Group.

Login as Sysadmin or Application Developer

Create Executable :- 

Navigate to Concurrent > Executable.Define your executable as shown below.

Create Concurrent Program

• Navigate to concurrent program (CP) screen.
• Eneter Name for CP.Short Name should be same as short name of executable.(It's Best Practice).
• Check the enable check box.Select executable name from LOV.
• Select the output format for CP.

• Define Parameter. For each Parameter
  1.Select the Value Set .
  2.Default Type.
  3.Default value for Set
  4.Default Value.

• After Saving the Concurrent Program, Attach it with requied Request Set.
• Choose the group where you want to attach CP
• Add Concurent program

Query to find runtime of a concurrent program

The following query finds total run-time (in minutes) for a concurrent program. Thus, with a little

modification to this query, you can track which concurrent programs take (very) long time

 to complete

and may need performance tuning.

Input the concurrent program name (tl.user_concurrent_program_name, see below)

according to your search criteria and run the query.

SELECT /*+ rule */

       rq.parent_request_id                   "Parent Req. ID",

       rq.request_id                          "Req. ID",

       tl.user_concurrent_program_name        "Program Name",

       rq.actual_start_date                   "Start Date",

       rq.actual_completion_date              "Completion Date",

       ROUND((rq.actual_completion_date -

           rq.actual_start_date) * 1440, 2)   "Runtime (in Minutes)"

  FROM applsys.fnd_concurrent_programs_tl  tl,

       applsys.fnd_concurrent_requests     rq

 WHERE tl.application_id        = rq.program_application_id

   AND tl.concurrent_program_id = rq.concurrent_program_id

   AND tl.LANGUAGE              = USERENV('LANG')

   AND rq.actual_start_date IS NOT NULL

   AND rq.actual_completion_date IS NOT NULL

   AND tl.user_concurrent_program_name = :P_user_Concurrent_prog_name

 ORDER BY rq.request_id DESC;

Current Running SQLs for a Concurrent Program

Current Running SQL for a Concurrent Program

1. gV$session.

SELECT C.sql_text
,C.module
FROM APPS.fnd_concurrent_requests A
,gV$SESSION B
,gV$SQLAREA C
WHERE A.oracle_session_id = B.audsid
AND B.sql_hash_value = C.hash_value
AND A.request_id = :p_request_id;

Pass the Request ID for the above Query.

SELECT SQLT.hash_value
,SQLT.sql_text
,VSES.username
,VSES.module
,VSES.command
FROM gv$sqltext SQLT
,gv$session VSES
,APPS.fnd_concurrent_requests FCONC
WHERE SQLT.hash_value = VSES.sql_hash_value
AND FCONC.oracle_session_id = VSES.audsid
AND FCONC.request_id = :p_request_id
ORDER BY SQLT.piece

2. V$session :-

SELECT C.sql_text
,C.module
FROM APPS.fnd_concurrent_requests A
,V$SESSION B
,V$SQLAREA C
WHERE A.oracle_session_id = B.audsid
AND B.sql_hash_value = C.hash_value
AND A.request_id = :p_request_id;

Pass the Request ID for the above Query.

SELECT SQLT.hash_value
,SQLT.sql_text
,VSES.username
,VSES.module
,VSES.command
FROM v$sqltext SQLT
,v$session VSES
,APPS.fnd_concurrent_requests FCONC
WHERE SQLT.hash_value = VSES.sql_hash_value
AND FCONC.oracle_session_id = VSES.audsid
AND FCONC.request_id = :p_request_id
ORDER BY SQLT.piece