Re: Result caching ?

Generally Oracle stores the Most Recently used data and frequently used data buffers in it's MRU end of DB_BLOCK_BUFFER_CACHE so that is is available in memory for further Use.

But if the read blocks are from a Full Table Scan as opposed to Index Scan, then it puts the Data blocks(after it's first use) into the LRU end of it's Buffer Cache which might be cached out onto the Disk more likely.

Again the Likelyhood of finding the Same data in the Cache depends upon various factors like BLOCK_BUFFER_CACHE_SIZE (depends on DB_BLOCK_BUFFERS * DB_BLOCK_SIZE) , DBWR_TIMEOUT etc..

You can manually PIN the data in Cache using

>Alter Table <Table_name> CACHE;

However this method is suggested for only small Look-up tables. You do not want to Cache a huge Table and force all other data to be flushed out causing a Performance drop.

or
> Select /* +CACHE(Table_name) */ ..from ..Table_name where ...;

The Buffer Cache Hit Ratio ( Calculated from V$sysstat as a ratio of (Logical Reads - Physical Reads)/Logical Reads * 100 is a good measure of whether the Data is read from Memory or from the Disk. If this Ratio is less than 95% , then you might want to increase DB_BLOCK_BUFFERS to make the SGA bigger.

Hope this helps..

-Thiru

smanux_at_dream.club-internet.fr wrote:

> Hello,
>
> I wonder if Oracle does something like result caching : for example, if
> i execute a query with some computation (SELECT ... FROM ... ORDER BY
> MyFunction(..)) and a few time latter, i redo the same query. Does
> Oracle compute again the second time or does it keep in a cache the
> result of the first one ?
>
> Have any idea ? :)
>
> Emmanuel
Received on Fri Jul 23 1999 - 12:18:26 CDT