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

"Heikki Siltala" <heikki.siltala_at_stakes.nojunk.fi> wrote in message news:bn77e0$rmn$2_at_phys-news1.kolumbus.fi...
>
> Niall Litchfield wrote:
> > But if you are doing a FTS then the blocks go on the LRU list and get
aged
> > out rapidly. After all no-one in their right mind would scan the same
blocks
> > 35000 times when they could do it once now would they :(. It really
seems
> > like these tables get accessed frequently by the id column (and indeed
there
> > may even be RI between the columns) in which case indexing the id
columns
> > will probably cure this and other issues.
>
> I admit that I'm not aware how Oracle's buffering mechanism ages out the
> blocks but it seems strange to me that the blocks are thrown out even
> when there is no need to free up buffer space for newer blocks. Why on
> earth blocks are thrown out or marked "no longer used" even when there
> are no data to replace them with? It would sound more logical to me to
> keep them there and also use them as long as there is no need to find
> room for some more important blocks. Well, I think I'll have to read and
> study the manuals.
>

Hi Heikki

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 Received on Sat Oct 25 2003 - 08:06:46 CDT