Oracle: DBMS_STATS.GATHER_DATABASE_STATS

DBMS_STATS.GATHER_DATABASE_STATS (
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,
gather_sys       BOOLEAN DEFAULT TRUE,
no_invalidate    BOOLEAN DEFAULT to_no_invalidate_type (get_param('NO_INVALIDATE'))
);

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 appropriate sample size for good statistics. This is the default.The default value can be changed using the SET_PARAM Procedure.

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]...]
size_clause is defined as size_clause := SIZE {integer | REPEAT | AUTO | SKEWONLY}
- integer : Number of histogram buckets. Must be in the range [1,254].
- REPEAT : Collects histograms only on the columns that already have histograms.
- AUTO : Oracle determines the columns to collect histograms based on data distribution and the workload of the
columns.
- SKEWONLY : Oracle determines the columns to collect histograms based on the data distribution of the columns.
The default is FOR ALL COLUMNS SIZE AUTO.The default value can be changed using the SET_PARAM Procedure.

degree Degree of parallelism. The default for degree is NULL. The default value can be changed using the SET_PARAM Procedure.
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.
The AUTO_DEGREE value determines the degree of parallelism automatically. This is either 1 (serial execution) or DEFAULT_DEGREE (the system default value based on number of CPUs and initialization parameters) according to size of the object.

granularity Granularity of statistics to collect (only pertinent if the table is partitioned).
'ALL' - gathers all (subpartition, partition, and global) statistics
'AUTO'- determines the granularity based on the partitioning type. This is the default value.
'DEFAULT' - gathers global and partition-level statistics. This option is obsolete, and while currently supported, it is included in the documentation for legacy reasons only. You should use the 'GLOBAL AND PARTITION' for this functionality. Note that the default value is now 'AUTO'.
'GLOBAL' - gathers global statistics
'GLOBAL AND PARTITION' - gathers the global and partition level statistics. No subpartition level statistics are gathered even if it is a composite partitioned object.
'PARTITION '- gathers partition-level statistics
'SUBPARTITION' - gathers subpartition-level 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 database in addition to gathering table and column statistics. Use the constant DBMS_STATS.AUTO_CASCADE to have Oracle determine whether index statistics to be collected or not. This is the default. The default value can be changed using the SET_PARAM
Procedure. Statistics for external tables are not collected by this procedure.
Exceptions
ORA-20000: Insufficient privileges.
ORA-20001: Bad input value.

stattab User statistics table identifier describing where to save the current statistics. The statistics table is assumed to reside in the same schema as the object being analyzed, so there must be one such table in each schema to use this option.

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 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. 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 a list of stale objects as determined by looking at the *_tab_modifications views.
LIST EMPTY: Returns a list of objects which currently have no statistics.

objlist List of objects found to be stale or empty

statown Schema containing stattab (if different from current schema)

gather_sys Gathers statistics on the objects owned by the 'SYS' user no_invalidate Does not nvalidate the dependent cursors if set to TRUE. The procedure invalidates the dependent
cursors immediately if set to FALSE. Use DBMS_STATS.AUTO_INVALIDATE. to have Oracle decide when to invalidate dependent cursors. This is the default. The default can be changed using the SET_PARAM Procedure.

no_invalidate Does not invalidate the dependent cursors if set to TRUE. The procedure invalidates the dependent cursors immediately if set to FALSE. Use DBMS_STATS.AUTO_INVALIDATE. to have Oracle decide when to invalidate dependent cursors. This is the default. The default can be changed using the SET_PARAM Procedure.

example:

Tables that have stale statistics:

SQL> SET SERVEROUTPUT ON

SQL> DECLARE
ObjList dbms_stats.ObjectTab;
BEGIN
dbms_stats.gather_database_stats(objlist=>ObjList, options=>’LIST STALE’);
FOR i in ObjList.FIRST..ObjList.LAST
LOOP
dbms_output.put_line(ObjList(i).ownname || ‘.’ || ObjList(i).ObjName || ‘ ‘ || ObjList(i).ObjType || ‘ ‘ || ObjList(i).partname);
END LOOP;
END;
/
– shows tables that have stale statistics
SYS.COL_USAGE$ TABLE
SYS.DEPENDENCY$ TABLE
SYS.HISTGRM$ TABLE
SYS.HIST_HEAD$ TABLE


grant analyze any to user;

SQL> EXEC DBMS_STATS.gather_database_stats;
SQL> EXEC DBMS_STATS.gather_database_stats(estimate_percent => 10);

SQL> EXEC DBMS_STATS.delete_database_stats;

или так:

begin
DBMS_STATS.DELETE_DATABASE_STATS;
end;

-- script to gather database statisticsbegin

dbms_stats.gather_database_stats(
options=>’gather auto’,
estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE,
degree=>3) ;
end;

dbms_stats.gather_database_stats (
estimate_percent => NULL,
method_opt => 'auto',
granularity => 'all,
cascade => 'true',
option => 'gather_auto'
);

begin
DBMS_STATS.GATHER_DATABASE_STATS (
estimate_percent =>100,
block_sample=>FALSE,
method_opt=>'for all columns size auto',
degree=>null,
cascade=>true,
no_invalidate=>false,
options=>'GATHER STALE',
gather_sys=>FALSE);

DBMS_STATS.GATHER_DATABASE_STATS (
estimate_percent =>100,
block_sample=>FALSE,
method_opt=>'for all columns size auto',
degree=>null,
cascade=>true,
no_invalidate=>false,
options=>'GATHER EMPTY',
gather_sys=>FALSE);
end;

declare
a dbms_stats.objecttab;
begin
dbms_stats.gather_database_stats
(OPTIONS=>'LIST EMPTY',OBJLIST=>a);
for i in 1 .. a.count
loop
dbms_output.put_line( a(i).ownname );
dbms_output.put_line( a(i).objType );
dbms_output.put_line( a(i).objName );
dbms_output.put_line( a(i).PartName );
dbms_output.put_line( a(i).subPartName );
dbms_output.put_line( a(i).Confidence );
dbms_output.put_line
( '-------------------------' );
end loop;
end;