Varying query performance

Environment: Oracle 8.1.6.3.0 on Sun SPARC Solaris 8.

We have a client-server application where one of the forms performs full table scan on a particular table. The table isn't very big (460,000 rows), and this full table scan normally takes 3 - 10 seconds depending on the load on the box. As this form is being actively used, even delay in 3 - 10 seconds is annoying to the users.

In order to speed up the operation I modified properties of this table: CACHE=Y and BUFFER_POOL=KEEP. Note that this is the only table in the KEEP pool, this pool has 15,000 blocks while the table has 8500 blocks so it should fit perfectly. Indeed, the runtime of the query went down to 0.3 second and V$SESSION_EVENT shows neither "db file scattered read" nor "db file sequential read".

However, in my test on average every 5th run has duration significantly longer than 0.3 second (basically back in 3 - 10 second range), and V$SESSION_EVENT shows that most of this could be attributed to "db file scattered read" and "db file sequential read".

So, what could cause Oracle to peform physical reads when the table should be in memory? The table gets about 1000 inserts per day and similar number of updates.

Thanks,
Sev Received on Thu Dec 18 2003 - 00:52:19 CST