I should add that there has been no memory pool resizing since over 2 months
Sr. Oracle DBA
From: Miller, Jay
Sent: Thursday, March 14, 2019 5:22 PM
Subject: Keep buffer cache question
Odd issue here. One of our apps reported slightly increased latency on a
heavily used database which started Monday evening and has been consistent
since. This is not large from a database perspective but the increase of
average response time from 1 to 3 microseconds has had a noticeable impact on
No execution plan changes, a slightly heavier load at peak times (up about 10%
from last week) but nothing that I would expect to have such an impact. We
still see the increased latency when the server is 90% idle and the load
average is 5 (32 cpus, 16 cores).
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
I checked with the app group and they have no explanation for why the app might
suddenly be querying blocks that aren't in cache whereas they weren't last week.
I am reluctantly considering adding the tables to the keep cache even though
they are huge (4-5G@). They are frequently accessed so I don't see any real
downside to this other than the huge amount of memory I would be reserving for
We are going to test this in a non-production environment tomorrow but my two
1. Is anyone aware of any problems this might cause? We have not used the
keep cache in the past.
2. Is there a way to preload the data once the db_keep_cache_size has
been set, the sga increased to accommodate it, and the table assigned to the
keep cache? A full table scan would usually bypass the buffer cache so I'm not
sure of the best approach here.
3. Any other ideas on what might cause this?
Sr. Oracle DBA