WARNING: "Memory Notification: Library Cache Object Loaded Into SGA"

Dear Friends,

In one of our Databases, we have found that the WARNING message "Memory Notification: Library Cache Object Loaded Into SGA" is being written continuously in alert log file. As per the Oracle Support Documentation, These are warning messages generated in Oracle 10g (Release 2).

To prevent or avoid these messages being generated, we need to adjust the size of a hidden initialization parameter "_kgl_large_heap_warning_threshold" to a high value or zero. Note: In 10.2.0.2, the threshold is increased to 50MB after regression tests, so this should be a reasonable and recommended value.

To find out a hidden parameter value in the database.

SELECT a.ksppinm "Parameter",
b.ksppstvl "Session Value",
c.ksppstvl "Instance Value"
FROM x$ksppi a, x$ksppcv b, x$ksppsv c
WHERE a.indx = b.indx AND
a.indx = c.indx AND
a.ksppinm LIKE '%kgl_large_heap%';

Parameter Session Value Instance Value
------------------------------------ -------------------- --------------------_kgl_large_heap_warning_threshold 2097152 2097152

To change the hidden parameter value.

If you are using spfile, login as sysdba

$sqlplus /nolog
SQL> connect /as sysdba
SQL> alter system set "_kgl_large_heap_warning_threshold"=8388608 scope=spfile ;

System altered.

Or if you are using "old-style" init prameter file

Then, edit the initialization parameter file and add the below line.

_kgl_large_heap_warning_threshold=8388608

Shut down and re-start the database after changes

SQL> SHUTDOWN IMMEDIATE;

SQL> STARTUP;

SQL> show parameter _kgl_large_heap_warning_threshold

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------_kgl_large_heap_warning_threshold integer 8388608

Regards,
Sabdar Syed.