第一次遇到Oracle Bug 留念

原文發表在 http://www.itpub.net/showthread.php?s=&threadid=666743

上週五在RAC環境下做資料遷移前的imp測試
6程式並行做imp
imp log中報錯:
IMP-00003: ORACLE error 604 encountered
ORA-00604: error occurred at recursive SQL level 1
ORA-04031: unable to allocate 2216 bytes of shared memory ("shared pool","TAB$","KGLS

heap","KGLS ME
M BLOCK")
IMP-00058: ORACLE error 604 encountered
ORA-00604: error occurred at recursive SQL level 1
ORA-04031: unable to allocate 2216 bytes of shared memory ("shared pool","SEG$","KGLS

heap","KGLS ME
M BLOCK")

[@more@]

資料庫版本:Oracle 9.2.0.7
作業系統: AIX
主機: 6C12G
資料庫引數:
Sga_max 7G
db_cache_size 4G
shared_pool_size 2G
sort_area_size 8388608
Large_pool 256M
log_buffer 20480000
Processes 600
open_cursors 3000
dml_locks 10000
pga_aggregate_target 3G
當時第一反應: imp過程難道發生大量硬解析sql 使得share pool空間沾滿無法分配連續記憶體解析sql
但從imp程式中可以得到答案 我只是按分割槽並行倒入CFG_ATTR_MODIFY_HISTORY一張表
另外shared_pool_reserve池才被請求4次 空餘102083200byts
所以說明shared_pool還有連續空間可以用來hard parse sql.
SQL> show parameter share

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
shared_pool_reserved_size big integer 107374182
shared_pool_size big integer 2147483648

SQL> select free_space,requests,request_misses from v$shared_pool_reserved;

FREE_SPACE REQUESTS REQUEST_MISSES
---------- ---------- --------------
102083200 4 0

第二反應 imp倒入內部是大量相同的 INSERT /*+NESTED_TABLE_SET_REFS+*/ INTO TABLE ... ...

操作，所以需要解析此sql的連續記憶體空間大小應該差別不大，難道share pool中相同size的Bucket

被擠暴，如果真是這樣的解釋，那就只能說明 INSERT /*+NESTED_TABLE_SET_REFS+*/ INTO TABLE

操作沒有bind var 這是說不通的

於是第三反應是Oracle Bug
上Metalink搜尋 "ORA-04031"
其中一個answer是:
bug3046725

ORA-4031 due to shared_pool fragmented with high ges resources & enqueues
which has not fixed in 9.2, the problem depend on the work load and the application

work style.
Please disable cluster_database when you do the import and enable it again after the

import.

如釋重負due to shared_pool fragmented with high ges resources & enqueues 在9.2版本中還

沒有fixed.看來是由於我並行imp導致work load過高所致.
於是停掉一個節點 imp恢復正常.

第一次遇到Oracle Bug，來源於我對RAC環境的不熟悉，但也正因如此，也使我準備努力去理解RAC.

整個過程還要感謝Biti大師的電話指導.受益良多

附:
alert_xxx.log
Sat Nov 11 18:00:03 2006
Errors in file /home/db/oracle/admin/ecidb/bdump/ecidb1_smon_35178.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-04031: unable to allocate 2216 bytes of shared memory ("shared pool","SEG$","KGLS

heap","KGLS ME
M BLOCK")
Sat Nov 11 18:00:13 2006
Errors in file /home/db/oracle/admin/ecidb/bdump/ecidb1_smon_35178.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-04031: unable to allocate 2216 bytes of shared memory ("shared pool","SEG$","KGLS

heap","KGLS ME
M BLOCK")
Sat Nov 11 18:00:23 2006
Errors in file /home/db/oracle/admin/ecidb/bdump/ecidb1_smon_35178.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-04031: unable to allocate 2216 bytes of shared memory ("shared pool","SEG$","KGLS

heap","KGLS ME
M BLOCK")
Sat Nov 11 18:00:33 2006
Errors in file /home/db/oracle/admin/ecidb/bdump/ecidb1_smon_35178.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-04031: unable to allocate 2216 bytes of shared memory ("shared pool","SEG$","KGLS

heap","KGLS ME
M BLOCK")
Sat Nov 11 18:00:43 2006
Errors in file /home/db/oracle/admin/ecidb/bdump/ecidb1_smon_35178.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-04031: unable to allocate 2216 bytes of shared memory ("shared pool","SEG$","KGLS

heap","KGLS ME
M BLOCK")