Re: Instance Efficiency Percentages - Library Hit %: in AWR
From: Lei Zeng <leizeng2003_at_yahoo.com>
Date: Thu, 5 Apr 2012 17:19:45 -0700 (PDT)
Message-ID: <1333671585.77820.YahooMailNeo_at_web114717.mail.gq1.yahoo.com>
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
Date: Thu, 5 Apr 2012 17:19:45 -0700 (PDT)
Message-ID: <1333671585.77820.YahooMailNeo_at_web114717.mail.gq1.yahoo.com>
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-lReceived on Thu Apr 05 2012 - 19:19:45 CDT