Re: buffer advisor

I have usd sga_target but I always set a minimum value for db_cache_size because I have seen in a few databases (configured by other dbas) using sga_target and memory_target favours shared pool, I have seen extreme cases in 11.2.0.3 where sga_target is set to 4GB and buffer cache had a miserable 200MB assigned. There was a customer a couple of months ago that had a critical problem in production because ASMM decided to lower db_cache_size to 700MB (there were 3GB sga and managed by asmm), suddently a query started to caused heavy physical I/O, it was because the query needed to do full table scan for a table but since db_cache_size was big enough the full scan was buffer read instead of direct path read, when db_cache_size dropped to 700MB the table was considered a large table so it switched from buffer read to direct path reads, caused a disaster in production!

My question about buffer advisor is because in the past (many years ago) I followed advisor's advices a couple of times and after increasing the db_cache_size a few days after it was suggesting double the cache again when the workload did not change. I stopped using the advisors since then. I wonder how good they are these days?

Thanks

On Thu, Sep 4, 2014 at 11: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-l