NOTES: Memory notification:library Cache Object loaded into SGA

NOTES: Memory notification:library Cache Object loaded into SGA

Some of these warning messages are found in the warning log:

Mon Nov 21 14:24:22 2011
Memory notification:library Cache Object loaded into SGA
Heap size 5800K exceeds notification threshold (2048K)
Details in trace file c:\oracle\product\10.2.0\admin\hy2003\udump\hy2003_ora_4372.trc
KGL object name:P CDM. Bin$c8iyfz9ts8orv9kud+hrsa==$0



On-Line search:

"Problem handling" Memory notification:library Cache Object loaded into SGA
1. Problem phenomena
The following warning message is found in the alert log during the daily patrol of the database.
...... Omitted......
Thu APR 15 22:06:31 2010
Memory notification:library Cache Object loaded into SGA
Heap size 3215K exceeds notification threshold (2048K)
KGL Object Name:select Towner, Tname, name, LENGTH, PRECISION, scale, TYPE, ISNULL, CONNAME, COLID, Intcolid,             Segcolid, comment$, default$, Dfltlen, ENABLED, DEFER, FLAGS, Colprop, Adtname, Adtowner, Charsetid, Charsetform, Fsprecision, Lfprecision, Charlen, Tflags, Typesyn, Colclass from SYS. Exu10coe WHERE Tobjid =: 1 ORDER by Colclass
Thu APR 15 22:06:55 2010
Memory notification:library Cache Object loaded into SGA
Heap size 5118K exceeds notification threshold (2048K)
Details in Trace File/home/oracle/oracle/product/10.2.0/db_1/admin/orcl/udump/orcl_ora_18031.trc
KGL Object Name:select Sys_xmlgen (VALUE (ku$), Xmlformat.createformat2 (' table_t ', ' 7 ')), ku$. Obj_num from SYS. Ku$_fhtable_view ku$ WHERE Not (Bitand (ku$. property,8192) =8192) and not Bitand (ku$. Schema_obj. flags,128)!=0 and ku$. Schema_obj.name=:name1 and ku$. Schema_obj. Owner_name=:schema2
Fri APR 16 05:00:07 2010
...... Omitted......

2. Cause of the problem
In the Oracle 10.2.0.1 version of the database, the implied parameter _kgl_large_heap_warning_threshold default value is 2M, which controls the size of objects loaded into memory, when the loaded object is greater than 2M. Will be prompted in the alert warning file. The default size of 2 m is relatively small, so this error message may be easily encountered in version 10.2.0.1. The default value of this parameter is adjusted to 50M in version 10.2.0.2.

1) Confirm that the version of the database where the warning appears is 10.2.0.1
[Email protected]> select * from V$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-prod
PL/SQL Release 10.2.0.1.0-production
CORE 10.2.0.1.0 Production
TNS for Linux:version 10.2.0.1.0-production
Nlsrtl Version 10.2.0.1.0-production

2) Confirm the default size of the implied parameter _kgl_large_heap_warning_threshold
[Email protected]> Select a.ksppinm NAME,B.KSPPSTVL value,a.ksppdesc description
2 from X$ksppi A,X$KSPPCV b
3 Where a.indx = B.indx
4 and A.KSPPINM = ' _kgl_large_heap_warning_threshold '
5/

NAME VALUE DESCRIPTION
--------------------------------- -------- --------------------------------------------------------------
_kgl_large_heap_warning_threshold 2097152 maximum heap size before KGL writes warnings to the alert log

[Email protected]> Select 2097152/1024/1024 MB from dual;

MB
----------
2

3. Problem-handling methods
Now that you know the cause of the problem, it's easy to deal with it. If you do not want to see these errors in the alert file, you can adjust the value of the implied parameter "_kgl_large_heap_warning_threshold" appropriately, or set it to "0".
1) Adjust the _kgl_large_heap_warning_threshold parameter size to 50M
[Email protected]> alter system set "_kgl_large_heap_warning_threshold" =52428800 scope=spfile;

System altered.

2) Restart the database

OK, this issue has been effectively handled.







Select A.KSPPINM NAME,B.KSPPSTVL value,a.ksppdesc description
From X$ksppi A,X$KSPPCV b
where a.indx = B.indx
and a.ksppinm = ' _kgl_large_heap_warning_threshold ';



NAME VALUE DESCRIPTION
----------------------- ------------------------------ -------------------------------
_kgl_large_heap_warning_threshold 2097152 maximum heap size before KGL writes warnings to the alert log

Modify the parameters as described:
System altered alter system set ' _kgl_large_heap_warning_threshold ' =8388608 scope=spfile;

Restart the database.



Note:

The default threshold in 10.2.0.1 is 2M.

So these messages could show up frequently in some application environments.

In 10.2.0.2, the threshold is increased to 50MB after regression tests, so this should is a reasonable and recommended V   Alue. If you continue to see the these warning messages in the alert log after applying 10.2.0.2 or higher, a SR may is in Orde R to investigate if is encountering a bug in the Shared Pool.

NOTES: Memory notification:library Cache Object loaded into SGA