Oracle heap size xxk exceeds notification threshold (2048 K) Solution

 

The database version is 10.2.0.1. When using expdp to export data, Alert Log displays the following information:

 

Weds 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_201720111026121403 ', 'kupc $ s_201720111026121403', 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 ');

 

Weds Oct 26 12:14:42 2011

Memory notification: Library cache object loaded into SGA

Heap size 5525 K exceedsnotification threshold (2048 K)

 

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 file content:

[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 5525 K exceeds icationicationthreshold (2048 K)

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-

Namespace = crsrflags = Ron/kghp/TIM/KEP/pn0/DBN/[10010044]

KKKK-dddd-llll = 0001-0001-0001 lock = n pin = 0 latch # = 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 = 92.16dd60

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 9425ddf8 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]

....

 

[Id330239.1] on Oracle MOS:

Memory notification: Library cache object loaded into SGA [ID 330239.1]

 

 

Cause of this prompt:

These are warning messages that shocould not cause the program responsible for these errorsto fail. They appear as a result of new event messaging mechanic 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.

-- This prompt means that when there are serious fragments in the memory, the process needs more time to find the free memory.

 

Solution:

In 10g we have anew uninitialized ented 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 2 m. So these messages cocould show up frequently in some applicationenvironments.

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

 

The library for executing the expdp is 10.2.0.1, so it is exactly the same as the above description. The solution is to modify the parameter parameters, but to upgrade the database version.

 

 

 

 

 

Bytes -------------------------------------------------------------------------------------------------------

All rights reserved. reprinted articles are allowed, but source addresses must be indicated by links. Otherwise, the documents will be held legally responsible!

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

WEAVER: http://weibo.com/tianlesoftware

Email: tianlesoftware@gmail.com

Skype: tianlesoftware

 

------- Add a group to describe the relationship between Oracle tablespace and data files in the remarks section. Otherwise, reject the application ----

Dba1 group: 62697716 (full); dba2 group: 62697977 (full) dba3 group: 62697850 (full)

Super DBA group: 63306533 (full); dba4 group: 83829929 (full) dba5 group: 142216823 (full)

Dba6 group: 158654907 (full) dba7 group: 69087192 (full) dba8 group: 172855474

DBA super group 2: 151508914 dba9 group: 102954821 chat group: 40132017 (full)