常用的统计信息收集脚本：
非分区表：
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 => 100, method_opt => 'for all columns size skewonly for columns(a,b)', no_invalidate => FALSE, degree => 8, cascade => TRUE); END;
|