Re: cacheing in Oracle

In article <4qolfv$la4_at_news.spar.ca> Chris.Warwick_at_Prior.CA (Chris Warwick) writes:
>We have been doing some timing trials with Oracle 7.1.6 under SCO OSR 5 and
>discovered that the RDDBMS is doing some sort of cacheing (yes I know if I
>had had time to read all the manuals I would have discovered this...)
>
>The manuals seem to indicate that the DBA can manipulate these values, but we
>are unsure as to how we should do this.

You can enlarge the SGA, look at the examples in init.ora for medium & large databases. You need to have lots of physical memory to do this, and will probably have to adjust some unix kernel parameters, too.

Especially, check out SHARED_POOL_SIZE and DB_BLOCK_BUFFERS.

select name, value from v$SYSSTAT where name in ('db block gets', 'consistent gets','physical reads');

if 1 - (physical reads / (db block gets + consistent gets)) < .8, increase DB_BLOCK_BUFFERS. select sum(gets) "data dictionary gest", sum(getmisses)) "DD Cache get misses" from v$rowcache;

You want getmisses/gets < .1, otherwise increase SHARED_POOL_SIZE.

Also, you can pin frequently used or large SQL statements into memory, this helps by letting you tune out wasted space in the library cache.

select sum(pins) "executions", sum(reloads) "Cache misses while executing" from v$librarycache;

You want reloads/pin < .01, otherwise increase SHARED_POOL_SIZE.

>
>We have performance requirements, so we need to understand the cacheing and
>how to get the best performance out of our database...
>
>Does anyone have any ideas?

rtfm about shared sql areas, data dictionary cache, database buffer cache, and library cache.

And, of course, brush up on the rules of SQL performance tuning, and explain plan.

jg

-- 
Joel Garry               joelga_at_rossinc.com               Compuserve 70661,1534
These are my opinions, not necessarily those of Ross Systems, Inc.   <> <>
%DCL-W-SOFTONEDGEDONTPUSH, Software On Edge - Don't Push.            \ V /
panic: ifree: freeing free inodes...                                   O