cancel
Showing results for 
Search instead for 
Did you mean: 

Sizing the library cache

Sizing the library cache

Hi Gurus,
Can you please tell me how to size the library cache?

Regards
Subodh
3 REPLIES
Yogeeraj_1
Honored Contributor

Re: Sizing the library cache

hi Subodh,

You did not mention the version of Oracle you are using.

If you are using Oracle 10g, there is a new feature called "automatic memory management" allows the dba to reserve a pool of shared memory that is used to allocate the shared pool, the buffer cache, the java pool and the large pool.

In general, when the database needs to allocate a large object into the shared pool and cannot find contiguous space available, it will automatically increase the shared pool size using free space from other SGA structure.

Since the space allocation is automatically managed by Oracle, the probability of getting ora-4031 errors may be greatly reduced. Automatic Memory Management is enabled when the parameter SGA_TARGET is greater than zero and the current setting can be obtained querying the v$sga_dynamic_components view.

hope this helps!

kind regards
yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Yogeeraj_1
Honored Contributor

Re: Sizing the library cache

Hi again,

When considering the sizing of the Library cache, you will have to look into the Library Cache Hit Ratio.

The hit ratio helps to measure the usage of the shared pool based on how many times a SQL/PLSQL statement needed to be parsed instead of being reused. The following SQL statement help you to calculate the library cache hit ratio:

SELECT SUM(PINS) "EXECUTIONS",
SUM(RELOADS) "CACHE MISSES WHILE EXECUTING"
FROM V$LIBRARYCACHE;

If the ratio of misses to executions is more than 1%, then try to reduce the library cache misses by increasing the shared pool size.

Hope this helps too!

kind regards
yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Ben Dehner
Trusted Contributor

Re: Sizing the library cache

Like any Oracle configuration, "it depends".

The minimum amount of memory avoids ORA-4031 errors. It can be hard to tell if you have enough, because the memory will, for the most part, always be "in use", even if it is caching some useless object or statement that only got executed once a week ago.

First, what is your configuration? If you use shared servers, you'll want to have a large_pool configured as well. In 10G let Oracle balance between the shared and large pools.

If you are in a data warehouse environment, which supports lots of ad-hoc queries, you do NOT want to try to optimize the library cache hit, because you're only storing lots of unique statements that never get reused. It's also not good practice to use parameterized SQL in an OLAP environment.

In an OLTP environment, which (hopefully) has lots of parameterized SQL, you want your library cache hit 95% or better. If your Oracle-based OLTP does not used parameterized SQL, you're going to be in for some big performance problems.

It also depends on your application design and the number of sessions. An applications that makes heavy use of internal Oracle objects such as PL/SQL, views, triggers, etc. may need much more UGA memory per session than an application that only hits a few tables and indexes. We have one application here that is written almost entirely in PL/SQL; hundreds of package and procedures that hit thousands of views. I've seen it take over 5MB of UGA per session. We have a second application that has NO Pl/SQL, it only hits tables and indexes, and it uses about 500k/session. The UGA comes out of the shared pool or the large pool depending on the configuration, but its got to be there regardless. Querying into v$session_stats can show you how much UGA/session you're using.

There's probably scads of other things to look at, hope this helps.
Trust me, I know what I'm doing