Month: November 2013

Analyze all objects in current schema

Zahar HilkevichLeave a comment

Since Oracle version 10, ANALYZE command has been deprecated for statistics gathering. The command stays in Oracle and is intended to be used to list chained rows and validate structure.

The ANALYZE command was superseded by procedures in dbms_stats package.

Specifically, if you need to collect statistics for a schema, you need to use dbms_stats.gather_schema_stats procedure.

If you want to run it for the current schema with all default parameters use this:

exec dbms_stats.gather_schema_stats(USER)

The full signature of this procedure:

DBMS_STATS.GATHER_SCHEMA_STATS ( 
   ownname          VARCHAR2, 
   estimate_percent NUMBER   DEFAULT to_estimate_percent_type 
                                                (get_param('ESTIMATE_PERCENT')), 
   block_sample     BOOLEAN  DEFAULT FALSE, 
   method_opt       VARCHAR2 DEFAULT get_param('METHOD_OPT'),
   degree           NUMBER   DEFAULT to_degree_type(get_param('DEGREE')), 
   granularity      VARCHAR2 DEFAULT GET_PARAM('GRANULARITY'), 
   cascade          BOOLEAN  DEFAULT to_cascade_type(get_param('CASCADE')), 
   stattab          VARCHAR2 DEFAULT NULL, 
   statid           VARCHAR2 DEFAULT NULL, 
   options          VARCHAR2 DEFAULT 'GATHER', 
   objlist          OUT      ObjectTab,
   statown          VARCHAR2 DEFAULT NULL, 
   no_invalidate    BOOLEAN  DEFAULT to_no_invalidate_type (
                                     get_param('NO_INVALIDATE')),
  force             BOOLEAN DEFAULT FALSE);