在報表資料庫的後臺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行。

這個問題也從另一個角度說明，進行跨版本遷移，測試工作的重要性。

一次ORA-4030問題診斷及解決（三）

相關文章