Memory Notification: Library Cache Object loaded into SGA

alert_SID.log Error.

Memory Notification: Library Cache Object loaded into SGA
Heap size 2550K exceeds notification threshold (2048K)
KGL object name :select t1.owner, t1.name, t1.queue_table, t1.queue_type, t1.max_retries, t1.retry_delay, t1.retention, t1.user_comment, t2.type, t2.object_type from all_queues t1, all_queue_tables t2 where t1.owner=:1 and t1.name=:2 and t2.owner=:3 and t1.queue_table=t2.queue_table
Sat Nov 8 03:36:15 2008
Memory Notification: Library Cache Object loaded into SGA
Heap size 2546K exceeds notification threshold (2048K)
Details in trace file /u01/11i/oracle/upgdb/10.2.0.1/admin/upg_zeus/udump/upg_ora_268966.trc


ORA-00060: Deadlock detected. More info in file /u01/11i/oracle/upgdb/10.2.0.1/admin/upg_zeus/udump/upg_ora_302372.trc.
Fri Oct 31 15:08:04 2008
ORA-00060: Deadlock detected. More info in file /u01/11i/oracle/upgdb/10.2.0.1/admin/upg_zeus/udump/upg_ora_129790.trc.
Fri Oct 31 15:08:35 2008
ORA-00060: Deadlock detected. More info in file /u01/11i/oracle/upgdb/10.2.0.1/admin/upg_zeus/udump/upg_ora_66976.trc.
Fri Oct 31 15:08:35 2008
ORA-00060: Deadlock detected. More info in file /u01/11i/oracle/upgdb/10.2.0.1/admin/upg_zeus/udump/upg_ora_196730.trc.
Fri Oct 31 15:08:37 2008
ORA-00060: Deadlock detected. More info in file /u01/11i/oracle/upgdb/10.2.0.1/admin/upg_zeus/udump/upg_ora_129790.trc.


Solutions:

SQL> SELECT a.ksppinm "Parameter",
2 b.ksppstvl "Session Value",
3 c.ksppstvl "Instance Value"
4 FROM x$ksppi a, x$ksppcv b, x$ksppsv c
5 WHERE a.indx = b.indx AND
6 a.indx = c.indx AND
7 a.ksppinm LIKE '%kgl_large_heap%';

Parameter Session Value Instance Value
--------------------------------------------------------------------------------

_kgl_large_heap_warning_threshold 2097152 2097152


Above parameter is hidden parameter. We have to change that hidden parameter.

If we using spfile, just login sql and change that parameter or Bounce the database and change the initSID.ora file.

$ sqlplus "/as sysdba"

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

System altered.

OR

Bounce the DB and edit the init parameter file and startup.

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