Oracle 9i數據庫的動態SGA特性探索 （2）

　　set lines 80;
　　set pages 999;
　　
　　column mydate heading 'Yr. Mo Dy Hr.' format a16
　　column c1 heading "execs" format 9,999,999
　　column c2 heading "Cache Misses|While Executing" format 9,999,999
　　column c3 heading "Library Cache|Miss Ratio" format 999.99999
　　
　　break on mydate skip 2;
　　
　　select 
　　to_char(snap_time,'yyyy-mm-dd HH24') mydate,
　　sum(new.pins-old.pins) c1,
　　sum(new.reloads-old.reloads) c2,
　　sum(new.reloads-old.reloads)/
　　sum(new.pins-old.pins) library_cache_miss_ratio
　　from 
　　stats$librarycache old,
　　stats$librarycache new,
　　stats$snapshot sn
　　where
　　new.snap_id = sn.snap_id
　　and
　　old.snap_id = new.snap_id-1
　　and
　　old.namespace = new.namespace
　　group by
　　to_char(snap_time,'yyyy-mm-dd HH24')
　　;
　　Cache Misses
　　Yr. Mo Dy Hr. execs While Executing LIBRARY_CACHE_MISS_RATIO
　　---------------- ---------- --------------- -----------------
　　2001-12-11 10 10,338 3 .00029
　　2001-12-12 10 182,477 134 .00073
　　2001-12-14 10 190,707 202 .00106
　　2001-12-16 10 2,803 11 .00392

　　RAM Area Too-small Condition Too-Large Condition 
　　Shared pool Library cache misses No misses 
　　Data buffer cache Hit ratio < 90% Hit ratio > 95% 
　　PGA aggregate high multi-pass executions 100% optimal executions