I have never got such an insightful analysis, ever. It is almost as if you could see what is happening on my system. Thanks very much!

Problem: You are on 7.3. You have a table that is accessed by full table scan every three seconds as well as being accessed by rowid. The number of blocks in the table represent about 12% of db_block_buffers. I/O against this table makes the disk on which it resides "busy".

Reasons: Because the table is greater than the _small_table_threshold blocks read for the full table scans are not cached - the buffers are immediately reused. Because the table is also accessed by rowid, some blocks are already cached and cannot be read by the multiblock reads, and so the multiblock reads are fragmented. The operating system is not caching the blocks because you are using raw or direct I/O (possibly NT).

Objective: To minimize the time spent accessing this table - both the disk access time, and the associated CPU time.

Metrics: 1. The READTIM shown in V$FILESTAT for the datafile. 2. The CPU used by this session statistics in V$SYSSTAT. For performance comparisons both these metrics should be divided by the consistent gets metric from V$SYSSTAT. The first metric can be expected to be a better indicator.

Strategy #1: Enable the CACHE attribute for the table, and raise both cache_size_threshold and db_block_buffers by the number of blocks in the table. Depending on how frequently the table is scanned relative to other buffer cache access, you may save some reads against this table, and by raising db_block_buffers by the same number of buffers you can ensure that there will be no negative impact on the overall cache hit rate.

Strategy #2: Set the degree of parallelism for the table to 2. That will force full table scan reads to bypass the cache entirely, making them more efficient both in terms of disk access and CPU time.

My guess is that strategy #2 will give you the best performance.

To answer your other questions:

2. The CACHE attribute has NO impact on the way in which blocks are treated in cache (with the one exception of the initial placement of blocks read for full table scans against tables that are between the _small_table_threshold and cache_size_threshold parameters in size).

3. Under 7.3 it is most unusual to have free buffers. Are you still speaking of a 7.3 instance here? What SQL did you use to measure this? Had you recently dropped or truncated any segments, or taken any tablespaces offline?

I read those pages you mentioned and did not find anything that differs from my current understanding. I will re-phrase my questions.

1. I have mid-sized table which occupies about 9-12% of the buffer cache (db_block_buffers). default value for cache_size_threshold is 10% of db_block_buffers. Any thing I need to watch out for if I change the value of cache_size_threshold? (or it can't be changed at all)

2. once all the buffers are lodged at the MRU end of the LRU list, what happens when a new record is inserted into this table? when a query is run against this table, will only the new record be disk read into buffer, or would it cause a full table disk read?

3. My understanding is that unused buffers will be used as a first preference. If none exist, only then will buffers from the LRU end of the cache be re-used. WHy then do I see unused buffers in my cache (select count(*) from x$bh) even after 30 days of instance uptime?

You seem to misunderstand how the CACHE keyword works. Please check the answers from 1 Nov 1999 and 9 Feb 2000 on this page from the Ixora web site, http://www.ixora.com.au/q+a/cache.htm.

Thanks for the below. I have the following observations.

1. The optimizer is using a full table scan on those queries that account for the majority of disk reads on this table. There are however a few other queries using indexes on this table.

2. This table occupies around 9-12% of the buffer cache almost all the time. If I cache this table and do a full table scan by select count(*) from table_name, then the blocks would be placed into the MRU end of the LRU list.

a) what would happen if the % buffer usage of this table crosses 10%, i.e default value of CACHE_SIZE_THRESHOLD. My guess is that then the blocks would be kicked to the tail of the LRU list and subject to aging. In this case, is it a good idea to change the CACHE_SIZE_THRESHOLD to say about 15% of buffer cache?

Any other things I need to watch out for when adjusting the CACHE_SIZE_THRESHOLD?

b) once all the buffers are lodged at the MRU end of the LRU list, what happens when a new record is inserted into this table? when a query is run against this table, will only the new record be disk read into buffer, or would it cause a full table disk read?

c) Is it true that if a query against this table (using an index) is issued (whilst its buffers are being held at the head of the LRU list), the buffers would then be pushed to the LRU end and subject to normal aging?

3. I did the following:

select count(*) from x$bh where state=0

and found quite a few unused buffers. Why then are the table (in question) buffers being aged out from the LRU list?

I couldn't get enough information from metalink on these topics.

If the optimizer is choosing a full table scan and the table takes between 2% and 10% of db_block_buffers, then enabling the CACHE attribute for the table may help. Alternatively, you could just increase db_block_buffers to more than 50 times the size of the table. You may also want to run the sparse_tables.sql script from the Ixora web site (or a variant of it, if the table is not in fact sparse) to get the best PCTUSED value for the table.

On our prod server, we had instances of high disk i/o - as high as 80% on one disk. We then relocated one table to another disk. Now this disk is being subjected to the high disk i/o

I trawled through v$sqlarea and found one SQL being called from a pro*c application with a very large number of executions and a even higher number of disk reads. This pro*c happens to be a daemon process constantly running this SQL, every 3 seconds. If records match its select criteria, it does some action. the checking frequency cannot be increased. It has to check every 3 seconds.

Seems that this table buffers are being aged out too fast. What steps must I take to ensure that this table is NEVER aged out from buffer and so never needs to access the disk. This table has about 3000 records at any given point of time. New ones are inserted at about 200 per day and also deleted at the same rate.

Is cache attribute for such a table recommended? Even if I do cache it, how should I then go about sizing my buffer pool?

Am i barking up the wrong tree and should be doing some thing else to address this problem?

This prod server is running Oracle 7.3.2