RE: Keep buffer cache question

  • From: "" <dmarc-noreply@xxxxxxxxxxxxx> (Redacted sender "Jay.Miller" for DMARC)
  • To: <chinaraliyev@xxxxxxxxx>, <jonathan@xxxxxxxxxxxxxxxxxx>
  • Date: Fri, 15 Mar 2019 14:24:53 +0000

Thank you all for you replies.

So sorry, I had been focusing on things that had changed from last week so I 
left off relevant information. By “a lot of physical i/o” I only meant that in 
the SQL ordered by Physical Reads (UnOptimized) section of the AWR reports 
those two queries are now showing up consistently during both slow and busy 
times whereas they are not showing up at all in any reports I run for last week.

In Segments by Physical Reads the tables in question appear in the same 
positions both last week and this week.  The tables are in the #3 and #4 
positions with the index used by one of the queries in the #5 position. But 
that hasn’t changed from the previous week. There is another table in #2 and 
that other table’s index in the #1 slot. Here is the information for the 2 
hours with names redacted:

<table owner>  <tablespace_name>      <index1 on table1>         INDEX  
11,169,159           54.84
<table owner>  <tablespace_name>      <table1>                              
TABLE    5,051,890             24.80
<table owner>  <tablespace_name>      <table2>                              
TABLE    880,373 4.32*
<table owner>  <tablespace_name>      <table3>                              
TABLE    595,843 2.93*
<table owner>  <tablespace_name>      <index2 on table 2>        INDEX   
520,725 2.56*

*tables and indexes involved in the 2 queries that started doing physical reads

With some sleep I’m wondering if I might get more bang for my buck by simply 
increasing the SGA and the db_cache_size.  The tables associated with these 
2queries are only responsible for about 10% of the physical reads.  Maybe 
ALTERING table 2 to cache?

I’ve been trying to get an answer on how the data is accessed (e.g., are 
certain accounts always queried at the same time of day, might this behavior be 
related to seasonal activity, etc.) but the developers don’t know and haven’t 
been able to find out so far.

Meanwhile as of this morning the query against table 2 is no longer showing up 
in the SQL ordered by Physical Reads (UnOptimized) section so this whole thing 
might be a red herring. I’ll look into some of the other listerv suggestions 
made today. Thank you all!

Jay Miller
Sr. Oracle DBA

From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On ;
Behalf Of Chinar Aliyev
Sent: Thursday, March 14, 2019 10:44 PM
To: Jonathan Lewis
Cc: Oracle-L Freelists
Subject: 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@xxxxxxxxxxxxxxxxxx<mailto:jonathan@xxxxxxxxxxxxxxxxxx>> 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

Jonathan Lewis

From: oracle-l-bounce@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx
<oracle-l-bounce@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx>> on behalf 
of dmarc-noreply@xxxxxxxxxxxxx<mailto:dmarc-noreply@xxxxxxxxxxxxx
Sent: 14 March 2019 21:22
To: oracle-l@xxxxxxxxxxxxx<mailto:oracle-l@xxxxxxxxxxxxx>
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.


Other related posts: