[Oracle-l] Instance Efficiency Percentages - Library Hit %: in AWR

Hi Sreejith,
Although I can't help you directly on the SQL you requested, but once you do start trend this phenomenon, here are a few things that will provide food for investigation and thought:

1) What the are Top 5 Wait Events when this Instance Efficiency Percentage is low?
2) Which SQL statements are being negatively affected by the Top 5 Wait Events? By how much?
3) Is there a problem on your database for the period of the snapshot(s)?
4) How does this snapshot's workload compare to a baseline snapshot?
5) Is there evidence of some sort of abnormal application workload?
6) Did you have more parsing than normal?

7) What caused the increased parsing (if applicable)?
8) Or was the increased parsing due to automatic memory management stealing from the shared pool to feed the db buffer cache in response to an increase I/O load?



Cheers,

Gaja

Gaja Krishna Vaidyanatha,
CEO & Founder, DBPerfMan LLC
http://www.dbperfman.com
http://www.dbcloudman.com

Phone - +1-650-743-6060
http://www.linkedin.com/in/gajakrishnavaidyanathaCo-author:Oracle Insights:Tales of the Oak Table - http://www.apress.com/book/bookDisplay.html?bID14
Co-author:Oracle Performance Tuning 101 - http://www.amazon.com/gp/reader/0072131454/ref=sib_dp_pt/102-6130796-4625766
Enabling Cloud Deployment & Management for Oracle Databases


________________________________
From: Sreejith S Nair <[email protected]>
To: [email protected]
Sent: Wednesday, April 4, 2012 2:39 AM
Subject: Instance Efficiency Percentages - Library Hit %: in AWR

Hi Friends,
In AWR under Instance Efficiency Percentages we see a Library Hit % and we
find it very low for one snapshot. I would like to know the trend for few
more days available in AWR snapshots
(dba_hist_snapshot,DBA_HIST_LIBRARYCACHE) using SQL Query.

Can some one please help with how this % is being calculated. What SQL can
I use to calculate this ? May be for a range of snapshot. Thanks in
Advance.

11.2.0.2.0 on Solairs 10 .

With Regards,
Sreejith

Hi Gaja ,

Many thanks for the hints.
Yes, we have experienced severe system slowness during this period. I have checked DBA hist active session history and I can see that for the problem time window 'Concurrency' wait ever came all of a sudden. This is happening only in one instance. Top foreground timed events being 'Cursor pin wait on X' and 'Library Cache Mutex-X' waits. A severity 1 SR is raised with Oracle for this.

There are bugs related to this 'mutex' , SQL high version count , which in fortunately I not available for our platform (Solaris x86 11.2.0.2)

Other recommendations were to increase sag , increase interval between memory resize operations ( yes we use AMM) , gathering dictionary and fixed object statistics.

The performance was really bad so that we see the Library Hit % being 1.7,0.8,-0.87 etc. I have analysed past trend which shows the % coming down.

The ADDM for problem window recommends some session holding a mutex lock and some others waiting for it which contributes 30% of the activity.
The SQLs reported were not so bad a these were critical ones which normally works not so bad.

The activities that I see in this time on database are the statistics collection jobs , which were running. Not sure whether this has contributed a pressure in Library cache.

After we increased sga it's getting to normal state. But due to a (possible) bug I feel this will come again.

Regards,
Sreejith
-- Sent from my iPhone

Hi Sreejith,
Just as I suspected! Given the 2 wait events that I you have mentioned - 'Cursor pin wait on X' and 'Library Cache Mutex-X' and the fact that AMM is enabled, I am pretty certain that the issue that you are facing is caused due to memory stealing. Did you check out the number or resize operations during the problem period? Rather than increasing the SGA, try to find a steady state for all the memory structures. Analyze and determine the minimum sizing for each of the memory structures (various BUFFER CACHES (if relevant), SHARED_POOL, LARGE_POOL and JAVA_POOL if used).Â

These are the times where I believe "too much automation" in database administration actually hurts. May be I am from the old school of Oracle Database Administration, but I still believe that SGA sizing  & PGA sizing needs to be a DBA's job and memory settings should be set manually (in most cases) by using the relevant "Memory Advisor Statistics". These statistics are clearly documented in every AWR report. I am a big fan of stability with the compromise of a little over-allocation of memory versus instability and inconsistent performance with automation. If you don't have the relevant bug fixes, I'd go the manual route, because the increase in SGA recommendation will be just a temporary Bandaid and it will be only a matter of time before it gives and falls apart.

Moral of the Story - In most tuning exercises, start with the Wait Events. Instance Efficiency Percentages (erstwhile known as Cache Hit Ratios) don't lead you to the root cause. Wait Events do! And when Wait Events don't low-level OS traces do (i.e. dtrace, truss, pstack...etc). May it is time for me to reiterate something from 2000 - Let Compulsive Tuning Disorder (CTD) not get the better of you...:)))
Â
Cheers,

Gaja

Gaja Krishna Vaidyanatha,
CEO & Founder, DBPerfMan LLC
http://www.dbperfman.com
http://www.dbcloudman.com

Phone - +1-650-743-6060
http://www.linkedin.com/in/gajakrishnavaidyanathaCo-author:Oracle Insights:Tales of the Oak Table - http://www.apress.com/book/bookDisplay.html?bID14
Co-author:Oracle Performance Tuning 101 - http://www.amazon.com/gp/reader/0072131454/ref=sib_dp_pt/102-6130796-4625766
Enabling Cloud Deployment & Management for Oracle Databases


________________________________
From: Sreejith S Nair <[email protected]>
To: "[email protected]" <[email protected]>
Cc: "[email protected]" <[email protected]>
Sent: Wednesday, April 4, 2012 6:33 PM
Subject: Re: Instance Efficiency Percentages - Library Hit %: in AWR

Hi Gaja ,

Many thanks for the hints.
Yes, we have experienced severe system slowness during this period.  I have checked DBA hist active session history and I can see that for the problem time window 'Concurrency' wait ever came all of a sudden. This is happening only in one instance. Top foreground timed events being 'Cursor pin wait on X' and 'Library Cache Mutex-X' waits. A severity 1 SR is raised with Oracle for this.

There are bugs related to this 'mutex' , SQL high version count , which in fortunately I not available for our platform (Solaris x86 11.2.0.2)

Other recommendations were to increase sag , increase interval between memory resize operations ( yes we use AMM) , gathering dictionary and fixed object statistics.

The performance was really bad so that we see the Library Hit % being 1.7,0.8,-0.87 etc. I have analysed past trend which shows the % coming down.

The ADDM for problem window recommends some session holding a mutex lock and some others waiting for it which contributes 30% of the activity.
The SQLs reported were not so bad a these were critical ones which normally works not so bad.

The activities that I see in this time on database are the statistics collection jobs , which were running. Not sure whether this has contributed a pressure in Library cache.

After we increased sga it's getting to normal state. But due to a (possible) bug I feel this will come again.

Regards,
Sreejith
-- Sent from my iPhone

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