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
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?
Received on Thu Dec 18 2003 - 07:19:11 CST
