Re: Keep buffer cache question

Hi Jay.
What does it mean 'a lot if physical i/o' , which wait events are observed for the problematic SQL statement.
Have you compared average wait times of the wait events also? . For example, If the sessions are waiting for DB FILE SEQUENTIAL READ you can compare average wait time of current with wait that of previous week. If average wait time(specially for this wait ) has been increased then there is a probability some hardware/disk configurations have done. For example, moved to RAID-6. Could you confirm it? (Asking by sys admin team you can check it).

On Fri, Mar 15, 2019, 02:24 Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk> wrote:

>
> Did any of the relevant segments appear in "Segments by physical reads" ?
> You need to find out whether it's the indexes or the tables.
> As a basic guideline you almost certainly WON'T beat the LRU algorithm by
> setting up the keep cache.
>
> A change like this can happen simply because objects (and particularly
> indexes) get bigger over time as the data sizes grow. You can get into the
> position (especially when the number of queries grows) that a query reads a
> leaf block into memory but causes another leaf block from the same index to
> be flushed, and a few seconds later some other query wants the leaf block
> that was flushed.
>
> Consider an index on (customer_id, order_date) - when the data is small
> index entries for "the most recent order for customer X" may find two or
> three customers in the same leaf block, so one query benefits from the
> caching caused by another. As the data gets larger you get to a position
> where every customer has several leaf blocks and every query for "the most
> recent order for customer X" has to read a different leaf block and queries
> don't get any benefit from each other. At this point your only solution is
> to increase the buffer cache to ensure that one block for each customer can
> stay in memory long enough for its next usage.
>
>
> If you do try implementing a KEEP pool, don't forget to check for the
> effects of read-consistency. Depending how CR blocks are created you may
> find them as copies created in the default cache, or the recycle cache (if
> you have one), and some (because of operation "copy current to new buffer")
> will be in the keep cache. Sizing the keep cache to keep the blocks AND
> the CR blocks can be problematic. You'll have to check what actually
> happens because the behaviour changes with version of Oracle and I haven't
> checked it recently
>
> Regards
> Jonathan Lewis
>
>
>
>
> ________________________________________
> From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on
> behalf of dmarc-noreply_at_freelists.org <dmarc-noreply_at_freelists.org>
> Sent: 14 March 2019 21:22
> To: oracle-l_at_freelists.org
> Subject: Keep buffer cache question
>
> In doing an AWR report comparison for comparable times one major
> difference I saw was that 2 frequently run queries were suddenly doing a
> lot of physical i/o. For a comparable 2 hour period they went from 1.5
> million to 1.8 million executions but physical reads increased from 0 to
> 1.2 million. I sampled a few other random times and this was consistent.
> The queries are both doing index access. One is an index range scan and the
> other a unique scan against the primary key.
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

--
http://www.freelists.org/webpage/oracle-l