1. 统计信息收集策略
1.1
统计信息收集策略的制定以满足系统的稳定运营为原则，兼顾
Oracle
数据库各版本的特性，按照实际应用系统需求，允许以下场景的组合收集策略存在。
(1) 收集级别
| 序号 | 收集级别 |
|---|---|
| 1 | 数据库（DB） |
| 2 | 用户（Schema） |
| 3 | 对象（Object） |
(2) 收集启用方式
| 序号 | 启用方式 |
|---|---|
| 1 | 收集启用 |
| 2 | 收集不启用 |
| 3 | 不收集 |
即允许数据库库级别设置收集启用，收集不启用或者不收集，也允许Schema级别收集启用，收集不启用或者不收集，还允许对象级别的收集启用，收集不启用或者不收集。同一个数据库中同时设置了以上各个级别的情况下，按照对象级别覆盖用户级别，用户级别覆盖数据库级别的策略进行。
1.2
为确保统计信息在收集窗口能尽可能优先收集需要重点收集的对象，按照DDL和DML的变化情况，制定了待收集对象的优先级，优先级的具体定义如表格。同优先级的待收集对象，新建对象按照创建时间从先到后排序；已有对象按照DML变化百分比从大到小排序，DML变化百分比相等的，上次最早收集的本次最先收集。
| 类型 | 优先级 | 是否启用 | 超时是否kill | 超时是否启动？ |
|---|---|---|---|---|
| 新建索引 | 1 | 是 | 否 | 是 |
| 新建表并初始化数据 | 2 | 是 | 否 | 是 |
| DML > 10%（不管是否有DDL） | 3 | 根据数据库策略决定 | 是 | 否 |
1.3
综合考虑Oracle各版本的特征，以及各类数据库变更后统计信息的总体情况，统一制定了统计信息收集总体原则：
I） 索引建立时，无论数据库的收集策略启用与否，都必须收集并启用该索引的统计信息，无需cascade表收集。10g及以上的Oracle数据库会自动收集索引统计信息，9i版本的数据库尚不具备该功能，变更时若为9i数据库，则需要通过自动或手动程序来收集。
II） 新建立空表时无需收集统计信息，但新建表并且初始化数据时，无论数据库的收集策略启用与否，都必须收集并启用。
III） 已有表无论是否发生DDL操作，直接按照DML>=10%来判断是否收集启用。对于收集启用的数据库，备份旧统计信息后，收集并启用新的统计信息，收集不启用的数据库，则收集后导回原统计信息，若超过收集窗口，不再启动收集。
I） 索引建立时，无论数据库的收集策略启用与否，都必须收集并启用该索引的统计信息，无需cascade表收集。10g及以上的Oracle数据库会自动收集索引统计信息，9i版本的数据库尚不具备该功能，变更时若为9i数据库，则需要通过自动或手动程序来收集。
II） 新建立空表时无需收集统计信息，但新建表并且初始化数据时，无论数据库的收集策略启用与否，都必须收集并启用。
III） 已有表无论是否发生DDL操作，直接按照DML>=10%来判断是否收集启用。对于收集启用的数据库，备份旧统计信息后，收集并启用新的统计信息，收集不启用的数据库，则收集后导回原统计信息，若超过收集窗口，不再启动收集。
1.4
基于统计信息收集策略的总原则，针对数据库各种变更场景，制定了Oracle数据库统计信息收集的具体策略指引图。如果在收集启用的数据库中，评估某个变更的关联影响时，确定需要按照收集不启用的策略单独处理这个变更，则需要参考收集不启用的操作方式处理，反之亦然。
统计信息具体策略指引图
（点击查看大图）
（点击查看大图）
1.5
统计信息收集后，发现统计值为
0
时，需要清空统计信息，等待应用需要时采样生成执行计划，或等待下次收集时统计值不为
0
后启用新的统计信息。
1.6
关于分区表，特别补充：
1
）如果分区表全表达到阀值，对表（包括所有分区及子分区）进行收集。
2
）如果分区表全表没有达到阀值，但某个分区达到阀值，对该分区（包括子分区）进行收集。
3
）如果分区表全表没有达到阀值，分区也没有达到阀值
，但某个子分区达到阀值，那么对该子分区进行
收集。
1.7
关于直方图，以前的收集策略有收集直方图的则必须收集直方图，否则不收集直方图。
1.8 关于收集采样率，为了尽可能控制一个数据库能在收集窗口内收集完所有的收集对象，通过分析9i、10g各数据库所有收集对象的平均收集时间确定：单个对象的收集时间最好不超过15分钟。因此，如果在9i，10g中一个对象的首次收集时间超过15分钟，则按照如下公式降低统计信息的收集采样率参数，最小的收集采样率默认为1%。 11g数据库由Oracle自行决定合适的采样率，不适用该公式。
本次采样率=Max(1,Min(100,(预期收集时间 * 上次采样率 / 上次收集时间)))
该公式说明：首次默认采样率取值100%，如上次成功收集时间超过15分钟，按比例降低采样率，最低为1%。
举列说明：某大表A首次收集时间长达5小时，或者由于收集达5小时后超过收集窗口而被系统自动终止，那么下次收集时，会自动调整该对象的收集采样率为：
Max (1, Min (100, (15 * 100 / (5*60)))) = 5
即下次该表的采样率会自动调整为5%收集。
注1：统计信息收集程序的具体收集参数，按照Oracle版本的不同，配置方式可以不同，但实际收集效果保持一致，具体9i/10g以及11g的收集参数配置说明，请参见附录1。
注2：本收集规范对Oracle数据字典等内部表的统计信息收集不适用，根据实际数据库的版本特征和系统需要，独立配置，具体9i/10g以及11g的Oracle系统表收集策略，请参见附录2。
注3：各种变更场景具体的策略的制定来源于两个因素：一是基于统计信息策略的总原则，二是基于各种场景下Oracle 的特性。特殊场景下统计信息相关特性的测试结果，请参见附录3。
2. 统计信息收集方式
2.1
方式一：为了能更加灵活方便的管理统计信息收集进程，增加对统计信息收集进程的掌控性，平安开发了一套自动收集统计信息的程序，该程序不区分是版本变更引发的统计信息收集需求，还是数据量变化引发的统计信息收集，通过部署统计信息自动收集程序，自动按照上述统计信息收集策略收集统计信息，无需在版本期间手动编写统计信息脚本。
2.2
方式二：在自动程序收集统计信息后，如果经影响分析发现当前的统计信息不满足应用要求，可以和架构师沟通，由架构师发起手动收集的生产变更，经变更委员会批准后，作为自动收集程序的补充，发布生产。
2.3
方式三：因自动收集程序尚未推广到该数据库而无法自动收集统计信息的情形，在版本发布期间，需要参照统计信息收集策略，提服务请求给DBA编写统计信息脚本并做风险评估，随DB版本的发布窗口在生产实施。统计信息编写模板，参见如下：
开发人员在request系统上报给开发DBA的需要填写的申请表模板：统计信息收集脚本书写申请表.xlsx
开发DBA填写后，反馈给开发人员放到版本中的评估模板：统计信息风险评估模板.xlsx
9i库新建索引收集统计信息模板：9i_crt_gather_index_stats.sql
9i库新建表收集统计信息模板：9i_crt_gather_table_stats.sql
10g库新建表收集统计信息模板：10g_crt_gather_table_stats.sql
11g库新建表收集统计信息模板：11g_crt_gather_table_stats.sql
注：统计信息的收集方式只限于以上三种情形之一，主体收集方式为全自动化收集，即方式一，其他两种方式为辅助收集方式。
3. 统计信息处理机制
3.1
生产环境发现可能因统计信息而导致执行计划变坏，或者因启用了新的统计信息而导致执行计划突变时，直接由运营
DBA
主导，联系系统运营人员和开发人员，架构师等，分析问题根本原因后，采取尽快利于问题解决的方式灵活处理，例如固定执行计划，导回旧的统计信息，重新收集统计信息等。
3.2
因数据库归档，数据库迁移，或者数据库升级等项目原因，需要做整个数据库的统计信息变更时，需要以库为单位，做统计信息变化后的风险评估和性能测试，满足出口标准后再应用于生产。归档用户下的表统计信息默认不收集，根据实际需要单独通过变更收集。
3.3
由于索引统计信息的变化不直接影响表的统计信息，对于收集不启用的数据库，收集表的统计信息后仍然会恢复为旧的统计值，新建立索引的统计信息可能与表的统计信息差异较大，而导致索引建立后，
SQL
执行计划不走新索引。
处理方式是：
1
）评估表启用新统计信息后对关联旧
SQL
的影响，如果评估不会带来负面问题，则可以通过变更的方式，提请启用新的统计信息；
2
）通过
hint
的方式，直接让需要应用到新索引的
SQL
走索引。
3.4 如果某个数据库中，单独的schema或者对象不需要收集统计信息，9i库通过以下方式配置：
1）通过 stats_schema_config配置不收集某个用户下的表的统计信息。
以不收集 USER1用户下的表为例：
INSERT INTO stats_schema_config (gather_owner,gather) VALUES('USER1', 'N');
COMMIT;
2）通过 stats_object_config配置不收集某个对象的统计信息。
以不收集 USER1.MY_TABLE表。
INSERT INTO dbmgr.stats_schema_config(gather_owner) values('USER1');
INSERT INTO dbmgr.stats_object_config(object_owner, object_type, object_name, gather)
values ('USER1', 'TABLE', 'MY_TABLE', 'N');
COMMIT;
3.5 如果某个数据库中，单独的schema或者对象的统计信息要设置为收集不启用，直接通过以下方式配置：
1）通过 stats_schema_config配置某个用户下的表的统计信息收集不启用。
以不收集 USER1用户下的表为例：
INSERT INTO dbmgr.stats_schema_config(gather_owner, gather, gather_dml, stats_enable)
VALUES('USER1', 'Y', 'Y', 'N');
COMMIT;
2）通过 stats_object_config配置某个对象的统计信息收集不启用。
以不收集 USER1.MY_TABLE表。
INSERT INTO dbmgr.stats_schema_config(gather_owner, gather, gather_dml, stats_enable)
values('USER1', 'Y', 'Y', 'N');
INSERT INTO dbmgr.stats_object_config
(object_owner, object_type, object_name, gather, gather_dml, stats_enable)
values('USER1', 'TABLE', 'MY_TABLE', 'Y', 'Y', 'N');
COMMIT;
3.6 如果在数据库统计信息收集过程中，降低大对象的收集采样率至1%后，仍然不能收集窗口收集完某个对象，则可以通过以下步骤提请生产变更，调整对象至可以完全收集成功：
1）调整并行度为合适的值。默认收集并行度参数degree设置为1，提高并行度参数时必须确保数据库本身业务需要的CPU消耗和统计信息CPU消耗之和满足P95要求；
2）数据库CPU资源不合适继续增加收集并行度时，可提请继续降低对象的收集采样率至1%以下，调整该参数之前，必须通过评估分析，确定收集的准确性满足应用的实际需求；
3）调整并行度和小于1%的采样率仍然不能收集完统计信息时，可以根据应用的实际需求，在常规收集窗口之外，例如周末白天为该对象单独设置收集窗口，直至可以收集完该对象。
3.7 为更加准确的处理一些关键库核心表的统计信息，提高数据库SQL执行计划的稳定性，特别建立了核心表的处理机制：
1）对于收集启用的数据库，由DBA提出核心表的定义，并监控符合核心表定义的对象进行维护；
2）通过功能开关控制核心表的统计信息自动收集动作，只有功能开关打开且满足收集阀值时，才能由自动程序按照统计信息的具体策略处理（自动程序无需对核心表提高收集优先级），否则无需收集；
3）核心表的开关是否打开，由DBA通过报表进行监控，并和相关部门一起进行影响分析后共同确定。
3.8 临时表统计信息的准确性对SQL执行计划的稳定性影响也非常大，因此也特别建立了临时表的处理机制：
1）无论数据库的收集策略启用与否，由DBA提出临时表的定义，并监控符合临时表定义的对象进行维护；
2）对于确定为临时表的对象，自动收集程序将其统计信息清空后，不再收集新的统计信息，由系统动态采样决定SQL的执行计划，如果要求保留该临时表的统计信息，自动程序不进行清空，也不再收集新的统计信息。
三 附录
1. 统计信息收集参数配置
收集统计信息用DBMS_STATS.GATHER_TABLE_STATS过程实现。各参数的配置说明如下：
1）
9i
、
10g
的参数配置
|
参数
|
说明
|
备注
|
|
ownname
|
属主
|
|
|
tabname
|
表名
|
|
|
partname
|
分区名
|
|
|
estimate_percent
|
采样率，取值为区间 [0.000001,100] 默认值为DBMS_STATS.AUTO_SAMPLE_SIZE， 由Oracle决定合适的采样率。 |
默认取值100%。
如上次成功收集时间超过15分钟，按比例降低采样率。
最低为1%。
本次采样率=max(1,min(100,(预期收集时间 * 上次采样率 / 上次收集时间)))
|
|
block_sample
|
是否采用随机块采样。 随机块采样性能较好，但准确性不可靠。 随机行采样性能较低，但准确性较高。 默认值为FALSE，采用随机行采样。 |
取值FALSE
|
|
method_opt
|
直方图建立方式。 格式为FOR ALL [INDEX] COLUMNS [size_clause] size_clause常取如下值： SIZE REPEAT 只在有直方图的列上，创建直方图 SIZE AUTO 由Oracle按数据分布及使用情况决定哪些列要创建直方图 默认值为：FOR ALL COLUMNS SIZE AUTO |
FOR ALL COLUMNS SIZE REPEAT
，首次直方图建立由人工评估、创建
|
|
degree
|
收集的并行度。 默认值为NULL，表示取CREATE TABLE或ALTER TABLE指定的并行度。 |
分大（>10g）、中（>1g）、小表（<=1g）三级， 各级默认值都为1。可根据需要调整各级并行度。 |
|
granularity
|
收集粒度。
|
对表为ALL， 对分区为PARTITION， 对有子分区的分区按PARTITION和SUBPARTITION各收集一次。 |
|
cascade
|
是否收集索引。
|
TRUE
|
|
stattab
|
统计信息收集后，保存历史统计信息的表。
|
9i
库表名：CBO_STATS_BAK。
10g
库不使用该参数，通过dba_tab_stats_history获得历史统计信息。
|
|
statid
|
统计信息收集后，保存历史统计信息的记录ID。
|
9i
库：按类型、日期时间及序列，自动生成。
10g
库不使用该参数，通过dba_tab_stats_history获得历史统计信息。
|
|
statown
|
统计信息收集后，保存历史统计信息的表的属主。
|
9i
库属主：DBMGR。
10g
库不使用该参数，通过dba_tab_stats_history获得历史统计信息。
|
|
no_invalidate
|
是否使相关游标失效。
|
收集启用为FALSE， 收集不启用为TRUE |
|
force
|
是否忽略锁定，强制收集。
|
9i
库不支持锁定统计信息，无此参数。
10g
库为FALSE。
|
2）
11g
的参数配置
|
参数
|
说明
|
备注
|
|
ownname
|
属主
|
|
|
tabname
|
表名
|
|
|
partname
|
分区名
|
|
|
estimate_percent
|
采样率，取值为区间[0.000001,100] 默认值为DBMS_STATS.AUTO_SAMPLE_SIZE， 由Oracle决定合适的采样率。 |
采用AUTO_SAMPLE_SIZE。
该参数值在11g中可达到之前版本100%采样率的准确性，资源消耗只有之前版本的10%。
|
|
block_sample
|
是否采用随机块采样。 随机块采样性能较好，但准确性不可靠。 随机行采样性能较低，但准确性较高。 默认值为FALSE，采用随机行采样。 |
取值FALSE
|
|
method_opt
|
直方图建立方式。 格式为FOR ALL [INDEX] COLUMNS [size_clause] size_clause常取如下值： SIZE REPEAT 只在有直方图的列上，创建直方图 SIZE AUTO 由Oracle按数据分布及使用情况决定哪些列要创建直方图 默认值为：FOR ALL COLUMNS SIZE AUTO |
FOR ALL COLUMNS SIZE REPEAT
，首次直方图建立由人工评估、创建
|
|
degree
|
收集的并行度。 默认值为NULL，表示取CREATE TABLE或ALTER TABLE指定的并行度。 |
分大（>10g）、中（>1g）、小表（<=1g）三级， 各级默认值都为1。可根据需要调整各级并行度。 |
|
granularity
|
收集粒度。
|
通过dbms_stats.set_global_prefs或dbms_stats.set_table_prefs设置表的INCREMENTAL为TRUE，PUBLISH为TRUE，取granularity为AUTO
|
|
cascade
|
是否收集索引。
|
TRUE
|
|
stattab
|
统计信息收集后，保存历史统计信息的表。
|
不使用。采用dba_tab_stats_history记录历史统计信息。
|
|
statid
|
统计信息收集后，保存历史统计信息的记录ID。
|
不使用。采用dba_tab_stats_history记录历史统计信息。
|
|
statown
|
统计信息收集后，保存历史统计信息的表的属主。
|
不使用。采用dba_tab_stats_history记录历史统计信息。
|
|
no_invalidate
|
是否使相关游标失效。
|
始终收集启用，为FALSE
|
|
Force
|
是否忽略锁定，强制收集。
|
FALSE
|
2. Oracle系统表的统计信息收集策略
1、
Oracle 9i、10g的数据库，不收集Oracle 自身对象统计信息
2、
Oracle 11g按照如下表格，收集Oracle自身对象的统计信息
| Oracle对象 | 收集策略说明 | 备注 |
|---|---|---|
| Dictionary统计信息 | 默认收集Dictionary的统计信息信息。 1，使用oracle的自动收集统计信息的job任务来收集，但需要设置prefs： dbms_stats.SET_GLOBAL_PREFS(pname=>'AUTOSTATS_TARGET',pvalue=>'ORACLE'); 2，修改自动收集的缺省的resoure plan 设置为空 exec dbms_scheduler.set_attribute('SYS.MONDAY_WINDOW','RESOURCE_PLAN',''); exec dbms_scheduler.set_attribute('SYS.TUESDAY_WINDOW','RESOURCE_PLAN',''); exec dbms_scheduler.set_attribute('SYS.WEDNESDAY_WINDOW','RESOURCE_PLAN',''); exec dbms_scheduler.set_attribute('SYS.THURSDAY_WINDOW','RESOURCE_PLAN',''); exec dbms_scheduler.set_attribute('SYS.FRIDAY_WINDOW','RESOURCE_PLAN',''); exec dbms_scheduler.set_attribute('SYS.SATURDAY_WINDOW','RESOURCE_PLAN',''); exec dbms_scheduler.set_attribute('SYS.SUNDAY_WINDOW','RESOURCE_PLAN',''); 3，做为11g数据库建库初始化的标准步骤。 | |
| Fixed Object统计信息 | 默认收集Fixed Object的统计信息。 通过定时监控： 上一次fix收集的时间 > 数据库创建时间（created_date） + 60天， 即建库后60天还未收集的库，报警出来，由运维dba安排变更，在非工作时间收集。 收集脚本如下： sqlplus '/as sysdba' SQL> alter system set aq_tm_processes = 1 scope = both; SQL> exec dbms_stats.gather_fixed_objects_stats; SQL> alter system set aq_tm_processes = 0 scope = both; | 建立报警机制 |
| SYSTEM统计信息 | 默认不收集系统统计信息。 若某些cpu 敏感的sql经过评估需要收集系统统计信息，请发变更，安排在生产典型负载工作时间段收集。 SQL> exec dbms_stats.gather_system_stats(gathering_mode=>'interval',interval=>30); SQL> select * from sys.aux_stats$; --说明：确认flag为0，且SREADTIM，MREADTIM，CPUSPEED和MBRC都存在对应值 |
3. 特殊变更场景下统计信息相关特性
1、
新增表字段后，收集不启用数据库表统计信息和字段统计信息变化
场景一：
| 变更步骤 | 表统计信息 | 字段统计信息 | 索引统计信息 |
|---|---|---|---|
| 1、新增空字段，并建立索引 | 不变 | 空 | 新增加的字段没有初始化数据，9i为0，10g及以上时为null) |
| 2、DML达到收集阀值，收集表统计信息 | 不变 | 最新(Oracle恢复备份统计信息的原则：如果原字段的统计信息为空，则用当前新值覆盖空值，包括0) | 不变 |
| 3、DML再次达到阀值，收集统计信息 | 不变 | 不变 | 不变 |
场景二：
| 变更步骤 | 表统计信息 | 字段统计信息 | 索引统计信息 |
|---|---|---|---|
| 1、已有字段新建索引 | 不变 | 不变 | 最新 |
| 2、DML达到收集阀值，收集表统计信息 | 不变 | 不变 | 不变 |
| 3、DML再次达到阀值，收集统计信息 | 不变 | 不变 | 不变 |
场景三：
| 变更步骤 | 表统计信息 | 字段统计信息 | 索引统计信息 |
|---|---|---|---|
| 1、新增字段初始化数据，并建立索引 | 不变 | 最新初始化值 | 最新 |
| 2、DML达到收集阀值，收集表统计信息 | 不变 | 不变 | 不变 |
| 3、DML再次达到阀值，收集统计信息 | 不变 | 不变 | 不变 |
以上测试结果说明：在收集不启用的数据库中，会出现表、字段和索引统计信息值所处的时间点不一致的情况，鉴于整个统计信息规范的收集最小粒度为表和索引（或同级别）对象，统计信息收集程序无需考虑是否清空新增字段的统计信息，而让新增字段按照最近一次的收集启用，老字段则恢复原有字段的旧统计信息。
2、
分区表新增、拆分、合并和删除分区的DML变化量和统计信息变化
| 数据库版本 | user_tab_modifications | 收集级别 | 变更导致的统计信息变化 | |||
|---|---|---|---|---|---|---|
| 表 | 发生改变的旧分区 | 新分区 | 表 | 分区 | ||
| split | 9i/10g | 不变 | 不变 | 无数据 | 无变化 | 被拆分的旧分区， 和拆分出的新分区统计信息都为空 |
| 数据库（DB） | 数据库（DB） | 数据库（DB） | 11g | delete增加 | delete增加 | |
| merge | 9i/10g | 不变 | 清除 | / | 无变化 | Merge后的分区统计信息为空 |
| 数据库（DB） | 数据库（DB） | 数据库（DB） | 11g | 数据库（DB） | 数据库（DB） | |
| add | 9i/10g/11g | 无变化 | / | 无数据 | 无变化 | 新分区统计信息为空 |
| drop | 9i | delete增加 | / | / | 无变化 | 保留的分区统计信息不变 |
| 数据库（DB） | 数据库（DB） | 数据库（DB） | 10g/11g | 不变 |
以上测试结果说明：拆分，合并，新增分区时，Oracle不会自动触发表级别的统计信息改变，但发生改变的分区和新产生的分区，统计信息都为空。因此，在进行分区表变更时，分区变化参照新建表的策略（一个分区类似一个表）处理。例如：合并两个有数据的分区，类似于新建表并初始化数据，因此是收集启用，并要做影响分析。拆分分区类似；新增分区如果不初始化数据，等价于新建空表。删除分区，剩余分区统计信息不变。
3、
MOVE操作时DML变化量和统计信息变化
| 表 | 索引 | ||||||
|---|---|---|---|---|---|---|---|
| user_tab_modifications | 统计信息 | created | last_ddl_time | timestamp | last_analyzed | ||
| 9i | 不变 | 清空 | 不变 | 改变 | 不变 | 清空 | status改变， 索引失效， 需要重建才可使用， 其他无变化 |
| 10g,11g | 不变 | 不变 | 不变 | 改变 | 不变 | 不变 |
结论：I）9i中做MOVE操作时，先备份统计信息再做MOVE操作，操作完毕后，收集新的统计信息，收集完毕后新的统计信息导出备用，重新导回旧的统计信息。
II）10g，11g做MOVE操作时，可以不处理表的统计信息，系统自动保持不变。
III）由于MOVE会改变索引的状态，要求进行索引索引重建，按照索引重建的方式对应处理。
4、
SHRINK操作时DML变化量和统计信息变化
| 表 | |||||||
|---|---|---|---|---|---|---|---|
| 操作 | 数据库 版本 | user_tab_modifications | 统计信息 | last_ddl_time | timestamp | created | last_analyzed |
| Shrink/ Shrink compact | 9i | 没有shrink功能 | |||||
| 10g/11g | 不变 | 不变 | 不变 | 不变 | 不变 | 不变 |
以上测试结果说明：这个操作不会对现有统计信息产生影响，并且不会触发自动收集程序。因此无需单独考虑统计信息的变化。
5、
REDEFINITION操作时DML变化量和统计信息变化
| 表 | 索引 | ||||||
|---|---|---|---|---|---|---|---|
| user_tab_modifi_ cations | 统计信息 | truncated | last_ddl _time | timestamp | last_ analyzed | 索引新建立 | |
| 9i,10g | 所有行insert | 空 | YES | 改变 | 改变 | 空 | |
| 11g | 所有行insert | 空 | NO | 不变 | 不变 | 空 |
Redefinition的原理是：将源表数据按预定规则插入到中间表，然后将两张表的名字互换,重定义后，表的created为最新且有数据插入，做为新建表而且初始化过数据处理。索引也是新建的，做为新建索引处理。结论：Redefinition 动作由于CREATED都是最新的，自动程序会按照新表初始化数据处理，都会重新收集并启用。由于重定义一般都需要在版本发布前期测试，因此要求此类操作都必须进行影响分析。注：目前仅允许以下几种情形的在线重定义，并且按照不同情形作不同处理。
| 版本 | 重定义类型 | 表和索引 | |
|---|---|---|---|
| 9i,10g 11g | 数据重整，结构不变 | 重定义前备份统计信息 | if !supportLists>1） endif>影响分析启用新统计信息可以提高性能时，重定义后，重新收集并启用新的统计信息。 if !supportLists>2） endif>影响分析不确定影响时，为确保稳定，重定义后收集统计信息导出，恢复原表和索引旧的统计信息。 |
| 分区表转普通表或普通表转分区表 | 无需备份统计信息（因结构变化，实施前备份的统计信息无法恢复，实施后备份的统计信息为空，恢复时会被新收集的统计信息覆盖） | 测试期间，要确定重定义后，表和索引统计信息改变，在新结构下SQL性能确实得到提升，才可以下发版本操作。 |
6、
REBUILD INDEX操作时统计信息变化
1） 直接DROP原有索引，然后重新create索引。此类操作方式等价于新建立索引，除非索引的结构变化需要，否则不建议采用删除再新建的方式。这种方式统计信息的策略和新建索引处理一致。
2） 采用REBUILD INDEX的方式，直接重建索引。
由于Rebuild索引不改变索引的结构，因此，除非影响分析确认必须重新启用新的统计信息，否则：重建索引前，备份旧的统计信息，重建索引后，收集新的统计信息，然后恢复旧的统计信息。10g和11g都会自动收集统计信息，因此无需重新编写收集脚本，只需备份新的统计信息，9i要求先收集重建后索引的统计信息，然后备份新统计信息，恢复旧的统计信息。
7、
TRUNCATE操作时DML变化量和统计信息变化
| dba_tab_modifications | 表 | ||||||
|---|---|---|---|---|---|---|---|
| DML量 | truncated | 统计信息 | last_ddl_time | timestamp | created | last_analyzed | |
| 9i | 不变 | 改变 | 不变 | 改变 | 不变 | 不变 | 不变 |
| 10g | 不变 | 改变 | 不变 | 改变 | 不变 | 不变 | 不变 |
| 11g | delete增加 | 改变 | 不变 | 改变 | 改变 | 不变 | 不变 |
测试truncate操作情况可以看到，11g倾向于将truncate既当作DDL，也当作DML变化来处理。因此，参照11g的特征，考虑truncate动作的统计信息收集策略为：只要对象发生Truncate操作，就必须重新收集并启用新的统计信息，类似于同结构重定义表。
4. 统计信息RBO和CBO的原理简述
1、
Oracle
优化器简介
Oracle优化器也被称为查询优化器，因为查询是影响数据库性能最主要的因素。不单单select语句是查询，DML语句带有where条件的也是需要做查询的。优化器应该是数据库引擎中最神秘也最吸引人的一部分，对性能的影响也是最大的，它的好坏直接决定数据库性能的好坏。 Oracle优化器在分析sql语句时，会依据自己内部的一些规则来决定一个sql的执行计划。
2、
基于规则的优化器RBO
在oracle较早的版本oracle 7以前，使用的优化器是RBO。顾名思义，这是一个基于规则的
优化器。ORACLE在基于规则的优化器中采用启发式的方法(Heuristic Approach)或规则(Rules)来生成执行计划。例如，如果一个查询的where条件(where clause)包含一个谓词(predicate，其实就是一个判断条件，如”=”, “>”, ”<”等)，而且该谓词上引用的列上有有效索引，那么优化器将使用索引访问这个表，而不考虑其它因素，如表中数据的多少、表中数据的易变性、索引的可选择性等。此时数据库中没有关于表与索引数据的统计性描述，如表中有多上行，每行的可选择性等。优化器也不考虑实例参数，如multi block i/o、可用排序内存的大小等，所以优化器有时就选择了次优化的计划作为真正的执行计划，导致系统性能不高。
RBO也会导致很多优化方法无法使用：
1. Hash join
2. 数据仓库中的 star transformation
3. Unnested sub query
4. Push predicate
1. Hash join
2. 数据仓库中的 star transformation
3. Unnested sub query
4. Push predicate
RBO有自己的执行路径顺序规则，RBO认为越往下执行的代价越大，即等级越低。在RBO生成执行计划时，如果它发现有等级高的执行路径可用，则肯定会使用等级高的路径，而不管任何其它影响性能的元素，即RBO通过下面的路径的等级决定执行路径的代价，执行路径的等级越高，则使用该执行路径的代价越小。
rbo的访问路径顺序如下：
RBO Path 1: Single Row by Rowid
RBO Path 2: Single Row by Cluster Join
RBO Path 3: Single Row by Hash Cluster Key with Unique or Primary Key
RBO Path 4: Single Row by Unique or Primary Key
RBO Path 5: Clustered Join
RBO Path 6: Hash Cluster Key
RBO Path 7: Indexed Cluster Key
RBO Path 8: Composite Index
RBO Path 9: Single-Column Indexes
RBO Path 10: Bounded Range Search on Indexed Columns
RBO Path 11: Unbounded Range Search on Indexed Columns
RBO Path 12: Sort Merge Join
RBO Path 13: MAX or MIN of Indexed Column
RBO Path 14: ORDER BY on Indexed Column
RBO Path 15: Full Table Scan
3、
基于成本的优化器CBO
可以看到，RBO中优先级最高的是Single Row by Rowid，这就意味着Oracle在生成SQL的执行计划时，如果有索引可以利用，即使全表扫描更有效率，也不会使用全表扫描，因为Full Table Scan 的优先级是最低的。这也是大家通常所说的RBO总是认为索引最优的原因。
可见，RBO对数据不敏感，数据的变化不会影响执行计划的生成，优化器不会考虑数据的多少、数据分布、索引的可选择性等等；而且RBO对于开发人员的要求也会相对比较高，在RBO模式，需要按照一定的规则来写SQL。
鉴于此，oracle推出了CBO，虽然在早期的版本中存在很多缺点，但是在oracle每个版本中都对它有增强，在平安常用的版本10.2.0.4以上已相对比较稳定了。数据库把一个代价引擎集成到数据库内核中，用来估计每个执行计划需要的代价，选择代价最小的一个执行计划作为这个sql的执行计划。
一个查询耗费的资源可以被分成3个基本组成部分：I/O代价、CPU代价、network代价。I/O代价是将数据从磁盘读入内存所需的代价。访问数据包括将数据文件中数据块的内容读入到SGA的数据高速缓存中，在一般情况下，该代价是处理一个查询所需要的最主要代价，所以我们在优化时，一个基本原则就是降低查询所产生的I/O总次数。CPU代价是处理在内存中数据所需要的代价，如一旦数据被读入内存，则我们在识别出我们需要的数据后，在这些数据上执行排序(sort)或连接(join)操作，这需要耗费CPU资源，常见的消耗CPU的操作：
1）Sum, Decode, Avg
2）Filter
3）Join
4）Sort
对于需要访问跨节点(即通常说的服务器)数据库上数据的查询来说，存在network代价，用来量化传输操作耗费的资源。查询远程表的查询或执行分布式连接的查询会在network代价方面花费比较大。
在使用CBO时，需要有表和索引的统计数据(分析数据)作为基础数据，有了这些数据，CBO才能为各个执行计划计算出相对准确的代价，从而使CBO选择最佳的执行计划。如果统计信息为空，oracle会做动态采样，会读取少量数据块来分析块中的数据分布来评估表中的数据分布以及索引的选择性等等信息。
主要是由optimizer_mode初始化参数决定Oracle选用的是哪种优化器模式。如下：
RULE：RBO优化器。
CHOOSE：根据实际情况，如果数据字典中包含被引用的表的统计数据，即引用的对象已经被分析，则就使用CBO优化器，否则为RBO优化器。
ALL_ROWS：是CBO优化器使用的第一种具体的优化方法，是以数据的吞吐量为主要目标，以便可以使用最少的资源完成语句。
FIRST_ROW：是优化器使用的第二种具体的优化方法，是以数据的响应时间为主要目标，以便快速查询出开始的几行数据。在使用first rows时，会更偏向于使用索引。
FIRST_ROWS_N : 是优化器使用的第三种具体的优化方法，让优化器选择一个能够把响应时间减到最小的查询执行计划，以迅速产生查询结果的前 n 行。该参数为ORACLE 9I新引入的。
注意，在使用这个参数时，由于某些bug会导致优化器选择次优化的执行计划。
从ORACLE V7以来，optimizer_mode参数的缺省设置应是"choose"，即如果对已分析的表查询的话选择CBO，否则选择RBO。在此种设置中，如果采用了CBO，则缺省为CBO中的all_rows模式，目前平安10g以上包括10g的库现在采用的都是ALL_ROWS的优化器。
需要注意的是，不管optimizer_mode参数如何设置，只要满足下面3个条件，就一定使用CBO：
1）如果使用Index Organized Tables(IOTs), 自动使用CBO。
2）Oracle 7.3以后，如果表上的Paralle degree option设为>1，则自动使用CBO, 而不管是否用rule hints.。
3）除rule以外的任何hints都将导致自动使用CBO来执行语句。
1217
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
