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

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_at_yahoo.com> 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_at_ibsplc.com>
> To: oracle-l_at_freelists.org
> 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_at_ibsplc.com
> 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."
>
>
>
>
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>

--
http://www.freelists.org/webpage/oracle-l