Currently Running SQL and SQL plan for a Concurrent Program

Many a times you keep looking for SQL ID and SQL plan for a concurrent Program which is stuck and taking long long time to complete. Here are the queries to debug those scenarios and get the exact SQL which is taking time to complete.

1.  Use below SQL to get SQLID Info :-

SELECT fcr.request_id "Req ID",
         parent_request_id "Parent",
         DECODE (fcpt.user_concurrent_program_name,
                 'Check Periodic Alert', 'Alert: ' || fcr.description,
                 fcpt.user_concurrent_program_name)
            "Name",
         r.responsibility_key "Responsibility",
         gvs.module "Module",
         gvs.sql_id "SQL ID",
         gvs.event "Wait event",
         fcr.priority "Priority",
         fcr.requested_start_date "Requested Start",
         fcr.actual_start_date "Actual Start",
         fcr.actual_completion_date "Actual End",
         DECODE (fcr.status_code,
                 'C', 'Complete',
                 'E', 'Error',
                 'R', 'Running',
                 'I', 'Pending',
                 'G', 'Warning',
                 'Q', 'Scheduled',
                 'D', 'Cancelled',
                 'W', 'Waiting',
                 'X', 'Terminated',
                 status_code)
            "Status",
         fcr.phase_code,
         fcr.status_code,
         DECODE (fcr.hold_flag, 'Y', 'Hold', 'Normal') "Hold?",
         ROUND (
              (  DECODE (fcr.actual_completion_date,
                         NULL, SYSDATE,
                         fcr.actual_completion_date)
               - actual_start_date)
            * 24
            * 60,
            2)
            "Time(m)",
         ROUND (
              (  DECODE (fcr.actual_completion_date,
                         NULL, SYSDATE,
                         fcr.actual_completion_date)
               - actual_start_date)
            * 24
            * 60
            * 60,
            2)
            "Time(s)",
         argument_text "Arguments",
         ROUND (
              (  DECODE (fcr.actual_start_date,
                         NULL, SYSDATE,
                         fcr.actual_start_date)
               - requested_start_date)
            * 24
            * 60
            * 60,
            1)
            "Start Delay (s)",
         r.responsibility_key "Responsibility",
         fu.user_name "User",
         gvs.inst_id "Inst",
         gvs.sid "SID",
         gvs.serial# "Serial#",
         gvs.sql_id "SQL ID",
         gvs.event "Wait event",
         gvs.status "S.Status",
         gvs.service_name "Service",
         gvs.sql_child_number "SQL Child",
         gvs.last_call_et "Call time",
         connstr1 "Conn Str",
         gvs.module "Module",
         argument_text "Arguments",
         fcr.priority_request_id "Priority Req ID",
         fcr.logfile_node_name,
         fcr.oracle_session_id,
         fcr.oracle_process_id spid,
         fcr.concurrent_program_id,
         fcr.status_code,
         fcp.os_process_id applmgr_pid,
         fu.user_id,
         fcr.concurrent_program_id "Prog ID",
         queue_id,
         fcqt.user_concurrent_queue_name queue_name,
         fcr.logfile_name,
         fcr.enable_trace
    FROM apps.fnd_concurrent_requests fcr,
         apps.fnd_concurrent_programs_tl fcpt,
         apps.fnd_user fu,
         apps.fnd_responsibility r,
         gv$session gvs,
         apps.fnd_concurrent_queues fcq,
         apps.fnd_concurrent_queues_tl fcqt,
         apps.fnd_concurrent_processes fcp
   WHERE     fcpt.application_id = fcr.program_application_id
         AND fcpt.concurrent_program_id = fcr.concurrent_program_id
         AND fcr.oracle_session_id = gvs.audsid(+)
         AND r.responsibility_id = fcr.responsibility_id
         AND fcr.requested_by = fu.user_id
         AND fcr.controlling_manager = fcp.concurrent_process_id(+)
         AND fcp.concurrent_queue_id = fcq.concurrent_queue_id(+)
         AND fcq.concurrent_queue_id = fcqt.concurrent_queue_id(+)
         AND fcr.request_id = :p_request_id
ORDER BY 10 DESC NULLS FIRST, 5 DESC;



2. Get the sql details from the above query for the running concurrent program and check the sql and its plan using the below SQL.

SELECT * FROM gv$sql where sql_id = :p_sql_id;

For SQL monitor report use the below query :-

SELECT DBMS_SQLTUNE.report_sql_monitor (sql_id         => 'dsf4z2dc63cfp',
                                        TYPE           => 'TEXT',
                                        report_level   => 'ALL')
          "SQL Monitor Report - Text"
  FROM DUAL;

get the sql details and you will see the sql which is causing the performance issue and analyse the same.