step-by-step query approach to handle this SQL plan regression.

 

Step 1: Get SQL_ID and Plan Hash Values

SELECT sql_id,
       plan_hash_value,
       executions,
       elapsed_time/1000000 elapsed_sec,
       cpu_time/1000000 cpu_sec,
       buffer_gets,
       disk_reads,
       rows_processed,
       last_active_time
FROM   gv$sql
WHERE  sql_text LIKE '%MTL_SYSTEM_ITEMS%'
ORDER BY last_active_time DESC;

Step 2: Check Historical Performance from AWR

SELECT s.sql_id,
       s.plan_hash_value,
       SUM(s.executions_delta) executions,
       ROUND(SUM(s.elapsed_time_delta)/1000000,2) elapsed_sec,
       ROUND(SUM(s.cpu_time_delta)/1000000,2) cpu_sec,
       SUM(s.buffer_gets_delta) buffer_gets,
       SUM(s.disk_reads_delta) disk_reads
FROM   dba_hist_sqlstat s
WHERE  s.sql_id = '&sql_id'
GROUP BY s.sql_id, s.plan_hash_value
ORDER BY elapsed_sec DESC;

This shows which plan was good and which plan was bad.

Step 3: Get Execution Plan for Current Bad Plan

SELECT *
FROM TABLE(dbms_xplan.display_cursor(
    sql_id => '&sql_id',
    cursor_child_no => NULL,
    format => 'ALLSTATS LAST +PEEKED_BINDS +OUTLINE +ALIAS'
));

Step 4: Get Historical AWR Plan

SELECT *
FROM TABLE(dbms_xplan.display_awr(
    sql_id => '&sql_id',
    plan_hash_value => '&good_plan_hash_value',
    format => 'ALL +OUTLINE +ALIAS'
));

Compare good plan and bad plan.

Focus on:

TABLE ACCESS FULL
INDEX RANGE SCAN
NESTED LOOPS
HASH JOIN
Cardinality difference
Cost difference
Predicate information

Step 5: Check SQL Mapped to Concurrent Program

SELECT r.request_id,
       r.oracle_process_id,
       r.os_process_id,
       r.phase_code,
       r.status_code,
       p.concurrent_program_name,
       pt.user_concurrent_program_name,
       r.actual_start_date,
       r.actual_completion_date
FROM   apps.fnd_concurrent_requests r,
       apps.fnd_concurrent_programs p,
       apps.fnd_concurrent_programs_tl pt
WHERE  r.concurrent_program_id = p.concurrent_program_id
AND    p.concurrent_program_id = pt.concurrent_program_id
AND    pt.language = 'US'
AND    r.request_id = '&request_id';

Step 6: Map SID to Concurrent Request

SELECT r.request_id,
       s.inst_id,
       s.sid,
       s.serial#,
       s.sql_id,
       s.event,
       s.status,
       s.module,
       s.action,
       p.spid os_pid,
       r.actual_start_date,
       r.phase_code,
       r.status_code
FROM   gv$session s,
       gv$process p,
       apps.fnd_concurrent_requests r
WHERE  s.paddr = p.addr
AND    s.inst_id = p.inst_id
AND    p.spid = r.oracle_process_id
AND    r.request_id = '&request_id';

Step 7: Check if SQL is Currently Running

SELECT inst_id,
       sid,
       serial#,
       sql_id,
       sql_exec_start,
       event,
       wait_class,
       seconds_in_wait,
       blocking_session,
       module,
       action
FROM   gv$session
WHERE  sql_id = '&sql_id';

Step 8: Load Good Plan from AWR into SQL Plan Baseline

Run from SYS or user with required privilege:

DECLARE
  l_plans_loaded PLS_INTEGER;
BEGIN
  l_plans_loaded := DBMS_SPM.LOAD_PLANS_FROM_AWR(
      begin_snap   => &begin_snap_id,
      end_snap     => &end_snap_id,
      basic_filter => 'sql_id = ''&sql_id'' and plan_hash_value = &good_plan_hash_value',
      fixed        => 'YES',
      enabled      => 'YES'
  );

  DBMS_OUTPUT.PUT_LINE('Plans loaded: ' || l_plans_loaded);
END;
/

Step 9: Verify Baseline Created

SELECT sql_handle,
       plan_name,
       enabled,
       accepted,
       fixed,
       created,
       last_executed
FROM   dba_sql_plan_baselines
WHERE  sql_text LIKE '%MTL_SYSTEM_ITEMS%'
ORDER BY created DESC;

Step 10: Verify SQL Uses Baseline

After user reruns the concurrent program:

SELECT sql_id,
       plan_hash_value,
       sql_plan_baseline,
       executions,
       elapsed_time/1000000 elapsed_sec,
       last_active_time
FROM   gv$sql
WHERE  sql_id = '&sql_id';

Also check plan:

SELECT *
FROM TABLE(dbms_xplan.display_cursor(
    sql_id => '&sql_id',
    format => 'ALLSTATS LAST +NOTE'
));

At bottom, you should see:

SQL plan baseline used for this statement

Step 11: If Baseline Is Not Used

Check exact SQL text matching issue:

SELECT sql_id,
       force_matching_signature,
       exact_matching_signature,
       sql_text
FROM   gv$sql
WHERE  sql_id = '&sql_id';

Check baseline signature:

SELECT sql_handle,
       signature,
       sql_text
FROM   dba_sql_plan_baselines
WHERE  sql_text LIKE '%MTL_SYSTEM_ITEMS%';

Step 12: Production RCA Statement

The concurrent request took longer due to SQL execution plan regression.
The SQL_ID &sql_id used a poor plan hash &bad_plan_hash_value involving expensive table access/full scan operations.
Historical AWR review showed that plan hash &good_plan_hash_value was performing better.
To prevent recurrence, the good plan was loaded from AWR into SQL Plan Management as a fixed and enabled SQL Plan Baseline.
The concurrent program should be terminated by business user and resubmitted so that the SQL can hard parse and pick the stabilized execution plan.

Important production point:

Do not kill the session directly unless business approval is received.
Ask business user to cancel/terminate the concurrent request from application and resubmit after baseline implementation.