1

Oracle by default caching the query results and function results(Buffer cache).

I have noticed this with AutoTrace utility, where the physical reads are huge on first execution, but from next execution onward it reduced dramatically.

Then what is the importance of query result cache, function result cache?

I have also noticed that the buffer cache does not gets invalidated even when I try to update physical table. But still I noticed physical reads are 0.

Could some one help on this to understand better?

2

Oracle by default does not cache query and function result, but both cache do exist.

http://www.oracle.com/technetwork/articles/sql/11g-caching-pooling-088320.html

The importance of these: there is more than caching blocks. Even if you process everything in memory, why process the same requests again and again, if you already know the answer?

What you are referring to when mentioning autotrace and physical reads, is the buffer cache.

"I have also noticed that the buffer cache does not gets invalidated even when I try to restart the server."

This is a false conclusion. When you restart the database, the buffer cache will be empty again. Post your test that confirms the opposite.

  • Its my mistake. I have not restarted the server. Somehow, i started re-started the other instance. I changed my question – madhu Apr 2 '14 at 17:14
1

There is no "result cache". There is the database buffer cache. Oracle stores recently-accessed data blocks in memory so that it doesn't have to go to disk every time for hot blocks - those chunks of data which are most frequently accessed. Function and query results are not stored, only the data blocks which are in demand. See here:

http://docs.oracle.com/cd/B28359_01/server.111/b28318/memory.htm#CNCPT1223

The first time an Oracle Database user process requires a particular piece of data, it searches for the data in the database buffer cache. If the process finds the data already in the cache (a cache hit), it can read the data directly from memory. If the process cannot find the data in the cache (a cache miss), it must copy the data block from a datafile on disk into a buffer in the cache before accessing the data. Accessing data through a cache hit is faster than data access through a cache miss.

So if you are seeing 0 physical reads for the first run of a particular SQL after a restart, this means some other SQL has already loaded the relevant data blocks into the buffer cache.

  • Yes, there is. Both query result cache and PL/SQL result cache exist. – Balazs Papp Apr 2 '14 at 16:47
  • Actually this is incorrect. In 11gR2 Oracle introduced the results cache. – steve Apr 2 '14 at 16:47
  • Thanks guys I didn't know this existed in 11gR2 as my studies were done before this version was released – Andrew Brennan Apr 2 '14 at 16:48
  • OK so reading the link provided by Balazs, the result cache has to be specifically invoked and is only intended for situations where we are reasonably certain the data is unchanged. This is not really relevant to the question asked, where the questioner assumed there was a result cache by default, when in fact there is a buffer cache. – Andrew Brennan Apr 2 '14 at 16:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.