Wednesday, May 23, 2007

Librarycache changes in 10g

Shared pool improvements:

Oracle introduced multiple shared pool latches (up to 7) in Oracle 9i, this is to reduce shared pool latch contention and the impact of shared pool fragmentation is localized to that latched pool only.

With Oracle 10g R2, some types of chunk (like sqlarea) allocation sizes are standardized to reduce shared pool fragmentation. Parent and child cursor heaps are allocated explicitly.

Since all the linked list structures (for buffer headers, etc) are mapped within the defined shared pool size, be sure to allocate more memory (may be 20%) to the shared pool.

Librarycache improvements:

Oracle implemented mutexes for some of the library cache latching operations, this will reduce the library cache latch gets tremendously if you are using bind variables in sql statements.

Implications of this change is greater scalability & availability, and increased shadow server process memory usage.