libcache.sql
-- -- Title : libcache.sql -- Description : To show library cache efficiency. -- -- Usage/Notes : -- -- Copyright : ABCdba.com 2008 -- SET TERMOUT OFF STORE SET save_env.sql REPLACE SET TERMOUT ON CLEAR BREAKS CLEAR COLUMNS CLEAR COMPUTES COL execu HEA " Executions " FOR 99999999999999999999 COL cache HEA " Cache Misses " FOR 99999999999999999999 COL ratio HEA " Percentage " FOR 9999999999999.999999 PROMPT PROMPT PROMPT If the percentage value is near to or greater than 1% add extra library cache PROMPT memory via the SHARED_POOL_SIZE and OPEN_CURSORS variables. PROMPT Also ensure identical SQL is used wherever possible to avoid reparsing. PROMPT PROMPT SELECT SUM(pins) execu, SUM(reloads) cache, ( ( SUM(reloads) / SUM(pins) ) * 100 ) ratio FROM v$librarycache / @save_env.sql host rm save_env.sql SET TERMOUT ON
page revision: 3, last edited: 22 Dec 2010 10:20
