Gather Stats Script

 -- -----------------------------------------------------------------------------------
-- File Name    : gather_stats_script.sql
-- Author       : Mohamed Fowjil
-- Description  : Gathers statistics for the database using DBMS_STATS procedures.
-- Last Modified: 2024-07-01
-- -----------------------------------------------------------------------------------
SET SERVEROUTPUT ON
-- Define variables for timing
VARIABLE start_time VARCHAR2(100);
VARIABLE end_time VARCHAR2(100);
-- Get current timestamp as start time
BEGIN
   SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') INTO :start_time FROM DUAL;
   DBMS_OUTPUT.PUT_LINE('Start Time: ' || :start_time);
END;
/
-- Spool start time to separate log file
SPOOL gather_stats_log.txt
SELECT 'Start Time: ' || :start_time FROM DUAL;
SPOOL OFF
-- Set global preferences for DBMS_STATS
EXEC DBMS_STATS.SET_GLOBAL_PREFS('DEGREE', 8);
EXEC DBMS_STATS.SET_GLOBAL_PREFS('CONCURRENT', 'AUTOMATIC');
-- Gather dictionary stats
BEGIN
   DBMS_STATS.GATHER_DICTIONARY_STATS();
   DBMS_OUTPUT.PUT_LINE('Gathered dictionary stats.');
EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Error gathering dictionary stats: ' || SQLERRM);
END;
/
-- Gather fixed objects stats
BEGIN
   DBMS_STATS.GATHER_FIXED_OBJECTS_STATS();
   DBMS_OUTPUT.PUT_LINE('Gathered fixed objects stats.');
EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Error gathering fixed objects stats: ' || SQLERRM);
END;
/
-- Gather database stats
DECLARE
   l_options VARCHAR2(200);
BEGIN
   l_options := 'GATHER STALE';
   DBMS_STATS.GATHER_DATABASE_STATS(
      options => l_options,
      estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
      method_opt => 'FOR ALL COLUMNS SIZE SKEWONLY',
      granularity => 'ALL',
      cascade => TRUE,
      degree => 8,
      no_invalidate => DBMS_STATS.AUTO_INVALIDATE
   );
   DBMS_OUTPUT.PUT_LINE('Gathered database stats.');
EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Error gathering database stats: ' || SQLERRM);
END;
/
-- Get current timestamp as end time
BEGIN
   SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') INTO :end_time FROM DUAL;
   DBMS_OUTPUT.PUT_LINE('End Time: ' || :end_time);
END;
/

--SET PAGESIZE 14
SET FEEDBACK ON
SET VERIFY ON
SET TIMING ON
SET HEADING ON