一次ORA-4030问题诊断及解决（三）

【 tulaoshi.com - 编程语言 】

　　在报表数据库的后台alert文件中发现了这个错误，简单记录一下问题的诊断和解决过程。数据库版本9204 for Solaris sparc64。

　　寻找产生问题的真正原因。

　　在第一篇文章中，定位了问题并且找到了解决方法;在第二篇文章中，找到了导致源数据库和目标数据库执行计划不同的原因。

　　但是到目前为止，还没有找到这个问题产生的真正原因。

　　首先理一下思路，根据第一篇文章的描述，产生ORA-4030问题的原因是由于一个大数据量的插入语句选择了一个十分糟糕的执行计划。而导致Oracle选择了这个执行计划的直接原因是由于列的统计信息出现了错误。而在第二篇文章中，可以确认由于源数据库的版本为9201，没有使用列统计信息中的DENSITY列，所以没有引发这个问题。而在目标数据库版本为9204，Oracle使用了统计信息列DENSITY的值，所以Oracle认为访问ORD_HIT_COMM表且通过ENABLE_FLAG列进行限制，只会返回1条记录，这就导致了Oracle产生了一个错误的离谱的执行计划。

　　现在的问题是什么导致了源数据库错误统计信息的产生。

　　这就需要检查源数据库数据和统计的

　　而且从920导入的统计信息可以看到，虽然直方图的统计信息被导入，但是USER_TAB_COLUMNS中的NUM_BUCKETS列的值为1，也就是说920中优化器根本不会去考虑直方图信息，而是直接通过NUM_DISTINCT和DENSITY的值来确定执行计划和返回记录数。

　　对于9201版本，Oracle都使用NUM_DISTINCT的值，也就避免了问题的产生。而在9204中，Oracle使用了DENSITY的值，而这个值并不是9204版本的DBMS_STATS包生成的统计信息，而是从10g环境中导入的，且这个值在10g的FREQUENCY类型的统计信息中已经改变了计算方法，使得计算结果比920环境中要小得多，从而导致了9204上错误执行计划的产生。

　　显然，整个问题完全是由于版本差异造成的。这个问题说明在将10g的表导入到920环境中，最好不要导入统计信息。

　　在导出阶段或在导入阶段设置STATISTICS = NONE，避免10g的统计信息导入到920环境中，在导入过程结束后，手工在920环境上重新收集统计信息。

　　一旦10g的统计信息被导入到920环境中，就必须重新收集统计信息：　

　　　SQLSELECT*FROMV$VERSION;
　　BANNER
　　----------------------------------------------------------------
　　Oracle9iEnterpriseEditionRelease9.2.0.4.0-64bitProductionPL/SQLRelease9.2.0.4.0-Production
　　CORE9.2.0.3.0Production
　　TNSforLinux:Version9.2.0.4.0-Production
　　NLSRTLVersion9.2.0.4.0-Production
　　SQLSELECTCOLUMN_NAME,NUM_DISTINCT,NUM_NULLS,DENSITY,NUM_BUCKETS
　　2FROMUSER_TAB_COLUMNS
　　3WHERETABLE_NAME='ORD_HIT_COMM'
　　4ANDCOLUMN_NAME='ENABLE_FLAG';
　　COLUMN_NAMENUM_DISTINCTNUM_NULLSDENSITYNUM_BUCKETS
　　-------------------------------------------------------------------------
　　ENABLE_FLAG202.8355E-071
　　SQLEXPLAINPLANFOR
　　2SELECT*FROMORD_HIT_COMMWHEREENABLE_FLAG='1';
　　已解释。
　　SQLSELECT*FROMTABLE(DBMS_XPLAN.DISPLAY);
　　PLAN_TABLE_OUTPUT
　　----------------------------------------------------------------------------------------
　　----------------------------------------------------------------------
　　|Id|Operation|Name|Rows|Bytes|Cost|
　　----------------------------------------------------------------------
　　|0|SELECTSTATEMENT||1|744|9817|
　　|*1|TABLEACCESSFULL|ORD_HIT_COMM|1|744|9817|
　　----------------------------------------------------------------------
　　PredicateInformation(identifiedbyoperationid):
　　---------------------------------------------------
　　1-filter("ORD_HIT_COMM"."ENABLE_FLAG"='1')
　　Note:cpucostingisoff

　　已选择14行。

　　这时10g的统计信息已经导入到9204环境中，如果忘记重新收集统计信息就会导致这个错误的产生：　

　　　SQLEXECDBMS_STATS.GATHER_TABLE_STATS(USER,'ORD_HIT_COMM')

　　PL/SQL 过程已成功完成。　　

　　　SQLSELECTCOLUMN_NAME,NUM_DISTINCT,NUM_NULLS,DENSITY,NUM_BUCKETS
　　2FROMUSER_TAB_COLUMNS
　　3WHERETABLE_NAME='ORD_HIT_COMM'
　　4ANDCOLUMN_NAME='ENABLE_FLAG';
　　COLUMN_NAMENUM_DISTINCTNUM_NULLSDENSITYNUM_BUCKETS
　　-------------------------------------------------------------------------
　　ENABLE_FLAG20.51
　　SQLEXPLAINPLANFOR
　　2SELECT*FROMORD_HIT_COMMWHEREENABLE_FLAG='1';
　　已解释。
　　SQLSELECT*FROMTABLE(DBMS_XPLAN.DISPLAY);
　　PLAN_TABLE_OUTPUT
　　---------------------------------------------------------------------------------
　　----------------------------------------------------------------------
　　|Id|Operation|Name|Rows|Bytes|Cost|
　　----------------------------------------------------------------------
　　|0|SELECTSTATEMENT||889K|632M|8932|
　　|*1|TABLEACCESSFULL|ORD_HIT_COMM|889K|632M|8932|
　　----------------------------------------------------------------------
　　PredicateInformation(identifiedbyoperationid):
　　---------------------------------------------------
　　1-filter("ORD_HIT_COMM"."ENABLE_FLAG"='1')
　　Note:cpucostingisoff

　　已选择14行。

　　这个问题也从另一个角度说明，进行跨版本迁移，测试工作的重要性。