-- ============================================================================================
-- Script Name : sql_plan_flip_diagnose.sql
-- Purpose : Detect SQLs with Plan Flips, Diagnose Plan Performance, Manage Baselines
-- Requirements: Must be run in SQL*Plus. Needs access to AWR views, DBA_SQL_PLAN_BASELINES
-- ============================================================================================
SET ECHO OFF
SET LINESIZE 300
SET PAGESIZE 100
SET VERIFY OFF
SET FEEDBACK OFF
SET TIMING OFF
SET TRIMSPOOL ON
SET LONG 1000000
SET LONGCHUNKSIZE 1000000
-- === Generate spool file with timestamp ===
COLUMN spool_file_name NEW_VALUE spool_file
SELECT 'sql_plan_flip_' || TO_CHAR(SYSDATE, 'YYYYMMDD_HH24MISS') || '.lst' AS spool_file_name FROM dual;
SPOOL &spool_file
PROMPT ============================================================================
PROMPT [Step 1] Detect SQLs with Plan Flips in Last 7 Days
PROMPT ============================================================================
COLUMN SQL_ID FORMAT A15
COLUMN PLAN_COUNT FORMAT 999
COLUMN FIRST_SEEN FORMAT A20
COLUMN LAST_SEEN FORMAT A20
SELECT
sql_id,
COUNT(DISTINCT plan_hash_value) AS plan_count,
MIN(sn.begin_interval_time) AS first_seen,
MAX(sn.end_interval_time) AS last_seen
FROM
dba_hist_sqlstat s
JOIN dba_hist_snapshot sn ON s.snap_id = sn.snap_id AND s.dbid = sn.dbid
WHERE
sn.begin_interval_time >= SYSDATE - 7
GROUP BY
sql_id
HAVING
COUNT(DISTINCT plan_hash_value) > 1
ORDER BY
plan_count DESC;
PROMPT
PROMPT ============================================================================
PROMPT [Step 2] Enter SQL_ID to Diagnose Plan Performance
PROMPT ============================================================================
ACCEPT v_sql_id CHAR PROMPT 'Enter SQL_ID to analyze: '
PROMPT ============================================================================
PROMPT [Step 3] Plan Performance Details for &v_sql_id (Last 7 Days)
PROMPT ============================================================================
COLUMN PLAN_HASH_VALUE FORMAT 9999999999
COLUMN ELAPSED_SEC FORMAT 999,999.99
COLUMN CPU_SEC FORMAT 999,999.99
COLUMN DISK_READS FORMAT 999,999,999
COLUMN BUFFER_GETS FORMAT 999,999,999
COLUMN EXECUTIONS FORMAT 999,999
COLUMN FIRST_SEEN FORMAT A20
COLUMN LAST_SEEN FORMAT A20
SELECT
plan_hash_value,
ROUND(SUM(elapsed_time_delta)/1e6, 2) AS elapsed_sec,
ROUND(SUM(cpu_time_delta)/1e6, 2) AS cpu_sec,
SUM(buffer_gets_delta) AS buffer_gets,
SUM(disk_reads_delta) AS disk_reads,
SUM(executions_delta) AS executions,
MIN(sn.begin_interval_time) AS first_seen,
MAX(sn.end_interval_time) AS last_seen
FROM
dba_hist_sqlstat s
JOIN dba_hist_snapshot sn ON s.snap_id = sn.snap_id AND s.dbid = sn.dbid
WHERE
s.sql_id = '&v_sql_id'
AND sn.begin_interval_time >= SYSDATE - 7
GROUP BY
plan_hash_value
ORDER BY
elapsed_sec ASC;
PROMPT ============================================================================
PROMPT [Step 4] Checking Existing Baselines for &v_sql_id
PROMPT ============================================================================
COLUMN SQL_HANDLE FORMAT A30
COLUMN PLAN_NAME FORMAT A30
COLUMN ENABLED FORMAT A8
COLUMN ACCEPTED FORMAT A8
COLUMN FIXED FORMAT A6
SELECT
sql_handle, plan_name, enabled, accepted, fixed
FROM
dba_sql_plan_baselines
WHERE
sql_text LIKE (
SELECT sql_text FROM dba_hist_sqltext
WHERE sql_id = '&v_sql_id' AND ROWNUM = 1
);
PROMPT ============================================================================
PROMPT [Step 5] Evaluate Best Plan (Lowest Elapsed Time)
PROMPT ============================================================================
-- === Identify best plan
COLUMN BEST_PLAN FORMAT 9999999999
SELECT plan_hash_value AS best_plan
FROM (
SELECT
plan_hash_value,
ROUND(SUM(elapsed_time_delta)/1e6, 2) AS elapsed_sec
FROM
dba_hist_sqlstat s
JOIN dba_hist_snapshot sn ON s.snap_id = sn.snap_id AND s.dbid = sn.dbid
WHERE
s.sql_id = '&v_sql_id'
AND sn.begin_interval_time >= SYSDATE - 7
GROUP BY plan_hash_value
ORDER BY elapsed_sec ASC
)
WHERE ROWNUM = 1;
-- === Threshold input
ACCEPT bad_threshold NUMBER DEFAULT 30 PROMPT 'Set max acceptable elapsed time (sec) for baseline (default 30): '
-- === Check if best plan is acceptable
VARIABLE plan_ok VARCHAR2(3)
DECLARE
min_elapsed NUMBER;
BEGIN
SELECT MIN(elapsed_sec)
INTO min_elapsed
FROM (
SELECT ROUND(SUM(elapsed_time_delta)/1e6, 2) AS elapsed_sec
FROM dba_hist_sqlstat s
JOIN dba_hist_snapshot sn ON s.snap_id = sn.snap_id AND s.dbid = sn.dbid
WHERE s.sql_id = '&v_sql_id'
AND sn.begin_interval_time >= SYSDATE - 7
GROUP BY plan_hash_value
);
:plan_ok := CASE WHEN min_elapsed <= &bad_threshold THEN 'YES' ELSE 'NO' END;
END;
/
PRINT plan_ok
-- === Prompt only if performance is acceptable
ACCEPT do_baseline CHAR PROMPT 'Create SQL Plan Baseline for best plan? (Y/N): '
BEGIN
IF UPPER(TRIM('&do_baseline')) = 'Y' AND :plan_ok = 'YES' THEN
DBMS_OUTPUT.PUT_LINE('Creating SQL Plan Baseline...');
DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
sql_id => '&v_sql_id',
fixed => 'YES',
enabled => 'YES'
);
ELSE
DBMS_OUTPUT.PUT_LINE('Baseline creation skipped.');
END IF;
END;
/
PROMPT ============================================================================
PROMPT [Done] Script completed. Output saved to &spool_file
PROMPT ============================================================================
SPOOL OFF
