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 
PROMPT cache 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

© copyright 2001-2014 ABCdba.com | all rights reserved