Re: buffer advisor
Date: Sun, 7 Sep 2014 18:05:46 -0700
Message-ID: <1410138346.30248.YahooMailNeo_at_web184306.mail.ne1.yahoo.com>
Hi Cheng/Mark,
At the risk of be called "old school", I am big believer that SGA sizing is a DBA's job whose understanding of the application, the workload and the "data/index block working set" obviates the unnecessary automation of ASMM and AMM. Believe me, I am big proponent of automating relevant DBA operations. I come from a time where we had to manually manage "rollback segments" (Oracle 6.0.31 Circa 1992) and I was so glad when Automatic Undo Management (AUM) was introduced in 8i/9i.
But I did not share the same levels of excitement when Automatic SGA Memory Management (ASMM) was introduced in 10g. Had to deal with tons of instability and many quirks like the one Mark mentioned. We used to observe constant flip-flops of memory resize operations due to "workload bursts" (parsing followed by physical I/O). There were shared pool and library cache mutex waits all over the place combined with significant I/O waits due to the cache getting constantly resized.
Last year, I was further convinced that I did NOT want to use AMM anymore, when even the so called "minimum granule sizes" were ignored for "immediate requests" even when SGA_TARGET=0. At that stage I was done with this. Way too much instability and unknowns for a feature that really does not do much, in my humble experience.
In my mind, things have gotten worse with Automatic Memory Management (AMM). We are unable to do what I consider as "rudimentary OS configuration" (like huge pages on Linux) with AMM (on my last check). As a rule, I'd never run any Oracle database on Linux without huge pages. For me that is a must have. It is similar to having "direct I/O" - another must have. I look at SGA & PGA memory allocation automation as something that is good for a novice Oracle DBA and who is trying to figure things out. Not something that I would hand over to Auto Oracle and run in production. Yet, I think this feature has made some of us believe that it is no longer our job to worry about SGA and PGA memory allocation and usage. And that is where I differ.
Are people using ASMM and AMM in production? Yes, of course they are. Does it work across the board for all application workloads? I seriously doubt it. At the end of the day, as I said, the cost of instability introduced by this automation FAR EXCEEDS the cost of memory over-allocation for SGA/PGA memory with manual configuration. This is where using the AWR Advisory Statistics is quintessential for sizing the SGA/PGA for stability and consistency in performance. And for me, stability and consistency in performance, trumps automation.
Cheers,
Gaja
Gaja Krishna Vaidyanatha,
CEO & Founder, DBPerfMan LLC
http://www.dbperfman.com
http://www.dbcloudman.com
Phone - +1 (650) 743-6060
LinkedIn - http://www.linkedin.com/in/gajakrishnavaidyanatha
Co-author: Oracle Insights:Tales of the Oak Table - http://www.apress.com/9781590593875 Primary Author: Oracle Performance Tuning 101 - http://www.amzn.com/0072131454 Enabling Exadata, Big Data and Cloud Deployment & Management for Oracle
From: Ls Cheng <exriscer_at_gmail.com>
To: Gaja Krishna Vaidyanatha <gajav_at_yahoo.com> Cc: Seth Miller <sethmiller.sm_at_gmail.com>; Chris Taylor <christopherdtaylor1994_at_gmail.com>; Oracle-L Freelists <oracle-l_at_freelists.org> Sent: Monday, September 8, 2014 2:46 AM
Subject: Re: buffer advisor
Hi Gaja
Thanks for the detailed opinion.
So you reckon the advisor works, glad to know that, I will test with a few databases when I have the opprtunity.
About AMM, I have a customer who's using AMM and it has 2200MB SGA, 64MB is set by AMM for db cache. If we look the advisory section of this database it says by doubling the cache will reduce almost 1/3 physical reads I wonder why AMM does not do it? The database has this 64MB db cache setting for weeks already! Also the database hasnt got exaggerate number of hard parses, there are roughly 160 parses per second and 8 hard parses per second so I dont understand why it keep a considerable sized shared pool (2GB). This is 11.2.0.3.7.
I am with you about having some extra hard parsing vs more physical reads.
Thanks
On Sun, Sep 7, 2014 at 8:41 AM, Gaja Krishna Vaidyanatha <gajav_at_yahoo.com> wrote:
Dear Oracle-L,
>
>
>This is an interesting discussion and from what has transpired thus far, it is pretty obvious that there are no draconian "rights or wrongs" here. Nevertheless, I wanted to share something relevant to the subject, as I have been dealing with this issue for over a year. Here it is:
>
>
>1) At the outset, I'd like to address Cheng's question on the usability of the db advisor. I find the data (not the recommendations) useful and here is how I use it. I look at the AWR section - "Advisory Statistics" and within that "Buffer Pool Advisory". I then compare the Estimated Phys Read & Est %DBTime for Rds with the Size Factor values (11.2.0.4). The Size Factor value of 1.0 is the current allocation and it is very useful to compare this to 1.5 (50% increase in allocation) or even 1.98/2.0 (doubling existing allocation). I have used this successfully from the 10g days, when it was first introduced. The data has consistently given me valid points on over/under allocation of the various pools. I then generate the perspective and recommendation on my own.
>
>
>Basically, the data allows me to determine at a high-level, whether increasing a given buffer pool will result in any significant benefit. On the flip side, it also allows me to make reasonable data-driven conclusions on whether "overallocation" has occurred for any of the pools. This is again done by comparing the values of 1.0 to say0.5. In this case, if the Phys Read and DB Time numbers are not significantly different, then it can be concluded that overallocation has in fact occurred and shrinking the pool and re-purposing the memory to another pool, can be done.
>
>
>***It is very important that the above method be utilized across multiple heavy-load time AWR reports. It is never a good idea to make any significant decision/change, based on a single data point from one AWR report. ***
>
>
>So for the aforementioned reasons, I keep db_cache_advice ON, as I find the data that it provides relevant and useful. I use the above to make my own decisions for resizing and ignore the recommendations from Advisor(in OEM), as I have found the recommendations to be unreliable. To be fair, the Advisor has no application knowledge or context and that is exactly where we as DBAs come into the picture.
>
>
>2) Like some of you, I have had to turn off ASMM completely, due to the nature of the application's SQL generation, in an effort to avoid unnecessary resize operations. I personally would rather have a static slightly over-sized shared pool, than MMAN doing its own thing and causing overhead on a very latency-sensitive application.
>
>
>I'm sure you folks know that just setting SGA_TARGET=0 does not turn the feature off, as ASMM will kick in, unbeknownst to you for "immediate" memory requests. We found this the hard way, as one day our db_cache was resized to automatically resized to 256MB and the shared_pool was also resized to 9GB, by these immediate requests. The original values for db_cache_size was 8GB and shared_pool_size was 512MB respectively. This was with SGA_TARGET set to 0.
>
>
>You can tell how much of immediate vs. deferred requests are being processed, by looking at v$sga_resize_ops. To turn off ASMM completely, you will have to set _memory_imm_mode_without_autosga to FALSE. When you do that, you potentially open yourself up for ORA-4031s, but if you size your shared pool correctly (using the Shared Pool Advisory in AWR), you can avoid the error for most part.
>
>
>Last year, at a customer project, ORA-4031s were occurring due to DDLs on interval-partitioned tables not being aged out (the last thing Oracle should cache in the shared pool are DDLs, but that was in fact happening). So, when a batch job generated a bunch of "alter table xxxx truncate partition for (yyyy);", the ORA-4031s showed up. We first reduced the number of sub-pools to 2 (_kghdsidx_count = 2), as the default number of sub-pools was shared_pool_size/512MB. The ORA-4031 occurred always on sub-pool #1. This was logged as a bug last year (Bug#16864042) on 11.2.0.3 and was supposed to be fixed in 11.2.0.4. Regrettably, the bug re-surfaced in the past few weeks on 11.2.0.4 and the new Bug#19461270 has been assigned.
>
>
>We are currently working with Oracle Development to resolve this, but in the interim we have set our shared_pool_size temporarily to 8GB and set _enable_shared_pool_durations=FALSE, basically having 1 sub-pool for all object types (instance, session, execution & cursor). As a precautionary measure, we also have a flush cron job that flushes the shared pool when free memory goes below a certain threshold.
>
>
>Non of this is pretty, but we'd rather pay a little extra in hard parsing, than run the risk of ASMM engaging in re-sizing operations when it shouldn't have. More often than not, the cost of additional hard parses are minuscule compared to valid/frequently used data and index blocks not present in the database buffer cache (due to resize operations). That fundamentally is the rationale behind this whole workaround. As soon as the bug related to caching DDLs is fixed, we will revert our shared_pool_size back to something reasonable (1GB) and we can turn off the flush cron job. I am an optimist and I hope that day will dawn soon :)
>
>
>Will keep you posted as things develop on our end!
>
>
>Cheers,
>
>
>Gaja
>
>
>
>
>Gaja Krishna Vaidyanatha,
>
>CEO & Founder, DBPerfMan LLC
>http://www.dbperfman.com
>http://www.dbcloudman.com
>
>Phone - +1 (650) 743-6060
>LinkedIn - http://www.linkedin.com/in/gajakrishnavaidyanatha
>
>Co-author: Oracle Insights:Tales of the Oak Table - http://www.apress.com/9781590593875
>Primary Author: Oracle Performance Tuning 101 - http://www.amzn.com/0072131454
>Enabling Exadata, Big Data and Cloud Deployment & Management for Oracle
>
>
>
>________________________________
> From: Ls Cheng <exriscer_at_gmail.com>
>To: Seth Miller <sethmiller.sm_at_gmail.com>
>Cc: Chris Taylor <christopherdtaylor1994_at_gmail.com>; Oracle-L Freelists <oracle-l_at_freelists.org>
>Sent: Sunday, September 7, 2014 2:45 AM
>Subject: Re: buffer advisor
>
>
>
>Hi Seth
>
>If an app does not use bind variables then no matter how shared pool is sized (we can set 1TB and hard parsing is still going on) hard parsing will always occur so have larger shared pool means hard parsing will not be reduced so I dont think ASMM should favor in such drastic way the shared pool. AMM and ASMM are features to simplify management so Oracle can tell customer that the database is easier to manage but I dont think experienced DBA should rely on them. Dont you think so?
>
>Thanks
>
>
>
>
>
>
>On Fri, Sep 5, 2014 at 7:22 PM, Seth Miller <sethmiller.sm_at_gmail.com> wrote:
>
>Chris,
>>
>>
>>I believe "a few situations" qualify as exceptions. The developers have to write these tools to work with the majority of cases, not the exceptions. This is why the DBA still has the ability to set a minimum size for each of the managed pools.
>>
>>
>>Hard parsing is extremely expensive so it doesn't surprise me that ASMM would favor the shared pool over the buffer cache, especially when you consider that there are alternatives to the buffer cache like the keep pool. There is no such in-memory alternative for the library cache.
>>
>>
>>Seth Miller
>>
>>
>>
>>On Thu, Sep 4, 2014 at 5:50 PM, Chris Taylor <christopherdtaylor1994_at_gmail.com> wrote:
>>
>>Seth,
>>>
>>>
>>>Really? I have run into a few situations where the advisor undersizes the buffer cache significantly in favor of the shared pool because of the workload of the application.
>>>I've got a db right _now_ that has a 128MB buffer cache and a 20GB shared pool that AMM resized because of the workload :)
>>>
>>>
>>>Obviously, the solution to this is to set floor (minimum) values for shared_pool_size and db_cache_size but it still amazes me that ASMM/AMM will significantly undersize the buffer cache when the workload uses a lot of SQL that isn't reuseable.
>>>
>>>
>>>And I clearly recognize that the workload is suboptimal (lots of SQL with literals and a few other things) that favor a large shared pool, and my only point is that it isn't uncommon for the automatic memory resizing to size the buffer cache to an absurd size :)
>>>
>>>
>>>Chris
>>>
>>>
>>>
>>>On Thu, Sep 4, 2014 at 4:56 PM, Seth Miller <sethmiller.sm_at_gmail.com> wrote:
>>>
>>>Ls,
>>>>I have found with very few exceptions that ASMM (SGA_TARGET) is very good at sizing the buffer cache. Have you tried this?
>>>>Seth Miller
>>>>
>>>>On Sep 4, 2014 3:38 PM, "Ls Cheng" <exriscer_at_gmail.com> wrote:
>>>>
>>>>Hi all
>>>>>
>>>>>Has anyone used buffer cache advisory in 10g or 11g to size a production buffer cache? If so how good is the advisor recommending the cache size? Did the recommended cache size meet the ohysical reads reduction goal?
>>>>>
>>>>>TIA
>>>>>
>>>>>
>>>>>
>>>
>>
>
>
>
-- http://www.freelists.org/webpage/oracle-lReceived on Mon Sep 08 2014 - 03:05:46 CEST