Re: Instance Efficiency Percentages - Library Hit %: in AWR
Date: Wed, 4 Apr 2012 23:16:26 -0700 (PDT)
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...:)))
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 <sreejithsna_at_gmail.com> To: "gajav_at_yahoo.com" <gajav_at_yahoo.com> Cc: "oracle-l_at_freelists.org" <oracle-l_at_freelists.org> 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 184.108.40.206)
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.
-- 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?
> Gaja Krishna Vaidyanatha,
> CEO & Founder, DBPerfMan LLC
> 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
> 220.127.116.11.0 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,
> ((Direct) +91 471 661 4707 ) +91 808 648 5523
> 8 www.ibsplc.com
> "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."