SQL Script Library Cache

Updated: 24-Nov-2004
Version: 1.0

Description

Library Cache Misses indicate that the Shared Pool is not big enough to hold the shared SQL area for all concurrently open cursors. If you have no Library Cache misses (PINS = 0), you may get a small increase in performance by setting CURSOR_SPACE_FOR_TIME = TRUE which prevents ORACLE from deallocating a shared SQL area while an application cursor associated with it is open. 

For Multi-threaded server, add 1K to SHARED_POOL_SIZE per user.  

Parameters

SQL Source

REM Copyright (C) Think Forward.com 1998- 2005. All rights reserved. 

/*
    LIBRARY CACHE STATISTICS: 
 
    PINS - # of times an item in the library cache was executed
 
    RELOADS - # of library cache misses on execution steps 
 
    Hit Ratio PINS / RELOADS * 100 - should be near 100%
   
    Increase memory until RELOADS is near 0 but watch out for  
    Paging/swapping 
    To increase library cache, increase SHARED_POOL_SIZE 
 
    ** NOTE: Increasing SHARED_POOL_SIZE will increase the SGA size. 
 
*/ 

column Pins        Format 999,999,999,990 Heading 'Executions'
column PinHits     Format 999,999,999,990 Heading 'Executions'
column PHitRat     Format 990.99      Heading 'Hit |Ratio'
column Reloads     Format 999,999,999,990 Heading 'Misses'
column HitRat      Format 990.99      Heading 'Hit |Ratio'

spool libcache.lst

Select Sum(Pins) Pins, Sum(PinHits) PinHits, 
       ((Sum(PinHits) / Sum(Pins)) * 100) PHitRat,
       Sum(Reloads) Reloads,
       ((Sum(Pins) / (Sum(Pins) + Sum(Reloads))) * 100)  HitRat
From V$LibraryCache
/

spool off

Return to Index of SQL Scripts

Site Map

General Information : Home | Company Profile | Contact Us | Legal
Database Support : Proactive Support | Pay Per Incident Support
Expert Services : High Availability | Creative Solutions | Training
Build Services : Oracle Installations | Revive your Oracle Setup | Low Cost Development
Free Oracle Resources: DBA Scripts | Oracle Links