SET SERVEROUTPUT ON SIZE UNLIMITED;
DECLARE
-- Define a collection for SQL IDs
TYPE t_sql_id_array IS TABLE OF VARCHAR2(23);
v_sql_ids t_sql_id_array := t_sql_id_array(
'g2969b8287x4',
'281v89047b7n1',
'b507v024r3g6a',
'another_sql_id' -- Replace with your full list of up to 50 valid SQL IDs
);
v_sql_id VARCHAR2(23);
v_found NUMBER;
v_prev_plan_hash NUMBER;
v_plan_change VARCHAR2(30); -- Increased size for safety
-- Variables to track last plan change
v_last_change_time DATE;
v_last_old_plan_hash NUMBER;
v_last_new_plan_hash NUMBER;
v_last_old_elapsed NUMBER;
v_last_new_elapsed NUMBER;
v_plan_changed BOOLEAN;
BEGIN
-- Enable DBMS_OUTPUT with unlimited buffer
DBMS_OUTPUT.ENABLE(NULL);
DBMS_OUTPUT.PUT_LINE('DBMS_OUTPUT buffer set to unlimited.');
-- Loop through each SQL ID
FOR i IN 1..v_sql_ids.COUNT LOOP
v_sql_id := v_sql_ids(i);
BEGIN
-- Validate SQL ID exists in AWR
SELECT COUNT(*)
INTO v_found
FROM dba_hist_sqlstat
WHERE sql_id = v_sql_id
AND ROWNUM = 1;
IF v_found = 0 THEN
DBMS_OUTPUT.PUT_LINE('Warning: No AWR data found for SQL_ID ' || v_sql_id);
CONTINUE;
END IF;
-- Initialize plan change tracking
v_prev_plan_hash := NULL;
v_last_change_time := NULL;
v_last_old_plan_hash := NULL;
v_last_new_plan_hash := NULL;
v_last_old_elapsed := NULL;
v_last_new_elapsed := NULL;
v_plan_changed := FALSE;
-- Print header for detailed output
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE('----------------------------------------------------');
DBMS_OUTPUT.PUT_LINE('-- AWR History for SQL_ID: ' || v_sql_id);
DBMS_OUTPUT.PUT_LINE('----------------------------------------------------');
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT(RPAD('Snapshot Time', 22) || ' | ');
DBMS_OUTPUT.PUT(RPAD('Plan Hash', 12) || ' | ');
DBMS_OUTPUT.PUT(RPAD('Avg Elapsed (s)', 15) || ' | ');
DBMS_OUTPUT.PUT(RPAD('Executions', 10) || ' | ');
DBMS_OUTPUT.PUT_LINE('Plan Change');
DBMS_OUTPUT.PUT_LINE(RPAD('-', 80, '-'));
-- Query AWR data for the SQL ID
FOR rec IN (
SELECT
s.begin_interval_time AS begin_time,
st.plan_hash_value,
ROUND(st.elapsed_time_delta / DECODE(st.executions_delta, 0, 1, st.executions_delta) / 1000000, 3) AS avg_elapsed_sec,
st.executions_delta AS executions
FROM
dba_hist_sqlstat st
JOIN
dba_hist_snapshot s
ON
st.snap_id = s.snap_id
WHERE
st.sql_id = v_sql_id
AND
st.dbid = (SELECT dbid FROM v$database)
AND
s.begin_interval_time > SYSDATE - 7 -- Limit to last 7 days
ORDER BY
s.begin_interval_time
) LOOP
-- Determine if plan hash changed
IF v_prev_plan_hash IS NULL THEN
v_plan_change := 'Initial';
ELSIF v_prev_plan_hash != rec.plan_hash_value THEN
v_plan_change := 'Changed';
-- Update last plan change details
v_last_change_time := rec.begin_time;
v_last_old_plan_hash := v_prev_plan_hash;
v_last_new_plan_hash := rec.plan_hash_value;
v_last_old_elapsed := v_last_new_elapsed; -- Previous row's elapsed time
v_last_new_elapsed := rec.avg_elapsed_sec;
v_plan_changed := TRUE;
ELSE
v_plan_change := 'Same';
END IF;
-- Print row with plan change indicator (split to avoid buffer issues)
DBMS_OUTPUT.PUT(RPAD(TO_CHAR(rec.begin_time, 'YYYY-MM-DD HH24:MI:SS'), 22) || ' | ');
DBMS_OUTPUT.PUT(RPAD(rec.plan_hash_value, 12) || ' | ');
DBMS_OUTPUT.PUT(RPAD(TO_CHAR(rec.avg_elapsed_sec, '99990.000'), 15) || ' | ');
DBMS_OUTPUT.PUT(RPAD(rec.executions, 10) || ' | ');
DBMS_OUTPUT.PUT_LINE(v_plan_change);
-- Update previous plan hash and elapsed time
v_prev_plan_hash := rec.plan_hash_value;
v_last_new_elapsed := rec.avg_elapsed_sec; -- Store for next iteration
END LOOP;
-- Print summary of last plan change
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE('--- Last Plan Change Summary for SQL_ID: ' || v_sql_id || ' ---');
IF v_plan_changed THEN
DBMS_OUTPUT.PUT('Last Change Time: ' || TO_CHAR(v_last_change_time, 'YYYY-MM-DD HH24:MI:SS') || ' | ');
DBMS_OUTPUT.PUT('Old Plan Hash: ' || v_last_old_plan_hash || ' | ');
DBMS_OUTPUT.PUT('Old Avg Elapsed: ' || TO_CHAR(v_last_old_elapsed, '99990.000') || 's | ');
DBMS_OUTPUT.PUT('New Plan Hash: ' || v_last_new_plan_hash || ' | ');
DBMS_OUTPUT.PUT_LINE('New Avg Elapsed: ' || TO_CHAR(v_last_new_elapsed, '99990.000') || 's');
ELSE
DBMS_OUTPUT.PUT_LINE('No plan change detected in the last 7 days.');
END IF;
DBMS_OUTPUT.PUT_LINE(RPAD('-', 80, '-'));
IF SQL%NOTFOUND THEN
DBMS_OUTPUT.PUT_LINE('No data found for SQL_ID ' || v_sql_id || ' in the last 7 days.');
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error processing SQL_ID ' || v_sql_id || ': ' || SQLERRM);
CONTINUE;
END;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Unexpected error: ' || SQLERRM);
END;
/
==
For single query:
SET SERVEROUTPUT ON SIZE UNLIMITED;
DECLARE -- Single SQL ID v_sql_id VARCHAR2(13) := 'g2969b8287x4'; -- Replace with your SQL ID v_found NUMBER; v_prev_plan_hash NUMBER; v_last_change_time DATE; v_last_old_plan_hash NUMBER; v_last_new_plan_hash NUMBER; v_last_old_elapsed NUMBER; v_last_new_elapsed NUMBER; v_plan_changed BOOLEAN; BEGIN -- Enable DBMS_OUTPUT with unlimited buffer DBMS_OUTPUT.ENABLE(NULL); DBMS_OUTPUT.PUT_LINE('DBMS_OUTPUT buffer set to unlimited.');
BEGIN
-- Validate SQL ID exists in AWR
SELECT COUNT(*)
INTO v_found
FROM dba_hist_sqlstat
WHERE sql_id = v_sql_id
AND ROWNUM = 1;
IF v_found = 0 THEN
DBMS_OUTPUT.PUT_LINE('Error: No AWR data found for SQL_ID ' || v_sql_id);
RETURN;
END IF;
-- Initialize plan change tracking
v_prev_plan_hash := NULL;
v_last_change_time := NULL;
v_last_old_plan_hash := NULL;
v_last_new_plan_hash := NULL;
v_last_old_elapsed := NULL;
v_last_new_elapsed := NULL;
v_plan_changed := FALSE;
-- Query AWR data for the SQL ID
FOR rec IN (
SELECT
s.begin_interval_time AS begin_time,
st.plan_hash_value,
ROUND(st.elapsed_time_delta / DECODE(st.executions_delta, 0, 1, st.executions_delta) / 1000000, 3) AS avg_elapsed_sec,
st.executions_delta AS executions
FROM
dba_hist_sqlstat st
JOIN
dba_hist_snapshot s
ON
st.snap_id = s.snap_id
WHERE
st.sql_id = v_sql_id
AND
st.dbid = (SELECT dbid FROM v$database)
AND
s.begin_interval_time > SYSDATE - 7 -- Limit to last 7 days
ORDER BY
s.begin_interval_time
) LOOP
-- Check for plan change
IF v_prev_plan_hash IS NOT NULL AND v_prev_plan_hash != rec.plan_hash_value THEN
-- Update last plan change details
v_last_change_time := rec.begin_time;
v_last_old_plan_hash := v_prev_plan_hash;
v_last_new_plan_hash := rec.plan_hash_value;
v_last_old_elapsed := v_last_new_elapsed; -- Previous row's elapsed time
v_last_new_elapsed := rec.avg_elapsed_sec;
v_plan_changed := TRUE;
END IF;
-- Update previous plan hash and elapsed time
v_prev_plan_hash := rec.plan_hash_value;
v_last_new_elapsed := rec.avg_elapsed_sec; -- Store for next iteration
END LOOP;
-- Print last plan change summary
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE('--- Last Plan Change Summary for SQL_ID: ' || v_sql_id || ' ---');
IF v_plan_changed THEN
DBMS_OUTPUT.PUT('Last Change Time: ' || TO_CHAR(v_last_change_time, 'YYYY-MM-DD HH24:MI:SS') || ' | ');
DBMS_OUTPUT.PUT('Old Plan Hash: ' || v_last_old_plan_hash || ' | ');
DBMS_OUTPUT.PUT('Old Avg Elapsed: ' || TO_CHAR(v_last_old_elapsed, '99990.000') || 's | ');
DBMS_OUTPUT.PUT('New Plan Hash: ' || v_last_new_plan_hash || ' | ');
DBMS_OUTPUT.PUT_LINE('New Avg Elapsed: ' || TO_CHAR(v_last_new_elapsed, '99990.000') || 's');
ELSIF SQL%NOTFOUND THEN
DBMS_OUTPUT.PUT_LINE('No data found for SQL_ID ' || v_sql_id || ' in the last 7 days.');
ELSE
DBMS_OUTPUT.PUT_LINE('No plan change detected in the last 7 days.');
END IF;
DBMS_OUTPUT.PUT_LINE(RPAD('-', 80, '-'));
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error processing SQL_ID ' || v_sql_id || ': ' || SQLERRM);
END;EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Unexpected error: ' || SQLERRM); END; /
No comments:
Post a Comment