在报表数据库的后台alert文件中发现了这个错误 简单记录一下问题的诊断和解决过程 数据库版本 for Solaris sparc
寻找产生问题的真正原因
在第一篇文章中 定位了问题并且找到了解决方法;在第二篇文章中 找到了导致源数据库和目标数据库执行计划不同的原因
但是到目前为止 还没有找到这个问题产生的真正原因
首先理一下思路 根据第一篇文章的描述 产生ORA 问题的原因是由于一个大数据量的插入语句选择了一个十分糟糕的执行计划 而导致Oracle选择了这个执行计划的直接原因是由于列的统计信息出现了错误 而在第二篇文章中 可以确认由于源数据库的版本为 没有使用列统计信息中的DENSITY列 所以没有引发这个问题 而在目标数据库版本为 Oracle使用了统计信息列DENSITY的值 所以Oracle认为访问ORD_HIT_M表且通过ENABLE_FLAG列进行限制 只会返回 条记录 这就导致了Oracle产生了一个错误的离谱的执行计划
现在的问题是什么导致了源数据库错误统计信息的产生
这就需要检查源数据库数据和统计的来源 因为在源数据库 上直接收集统计信息 是不会得到这种DENSITY的
经过检查发现这个 的源数据库仍然不是数据的真正源头 而真正的来源数据库版本是
发现了这个信息 那么问题的产生就不奇怪了
看一下 上这张表的统计信息
SQL> SELECT COLUMN_NAME NUM_DISTINCT NUM_NULLS DENSITY NUM_BUCKETS HISTOGRAM FROM USER_TAB_COLUMNS WHERE TABLE_NAME = ORD_HIT_M AND COLUMN_NAME = ENABLE_FLAG ; COLUMN_NAME NUM_DISTINCT NUM_NULLS DENSITY NUM_BUCKETS HISTOGRAM ENABLE_FLAG E FREQUENCY
可以看到 在Oracle g使用了BUCKETS的设置 而且USER_TAB_COLUMNS添加了一个字段HISTOGRAM用来表示列的统计信息的类型
FREQUENCY类型和以往的HEIGHT BALANCED类似的列统计不同 使用FREQUENCY类型 Oracle会选择与NUM_DISTINCT相同数量的NUM_BUCKETS来进行直方图统计 而直方图统计信息方式和基于高度的统计信息是不同的 最关键的是 这种统计方式的DENSITY的结果和HEIGHT BALANCED的计算方式大不相同
因此在 g中 由于Oracle了解当前列的统计信息方式为FREQUENCY类型 因此可以根据直方图的信息得到正确的执行计划和返回记录数
SQL> SELECT COLUMN_NAME NUM_DISTINCT NUM_NULLS DENSITY NUM_BUCKETS HISTOGRAM FROM USER_TAB_COLUMNS WHERE TABLE_NAME = ORD_HIT_M AND COLUMN_NAME = ENABLE_FLAG ; COLUMN_NAME NUM_DISTINCT NUM_NULLS DENSITY NUM_BUCKETS HISTOGRAM ENABLE_FLAG E FREQUENCY row selected SQL> EXPLAIN PLAN FOR SELECT * FROM ORD_HIT_M WHERE ENABLE_FLAG = ;
Explained SQL> SELECT * FROM TABLE(DBMS_XPLAN DISPLAY); PLAN_TABLE_OUTPUT | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| | | SELECT STATEMENT | | K| M| ( )| | | TABLE ACCESS FULL| ORD_HIT_M | K| M| ( )| rows selected
但是如果将统计信息导入到 数据库中 就会存在严重的问题 由于 i的数据库中没有表示统计信息类型的HISTOGRAM列 因此即使是基于FREQUENCY类型的统计信息 也会被当作基于HEIGHT BALANCED类型的统计信息
而且从 导入的统计信息可以看到 虽然直方图的统计信息被导入 但是USER_TAB_COLUMNS中的NUM_BUCKETS列的值为 也就是说 中优化器根本不会去考虑直方图信息 而是直接通过NUM_DISTINCT和DENSITY的值来确定执行计划和返回记录数
对于 版本 Oracle都使用NUM_DISTINCT的值 也就避免了问题的产生 而在 中 Oracle使用了DENSITY的值 而这个值并不是 版本的DBMS_STATS包生成的统计信息 而是从 g环境中导入的 且这个值在 g的FREQUENCY类型的统计信息中已经改变了计算方法 使得计算结果比 环境中要小得多 从而导致了 上错误执行计划的产生
显然 整个问题完全是由于版本差异造成的 这个问题说明在将 g的表导入到 环境中 最好不要导入统计信息
在导出阶段或在导入阶段设置STATISTICS = NONE 避免 g的统计信息导入到 环境中 在导入过程结束后 手工在 环境上重新收集统计信息
一旦 g的统计信息被导入到 环境中 就必须重新收集统计信息
SQL> SELECT * FROM V$VERSION; BANNER Oracle i Enterprise Edition Release bit Production PL/SQL Release Production CORE Production TNS for Linux: Version Production NLSRTL Version Production SQL> SELECT COLUMN_NAME NUM_DISTINCT NUM_NULLS DENSITY NUM_BUCKETS FROM USER_TAB_COLUMNS WHERE TABLE_NAME = ORD_HIT_M AND COLUMN_NAME = ENABLE_FLAG ; COLUMN_NAME NUM_DISTINCT NUM_NULLS DENSITY NUM_BUCKETS ENABLE_FLAG E SQL> EXPLAIN PLAN FOR SELECT * FROM ORD_HIT_M WHERE ENABLE_FLAG = ; 已解释 SQL> SELECT * FROM TABLE(DBMS_XPLAN DISPLAY); PLAN_TABLE_OUTPUT | Id | Operation | Name | Rows | Bytes | Cost | | | SELECT STATEMENT | | | | | |* | TABLE ACCESS FULL | ORD_HIT_M | | | | Predicate Information (identified by operation id): filter( ORD_HIT_M ENABLE_FLAG = ) Note: cpu costing is off
已选择 行
这时 g的统计信息已经导入到 环境中 如果忘记重新收集统计信息就会导致这个错误的产生
SQL> EXEC DBMS_STATS GATHER_TABLE_STATS(USER ORD_HIT_M )
PL/SQL 过程已成功完成
SQL> SELECT COLUMN_NAME NUM_DISTINCT NUM_NULLS DENSITY NUM_BUCKETS FROM USER_TAB_COLUMNS WHERE TABLE_NAME = ORD_HIT_M AND COLUMN_NAME = ENABLE_FLAG ; COLUMN_NAME NUM_DISTINCT NUM_NULLS DENSITY NUM_BUCKETS ENABLE_FLAG SQL> EXPLAIN PLAN FOR SELECT * FROM ORD_HIT_M WHERE ENABLE_FLAG = ; 已解释 SQL> SELECT * FROM TABLE(DBMS_XPLAN DISPLAY); PLAN_TABLE_OUTPUT | Id | Operation | Name | Rows | Bytes | Cost | | | SELECT STATEMENT | | K| M| | |* | TABLE ACCESS FULL | ORD_HIT_M | K| M| | Predicate Information (identified by operation id): filter( ORD_HIT_M ENABLE_FLAG = ) Note: cpu costing is off
已选择 行
lishixinzhi/Article/program/Oracle/201311/18563