Pavan DBA's Blog

GRANT CREATE SESSION TO &&user;
GRANT ANALYZE ANY TO &&user;
GRANT ANALYZE ANY DICTIONARY TO &&user;
GRANT SELECT ANY TABLE TO &&user;
GRANT SELECT ANY SEQUENCE TO &&user;
GRANT SELECT ANY DICTIONARY TO &&user;
GRANT EXECUTE ON DBMS_STATS TO &&user;
GRANT SELECT ON DBA_TAB_MODIFICATIONS TO &&user;
GRANT CREATE JOB TO &&user;
GRANT SCHEDULER_ADMIN TO &&user;
GRANT ALL ON SYS.GATHER_STATS_JOB TO &&user;
GRANT ALL ON SYS.AUTO_SPACE_ADVISOR_JOB TO &&user;

CREATE OR REPLACE PACKAGE &&user..dba_stats_maint_pkg
/* Package is intended for use when managing
   the optimizer statistics for 9i and 10g
   databases. Once on 11g, each tables’ statistics
   setting can be customized and used by Oracle’s automatic
   job

   Assumptions:

   For 10g, STATISTICS_LEVEL is set to a level
   above basic, so that DBA_TAB_MODIFICATIONS
   is populated (i.e. tables are monitored)

   For 9i, relevant tables have their MONITORING
   attribute set, so that DBA_TAB_MODIFICATIONS
   view is populated

   This package is either compiled as a privileged user
   and/or it is compiled under someone else and
   all applicable grants on system packages and
   dynamic performance views are made

   Has a dependency to dbms_lock.sleep procedure – this is assumed to be present

   Explicit ANALYZE ANY grant has been made to DB_UTIL user
*/
AS
   –Globals change factor setting
   –This defines what % a segment must change by in order to update its statistics
   gn_change_factor number(16,2) := 5.00;
  
   –Removes all Statistic for the Database
   PROCEDURE delete_all_stats;

   –Procedure that disables the automatic gathering of stats
   –since, until 10g, this ‘canned’ procedure is not adequate
   –to gather statistics responsibly
   PROCEDURE disable_auto_stats_job;

   –Procedure that disables the automatic segment space advisor job
   –The running of this job can use up a lot of resources, hence
   –why many disable it
   PROCEDURE disable_auto_space_job;

   –Procedure gathers stats for SYSTEM and SYS schemas
   –Basically a wrapped calls to the applicable DBMS_STATS procedure
   PROCEDURE gather_data_dict_stats;

   –Procedure gathers fixed object stats
   –Whereas, data dictionary stats should be gathers constantly
   –fixed object stats only need to be gathered after upgrades, or
   –if the profile of the database’s usage changes significantly
  
   PROCEDURE gather_fixed_stats;

   –Procedure that gathers non-fixed, non-locked table, index, partition, and subpartition statistics
   –based upon whether there have been significant changes (i.e. DBA_TAB_MODIFICATIONS)
   –and or whether stats are empty
   –Package level defaults can be modified for your
   PROCEDURE gather_stats;

   –Procedure makes internal calls to first delete all stats
   –and then gather them, including a gathering of system statistics
   PROCEDURE start_over;
END dba_stats_maint_pkg;
/

SHOW ERRORS;

CREATE OR REPLACE PACKAGE BODY &&user..dba_stats_maint_pkg
/* Package is intended for use when managing
   the optimizer statistics for 9i and 10g
   databases. Once on 11g, each tables’ statistics
   setting can be customized and used by Oracle’s automatic
   job

   Assumptions:

   For 10g, STATISTICS_LEVEL is set to a level
   above basic, so that DBA_TAB_MODIFICATIONS
   is populated (i.e. tables are monitored)

   For 9i, relevant tables have their MONITORING
   attribute set, so that DBA_TAB_MODIFICATIONS
   view is populated

   This package is either compiled as a privileged user
   and/or it is compiled under someone else and
   all applicable grants on system packages and
   dynamic performance views are made

   */
AS
   –Removes all Statistic for the Database
   PROCEDURE delete_all_stats
   AS
   BEGIN
      DBMS_STATS.delete_dictionary_stats;
      DBMS_STATS.delete_database_stats;
   EXCEPTION
      WHEN OTHERS
      THEN
         RAISE;
   END;

   –Procedure that disables the automatic gathering of stats
   –since, until 10g, this ‘canned’ procedure is not adequate
   –to gather statistics responsibly
  
   PROCEDURE disable_auto_stats_job
   AS
   BEGIN
      dbms_scheduler.disable(‘SYS.GATHER_STATS_JOB’);
   END;

   –Procedure that disables the automatic segment space advisor job
   –The running of this job can use up a lot of resources, hence
   –why many disable it

   PROCEDURE disable_auto_space_job
   AS
   BEGIN
      dbms_scheduler.disable(‘SYS.AUTO_SPACE_ADVISOR_JOB’);
   END;

   PROCEDURE gather_data_dict_stats
   AS
   BEGIN
      DBMS_STATS.gather_dictionary_stats;
   EXCEPTION
      WHEN OTHERS
      THEN
         RAISE;
   END;
  
   PROCEDURE gather_fixed_stats
   AS
   BEGIN
      DBMS_STATS.gather_fixed_objects_stats;
   EXCEPTION
      WHEN OTHERS
      THEN
         RAISE;
   END;
  
   PROCEDURE gather_stats
   AS
      /* Cursor with the names of all the non-partitioned, non-system, non-locked, non-iot overflow type segments
         which don’t currently have statistics */
      lv_owner           dba_tables.owner%TYPE;
      lv_table_name      dba_tables.table_name%TYPE;
      lv_part_name       dba_tab_partitions.partition_name%TYPE;
      lv_change_factor   NUMBER (16, 2);
      lv_partitioned     VARCHAR (10);

      CURSOR lc_ns_np_nostats
      IS
         SELECT owner, table_name
           FROM dba_tables
          WHERE partitioned = ‘NO’
            AND NOT owner IN (‘SYS’,’SYSTEM’,’OUTLN’)
            AND num_rows IS NULL
            AND iot_type IS NULL
            AND NOT (owner, table_name) IN (SELECT owner, table_name
                                              FROM dba_tab_statistics
                                             WHERE stattype_locked = ‘ALL’)
            AND NOT (owner, table_name) IN (SELECT owner, table_name
                                              FROM dba_external_tables);

      CURSOR lc_ns_p_nostats
      IS
         SELECT dtp.table_owner, dtp.table_name, dtp.partition_name
           FROM dba_tab_partitions dtp, dba_tables dt
          WHERE dtp.num_rows IS NULL
            AND dtp.table_name = dt.table_name
            AND dtp.table_owner = dt.owner
            AND dt.iot_type IS NULL
            AND NOT dtp.table_owner IN (‘SYS’,’SYSTEM’,’OUTLN’)
            AND NOT (dtp.table_owner, dtp.table_name) IN (
                                                 SELECT owner, table_name
                                                   FROM dba_tab_statistics
                                                  WHERE stattype_locked =
                                                                         ‘ALL’)
            AND NOT (dtp.table_owner, dtp.table_name) IN (SELECT owner, table_name
                                                    FROM dba_external_tables);

      CURSOR lc_ss
      IS
         SELECT dtm.table_owner, dtm.table_name, dtm.partition_name,
                  ROUND (  (dtm.inserts + dtm.updates + dtm.deletes)
                         / dt.num_rows,
                         2
                        )
                * 100 “CHANGE_FACTOR”,
                dt.partitioned
           FROM sys.dba_tab_modifications dtm, dba_tables dt
          WHERE dtm.table_owner = dt.owner
            AND dtm.table_name = dt.table_name
            AND NOT dtm.table_owner IN (‘SYS’,’SYSTEM’,’OUTLN’)
            AND NOT dt.num_rows IS NULL
            AND iot_type IS NULL
            AND (   (dt.partitioned = ‘YES’ AND NOT dtm.partition_name IS NULL
                    )
                 OR (dt.partitioned = ‘NO’ AND dtm.partition_name IS NULL)
                )
            AND NOT (dtm.table_owner, dtm.table_name) IN (
                                             SELECT dts.owner, dts.table_name
                                               FROM dba_tab_statistics dts
                                              WHERE dts.stattype_locked =
                                                                         ‘ALL’)
            AND NOT (dtm.table_owner, dtm.table_name) IN (
                                              SELECT det.owner,
                                                     det.table_name
                                                FROM dba_external_tables det);
   BEGIN
      /**************************BEGIN EMPTYSTATS************************************/
     
      — First process the cursor above; alternatively, you could
      — do something similar via a call to DBMS_STATS with the ‘GATHER EMPTY’ option
     
      OPEN lc_ns_np_nostats;               — open the cursor before fetching

      LOOP
         FETCH lc_ns_np_nostats
          INTO lv_owner, lv_table_name;   — fetches 2 columns into variables

         EXIT WHEN lc_ns_np_nostats%NOTFOUND;
         — Call stats package
         DBMS_OUTPUT.put_line (   ‘Gathering Stats for Table and Indexes of ‘
                               || lv_owner
                               || ‘.’
                               || lv_table_name
                               || ‘ because they are empty…’
                              );
         — For these tables we will use most of the oracle defaults
         — We are assuming that partitioned tables are to be treated differently in terms
         — of sample size, parallelism degree and the like
         –trap any errors by placing in its own block
         BEGIN
            DBMS_STATS.gather_table_stats
                              (ownname               => lv_owner,
                               tabname               => lv_table_name,
                               granularity           => ‘AUTO’,
                               method_opt            => ‘for all columns’,
                               CASCADE               => DBMS_STATS.auto_cascade,
                               estimate_percent      => DBMS_STATS.auto_sample_size
                              );   
         EXCEPTION
           WHEN OTHERS THEN
              DBMS_OUTPUT.put_line (SQLCODE || ‘ – ‘ || SQLERRM);           
         END;                    
      END LOOP;

      CLOSE lc_ns_np_nostats;

      — Next we process table partitions whose statistics are null/empty
      — We will use smaller samples and parallelism when appropriate
     
      OPEN lc_ns_p_nostats;                 — open the cursor before fetching

      LOOP
         FETCH lc_ns_p_nostats
          INTO lv_owner, lv_table_name, lv_part_name;

         — fetches 3 columns into variables
         EXIT WHEN lc_ns_p_nostats%NOTFOUND;
         — Call stats package
         DBMS_OUTPUT.put_line (   ‘Gathering Stats for Partition ‘
                               || lv_owner
                               || ‘.’
                               || lv_table_name
                               || ‘.’
                               || lv_part_name
                               || ‘ because they are empty…’
                              );
                             
         — For these partitioned tables we will use smaller sample sizes
         — Trap Errors in anonymous block
        
         BEGIN
         DBMS_STATS.gather_table_stats (ownname               => lv_owner,
                                        tabname               => lv_table_name,
                                        partname              => lv_part_name,
                                        granularity           => ‘AUTO’,
                                        method_opt            => ‘for all columns’,
                                        CASCADE               => DBMS_STATS.auto_cascade,
                                        DEGREE                => 1,
                                        estimate_percent      => .00001
                                       );
         EXCEPTION
           WHEN OTHERS THEN
             DBMS_OUTPUT.put_line (SQLCODE || ‘ – ‘ || SQLERRM);
         END;                             
                                   
      END LOOP;

      CLOSE lc_ns_p_nostats;

      –Execute Global Database Stats Gathering with GATHER EMPTY option
      –in order to catch any objects that were missed above because of errors, etc.
      DBMS_OUTPUT.put_line (‘Executing Global Stats Procedure to Catch Any Missed Empty Objects…’);
     
      BEGIN
         dbms_stats.gather_database_stats(method_opt=>’for all columns’,
                                          granularity=>’AUTO’,
                                          estimate_percent=>DBMS_STATS.auto_sample_size,
                                          degree=>DBMS_STATS.default_degree,
                                          cascade=>DBMS_STATS.auto_cascade,
                                          options=>’GATHER EMPTY’);
      EXCEPTION
         WHEN OTHERS THEN
            DBMS_OUTPUT.put_line (SQLCODE || ‘ – ‘ || SQLERRM);
      END;     
      /**************************END EMPTYSTATS************************************/

      — Lastly we flush the database monitoring information
      — and then use the information therein to gather stats
      — on significantly changed objects
     
      /**************************BEGIN CHANGED OBJECTS STATS***********************/
     
      DBMS_STATS.flush_database_monitoring_info ();
     
      OPEN lc_ss;                           — open the cursor before fetching

      LOOP
         FETCH lc_ss
          INTO lv_owner, lv_table_name, lv_part_name, lv_change_factor,
               lv_partitioned;

         EXIT WHEN lc_ss%NOTFOUND;

         — If change factor is greater than change factor defined in the package header then we will analyze the changed object in question
         IF lv_change_factor >= gn_change_factor
         THEN
            — Now determine whether the Object is a partition
            IF lv_partitioned = ‘YES’
            THEN
               DBMS_OUTPUT.put_line
                                  (   ‘Gathering Stats for Partition ‘
                                   || lv_owner
                                   || ‘.’
                                   || lv_table_name
                                   || ‘.’
                                   || lv_part_name
                                   || ‘ because its change factor exceeds ‘||gn_change_factor||’%…’
                                  );
               BEGIN
               DBMS_STATS.gather_table_stats
                                          (ownname               => lv_owner,
                                           tabname               => lv_table_name,
                                           partname              => lv_part_name,
                                           granularity           => ‘AUTO’,
                                           method_opt            => ‘for all columns’,
                                           CASCADE               => DBMS_STATS.auto_cascade,
                                           DEGREE                => 1,
                                           estimate_percent      => .00001
                                          );
               EXCEPTION
                 WHEN OTHERS THEN
                   DBMS_OUTPUT.put_line (SQLCODE || ‘ – ‘ || SQLERRM);                          
               END;                          
            ELSE
               DBMS_OUTPUT.put_line
                              (   ‘Gathering Stats for Table and Indexes of ‘
                               || lv_owner
                               || ‘.’
                               || lv_table_name
                               || ‘ because its change factor exceeds ‘||gn_change_factor||’%…’
                              );
               BEGIN
               DBMS_STATS.gather_table_stats
                              (ownname               => lv_owner,
                               tabname               => lv_table_name,
                               granularity           => ‘AUTO’,
                               method_opt            => ‘for all columns’,
                               CASCADE               => DBMS_STATS.auto_cascade,
                               estimate_percent      => DBMS_STATS.auto_sample_size
                              );
               EXCEPTION
                 WHEN OTHERS THEN
                    DBMS_OUTPUT.put_line (SQLCODE || ‘ – ‘ || SQLERRM);
               END;                  
            END IF;
         ELSE
            NULL;
         END IF;
      END LOOP;

      CLOSE lc_ss;
     
      /**************************END CHANGED OBJECTS STATS**************************/
   END;

   –Procedure makes internal calls to first delete all stats
   –and then gather them all over again
  
   PROCEDURE start_over
   AS
   BEGIN
      DBMS_OUTPUT.put_line (‘Deleting all stats…’);
      delete_all_stats;
      DBMS_OUTPUT.put_line (‘Gathering fixed stats…’);
      gather_fixed_stats;
      DBMS_OUTPUT.put_line (‘Gathering user stats…’);
      gather_stats;
      DBMS_OUTPUT.put_line (‘Gathering dictionary stats…’);
      gather_data_dict_stats;
   EXCEPTION
      WHEN OTHERS
      THEN
         RAISE;
   END;
END dba_stats_maint_pkg;
/

SHOW ERRORS;