Re: Pinning Tables in Memory

In article <3A8B7F48.399E8562_at_home.com>, mark says...
>
>Does anyone know of a good reference for understanding the effect of
>pinning tables in memory.
>
>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)?
>
>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
>

1 The Oracle guide line is to use no more than one third of physical RAM, so you definitely shouldn't use up it completely, as that will result in heavy paging. 2 the only way to pin tables is to use alter table cache. The blocks read in this way are placed on top of the LRU list, just as a full table scan, so they could be paged out immediately, depending on your load in db_block_buffers. So IMO it is advisable to limit the use of alter table cache to small lookup tables *only*
3 If you have a hit rate of 99 percent already, pinning tables is not going to help *anything*
4 You can verify with dbms_shared_pool.sizes(<n>) where <n> is in k, which objects bigger than n k are in the shared pool. I would concentrate on pinning those, and not pin indiscriminately everything.

Hth,

Sybrand Bakker, Oracle DBA Received on Thu Feb 15 2001 - 02:39:41 CST