The explain plan for the statement:

Solution A:

1) Get the SQL_ID of he statement you are interested in (from AWR report or V$SQL or OEM etc).

2) Run the following script:

SQL> start $ORACLE_HOME/rdbms/admin/awrsqrpt.sql

3) Specify the begin/end snapshots and the sql_id.

4) A report will be produced containing the explain plan for the statement (if available)

Solution B:

  

This SQL only displays first child even if documentation says :

SQL> select * from TABLE(dbms_xplan.display_cursor('&SQL_ID'));

Below SQL displays execution plan for all child cursors for '&SQL_ID' with DEFAULT output format parameter.

select t.* from v$sql s

, table(dbms_xplan.display_cursor(s.sql_id,s.child_number)) t

where s.sql_id = '&SQL_ID' ;

To gather execution statistics, set statistics_level = ALL at session level or add hint /*+ gather_plan_statistics */ into SQL.

   see detail in $ORACLE_HOME/rdbms/admin/dbmsxpln.sql

    -> function display_cursor - format

select t.*

from v$sql s

   , table(dbms_xplan.display_cursor(s.sql_id,s.child_number,'ALLSTATS LAST')) t where s.sql_id = '&SQL_ID' ;

Solution C:

Since the program would have many SQL's we need to trace the entire programe run. TKPROF find the top SQL's and then run explain plan on them.
When Running the Concurrent Program

Click Debug Options (B)
Select the SQL trace checkbox - SQL Trace with Binds and Waits
and submit the request & possily let us know before the request completes.

tkprof SUPT_ora_696_IN.trc SUPT_ora_696_IN.trc.out explain=apps/apps print=10 sort='(prsela,exeela,fchela)' sys=no

Solution D:

Else you can run below sql when request is running and provide the output (if we know the conc program id/name )

SELECT a.request_id, d.sid, d.serial# ,d.osuser,d.process , c.SPID
FROM apps.fnd_concurrent_requests a,
apps.fnd_concurrent_processes b,
v$process c,
v$session d
WHERE a.controlling_manager = b.concurrent_process_id
AND c.pid = b.oracle_process_id
AND b.session_id=d.audsid
AND a.request_id = &Request_ID
AND a.phase_code = 'R';

+++++++++++++++++++++++++

How to Display All Loaded Execution Plan for a specific sql_id [ID 465398.1]

ID 795204.1 , ID 344196.1