第一次遇到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")

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/76065/viewspace-877186/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/76065/viewspace-877186/