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).

About these ads
11 comments
  1. venu said:

    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 ?

  2. Tanweer said:

    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

  3. Marwan said:

    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

  4. Stan Ziemba said:

    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

  5. Administrator said:

    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.

  6. 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

  7. A.Fernando Oliveira said:

    this alert log

    “Library Cache Object loaded into SGA”

    have this a Impact on database performance ?

  8. Administrator said:

    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.

    • shilpa said:

      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.

  9. Gus Spier said:

    Good information. Thanks.

    Regards,
    Gus

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 1,883 other followers

%d bloggers like this: