Re: buffer advisor
- From: Seth Miller <sethmiller.sm@xxxxxxxxx>
- To: Ls Cheng <exriscer@xxxxxxxxx>
- Date: Sun, 7 Sep 2014 16:39:16 -0500
Ls,
Have you disabled cursor sharing? If not, it is likely that you are reusing
cursors, especially with simple predicates.
Seth Miller
On Sat, Sep 6, 2014 at 4:15 PM, Ls Cheng <exriscer@xxxxxxxxx> wrote:
> 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:
