Re: Keep buffer cache question

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Thu, 14 Mar 2019 22:23:16 +0000
Message-ID: <CWXP265MB0406B8C4BED00D46C71E40FBA54B0_at_CWXP265MB0406.GBRP265.PROD.OUTLOOK.COM>


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
Received on Thu Mar 14 2019 - 23:23:16 CET

Original text of this message