APPS - dbms_stats.gather_schema_stats

dbms_stats.gather_schema_stats


PROCEDURE  schema_stats

IS
/*
      ------------------------------------------------------------------
      Procedure to run gather_schema_stats
      ------------------------------------------------------------------
*/
   v_sql                     VARCHAR2(32000);
   v_proc                    VARCHAR2(30) := 'schema_stats';
   v_msg                     VARCHAR2(4000);
   v_procedure               NUMBER  := 0;
   v_start_time              NUMBER;
   v_end_time                NUMBER;
   schema_name   VARCHAR2(30) := '<Schema_Name>';


BEGIN
/* This script deriving set default session parameters  */
   pre_logon;

   v_start_time := DBMS_UTILITY.get_time;
 
/*
      ------------------------------------------------------------------
      check the demantra schema global variable
      ------------------------------------------------------------------
*/

IF v_lc_dem_schema IS NOT NULL THEN

 BEGIN
   sys.dbms_stats.gather_schema_stats(ownname => schema_name, estimate_percent => 80,degree => 16);
   EXCEPTION
   WHEN OTHERS THEN
   v_msg :=(' - Error while gathering stats on '||v_lc_dem_schema||' '||SQLERRM);
   DBEX( v_msg, v_proc, 'E' );
  END;
/*
      ------------------------------------------------------------------
      Record timings
      ------------------------------------------------------------------
*/

   v_end_time  := DBMS_UTILITY.get_time;
   v_procedure := (( v_end_time - v_start_time ) / 100 );

   v_msg       :='Procedure Completed in '|| LPAD(TO_CHAR(v_procedure),8) ||' seconds';
   DBEX( v_msg, v_proc, 'T' );
COMMIT;
ELSE
v_msg := '- Procedure completed with Error';
  DBEX( v_msg, v_proc,'E');
END IF;
EXCEPTION
WHEN OTHERS THEN
  v_msg := 'Demantra Maintenance Process - SCHEMA_STATS status:- '||SQLERRM;
  DBEX( v_msg, v_proc, 'E' );
END schema_stats;



Parameter
ownname
 
Schema to analyze (NULL means current schema).

estimate_percent
 
Percentage of rows to estimate (NULL means compute): The valid range is [0.000001,100). Use the constant DBMS_STATS.AUTO_SAMPLE_SIZE to have Oracle determine the best sample size for good statistics.

block_sample
 
Whether or not to use random block sampling instead of random row sampling. Random block sampling is more efficient, but if the data is not randomly distributed on disk, then the sample values may be somewhat correlated. Only pertinent when doing an estimate statistics.

method_opt
 
Accepts:

FOR ALL [INDEXED | HIDDEN] COLUMNS [size_clause]
FOR COLUMNS [size clause] column|attribute [size_
clause] [,column|attribute [size_clause]...],
where size_clause is defined as:

size_clause := SIZE {integer | REPEAT | AUTO |
SKEWONLY},
where integer is in the range [1,254]. This value is passed to all of the individual tables.

degree
 
Degree of parallelism. NULL means use the table default value specified by the DEGREE clause in the CREATE TABLE or ALTER TABLE statement. Use the constant DBMS_STATS.DEFAULT_DEGREE to specify the default value based on the initialization parameters.

granularity
 
Granularity of statistics to collect (only pertinent if the table is partitioned).

DEFAULT: Gather global- and partition-level statistics.

SUBPARTITION: Gather subpartition-level statistics.

PARTITION: Gather partition-level statistics.

GLOBAL: Gather global statistics.

ALL: Gather all (subpartition, partition, and global) statistics. 

cascade
 
Gather statistics on the indexes as well.

Index statistics gathering is not parallelized. Using this option is equivalent to running the gather_index_stats procedure on each of the indexes in the schema in addition to gathering table and column statistics.

stattab
 
User stat table identifier describing where to save the current statistics.

statid
 
Identifier (optional) to associate with these statistics within stattab.

options
 
Further specification of which objects to gather statistics for:

GATHER: Gathers statistics on all objects in the schema.

GATHER AUTO: Gathers all necessary statistics automatically. Oracle implicitly determines which objects need new statistics, and determines how to gather those statistics. When GATHER AUTO is specified, the only additional valid parameters are ownname, stattab, statid, objlist and statown; all other parameter settings are ignored. Returns a list of processed objects.

GATHER STALE: Gathers statistics on stale objects as determined by looking at the *_tab_modifications views. Also, return a list of objects found to be stale.

GATHER EMPTY: Gathers statistics on objects which currently have no statistics. also, return a list of objects found to have no statistics.

LIST AUTO: Returns a list of objects to be processed with GATHER AUTO.

LIST STALE: Returns list of stale objects as determined by looking at the *_tab_modifications views.

LIST EMPTY: Returns list of objects which currently have no statistics.

objlist
 
List of objects found to be stale or empty.

statown
 
Schema containing stattab (if different than ownname).