Re: Oracle 8i and poor query performance in some queries

Although it's not documented, Oracle does do as you suggest - try restarting an instance and doing a large tablescan, and you will find that there will be far more than the 'normal' db_file_multiblock_read_count number of
blocks in the buffer when the scan finishes.

However, Oracle can only use FREE buffer blocks to do this and, as you point out at the end of your post, in a multi-user system, the number of FREE blocks tends to drop to zero very soon after database startup.

There are operations which cause buffer blocks to become FREE, of course, but they are not ones you would expect to see happening frequently in a production system.

--
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

  The educated person is not the person
  who can answer the questions, but the
  person who can question the answers -- T. Schick Jr


One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html
____Belgium__November (EOUG event - "Troubleshooting")
____UK_______December (UKOUG conference - "CBO")


Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html
____UK___November


The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html


"Heikki Siltala" <heikki.siltala_at_stakes.nojunk.fi> wrote in message
news:bnkan1$621$1_at_phys-news1.kolumbus.fi...


>

> Hello,

>

> I've studied the documentation and as Richard and others have

pointed


> out the data blocks retrieved for full table scans are added into

LRU


> end of the LRU list so they are the first to age out when buffer

space


> is needed. It would sound more logical to use unused buffer blocks

first


> when buffer space is needed and not to age out full table scan

blocks.


> The documentation does not explain why it does not work this way. I

have


> opened iTAR for this and so we'll see what Oracle's technical stuff

> thinks about it. Still, in a real-life multi-user databases there

would


> not be any significant peformance gain since there would be these

"free


> clean buffer blocks" available only for some moments from the

database


> startup.

>

> --

> Heikki

>