发表于: 2008.09.02 23:11
分类: ORACLE
出处: http://yangtingkun.itpub.net/post/468/470046
---------------------------------------------------------------
在报表数据库的后台alert文件中发现了这个错误，简单记录一下问题的诊断和解决过程。数据库版本9204 for Solaris sparc64。
寻找产生问题的真正原因。
一次ORA-4030问题诊断及解决（一）：http://yangtingkun.itpub.net/post/468/469922
一次ORA-4030问题诊断及解决（二）：http://yangtingkun.itpub.net/post/468/469965
在第一篇文章中，定位了问题并且找到了解决方法；在第二篇文章中，找到了导致源数据库和目标数据库执行计划不同的原因。
但是到目前为止，还没有找到这个问题产生的真正原因。
首先理一下思路，根据第一篇文章的描述，产生ORA-4030问题的原因是由于一个大数据量的插入语句选择了一个十分糟糕的执行计划。而导致Oracle选择了这个执行计划的直接原因是由于列的统计信息出现了错误。而在第二篇文章中，可以确认由于源数据库的版本为9201，没有使用列统计信息中的DENSITY列，所以没有引发这个问题。而在目标数据库版本为9204，Oracle使用了统计信息列DENSITY的值，所以Oracle认为访问ORD_HIT_COMM表且通过ENABLE_FLAG列进行限制，只会返回1条记录，这就导致了Oracle产生了一个错误的离谱的执行计划。
现在的问题是什么导致了源数据库错误统计信息的产生。
这就需要检查源数据库数据和统计的来源。因为在源数据库9201上直接收集统计信息，是不会得到这种DENSITY的。
经过检查发现这个9201的源数据库仍然不是数据的真正源头，而真正的来源数据库版本是10203。
发现了这个信息，那么问题的产生就不奇怪了。
看一下10203上这张表的统计信息：
SQL> SELECT COLUMN_NAME, NUM_DISTINCT, NUM_NULLS, DENSITY, NUM_BUCKETS, HISTOGRAM
2 FROM USER_TAB_COLUMNS
3 WHERE TABLE_NAME = 'ORD_HIT_COMM'
4 AND COLUMN_NAME = 'ENABLE_FLAG';
COLUMN_NAME NUM_DISTINCT NUM_NULLS DENSITY NUM_BUCKETS HISTOGRAM
------------ ------------ ---------- ---------- ----------- ---------------
ENABLE_FLAG 2 0 2.8355E-07 2 FREQUENCY
可以看到，在Oracle10g使用了BUCKETS的设置，而且USER_TAB_COLUMNS添加了一个字段HISTOGRAM用来表示列的统计信息的类型。
FREQUENCY类型和以往的HEIGHT BALANCED类似的列统计不同。使用FREQUENCY类型，Oracle会选择与NUM_DISTINCT相同数量的NUM_BUCKETS来进行直方图统计，而直方图统计信息方式和基于高度的统计信息是不同的。最关键的是，这种统计方式的DENSITY的结果和HEIGHT BALANCED的计算方式大不相同。
因此在10g中，由于Oracle了解当前列的统计信息方式为FREQUENCY类型，因此可以根据直方图的信息得到正确的执行计划和返回记录数：
SQL> SELECT COLUMN_NAME, NUM_DISTINCT, NUM_NULLS, DENSITY, NUM_BUCKETS, HISTOGRAM
2 FROM USER_TAB_COLUMNS
3 WHERE TABLE_NAME = 'ORD_HIT_COMM'
4 AND COLUMN_NAME = 'ENABLE_FLAG';
COLUMN_NAME NUM_DISTINCT NUM_NULLS DENSITY NUM_BUCKETS HISTOGRAM
------------ ------------ ---------- ---------- ----------- ---------------
ENABLE_FLAG 2 0 2.8355E-07 2 FREQUENCY
1 row selected.
SQL> EXPLAIN PLAN FOR
2 SELECT * FROM ORD_HIT_COMM WHERE ENABLE_FLAG = '1';
Explained.
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------
-----------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1691K| 1200M| 34103 (2)|
| 1 | TABLE ACCESS FULL| ORD_HIT_COMM | 1691K| 1200M| 34103 (2)|
-----------------------------------------------------------------------
11 rows selected.
但是如果将统计信息导入到920数据库中，就会存在严重的问题。由于9i的数据库中没有表示统计信息类型的HISTOGRAM列，因此即使是基于FREQUENCY类型的统计信息，也会被当作基于HEIGHT BALANCED类型的统计信息。
而且从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环境中，就必须重新收集统计信息：
SQL> SELECT * FROM V$VERSION;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production PL/SQL Release 9.2.0.4.0 - Production
CORE 9.2.0.3.0 Production
TNS for Linux: Version 9.2.0.4.0 - Production
NLSRTL Version 9.2.0.4.0 - Production
SQL> SELECT COLUMN_NAME, NUM_DISTINCT, NUM_NULLS, DENSITY, NUM_BUCKETS
2 FROM USER_TAB_COLUMNS
3 WHERE TABLE_NAME = 'ORD_HIT_COMM'
4 AND COLUMN_NAME = 'ENABLE_FLAG';
COLUMN_NAME NUM_DISTINCT NUM_NULLS DENSITY NUM_BUCKETS
------------------------------ ------------ ---------- ---------- -----------
ENABLE_FLAG 2 0 2.8355E-07 1
SQL> EXPLAIN PLAN FOR
2 SELECT * FROM ORD_HIT_COMM WHERE ENABLE_FLAG = '1';
已解释。
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------
----------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 744 | 9817 |
|* 1 | TABLE ACCESS FULL | ORD_HIT_COMM | 1 | 744 | 9817 |
----------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ORD_HIT_COMM"."ENABLE_FLAG"='1')
Note: cpu costing is off
已选择14行。
这时10g的统计信息已经导入到9204环境中，如果忘记重新收集统计信息就会导致这个错误的产生：
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'ORD_HIT_COMM')
PL/SQL 过程已成功完成。
SQL> SELECT COLUMN_NAME, NUM_DISTINCT, NUM_NULLS, DENSITY, NUM_BUCKETS
2 FROM USER_TAB_COLUMNS
3 WHERE TABLE_NAME = 'ORD_HIT_COMM'
4 AND COLUMN_NAME = 'ENABLE_FLAG';
COLUMN_NAME NUM_DISTINCT NUM_NULLS DENSITY NUM_BUCKETS
------------------------------ ------------ ---------- ---------- -----------
ENABLE_FLAG 2 0 .5 1
SQL> EXPLAIN PLAN FOR
2 SELECT * FROM ORD_HIT_COMM WHERE ENABLE_FLAG = '1';
已解释。
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------
----------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 889K| 632M| 8932 |
|* 1 | TABLE ACCESS FULL | ORD_HIT_COMM | 889K| 632M| 8932 |
----------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ORD_HIT_COMM"."ENABLE_FLAG"='1')
Note: cpu costing is off
已选择14行。
这个问题也从另一个角度说明，进行跨版本迁移，测试工作的重要性。
re: 一次ORA-4030问题诊断及解决（三） 
