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

From: Gaja Krishna Vaidyanatha <>
Date: Wed, 4 Apr 2012 13:56:11 -0700 (PDT)
Message-ID: <>

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?




Gaja Krishna Vaidyanatha,
CEO & Founder, DBPerfMan LLC

Phone - +1-650-743-6060 Insights:Tales of the Oak Table - Co-author:Oracle Performance Tuning 101 - Enabling Cloud Deployment & Management for Oracle Databases

 From: Sreejith S Nair <> To:
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. on Solairs 10 .

With Regards,


Sreejith S Nair
Associate Systems Architect | AOS DBA Team IBS Software Services Private Ltd.
2nd Floor, IBS Campus, Technopark Campus, Trivandrum - 695 581, Kerala, India
((Direct) +91 471 661 4707 ) +91 808 648 5523 *

DISCLAIMER: "The information in this e-mail and any attachment is intended only for the person to whom it is addressed and may contain confidential and/or privileged material. If you have received this e-mail in error, kindly contact the sender and destroy all copies of the original communication. IBS makes no warranty, express or implied, nor guarantees the accuracy, adequacy or completeness of the information contained in this email or any attachment and is not liable for any errors, defects, omissions, viruses or for resultant loss or damage, if any, direct or indirect."


-- Received on Wed Apr 04 2012 - 15:56:11 CDT

Original text of this message