2008年7月3日

Gather DBMS_STATS Default parameter

mac

Author

2 min

Read Time

Views

What are the default parameter values ?

   select dbms_stats.get_param('cascade') from dual;
   select dbms_stats.get_param('degree') from dual;
   select dbms_stats.get_param('estimate_percent') from dual;
   select dbms_stats.get_param('method_opt') from dual;
   select dbms_stats.get_param('no_invalidate') from dual;
   select dbms_stats.get_param('granularity') from dual;


DEFAULT PARAMETER

DBMS_STATS.AUTO_CASCADE
NULL
DBMS_STATS.AUTO_SAMPLE_SIZE
FOR ALL COLUMNS SIZE AUTO
DBMS_STATS.AUTO_INVALIDATE
AUTO

maclean 2011-12-01

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). 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 : Method options of the following format (the phrase 'SIZE 1' is required to ensure gathering statistics in parallel and for use with the phrase hidden): FOR ALL [INDEXED | HIDDEN] COLUMNS [SIZE integer] This value is passed to all of the individual tables. degree : Degree of parallelism (NULL means use table default value). 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: Gather statistics on all objects in the schema. GATHER STALE: Gather 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: Gather statistics on objects which currently have no statistics. also, return a list of objects found to have no statistics. LIST STALE: Return list of stale objects as determined by looking at the *_tab_modifications views. LIST EMPTY: Return 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).

maclean 2011-12-26

DBMS_STATS.GATHER_TABLE_STATS This procedure gathers table and column (and index) statistics. It attempts to parallelize as much of the work as possible, but there are some restrictions as described in the individual parameters. This operation will not parallelize if the user does not have select privilege on the table being analyzed. Input arguments: ownname - schema of table to analyze tabname - name of table partname - name of partition 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 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 - method options of the following format method_opt := FOR ALL [INDEXED | HIDDEN] COLUMNS [size_clause] FOR COLUMNS [size_clause] column|attribute [size_clause] [,column|attribute [size_clause] ... ] size_clause := SIZE [integer | auto | skewonly | repeat], where integer is between 1 and 254 column := column name | extension name | extension default is FOR ALL COLUMNS SIZE AUTO. The default value can be changed using set_param procedure. Optimizer related table statistics are always gathered. If an extension is provided, the procedure create the extension if it does not exist already. Please refer to create_extended_stats for description of extension. degree - degree of parallelism (NULL means use of table default value which was specified by DEGREE clause in CREATE/ALTER TABLE statement) Use the constant DBMS_STATS.DEFAULT_DEGREE for the default value based on the initialization parameters. default for degree is NULL. The default value can be changed using set_param procedure. granularity - the granularity of statistics to collect (only pertinent if the table is partitioned) 'AUTO' - the procedure determines what level of statistics to collect 'GLOBAL AND PARTITION' - gather global- and partition-level statistics 'APPROX_GLOBAL AND PARTITION' - This option is similar to 'GLOBAL AND PARTITION'. But the global statistics are aggregated from partition level statistics. It will aggregate all statistics except number of distinct values for columns and number of distinct keys of indexes. The existing histograms of the columns at the table level are also aggregated.The global statistics are gathered (i.e., going back to GLOBAL AND PARTITION behaviour) if partname argument is null. The aggregation will use only partitions with statistics, so to get accurate global statistics, user has to make sure to have statistics for all partitions. This option is useful when you collect statistics for a new partition added into a range partitioned table (for example, a table partitioned by month). The new data in the partition makes the global statistics stale (especially the min/max values of the partitioning column). This stale global statistics may cause suboptimal plans. In this scenario, users can collect statistics for the newly added partition with 'APPROX_GLOBAL AND PARTITION' option so that the global statistics will reflect the newly added range. This option will take less time than 'GLOBAL AND PARTITION' option since the global statistics are aggregated from underlying partition level statistics. Note that, if you are using APPROX_GLOBAL AND PARTITION, you still need to collect global statistics (with granularity = 'GLOBAL' option) when there is substantial amount of change at the table level. For example you added 10% more data to the table. This is needed to get the correct number of distinct values/keys statistic at table level. 'SUBPARTITION' - gather subpartition-level statistics 'PARTITION' - gather partition-level statistics 'GLOBAL' - gather global statistics 'ALL' - gather all (subpartition, partition, and global) statistics default for granularity is AUTO. The default value can be changed using set_param procedure. cascade - gather statistics on the indexes for this table. Use the constant DBMS_STATS.AUTO_CASCADE to have Oracle determine whether index stats to be collected or not. This is the default. The default value can be changed using set_param procedure. Using this option is equivalent to running the gather_index_stats procedure on each of the table's indexes. stattab - The user stat table identifier describing where to save the current statistics. statid - The (optional) identifier to associate with these statistics within stattab. statown - The schema containing stattab (if different then ownname) no_invalidate - Do not invalide 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 dependend cursors. This is the default. The default can be changed using set_param procedure. When the 'cascade' argument is specified, not pertinent with certain types of indexes described in the gather_index_stats section. force - gather statistics of table even if it is locked. context - internal use only. Exceptions: ORA-20000: Table does not exist or insufficient privileges ORA-20001: Bad input value ORA-20002: Bad user statistics table, may need to upgrade it ORA-20005: object statistics are locked

评论 (2)