Oracle dbms_stat 包

Dbms_stats包

execdbms_stats.gather_schema_stats( -
ownname => 'SCOTT', -
options => 'GATHER AUTO', -
estimate_percent => dbms_stats.auto_sample_size, -
method_opt => 'for all columns size repeat', -
degree => 15 -
) 

Options 参数

1>gather 重构分析整个架构（schema）

2>gather empty 只分析目前还没有统计的表

3>gather stale 只重新分析修改量超过10%的表（insert，update，delete）

4>gather auto 重新分析当前没有统计的对象，以及统计数据过期（变脏）的对象。注意，使用gather auto类似于组合使用gather stale和gather empty 

注意:

无论gather stale还是gather auto，都要求进行监视。如果你执行一个alter table xxx monitoring命令，Oracle会用dba_tab_modifications视图来跟踪发生变动的表。这样一来，你就确切地知道，自从上一次分析统计数据以来，发生了多少次插入、更新和删除操作。 

estimate_percent 选项

以下estimate_percent参数是一种比较新的设计，它允许Oracle的dbms_stats在收集统计数据时，自动估计要采样的一个segment的最佳百分比：
estimate_percent => dbms_stats.auto_sample_size

要验证自动统计采样的准确性，你可检视dba_tables sample_size列。一个有趣的地方是，在使用自动采样时，Oracle会为一个样本尺寸选择5到20的百分比。记住，统计数据质量越好，CBO做出的决定越好。 

Method_opt选项

Dbms_stats的method_opt参数尤其适合在表和索引数据发生变化时刷新统计数据 method_opt参数也适合用于判断哪些列需要直方图（histograms）。 

某些情况下，索引内的各个值的分布会影响CBO是使用一个索引还是执行一次全表扫描的决策。例如，假如在where子句中指定的值的数量不对称，全表扫描就显得比索引访问更经济。

如果你有一个高度倾斜的索引（某些值的行数不对称），就可创建Oracle直方图统计。但在现实世界中，出现这种情况的机率相当小。使用CBO时，最常见的错误之一就是在CBO统计中不必要地引入直方图。根据经验，只有在列值要求必须修改执行计划时，才应使用直方图。

为了智能地生成直方图，Oracle为dbms_stats准备了method_opt参数。

在method_opt子句中，还有一些重要的新选项，包括skewonly，repeat和auto：method_opt=>'for all columns size skewonly'
method_opt=>'for all columns size repeat'
method_opt=>'for all columns size auto' 

skewonly选项会耗费大量处理时间，因为它要检查每个索引中的每个列的值的分布情况。

假如dbms_stat发现一个索引的各个列分布得不均匀，就会为那个索引创建直方图，帮助基于代价的SQL优化器决定是进行索引访问，还是进行全表扫描访问。例如，在一个索引中，假定有一个列在50%的行中，如清单B所示，那么为了检索这些行，全表扫描的速度会快于索引扫描。

补充：

在dbms_stats分析表的时候，我们经常要保存之前的分析，以防止分析后，性能下降用来恢复。

--首先创建一个分析表，该表用来保存之前的分析值

SQL>

begin

dbms_stats.create_stat_table(ownname => 'SYS',stattab => 'STAT_TABLE');
end;

--分析表

SQL>

Begin

Dbms_stats.gather_table_stats(ownname => 'SYS',tabname => 'TESTT');
end;

--导出表分析信息到stat_table中

SQL>

BEGIN
 dbms_stats.export_table_stats(ownname => 'SYS',tabname => 'TESTT',stattab => 'STAT_TABLE');
END;

--删除分析信息

SQL>

Begin 
DBMS_STATS.delete_table_stats(ownname => 'SYS',tabname => 'TESTT');
END;

--导入分析信息

SQL>

BEGIN 
DBMS_STATS.import_table_stats(ownname => 'SYS',tabname => 'TESTT',stattab => 'STAT_TABLE');
END;

同理也有
EXPORT_COLUMN_STATS：导出列的分析信息
EXPORT_INDEX_STATS：导出索引分析信息
EXPORT_SYSTEM_STATS：导出系统分析信息
EXPORT_TABLE_STATS：导出表分析信息
EXPORT_SCHEMA_STATS：导出方案分析信息
EXPORT_DATABASE_STATS：导出数据库分析信息
IMPORT_COLUMN_STATS：导入列分析信息
IMPORT_INDEX_STATS：导入索引分析信息
IMPORT_SYSTEM_STATS：导入系统分析信息
IMPORT_TABLE_STATS：导入表分析信息
IMPORT_SCHEMA_STATS：导入方案分析信息
IMPORT_DATABASE_STATS：导入数据库分析信息
GATHER_INDEX_STATS：分析索引信息
GATHER_TABLE_STATS：分析表信息，当cascade为true时，分析表、列（索引）信息
GATHER_SCHEMA_STATS：分析方案信息
GATHER_DATABASE_STATS：分析数据库信息
GATHER_SYSTEM_STATS：分析系统信息 

/**********************************************************/

--oracle10g环境

--大表estimate_percent=1 

--小表estimate_percent=10

--全表分析

begin 

DBMS_STATS.GATHER_TABLE_STATS

(ownname=>'sys',

tabname=>'tt8',

estimate_percent=>1,

degree=>10,

method_opt=>'FOR ALL COLUMNS SIZE 1',

no_invalidate=>false,

cascade=>true,

force=>true);

end;

/

--强制分析表

--force=>true

--当前表分区分析

begin 

DBMS_STATS.GATHER_TABLE_STATS

(ownname=>'v_username',

tabname=>'v_obj_name',

partname=>'v_tab_part_name',

estimate_percent=>v_percent,

degree=>10,

method_opt=>'FOR ALL COLUMNS SIZE 1',

no_invalidate=>false,

cascade=>true);

end;

/

--强制分析表

--force=>true

--oracle9i环境

--全表分析

begin

DBMS_STATS.GATHER_TABLE_STATS

(ownname=>'v_username',

tabname=>'v_obj_name',

estimate_percent=>v_percent,

degree => 10,

cascade => true);

end;

/

--锁住统计信息

SQL>

begin

dbms_stats.lock_table_stats(ownname => 'aicbs',tabname => 'boss_user');

end;

/