Pavan DBA's Blog

The DBA Knowledge Store

script which collects statistics in 9i,10g for tables modified > 5%

Posted by Pavan DBA on November 6, 2009


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;

3 Responses to “script which collects statistics in 9i,10g for tables modified > 5%”

  1. jagat mohanty said

    The OUI installs CRS on each node on which the OUI detects that vendor clusterware is running. In addition, the CRS home is distinct from the RAC-enabled Oracle Home.

    root> crsctl check crs
    root> srvctl status nodeapps -n u01
    root> srvctl status asm -n u01
    root> srvctl status service -d racumup
    root> srvctl status database -d racumup
    ——————————————————————————————

    > srvctl status database -d database_name
    > srvctl status instance -d database_name
    -i instance_name [,instance_name-list]

    For example, to get the current status of two instances:

    > srvctl status instance -d RAC -i racdb1, racdb2

    srvctl stop

    This command stops the database and all or named instances. The syntax is:

    > srvctl stop database -d database_name
    [-o stop_options] [-c connect_string]
    ————————————————————————————–
    SRVCTL syntax has the following components:

    > srvctl verb noun options

    srvctl is the SRVCTL command
    ———————————-

    SRVCTL VERB BBREVIATION DESCRIPTION
    —————————————-
    add
    Add a database or instance.

    config
    Lists the configuration for the database or instance.

    Getenv
    Lists the environment variables in the SRVM configuration.

    Modify
    Modifies the instance configuration.

    remove
    Removes the database or instance.

    Setenv
    Sets the environment variable in the SRVM configuration.

    Start
    Starts the database or instance.

    Status
    Status of the database or instance.

    Stop
    Stops the database or instance.

    Unsetenv
    Sets the environment variable in the SRVM configuration to unspecified

    NOUN ABBREVIATION DESCRIPTION
    ————————————————

    database
    ABBREVIATION– db
    Operation refers to objects for the database.

    asm
    ABBREVIATION– asm
    To add, configure, enable, start, obtain the status of, stop, disable, and remove ASM instances.

    instance
    ABBREVIATION–Inst
    Operation refers to objects for the instances.

    nodeapps

    To add, configure, modify, manage environment variables for, start, obtain the status of, stop, and remove node applications.

    Service
    ABBREVIATION– serv

    To add, configure, modify, manage environment variables for, enable, start, obtain the status of, relocate, disable, stop, and remove services from your cluster database.

    OPTION MEANING
    ——————-

    -d
    Database name

    -h
    Print Usage

    -i
    Comma-separated list of instance names

    -n
    Node names or comma-separated node list

    -n
    Node name that will support an instance.

    -o
    $ORACLE_HOME to locate lsnrctl (node option) and oracle binaries (other options)

    -s
    SPFILE name

    -m
    Database domain name, in the form ‚Äúus.domain.com‚ÄĚ

    For example, to add a new database:

    > srvctl add database -d racdb -o /app/oracle/product/920

    To add named instances to a database:

    > srvctl add instance -d racdb -i rac1 -n mynode1
    > srvctl add instance -d racdb -i rac2 -n mynode2
    > srvctl add instance -d racdb -i rac3 -n mynode3
    srvctl config

    This command displays a list of configured databases. The syntax is:

    srvctl config database -d database_name

    For example, to display configured databases:

    > srvctl config database -d RACDB

    This command sets values for the environment in the SRVM configuration file. The syntax is:

    > srvctl setenv database -d database_name
    -t name=value [,name=value,…]

    srvctl setenv instance -d database_name
    [-i instance_name] -t name=value [,name=value,…]

    For example, to set list all environment variables for a database:

    > srvctl setenv database -d mydb -t LANG=en
    srvctl start

    This command starts the database, all or named instances, and all listeners associated with the database. The syntax is:

    > srvctl start database -d database_name
    [-o start_options] [-c connect_string]

    > srvctl start instance -d database_name -i instance_name
    [,insta_name-list] [-o start_options] [-c connect_string]

    Command-Specific Options for srvctl start

    OPTION
    MEANING

    -o
    Options passed directly to startup command in SQL*Plus including PFILE.

    -c
    Connect string for connecting to the Oracle instance using SQL*Plus.

    For example, to start the database and all enabled instances:

    > srvctl start database -d mydb

    To start specified instances:

    > srvctl start instance -d racdb -i racdb1, racdb3
    srvctl status

    This command displays the status of the database and instance. The syntax is:

    > srvctl status database -d database_name

    > srvctl status instance -d database_name
    -i instance_name [,instance_name-list]

    For example, to get the current status of two instances:

    > srvctl status instance -d RAC -i racdb1, racdb2

    Gives the output:

    Instance RACDB1 is running on node mynode1
    Instance RACDB2 is not running on node mynode1
    srvctl stop

    This command stops the database and all or named instances. The syntax is:

    > srvctl stop database -d database_name
    [-o stop_options] [-c connect_string]

    > srvctl stop instance -d database_name
    -i instance_name [,instance_name_list]
    [-o stop_options][-c connect_string]

    For example, to stop the database all instances:

    > srvctl stop database -d RACDB

    To stop named instances:

    > srvctl stop instance -d RACDB -i racdb1

    Use the srvconfig command to export/import the SRVM configuration information. The following is an example of how to export the contents of the configuration information to the text file:

    > srvconfig -exp file_name

    To import the configuration information from the text file named to the configuration repository for the RAC, use the command:

    > srvconfig -imp file_name

    ——————————————————————————————-

    Administering ASM Instances with SRVCTL in RAC

    Use the following syntax to remove an ASM instance:

    srvctl remove asm -n [-i ]

    Use the following syntax to enable an ASM instance:

    srvctl enable asm -n [-i ]

    Use the following syntax to disable an ASM instance:

    srvctl disable asm -n [-i ]

    You can also use SRVCTL to start, stop, and obtain the status of an ASM instance as in the following examples.

    Use the following syntax to start an ASM instance:

    srvctl start asm -n [-i ] [-o srvctl start instance -d database_name -i instance_name
    [,insta_name-list] [-o start_options] [-c connect_string]

    Command-Specific Options for srvctl start

    -o Options passed directly to startup command in SQL*Plus including PFILE.

    -c Connect string for connecting to the Oracle instance using SQL*Plus.

    For example, to start the database and all enabled instances:

    > srvctl start database -d mydb

    To start specified instances:

    > srvctl start instance -d racdb -i racdb1, racdb3

    srvctl status

    —————————————————————————————

    Administering ASM Instances with SRVCTL in RAC

    Use the following syntax to add configuration information about an existing ASM instance:

    srvctl add asm -n -i -o

    Use the following syntax to remove an ASM instance:

    srvctl remove asm -n [-i ]

    Use the following syntax to enable an ASM instance:

    srvctl enable asm -n [-i ]

    Use the following syntax to disable an ASM instance:

    srvctl disable asm -n [-i ]

    Use SRVCTL to start, stop, and obtain the status of an ASM instance as in the following examples.

    Use the following syntax to start an ASM instance:

    srvctl start asm -n [-i ] [-o <start_options]

    Use the following syntax to stop an ASM instance:

    Note: For all of the SRVCTL commands in this section for which the -i option is not required, if an instance name is not specified, then the command applies to all the ASM instances on the node.

    srvctl stop asm -n [-i ] [-o <stop_options]

    Use the following syntax to configure an ASM instance:

    srvctl config asm -n

    Use the following syntax to obtain the status of an ASM instance:

    srvctl status asm -n

    —————————————————————————————
    Grid Computing with RAC

    > srvctl stop instance -d database_name

    -i instance_name [,instance_name_list]

    [-o stop_options][-c connect_string]

    For example, to stop the database all instances:

    > srvctl stop database -d RACDB

    To stop named instances:

    > srvctl stop instance -d RACDB -i racdb1

    Use srvconfig command to export / import the SRVM configuration information. The following is an example of how to export the contents of the configuration information to the text file:

    > srvconfig -exp file_name
    —————————————————————————————-. Stop/START the following OCR daemons: CSSD, CRSD, EVMD, and the EVM logger
    >>>>/etc/init.d/init.crs stop
    >>>>etc/init.d/init.crs start

  2. jagat mohanty said

    The Following query is used to find Tables which are analyzed 80 days Before.

    select OWNER,
    TABLE_NAME,
    last_analyzed
    from dba_tables
    where OWNER not in (‘SYS’,’SYSTEM’,’ORACLE’,’PERFSTAT’,’OPS$ORACLE’,’DBSNMP’,’OUTLN’,’PUBLIC’)
    and LAST_ANALYZED is not null
    and (SYSDATE-LAST_ANALYZED) >80
    order by (SYSDATE-LAST_ANALYZED)

  3. jagat mohanty said

    Steps to upgrade agent from 10204 to 10205
    ==========================================

    stop the agent:
    ===============

    export AGENT_HOME=/oracle/app/oracle/product/agent10g
    cd /oracle/app/oracle/product/agent10g/bin
    ./emctl stop agent

    Backup the inventory and Agent home:
    ====================================
    cd /oracle/app/dev/dba
    tar -cvf oraInv_back.tar /oracle/app/oraInventory
    tar -cvf agent_home_back.tar /oracle/app/oracle/product/agent10g

    display opatch inventory before the upgrade:
    ============================================
    cd /oracle/app/oracle/product/agent10g/OPatch
    ./opatch lsinventory

    run the 10205 patch:
    ====================
    cd /stage/3731593/Disk1
    unset LD_LIBRARY_PATH
    unset PATH
    unset ORACLE_SID
    unset TNS_ADMIN
    unset ORA_NLS10
    export ORACLE_HOME=/oracle/app/oracle/product/agent10g

    Rename the old nohup output and run the agen.sh script.

    $nohup ./agen.sh &

    Check the installation log

    Check agent status:
    ===================
    cd /oracle/app/oracle/product/agent10g/bin
    ./emctl status agent
    If agent is not up then upload the agent
    ./emctl start agent
    ./emctl upload agent
    (till upload files gets zero).

    display opatch inventory after the upgrade:
    ============================================
    cd $ORACLE_HOME/OPatch
    ./opatch lsinventory

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

 
%d bloggers like this: