- Community Home
- >
- Servers and Operating Systems
- >
- Operating Systems
- >
- Operating System - HP-UX
- >
- Sizing the library cache
Operating System - HP-UX
1752292
Members
4458
Online
108786
Solutions
Forums
Categories
Company
Local Language
юдл
back
Forums
Discussions
Forums
- Data Protection and Retention
- Entry Storage Systems
- Legacy
- Midrange and Enterprise Storage
- Storage Networking
- HPE Nimble Storage
Discussions
Discussions
Discussions
Forums
Forums
Discussions
юдл
back
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
- BladeSystem Infrastructure and Application Solutions
- Appliance Servers
- Alpha Servers
- BackOffice Products
- Internet Products
- HPE 9000 and HPE e3000 Servers
- Networking
- Netservers
- Secure OS Software for Linux
- Server Management (Insight Manager 7)
- Windows Server 2003
- Operating System - Tru64 Unix
- ProLiant Deployment and Provisioning
- Linux-Based Community / Regional
- Microsoft System Center Integration
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Blogs
Information
Community
Resources
Community Language
Language
Forums
Blogs
Topic Options
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО02-02-2007 01:19 AM
тАО02-02-2007 01:19 AM
Sizing the library cache
Hi Gurus,
Can you please tell me how to size the library cache?
Regards
Subodh
Can you please tell me how to size the library cache?
Regards
Subodh
3 REPLIES 3
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО02-02-2007 04:59 PM
тАО02-02-2007 04:59 PM
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
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)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО02-02-2007 05:04 PM
тАО02-02-2007 05:04 PM
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
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)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО02-08-2007 09:58 AM
тАО02-08-2007 09:58 AM
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.
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
The opinions expressed above are the personal opinions of the authors, not of Hewlett Packard Enterprise. By using this site, you accept the Terms of Use and Rules of Participation.
News and Events
Support
© Copyright 2024 Hewlett Packard Enterprise Development LP