restore_tables_stats_v1.sql

ADVERTISEMENT

-------------------------------------------------------------------------------
-- Script:     RESTORE_TABLES_STATS_BY_SQLID_v1.sql
-- Purpose:    Generates DBMS_STATS.RESTORE_TABLE_STATS commands for all tables
--             involved in the execution plan of a given SQL ID.
--
-- Description:
--             This script scans V$SQL_PLAN for a specified SQL ID,
--             extracts all referenced tables, and prints EXEC statements to
--             restore their optimizer statistics as of a user-provided timestamp.
--
-- Usage:
--             Run in SQL*Plus or SQL Developer.
--             Prompts for:
--               - SQL ID (e.g., 9f3ac2b7k1mxy)
--               - Restore timestamp in format: DD/MM/YYYY HH24:MI
--
-- Output:
--             A list of executable DBMS_STATS.RESTORE_TABLE_STATS commands.
--             These can be manually executed or spooled to a script file.
--
-- Example:
--             Enter SQL ID: f7gq9x4h8lm2a
--             Enter restore timestamp: 18/06/2025 11:30
--
--             Output:
--             EXEC DBMS_STATS.RESTORE_TABLE_STATS('HR', 'EMPLOYEES', as_of_timestamp => TO_DATE('18/06/2025 11:30', 'DD/MM/YYYY HH24:MI'), no_invalidate => FALSE);
--             EXEC DBMS_STATS.RESTORE_TABLE_STATS('HR', 'DEPARTMENTS', as_of_timestamp => TO_DATE('18/06/2025 11:30', 'DD/MM/YYYY HH24:MI'), no_invalidate => FALSE);
--             EXEC DBMS_STATS.RESTORE_TABLE_STATS('HR', 'JOBS', as_of_timestamp => TO_DATE('18/06/2025 11:30', 'DD/MM/YYYY HH24:MI'), no_invalidate => FALSE);
--
-- Author:     W3Buddy
-- Version:    v1.0
-- Date:       2025-06-18
-------------------------------------------------------------------------------

SET SERVEROUTPUT ON
ACCEPT v_sqlid CHAR PROMPT 'Enter SQL ID: '
ACCEPT v_asof_ts CHAR PROMPT 'Enter restore timestamp (DD/MM/YYYY HH24:MI): '

DECLARE
  v_sqlid   VARCHAR2(20) := '&v_sqlid';
  v_ts_str  VARCHAR2(30) := '&v_asof_ts';
  v_asof_ts DATE;
BEGIN
  v_asof_ts := TO_DATE(v_ts_str, 'DD/MM/YYYY HH24:MI');

  FOR rec IN (
    SELECT DISTINCT OBJECT_OWNER, OBJECT_NAME
    FROM   V$SQL_PLAN
    WHERE  SQL_ID = v_sqlid
    AND    OBJECT_TYPE = 'TABLE'
    AND    OBJECT_NAME IS NOT NULL
  )
  LOOP
    DBMS_OUTPUT.PUT_LINE(
      'EXEC DBMS_STATS.RESTORE_TABLE_STATS(''' ||
      rec.OBJECT_OWNER || ''', ''' ||
      rec.OBJECT_NAME || ''', as_of_timestamp => TO_DATE(''' || v_ts_str || ''', ''DD/MM/YYYY HH24:MI''), no_invalidate => FALSE);'
    );
  END LOOP;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;
/

ADVERTISEMENT