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

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

Richard Foote wrote:


> You have to remember that most installations of Oracle are multi user and

> what potentially benefits one user may need to be weighed against the impact

> on all others. The caching/aging algorithm is somewhat ignorant of what else

> is currently cached. A FTS whereby all blocks are read would potentially

> overwrite/age out much useful data if were allowed to simply cache itself by

> default. Hence Oracle takes the safety first approach of limiting what

> effects a FTS can have to the buffer cache. Overall, it's a sensible

> approach.

> 

> Look for a recent post of mine in the "cache a table" tread that might help

> explain some of this.

> 

> Cheers

> 

> Richard

> 

>