Re: Instance Efficiency Percentages - Library Hit %: in AWR

Sreejith:
 
For your original question, the following query can help you calculate the 
library cache hit ratio over the history AWR data:
 
WITH x AS (    
    SELECT DBID, INSTANCE_NUMBER, SNAP_ID, SUM(PINS) pins, SUM(RELOADS) reloads
    FROM DBA_HIST_LIBRARYCACHE
    WHERE DBID=? AND INSTANCE_NUMBER=? AND SNAP_ID BETWEEN ? AND ?
    GROUP BY DBID, INSTANCE_NUMBER, SNAP_ID
),
y AS (
    SELECT DBID, INSTANCE_NUMBER, 
    LAG(SNAP_ID, 1) OVER (PARTITION BY DBID, INSTANCE_NUMBER ORDER BY SNAP_ID) 
begin_snap_id, SNAP_ID end_snap_id,
    LAG(PINS, 1) OVER (PARTITION BY DBID, INSTANCE_NUMBER ORDER BY SNAP_ID) 
begin_pins, PINS end_pins,
    LAG(RELOADS, 1) OVER (PARTITION BY DBID, INSTANCE_NUMBER ORDER BY SNAP_ID) 
begin_reloads, RELOADS end_reloads
    FROM x
)
SELECT DBID, INSTANCE_NUMBER, begin_snap_id||'-'||end_snap_id snap_id,
    ROUND (((end_pins-begin_pins) - (begin_reloads-end_reloads))*100 
/(end_pins-begin_pins)) library_hit_ratio
FROM y
WHERE y.begin_snap_id IS NOT NULL
 
My opinion is that this metric itself doesn't tell much about database 
performance issue. Each database has its own characteristic and there is no 
absolute threshold value for this metrics.
I have seen some databases with low library hit ratio but still can perform its 
job duty well. 
 
As shown in DBspeed database performance tuning tool, I would like check for 
those dimensions (variables which play roles in database performance issue) for 
more tuning strategies, such as top SQL, top wait event, top session, hot 
objects, hot files, etc. Some examples in 
http://www.dbspeed.com/case_study.html ;
 
Regards,
Lei
DBspeed http://www.dbspeed.com/index.html  ;
data mining AWR & ASH data for database performance tuning
--
http://www.freelists.org/webpage/oracle-l


Other related posts: