Library cache object loaded into SGA

In alert log there are reported messages like this:

“Memory Notification: Library Cache Object loaded into SGA. Heap size exceeds notification threshold (51200K)”

These are warning messages that are not causing process failure. As large objects in the shared pool can potentially cause problems this warning threshold was implemented. Items/SQLs which allocate more space than this warning threshold, outputs a warning to the alert log. This warning is only to inform that a given heap object exceeds the defined threshold size and a trace file is generated. These are purely warning messages and have no impact on the database functionality, although they are designed to indicate possible tuning opportunities in customers’ applications.

This messages do not imply that an ORA-4031 is about to happen immediately unless the size of shared pool is very small. Warnings are written if heap size in shared pool exceeds the “_kgl_large_heap_warning_threshold”

Change this parameter (value needs to be set in bytes) requires database been restarted.

SQL> alter system set "_kgl_large_heap_warning_threshold"=943881020 scope=spfile ; 
SQL> shutdown immediate
SQL> startup

In 12c an enhancement to restrict the size of SGA base library cache heaps was introduced in order to avoid running out of space inside shared pool and hence ORA-4031 errors. With this fix, a new hidden parameter (_kgl_large_heap_assert_threshold) was also introduced. Its value represents the maximum heap size before raising the ORA-600 internal error like:

ORA-00600: internal error code, arguments: [KGL-heap-size-exceeded], [0x7FF91F844240], [6], [532279608], [], [], [], [], [], [], [], []

To check current value of the parameters, one can run the following query

select
nam.ksppinm NAME,
nam.ksppdesc DESCRIPTION,
val.KSPPSTVL
from
x$ksppi nam,
x$ksppsv val
where nam.indx = val.indx and nam.ksppinm like '%kgl_large_heap_%_threshold%';

The default value for _kgl_large_heap_assert_threshold can be too small for objects with a large number of partitions/subpartitions in which case the value should be increased to avoid the ORA-600 error.

For example setting:
“_kgl_large_heap_assert_threshold” = 1610612736 will raise the threshold for the assert to 1.5 GB.