10.2.0.1 Annoying message "Memory Notification: Library Cache Object loaded into SGA"
In the 10.2.0.1 version of the oracle database a new heap checking mechanism, together with a new messaging system is introduced. This new mechanism reports memory allocations above a threshold in the alert.log, together with a tracefile in the udump directory.
In certain situations it can be handy to know if large allocations are being done in the sga heap (shared pool), but only to troubleshoot memory allocation problems (which eventually will appear as the infamous ORA-4031). For normal day to day work, I doubt the effectivity of these messages.
The default limit is set at 2048K. It appears normal usage can result in messages from the memory manager. During my last installation (with all options set default, all options installed), this resulted in 125 messages from the memory manager.
Oracle gives a solution on metalink in note 330239.1:
Set _kgl_large_heap_warning_threshold to a “reasonable high” value. This parameter is set in bytes, and oracle recommends the value 8388608 (8192K).
can we change this parameter oline? without restarting,
can you give us the syntax of changing this parameter.
how to check what value is set ?
I tried searching for this parameter but was not able to locate it. How do I change the parameter so that this message “Memory Notification: Library Cache Object loaded into SGA ” stops showing up on the alert log.
Thanks
SQL>alter system set “_kgl_large_heap_warning_threshold”=8388608 scope=spfile ;
** oracle recommends the value of 8 MB which the value in the command(bytes)
** you will have to restart the instance for the changes to take effect since the parameter is STATIC
I set this as you indicated to 8388608 and all seemed fine until I upgraded 10.2.0.1 to 10.2.0.3. During the process somewhere I got this and otheres:
Memory Notification: Library Cache Object loaded into SGA
Heap size 8535K exceeds notification threshold (8192K)
Details in trace file /u03/oracle/admin/ORACLE_SID/udump/ORACLE_SID_ora_3882.trc
KGL object name :ALTER VIEW “SYS”.”KU$_PHTABLE_VIEW” COMPILE
So, is still a valid issue that Oracle needs to take care of.
R,
Stan
Well, it is not really an issue, because nothing breaks. It’s a notification service to report shared pool memory allocations above a certain threshold.
The 8MB oracle advises is just an amount which should reduce the number of notifications in most cases. I think it’s justifiable to have some allocations of more than 8MB during a database upgrade.
I am getting the above error notification message.
But i am also getting some database connectivity problem every 3 days after i restart my web application serverAre these two connected.
the error i get is
SQL Driver Details SQLState: null
Code: 17008
this alert log
“Library Cache Object loaded into SGA”
have this a Impact on database performance ?
No. This message is generated because a space allocation in the shared pool exceeded a certain threshold (_kgl_large_heap_warning_threshold). The resolution oracle gives (as you can read in the article) is to raise the threshold.
is it make database slow and locks on it..
Not really. The undocumented parameter mentioned above just raises the limit before Oracle notifies you about a large allocation.
Of course every allocation in a shared memory region needs to make sure to play nice with the other sessions, so some form of locking is involved. But that’s a fact with shared components.
Good information. Thanks.
Regards,
Gus