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

vafanassiev@aapt.com.au (Vsevolod Afanassiev) wrote in message news:<4f7d504c.0312172252.566510aa@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

> 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.

Why guess?

Query v$bh and see if the blocks are in the cache.

Try this script:

http://www.dba-oracle.com/art_builder_buffer.htm

vafanassiev@aapt.com.au (Vsevolod Afanassiev) wrote in message news:<4f7d504c.0312172252.566510aa@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

I can't answer your technical question, but my initial reaction is:
WHY do you have a form that scans 460,000 rows frequently? No user
could possibly scroll through that many rows; if the users are
querying all records but then just viewing the first few, maybe a
FIRST_ROWS hint would help? Or an enforced restriction on the rows
queried?

sev,
The number of buffers needed for the table are more than the number of
blocks in the table below HWM.
This is because the query needs CR buffers whereas INSERTS and UPDATES
need CURRENT Buffers.
You can check all these in V$BH.
Just check whether the UPDATES are being done with an INDEX scan or
FTS.
regards
Srivenu