Oracle statistical information collection tool DBMS_ Stats introduction


You can use Oracle’s built-in package DBMS_ Stats to view or modify the collected database statistics. This article mainly introduces gate_ TABLE_ Stats process, through which statistics of tables and columns (or indexes) can be collected.

1 Grammar

dbms_stats.gather_table_stats(ownname varchar2,

tabname varchar2,

partname varchar2 default null,

estimate_percent number default default_estimate_percent,

block_sample boolean default false,

method_opt varchar2 default default_method_opt,

degree number default default_degree_value,

granularity varchar2 default default_granularity,

cascade boolean default default_cascade,

stattab varchar2 default null,

statid varchar2 default null,

statown varchar2 default null,

no_invalidate boolean default to_no_invalidate_type(get_param(‘NO_INVALIDATE’)),

stattype varchar2 default ‘DATA’,

force boolean default false,

— the context is intended for internal use only.

context dbms_stats.ccontext default null,

options varchar2 default default_options);

The parameters marked in red are commonly used.

2 parameter description

Focus on the following parameters.

2.1 method_opt

method_ Opt corresponds to the following two values or a combination of them. The default value is for all columns size Auto:

  • FOR ALL [ INDEXED | HIDDEN ] COLUMNS [size_clause]
  • FOR COLUMNS [column_clause] [size_clause]

size_clause := SIZE {integer | REPEAT | AUTO | SKEWONLY}

column_clause := column_name | extension name | extension

Among them,

  • Integer: the number of histogram buckets, which must be in the range of 1-2048;
  • Repeat: only collect histograms on columns that already have histograms;
  • Auto: Oracle is responsible for determining the columns to collect histogram according to the data distribution and column work;
  • Skewonly: Oracle determines the columns to collect histogram according to the data distribution of columns;
  • column_ Name: column name;
  • Extension: it can be a column group in the format of (column)_ name,column_ It can also be an expression;

2.2 degree

This parameter sets the parallelism, which is null by default. It means that the parallelism set by default when creating a table is used. By setting the parallelism, the execution efficiency can be improved.

2.3 granularity

This parameter sets the granularity of statistics to be collected (only used when partitioning tables). Values include:

  • All: collect all (sub partition, partition, global) statistical information;
  • APPROX_ Global and partition: similar to global and partition, but in this case, global statistics are aggregated from partition level statistics. This option will aggregate all statistics except different number of values of columns and different key values of indexes;
  • Auto: the default value, which determines the granularity of partition based on the type of partition;
  • Default: collect global and partition level statistics. This option has expired;
  • Global: collect global statistical information;
  • Global and partition: collect global and partition level statistics, not sub partition statistics;
  • Partition: collect statistical information of partition level;
  • Support: collects statistics at the sub partition level.

For partitioned tables, it is recommended to set the parameter value to all.

2.4 cascade

This parameter collects index statistics as well as table statistics. Using this option is equivalent to running gate_ INDEX_ Stats process.

2.5 no_invalidate

If the parameter value is set to true, the cursor associated with the table will not be invalid. If the parameter value is set to false, the corresponding cursor will be invalid immediately. It is recommended to set the parameter to false when re collecting statistical information for mass data operation, so as to avoid making the original SQL select the wrong execution plan because statistical information is collected.

2.6 options

The parameter value further describes which objects collect statistical information

  • Gather: collect statistical information on all objects;
  • Gather Auto: automatically collect all necessary statistical information, and Oracle implicitly determines which objects need new statistical information.


Recommended Today

Practice of query operation of database table (Experiment 3)

Following the previous two experiments, this experiment is to master the use of select statements for various query operations: single table query, multi table connection and query, nested query, set query, to consolidate the database query operation.Now follow Xiaobian to practice together!Based on the data table (student, course, SC, teacher, TC) created and inserted in […]