Tuning SGA with scripts (Part 3) - Library Cache
in Oracle,
“The library cache includes the shared SQL areas, private SQL areas (in the case of a multiple transaction server), PL/SQL procedures and packages, and control structures such as locks and library cache handles.
Shared SQL areas are accessible to all users, so the library cache is contained in the shared pool within the SGA.”
To tune Library Cache, we also need to calculate GetHit Ratio and PinHit Ratio.
(Script to “Calculate GetHit Ratio and PinHit Ratio of Library Cache”)
Also, reference the scripts offered at http://www.dba-oracle.com/concepts/pinned_objects.htm
There are two scripts useful there.
- Show Objects Which Should Be Kept
- use DBMS_SHARED_POOL.KEEP to retain object in library cache
Besides of above, Parse Ratio and Recursive Call Ratio also need to be paid attention to.
(Script for “Calculate Parse Ratio and Recursive Call Ratio of Library Cache”)
Last 3 posts in OraclePoint
- Creating Core Repository for Oracle Designer - July 24th, 2009
- Troubleshooting 01157 and 01110 errors while recreating Oracle 10g Undo tablespace - June 30th, 2009
- Using Crontab to Create Statspack Reports - April 23rd, 2009
Popularity: 5% [?]
Tags:none
January 11th, 2008 at 4:41 pm
Another helpful scripts for tuning library cache hit ratio is available at http://www.oracledba.co.uk/tips/choose.htm