Re: How to pin a table in memory?

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:963584579.7623.0.nnrp-01.9e984b29_at_news.demon.co.uk...
>
> Answers in-line
>
> --
>
> Jonathan Lewis
> Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk
>
> Howard J. Rogers wrote in message <396f1859_at_news.iprimus.com.au>...
> >
> >> The CACHE option was to tell Oracle that
> >> a table that exceeded the small table threshold
> >> should nevertheless be treated as a small
> >> table for LRU/MRU purposes, provided it
> >> also fell below cache_size (default 10% of
> >> db_block_buffers).
> >>
> >
> >Yup. And the enormous performance benefits from telling Oracle this
> >are....?
> >
>
> That when a specific table is larger than a
> size you would normally wish to consider
> small, Oracle will nevertheless load it into
> the top end of the buffer so that it has to
> fall through to the LRU end, rather than
> cycling it through a small window and re-reading
> it from disc all the time.
>
> Continuous scanned access to a table
> will, in general, NOT keep the whole table
> in the buffer.
>
>
> >>
> >> If the system is Oracle 8, then one option
> >> is to declare a KEEP buffer pool larger
> >> than the size of the table, and allocate
> >> the table to the KEEP pool. Once the table
> >> is pulled into the pool by non-scanning
> >> activity it will stay there.
> >>
> >
> >And hence the CACHE clause is....
> >
> >useless?
> >
>
> Which is why Oracle has been telling us
> since the year dot (I mean, since Oracle 8
> came out) to use the KEEP buffer pool and
> not to use the CACHE clause.
>
>
> In fact if you use the CACHE clause with
> Oracle 8 (.0.5 particularly) you can blast
> the entire db_block_buffer with the contents
> of a single large table. So you should definitely
> NOT use the CACHE clause.
>

So it seems we agree and are of the same opinion!

> The entire buffer management system has
> changed dramatically for 8.1, though, so
> all bets are off at that point.
>

Fair enough. I'm still getting to grips with the 8.1 changes myself. So long as we can agree that the CACHE clause is useless, I'm a happy man!!

Regards
HJR
>
>
>
>
Received on Sat Jul 15 2000 - 00:00:00 CDT