Oracle result cache behaviour

oracleoracle-11g-r2performancequery-performance

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?

Best Answer

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.

Related Solutions

Sql-server – SSAS queries time out when Windows File System Cache drops

I believe you seeing the symptoms of an issue with Windows 2003 requiring contiguous memory and this causes the running processes including Analysis Service to trim their Working Set memory. With large amounts of memory allocated this trim process can take a significant amount of time to complete, while this is running new allocations will be blocked, causing the process to stall. This issue is fixed in Windows 2008 onwards this support article is for SQL Server but has all the links to the relevant pages http://support.microsoft.com/kb/918483 in particular this quote

In Windows Server 2008, the allocation of physically contiguous memory is greatly enhanced. Requests to allocate contiguous memory are much more likely to succeed because the memory manager now dynamically replaces pages, typically without trimming the working set or performing I/O operations. In addition, many more types of pages—such as kernel stacks and file system metadata pages, among others—are now candidates for replacement. Consequently, more contiguous memory is generally available at any given time. In addition, the cost to obtain such allocations is greatly reduced.

I believe that with this issue you will either have to upgrade to Windows 2008 onwards or test granting Lock Pages In Memory to the Analysis Services account. I have also had issues with memory allocation on Windows 2003 which requires a reboot to clear.

This SQLCAT blog compares the changes between Windows Server 2003 & 2008.

This blog mentions some of the issues of LPIM with Analysis Services (near the end)

Also this knowledge base article on working set trimming causing performance problems and this article shows how to Identifying Drivers That Allocate Contiguous Memory

Oracle 10g using collection with sql

One of the problems with using collections in SQL is that the optimizer isn't able to guess how many elements the collection has. It defaults to assuming that the collection has a few thousand elements (I want to say 4k elements but I wouldn't wager on that). If that is roughly 1,000 times more elements than you have in your actual collection, that's will certainly tend to cause the optimizer to make poor choices about the query plan.

The best way to alleviate that problem is to use the CARDINALITY hint.

SELECT /*+ cardinality(a 4) */ a.col1, b.col2, b.col3
  FROM 
  TABLE(FOO.my_table) a
  INNER JOIN existing_table b ON (b.existing_table_id = a.existing_table_id)
  --b.existing_table_id - primary key supported by unique index
  ORDER BY a.existing_table_id

tells the optimizer to assume that the collection aliased to a has only 4 elements which should cause it to pick a more appropriate query plan.

Best Answer

Related Solutions

Sql-server – SSAS queries time out when Windows File System Cache drops

Oracle 10g using collection with sql

Related Question