Re: Instance Efficiency Percentages - Library Hit %: in AWR
- From: Sreejith S Nair <sreejithsna@xxxxxxxxx>
- To: "gajav@xxxxxxxxx" <gajav@xxxxxxxxx>
- Date: Thu, 5 Apr 2012 07:03:23 +0530
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
On 05-Apr-2012, at 2:26 AM, Gaja Krishna Vaidyanatha <gajav@xxxxxxxxx> wrote:
> 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 <Sreejith.Sreekantan@xxxxxxxxxx>
> To: oracle-l@xxxxxxxxxxxxx
> 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
>
> --
> 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
> *sreejith.sreekantan@xxxxxxxxxx
> 8 www.ibsplc.com
>
>
>
>
>
> 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."
>
>
>
>
>
>
> --
> //www.freelists.org/webpage/oracle-l
>
> --
> //www.freelists.org/webpage/oracle-l
>
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
