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 LLChttp://www.dbperfman.comhttp://www.dbcloudman.com
Phone - +1-650-743-6060http://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
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
18.104.22.168.0 on Solairs 10 .
Sreejith S Nair
Associate Systems Architect | AOS DBA Team
IBS Software Services Private Ltd.
2nd Floor, IBS Campus, Technopark Campus, Trivandrum - 695 581, Kerala,
((Direct) +91 471 661 4707 ) +91 808 648 5523
"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."