V1.0
|
Revision |
Author/Modifier | Comments | |
|
No. |
Date |
||
| 1.0 | 2012-02 | 魏兴华 | 初稿 |
一、 目的
ORACLE优化器依据对象、系统的统计信息来产生执行计划。因此如何收集对象、系统的统计信息尤其重要，本文档主要介绍收集对象统计信息的操作方法。某些情况下，如执行计划走错、表缺少关键字段统计信息，需要我们手工的设置统计信息，因此也会涉及修改对象统计信息的内容。对于执行计划走错，通过修改统计信息来修正的情况，要对CBO算法有简单的了解，知道哪些统计信息涉及到cost计算的过程。可以参考lewis的CBO优化法则来了解cost的计算.本手册不会涉及这些内容。
二、 适用范围
l 新建表，表里初始化了大量的数据。
l 对于已经存在的表，表里数据量变化比较大。比如表删除了大量数据。需要重新收集统计信息。
l 由于表上统计信息不准确或缺失导致执行计划走错。
l 表统计信息过于陈旧，可能导致执行计划错误
三、 风险评估
l 统计信息的改变会涉及到表上所有SQL在下一次硬解析的时候用到，因此影响面广。在操作的时候，需要确认影响的范围，不要单纯为了某一个SQL的执行计划正确，而导致更多的SQL执行计划走错。
l 请仔细评估好，no_invalidate的设置问题，这个参数设置为true，表上依赖的SQL不会立马失效，即不会立刻采用表上新的统计信息。只有下一次硬解析的时候才会用新的统计信息来生成执行计划。绝大多数时候，我们这边采用的参数值是false.代表让表上依赖的游标立刻失效，在下一次解析的时候，能够立刻用上表上新的统计信息。
l no_invalidate在设置为false会导致在收集统计信息完成后，表上所有的sql重新解析，对于核心表以及一些依赖sql很多的表，要尽量放到业务低峰期去操作，否则可能遭遇硬解析的风暴，导致系统CPU繁忙，latch争用（shared pool latch ,library cache latch，library cache pin,cursor pin s:wait x）.如果遭遇这种情况，大多数时候，这种争用会随着硬解析完成而很快结束，但是也有可能会导致ORACLE CRASH。
l ORACLE优化器依赖准确健全的统计信息来产生优秀的执行计划，虽然收集统计信息理论上是为了让更精准的、更能反映目前数据的分布的统计信息产生出更优秀的执行计划，但是无论如何ORACLE无法保证这一点，有可能执行计划更优秀或者不变，有可能更糟糕了，相信随着ORACLE版本的不断提升，优化器的BUG会越来越少。
l ORACLE的dbms_stats不能单独收集列的统计信息，要了解到，如果收集了某一列的统计信息，表的统计信息会随着更新。
l 把estimate_percent设置的比较小，可以加快收集统计信息的时间，在不收集直方图的情况下，设置较小的值一般也不会有任何问题。可是如果表存在直方图，那么还是建议你根据情况把这个参数设置的大点。
l 目前生产环境都关闭了绑定变量窥探的功能，因此对于收集了直方图的列，需要确认传入的是文本变量。
四、 操作流程
几个重要收集参数的介绍以及使用规范：
1. no_invalidate 是否让表上的游标立即失效：自动定期执行（crontab）设置true，手动收集设置false。
2. force 是否对锁定统计信息的表收集统计信息 ：不指定，统一规范使用默认值false 不收集锁定表的统计信息，如果需要收集请提单给出原因。
3. degree 收集统计信息的并行度 ： 不指定，使用默认值1；如果为了加快收集时间，可以设置高的并行度，需要提单给出理由。
4. estimate_percent 采样百分比：一般设置成 0.5 ，可以让收集统计信息的时间缩短。这个值是个最小值，如果ORACLE觉得这个值小，会自动调大。采样的大小不要超过100M，采样的时间控制在1分钟以内。
5. method_opt 收集直方图的方法 ：分以下几种情况：
a) 执行计划走错：
1. 收集指定列的基本统计信息：for columns A size repeat,B size repeat
2. 收集指定列的直方图：for columns A size auto,B size auto
b) 统计信息全为空：收集所有列的基本信息，同时收集个别列的直方图：
for columns size repeat, A size auto,B size auto
c) 定时收集统计信息：for all columns size repeat
6. cascade 设置成true，收集索引的统计信息。9I默认值是false,10G默认值是true.
7. 如果是分区表，需要指定partname参数。
更多参数的说明请参照我写的dbms_stats包参数介绍。文档位于：数据库管理—–à知识总结—-àdbms_stats包使用规范
1. 准备工作
a) 准备脚本，确认好收集对象的schema,table name，以及收集统计信息的参数设置等。参数的设置要依据规范。不同与规范的参数要提单给出理由。
b) 9I统计信息收集、修改前，请做好统计信息的备份。stattab为备份统计信息表。
begin
dbms_stats.export_table_stats(ownname=>’xx’,tabname=>’xx’,stattab=>’stattab’);
end;
/
备份表的创建方式如下：
begin
dbms_stats.CREATE_STAT_TABLE(OWNNAME=>’xx’,STATTAB=>’stattab’,TBLSPACE=>’xx’);
end;
/
2. 执行过程
以收集统计信息对象所在的用户登录数据库，以下为参考。
a) 分析表的统计信息
begin
dbms_stats.gather_table_stats(ownname => ‘xx’,
tabname => ‘xx’,
no_invalidate => FALSE,
estimate_percent => 0.5,
method_opt => ‘for all columns size repeat’,
cascade => true);
end;
/
以上仅为举例，特别是直方图的设置要依据情况，直方图的收集方法请依据规范来。
b) 设置列的统计信息
begin
dbms_stats.set_column_stats( ownname => user,
tabname => ‘table_name‘,
colname => ‘column_name‘,
no_invalidate => false,
distcnt => 1000000,
density =>1/100000);
end;
/
说明：针对特定表的特定字段，如何获取准确的distinct 值：
SELECT COUNT(DISTINCT column_name) FROM table_name;
density统一设置为1/distinct值.
c) 设置表的统计信息
begin
dbms_stats.set_table_stats( ownname => user,
tabname => ‘table_name’,
numrows => 1000000,
numblks => 6654,
no_invalidate => false,
force => false);
end;
/
说明：针对特定表，如何获取准确的 numrows:
SELECT COUNT(*) FROM table_name;
d) 修改索引的统计信息
begin
dbms_stats.set_index_stats(ownname => user,
indname => ‘index_name’,
numrows => 1000,
numlblks => 300,
numdist => 600,
clstfct => 400,
no_invalidate => false);
end;
/
clstfct的含义为聚簇因子。将群集因子改大，会增大该索引访问的cost；反之，则减小cost。
e) 删除表、索引、列的统计信息
begin
dbms_stats.delete_column_stats(ownname => user,
tabname => ‘table_name’,
colname => ‘column_name’,
no_invalidate => false,
force => false);
end;
/
begin
dbms_stats.delete_table_stats(ownname => user,
tabname => ‘table_name’,
no_invalidate => false,
force => false);
end;
/
需要注意删除表的统计信息默认会把列、索引、表的都删除。
begin
dbms_stats.delete_index_stats(ownname => user,
indname => ‘index_name’,
no_invalidate => false,
force => false);
end;
/
3. 验证方案
@tabstat脚本查看统计信息是否为最新，如果修改统计信息是为了验证执行计划，那么还需要explain出问题的sql的执行计划，确认统计信息修改后，走了期望的执行计划。
五、 核心对象风险
数据库的核心对象上往往依赖SQL比较多，而且执行频率上也往往比较高。因此分析、修改核心表的统计信息要尽量放到业务低峰期去操作，1）以避免大量硬解析所导致的系统风险，2)分析统计信息也可能遭遇原有SQL执行计划走错的风险。
六、 回退方案
1. 10G之后回退统计信息，可以采取ORACLE dbms_stats提供的restore_table_stats来闪回统计信息
begin
dbms_stats.restore_table_stats(ownname => user,
tabname => ‘table_name’,
as_of_timestamp => SYSTIMESTAMP-1,
no_invalidate => FALSE);
end;
/
代表把表的统计信息闪回到一天以前。这个闪回不仅会闪回表的统计信息，索引，列的统计信息都会闪回。
2. 9I回退统计信息
begin
dbms_stats.import_table_stats(ownname=>user,tabname=>’table_name’,stattab=>’stattab’);
end;
/
七、 历史故障及教训
无
