Re: Varying query performance

vafanassiev_at_aapt.com.au (Vsevolod Afanassiev) wrote in message news:<4f7d504c.0312172252.566510aa_at_posting.google.com>...
> 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

KEEP replaces cache. Both KEEP and CACHE do NOT guarantee the table is never removed from the cache.
You should ask yourself why you want to perform FTS all the time on a table of 460000 rows. A FTS ALWAYS proceed to the HWM, if the table is volatile, you will encounter severe problems soon. I guess you are better off rethinking and redesigning, removing the Fundamental Flaws of the app, rather than throwing memory at the problem. Your app will never scale if you don't address this issue.

Sybrand Bakker
Senior Oracle DBA Received on Thu Dec 18 2003 - 04:32:40 CST