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.

6 Comments:

At September 13, 2007 at 10:36:00 PM PDT, Blogger Aman Sharma said...

Hi sai,
Can you throw some more insight on these changes, especially about mutexes?
Cheers,
Aman....

 
At October 14, 2007 at 7:00:00 PM PDT, Blogger Saibabu Devabhaktuni said...

Hi Aman,

Sorry for the delayed reply, I was away. As I understand it, Oracle started using mutexes (instead of library cache pins) from Oracle 10.2 onwards. It can be turned off by setting "_kks_use_mutex_pin=false". Mutexes are about 5 times lighter than latches and hence it improves scalability and availability.

Thanks,
Sai.

 
At October 21, 2007 at 11:54:00 PM PDT, Blogger Aman Sharma said...

Hi sai,
Thanks alot for the reply.I am trying to find more information about Mutexes and their inside.Can you point me to some document on metalink or elsewhere where they are described in detail?I didnt get too much lucky myself in this search :(.
Cheers,
Aman....

 
At October 23, 2007 at 9:49:00 PM PDT, Blogger Saibabu Devabhaktuni said...

Aman,

I also couldn't find any good documents about Oracle's implementation of mutexes. But the following link will be a good starting point to learn more about mutexes.

http://en.wikipedia.org/wiki/Mutual_exclusion

Thanks,
Sai.

 
At October 24, 2007 at 2:45:00 AM PDT, Blogger Aman Sharma said...

hi Sai,
Thanks alot.Yes I am not able to find the docs also.But thanks a bunch for this link.
Cheers,
Aman....

 
At December 11, 2011 at 11:35:00 PM PST, Anonymous sap upgrade project said...

This post explains librarycache changes in 10g. Although I took time to understand it until I explore them in application. I find your post very helpful. I can see the effort in your work. I am looking forward to see your next post also. Thanks.

 

Post a Comment

Links to this post:

Create a Link

<< Home