阿里巴巴数据库操作手册 (4)

表上面的依赖对象如果有存储过程或触发器等，逻辑是否需要相应调整。

l 是否涉及到同步。

同步中的表需要两地都要变更。涉及到erosa的要更新一下数据字典。Erosa是否需要重启取决于erosa版本。

l 是否要通知其他关联的部门。如DW, ASC或CRM等等。

有些表很多部门都用，需要沟通约定时间一起变更。如果有同步方案，同步方案的变更也要考虑。

四、 操作流程

1. 准备工作

a) 该表的数据量以及大小，以及数据变更量（按日/时/分/秒等） b) 该表的并发访问数，以及频率最高的几种sql的访问方式 2. 执行过程

以表T1 加字段 col2为例。T1的数据量非常大，访问频率很高。 a) 在sysdba下开启trigger禁用客户端自动编译功能。（可选）

Alter trigger SYS.ddl_trigger_for_database enable; b) 变更字段

以下加字段同编译失效对象连着执行。编译时先编译trigger再编译存储过程或package等 @conn zzzzzz/aaa

Alter table t1 modify col2 varchar2(50); Alter trigger trg_t1_search compile; @conn retl/rrr

Alter trigger trg_t1_sync compile; @conn bopsretl/bbb

Alter trigger trg_t1_sync compile; @conn zzzzzz/aaa

Alter procedure sp_test compile;

后面3个trigger的编译可以开三个窗口同时进行。 另开一个窗口，在admin用户下查看当前失效对象 @dbcheck

c) 禁用ddl trigger

Alter trigger SYS.ddl_trigger_for_database disable;

d) 涉及到同步的表，各个节点都变更，erosa重启取决于版本 –更新erosa数据字典 ./getDict.sh –erosa重启

./erctl stop ./erctl start 3. 验证方案

a) 验证sys下的trigger已经禁用

Select owner,trigger_name,status from dba_triggers where owner in (?SYS‘) and trigger_name=upper(? ddl_trigger_for_database ‘); b) 验证结构正确

Desc alibaba1949.t1

c) 验证无失效依赖对象 @dbcheck

d) 跟测试库比对结构

五、 核心对象风险

核心对象风险指的是业务上重要的表，并且数据量很大或表大小很大或并发访问数很高时，变更的潜在风险。前面已经阐述。

09-改动统计信息

一、 目的

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; /

搜索“diyifanwen.net”或“第一范文网”即可找到本站免费阅读全部范文。收藏本站方便下次阅读，第一范文网，提供最新幼儿教育阿里巴巴数据库操作手册 (4)全文阅读和word下载服务。