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>
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@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> on
behalf of dmarc-noreply@xxxxxxxxxxxxx <dmarc-noreply@xxxxxxxxxxxxx>
Sent: 14 March 2019 21:22
To: 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.
--
//www.freelists.org/webpage/oracle-l
