Re: buffer advisor
- From: Ls Cheng <exriscer@xxxxxxxxx>
- To: Seth Miller <sethmiller.sm@xxxxxxxxx>
- Date: Sat, 6 Sep 2014 23:15:03 +0200
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@xxxxxxxxx> 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@xxxxxxxxx> 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@xxxxxxxxx>
>> 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@xxxxxxxxx> 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
>>>>
>>>>
>>>>
>>
>
Other related posts:
