Oracle 搜集统计信息

常用的统计信息收集脚本:

非分区表:

BEGIN

  DBMS_STATS.GATHER_TABLE_STATS(ownname          => 'SCOTT',

                                tabname          => 'DEPT',

                                estimate_percent => 30,

                                method_opt       => 'for all columns size repeat',

                                no_invalidate    => FALSE,

                                degree           => 8,

                                cascade          => TRUE);

END;

/

对分区表收集统计信息

BEGIN

  DBMS_STATS.GATHER_TABLE_STATS(ownname          => 'ROBINSON',

                                tabname          => 'P_TEST',

                                estimate_percent => 30,

                                method_opt       => 'for all columns size repeat',

                                no_invalidate    => FALSE,

                                degree           => 8,

                                granularity      => 'ALL',

                                cascade          => TRUE);

END;

/

method_opt=> 'for all columns size repeat'

repeat表示以前收集过直方图,现在收集统计信息的时候就收集直方图,如果以前没收集过直方图,现在收集统计信息的时候就不收集。

method_opt => 'for all columns size auto',(Oracle默认行为)

auto表示Oracle根据谓词过滤信息(前文讲解直方图的时候提到过的where条件过滤),自动判断该列是否收集直方图。一个稳定的系统,不应该让Oracle去自动判断,自动判断很可

能就会出事,比如某列不该收集直方图,设置auto过后它自己去收集直方图了,从而导致系统不稳定。

method_opt => ‘FOR COLUMNS size 1 object_id’ 删除object_id列直方图

method_opt => ‘for all columns size repeat’ 重新分析现有直方图

method_opt => ‘for all columns size auto’ oracle决定收集哪些列的直方图(需要设置table monitoring)

method_opt => ‘for all columns size skewonly’ oracle分析所有列的分布情况,生成直方图

method_opt => ‘FOR COLUMNS object_id size SKEWONLY’ 收集object_id列分布情况,生成直方图

method_opt => ‘FOR all INDEXED COLUMNS size SKEWONLY’ 收集index列分布情况,并生成直方图

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.

11g搜集相关列统计信息:

收集相关列统计信息:

BEGIN

DBMS_STATS.GATHER_TABLE_STATS(ownname         
=> 'GJYW',

tabname          => 'T',

estimate_percent => ,

method_opt       => 'for all columns size skewonly for columns(a,b)',

no_invalidate    => FALSE,

degree           => ,

cascade          => TRUE);

END;

上一篇:NO17--vue父子组件间单向数据流的解决办法


下一篇:【攻防世界】 高手进阶区 Recho WP