Oracle Heap size XXK exceeds notification threshold (2048K) 解決方案

 

資料庫版本是10.2.0.1，使用EXPDP 導資料的時候，alert log 出現如下資訊：

 

Wed Oct 26 12:14:02 2011

The value (30) of MAXTRANS parameterignored.

kupprdp: master process DM00 started withpid=73, OS id=4731

        to execute - SYS.KUPM$MCP.MAIN('SYS_EXPORT_SCHEMA_01', 'SYS','KUPC$C_1_20111026121403', 'KUPC$S_1_20111026121403', 0);

kupprdp: worker process DW01 started withworker id=1, pid=77, OS id=4774

        to execute - SYS.KUPW$WORKER.MAIN('SYS_EXPORT_SCHEMA_01', 'SYS');

 

Wed Oct 26 12:14:42 2011

Memory Notification:Library Cache Object loaded into SGA

Heap size 5525K exceedsnotification threshold (2048K)

 

KGL object name :SELECT /*+rule*/SYS_XMLGEN(VALUE(KU$), XMLFORMAT.createFormat2('TABLE_T', '7')), KU$.OBJ_NUM,KU$.ANC_OBJ.NAME ,KU$.ANC_OBJ.OWNER_NAME ,KU$.ANC_OBJ.TYPE_NAME,KU$.BASE_OBJ.NAME ,KU$.BASE_OBJ.OWNER_NAME ,KU$.BASE_OBJ.TYPE_NAME ,KU$.SPARE1,KU$.XMLSCHEMACOLS ,KU$.SCHEMA_OBJ.NAME ,KU$.SCHEMA_OBJ.NAME ,'TABLE',KU$.PROPERTY ,KU$.SCHEMA_OBJ.OWNER_NAME ,KU$.TS_NAME ,KU$.TRIGFLAG FROMSYS.KU$_FHTABLE_VIEW KU$ WHERE NOT (BITAND (KU$.PROPERTY,8192)=8192) AND  NOT BITAND(KU$.SCHEMA_OBJ.FLAGS,128)!=0AND   KU$.OBJ_NU

 

Trace 檔案內容：

[xezf@localhost bdump]$ cat/home/oracle_app/admin/XEZF/bdump/xezf_dw01_4774.trc

/home/oracle_app/admin/XEZF/bdump/xezf_dw01_4774.trc

Oracle Database 10g Enterprise EditionRelease 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Miningoptions

ORACLE_HOME = /home/oracle_app

System name:    Linux

Node name:      localhost.localdomain

Release:        2.6.18-8.el5

Version:        #1 SMP Thu Mar 15 19:57:35 EDT 2007

Machine:        i686

Instance name: XEZF

Redo thread mounted by this instance: 1

Oracle process number: 77

Unix process pid: 4774, image:oracle@localhost.localdomain (DW01)

 

*** 2011-10-26 12:14:42.557

*** ACTION NAME:(SYS_EXPORT_SCHEMA_01)2011-10-26 12:14:42.529

*** MODULE NAME:(Data Pump Worker)2011-10-26 12:14:42.529

*** SERVICE NAME:(SYS$USERS) 2011-10-2612:14:42.529

*** SESSION ID:(106.8016) 2011-10-2612:14:42.529

Memory Notification: Library Cache Objectloaded into SGA

Heap size 5525K exceeds notificationthreshold (2048K)

LIBRARY OBJECT HANDLE: handle=902ccc68mutex=0x902ccd1c(0)

name=

SELECT /*+rule*/ SYS_XMLGEN(VALUE(KU$), XMLFORMAT.createFormat2('TABLE_T','7')), KU$.OBJ_NUM ,KU$.ANC_OBJ.NAME ,KU$.ANC_OBJ.OWNER_NAME,KU$.ANC_OBJ.TYPE_NAME ,KU$.BASE_OBJ.NAME ,KU$.BASE_OBJ.OWNER_NAME,KU$.BASE_OBJ.TYPE_NAME ,KU$.SPARE1 ,KU$.XMLSCHEMACOLS ,KU$.SCHEMA_OBJ.NAME,KU$.SCHEMA_OBJ.NAME ,'TABLE' ,KU$.PROPERTY ,KU$.SCHEMA_OBJ.OWNER_NAME,KU$.TS_NAME ,KU$.TRIGFLAG FROM SYS.KU$_FHTABLE_VIEW KU$ WHERE NOT (BITAND(KU$.PROPERTY,8192)=8192) AND  NOTBITAND(KU$.SCHEMA_OBJ.FLAGS,128)!=0 AND  KU$.OBJ_NU

hash=cee7e1d7e94b0fa1690a65161375b4d6 timestamp=10-26-201112:14:37

namespace=CRSRflags=RON/KGHP/TIM/KEP/PN0/DBN/[10010044]

kkkk-dddd-llll=0001-0001-0001 lock=N pin=0latch#=5 hpc=0002 hlc=0002

lwt=0x902cccc4[0x902cccc4,0x902cccc4]ltm=0x902ccccc[0x902ccccc,0x902ccccc]

pwt=0x902ccca8[0x902ccca8,0x902ccca8]ptm=0x902cccb0[0x902cccb0,0x902cccb0]

ref=0x902ccce4[0x902ccce4,0x902ccce4]lnd=0x902cccf0[0x962477a4,0x9681c18c]

 LIBRARY OBJECT: object=9487dd60

 type=CRSR flags=EXS[0001] pflags=[0000] status=VALD load=0

 CHILDREN: size=16

 child#    table reference   handle

 ------ -------- --------- --------

      0 9414ed1c  9414e9d0 90566b6c

 DATA BLOCKS:

 data#     heap  pointer   status pins change whr alloc(K) size(K)

 ----- -------- -------- --------- ---- ------ --- -------- --------

      0 9d323b60 9487ddf8 I/P/A/-/-    0 NONE  00      0.67     1.05

LIBRARY OBJECT HANDLE: handle=90566b6cmutex=0x90566c20(0)

namespace=CRSRflags=RON/KGHP/PN0/[10010000]

kkkk-dddd-llll=0000-0001-0000 lock=N pin=Xlatch#=5 hpc=0002 hlc=0002

lwt=0x90566bc8[0x90566bc8,0x90566bc8]ltm=0x90566bd0[0x90566bd0,0x90566bd0]

pwt=0x90566bac[0x90566bac,0x90566bac]ptm=0x90566bb4[0x90566bb4,0x90566bb4]

ref=0x90566be8[0x9414e9d0,0x9414e9d0]lnd=0x90566bf4[0x90566bf4,0x90566bf4]

 LIBRARY OBJECT: object=94fc1c00

 type=CRSR flags=EXS[0001] pflags=NST[0001] status=VALD load=0

 DEPENDENCIES: count=101 size=112

 dependency#    tablereference   handle position flags

 ----------- -------- --------- -------- -------- -------------------

           0 94fc1f70  9a943858 902ccb10      386 DEP[01]

           1 94fc1f70  9a94388c 9dadaffc      229 DEP[01]

           2 94fc1f70  9a9438c0 9dae1bac       80 DEP[01]

 ....

 

Oracle MOS 上[ID330239.1] 對此問題有說明：

Memory Notification: Library Cache Object Loaded Into Sga [ID 330239.1]

 

 

導致這個提示的原因：

These are warning messages that should not cause the program responsible for these errorsto fail.  They appear as a result of new event messaging mechanism andmemory manager in 10g Release 2.

The meaning is that the process is just spending a lot oftime in finding free memory extents during an allocate as the memory may beheavily fragmented.  Fragmentation in memory isimpossible to eliminate completely, however, continued messages of largeallocations in memory indicate there are tuning opportunities on the application.  

The messages donot imply that an ORA-4031 is about to happen. 

--這個提示意味著在記憶體中有嚴重的片段時，進程需要用更多的時間來尋找空餘的記憶體。

 

解決方案：

In 10g we have anew undocumented parameter that sets the KGL heap size warningthreshold.   This parameter was not present in 10gR1.  Warningsare written if heap size exceeds this threshold.
   

Set _kgl_large_heap_warning_threshold  to a reasonable high value or zero toprevent these warning messages. Value needs to be set in bytes. 

If you want to set this to 8192 (8192 * 1024) and are using an spfile: 

(logged in as "/ as sysdba")

SQL> alter system set "_kgl_large_heap_warning_threshold"=8388608 scope=spfile ; 

SQL> shutdown immediate 

SQL> startup 

If using an "old-style" init parameter, 

Edit the init parameter file and add 
_kgl_large_heap_warning_threshold=8388608

 

NOTE: 

The default threshold in 10.2.0.1 is 2M.   So these messages could show up frequently in some applicationenvironments.

In 10.2.0.2,  the threshold was increased to 50MB after regression tests, so this should be a reasonable andrecommended value. 

 

我執行expdp的庫是10.2.0.1的版本，所以正好和上面的說明一致。 解決方案一是修改參數參數，而是升級資料庫版本。

 

 

 

 

 

-------------------------------------------------------------------------------------------------------

著作權，文章允許轉載，但必須以連結方式註明源地址，否則追究法律責任!

Blog:     http://blog.csdn.net/tianlesoftware

Weibo: http://weibo.com/tianlesoftware

Email:   tianlesoftware@gmail.com

Skype: tianlesoftware

 

-------加群需要在備忘說明Oracle資料表空間和資料檔案的關係，否則拒絕申請----

DBA1 群：62697716(滿);   DBA2 群：62697977(滿)  DBA3 群：62697850(滿)  

DBA 超級群：63306533(滿);  DBA4 群：83829929(滿) DBA5群： 142216823(滿) 

DBA6 群：158654907(滿)   DBA7 群：69087192(滿)  DBA8 群：172855474

DBA 超級群2：151508914  DBA9群：102954821     聊天 群：40132017(滿)