Re: Pinning Tables in Memory

"mark tompkins" <mdtompkins_at_home.com> wrote in message news:3A8B7F48.399E8562_at_home.com...
> Does anyone know of a good reference for understanding the effect of
> pinning tables in memory.

You can't pin a table in memory. "Pinning" is usually used (incorrectly) to describe what you to do to keep parsed sql statements in the Shared Pool. That really does guarantee that those things stay there. With tables, there are a number of things you can do to help persuade the table blocks to stay in the buffer cache, but there are no guarantees, and a single large tablescan could flush the lot out with ease.

>
> Assume 1 Gig of RAM.
>
> 1. What performance boost could I get for pinning all tables in an
> address database (static, read only for the most part) (streets table -
> 20K rows, blocks table 300 K rows, units table 100K rows). If the cache
> hit ratio indicates > 99%, is pinning any tables a waste of time
> (address reads frequent, tables indexed, queries use indexes)?

If you've a 99% cache hit ratio on the buffer cache, you are doing extraordinarily well (the usual target recommended is around 85%). It effectively means you have managed to cache everything you require in memory anyway -so I'd suggest further time spent flogging this particular horse would be a waste.

>
> 2. Mulitiple language support means abstracting descriptions so that a
> user can view data in different languages. What effect does pinning
> have on lookup tables? The point here - don't know if the cache will
> always have the data. And, there isn't a large amount of data( < 20k
> rows).
>
> 3. If I have lots of RAM, why not keep pinning tables until I use up a
> good chunk of memory? Or, pin packages/procedures that are most
> commonly used (or infrequently used - because then, they wouldn't be in
> the cache)???
>
> Oracle is not clear about how to best take advantage of available RAM.
> Especially, if gobs of RAM is available.
>
> thx
>
> Mark
>

You don't say what version of Oracle you have, but anything earlier than 8i, and the best you can do to try and "pin" tables is to use the CACHE clause. In 8i, you have the "Keep Buffer Pool" which can make what you're after easier -but there are still no guarantees... if the Keep Buffer gets full, it will flush just like any other part of the cache.

Take a hike over to www.ixora.com.au ...Steve Adams is the guy for explaining this sort of stuff (or Jonathan Lewis, who posts here frequently).

Regards
HJR Received on Thu Feb 15 2001 - 17:29:07 CST