oracle 11g 手动收集用户统计信息

yw6866

GATHER_SCHEMA_STATS Procedures　　This procedure gathers statistics for all objects in a schema.
　　Syntax
　　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,
　　obj_filter_list  ObjectTab DEFAULT NULL);
　　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',
　　statown          VARCHAR2 DEFAULT NULL,
　　no_invalidate    BOOLEAN  DEFAULT to_no_invalidate_type (
　　get_param('NO_INVALIDATE'),
　　force            BOOLEAN DEFAULT FALSE,
　　obj_filter_list  ObjectTab DEFAULT NULL);
　　Parameters
　　Table 142-43 GATHER_SCHEMA_STATS Procedure Parameters
　　Parameter   Description
　　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 appropriate sample>　　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 [size_clause] [,column [size_clause]...]

　　size_clause is defined as>　　column is defined as column := column_name | extension name | extension
　　- 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 on which to collect histograms based on data distribution and the workload of the columns.
　　- SKEWONLY : Oracle determines the columns on which to collect histograms based on the data distribution of the columns.

　　The default is FOR ALL COLUMNS>　　degree

　　Degree of parallelism. The default for degree is NULL. The default value can be changed using the SET_DATABASE_PREFS Procedure, SET_GLOBAL_PREFS Procedure, SET_SCHEMA_PREFS Procedure and SET_TABLE_PREFS Procedure. NULL means use the table default value specified by the DEGREE clause in the CREATE TABLE or>　　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. 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. 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_DATABASE_PREFS Procedure, SET_GLOBAL_PREFS Procedure, SET_SCHEMA_PREFS Procedure and SET_TABLE_PREFS Procedure.
　　stattab

　　User statistics table>　　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)
　　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_DATABASE_PREFS Procedure, SET_GLOBAL_PREFS Procedure, SET_SCHEMA_PREFS Procedure and SET_TABLE_PREFS Procedure.
　　force
　　Gather statistics on objects even if they are locked
　　obj_filter_list
　　A list of object filters. When provided, GATHER_SCHEMA_STATS will gather statistics only on objects which satisfy at least one object filter in the list as needed. In a single object filter, we can specify the constraints on the object attributes. The attribute values specified in the object filter are case- insensitive unless double-quoted. Wildcard is allowed in the attribute values. Suppose non-NULL values s1, s2, ... are specified for attributes a1, a2, ... in one object filter. An object o is said to satisfy this object filter if (o.a1 like s1) and (o.a2 like s2) and ... is true. See Applying an Object Filter List.