Oracle 11g R2 – Will Inserting a Row Keep It in the Buffer Cache?

testbed:

create table foo as 
select add_months(sysdate, -(level-1)*3) as eventdate from dual connect by level<10;

select * from foo;
/*
EVENTDATE                 
------------------------- 
30-DEC-11 16.09.20        
30-SEP-11 16.09.20        
30-JUN-11 16.09.20        
30-MAR-11 16.09.20        
30-DEC-10 16.09.20        
30-SEP-10 16.09.20        
30-JUN-10 16.09.20        
30-MAR-10 16.09.20        
30-DEC-09 16.09.20        
*/

query:

select to_char(eventdate,'YYYY "Q"- Q') -- "Q" is treated as a literal due to the quotes
from foo
where eventdate>=add_months(trunc(sysdate, 'Q'), -12); -- trunc() gets first day of the Q
                                                       -- then add_months takes us back 4 Qs
/*
TO_CHAR(EVENTDATE,'YYYY"Q"-Q') 
------------------------------ 
2011 Q- 4                      
2011 Q- 3                      
2011 Q- 2                      
2011 Q- 1                      
2010 Q- 4            
*/

In any system, if you make the part of the system that is not the bottleneck more efficient, there is the potential to make the system as a whole less efficient. If, for example, your system's bottleneck is the CPU, making I/O more efficient by increasing the size of the buffer cache may end up causing many more threads to be waiting on the CPU which can make the system as a whole much slower as the operating system is now spending more time swapping between the competing thread, the CPU caches get overwhelmed, and you get the classic hockey stick graph of response time where once you get to the "knee", adding a bit more load adds a lot to the response time of the system.

If you have a system where I/O requests are highly uncorrelated, increasing the size of the cache could mean that Oracle will spend more time looking through the cache for a particular block before being forced to do a physical read after all. That's probably relatively unlikely in an OLTP application but it's possible in a data warehousing type system where various reports are asking for blocks that no other report will want to reuse.

Of course, whether this is actually the problem that they encountered in the past is still an open question. It is entirely possible that it was simply a matter of having a cold cache that needed to be warmed up or a perception problem where someone drew an incorrect conclusion from an earlier test or a poorly constructed test where, for example, someone inadvertently increased the size of the buffer cache at the expense of the PGA rather than at the expense of the operating system I/O cache. I tend to share your distrust of human recollections of the past that aren't corroborated by some nice AWR reports so I'm not claiming that either of the above explanations are likely merely that they are potential explanations if the human recollections are correct.