Re: Memory-resident table -- VMS

We don't have Oracle Financials here but we do run many Oracle databases so...

In article <1993Jul9.002647.6819_at_govonca.gov.on.ca>, millsp_at_govonca.gov.on.ca (Phil Mills) writes:
> (When reading the following, be aware that the author knows a fair
> amount about VMS, but next to nothing about Oracle. In other words,
> when mentioning DBA-type things, assume you're talking to an ignoramus.)
>
> Using Oracle Financials on a VAX running VMS 5.5-2 with a reasonable
> amount of free memory, we have discovered that one table is getting
> a massive number of I/O requests. The table, itself, is rather small
> and would fit comfortably in physical memory.
>
> If this was a normal VMS file, I could improve performance drastically
> by putting the entire thing into a shared global section and satisfying
> all requests from memory.
>
> In Oracle, I understand that there is something called the SGA which
> handles caching of structures and data. Is there any way to force (or
> trick) Oracle into locking the entire table in question into the SGA
> and thereby avoiding the majority of disk accesses?

No. The SGA cache (ie Block Buffers) is fully shared and there is no way to force a single table in there unless of course its the only table ever accessed by your database.

Check that all appropriate indexes are on the table first.

There are a few things you can try:

1. Increase your SGA size and bump up the number of DB_BLOCK_BUFFERS in the INIT.ORA file. If the table is hit as hard as you say then it will probably end up all in the cache. You will still incur I/O for updates to disk but they should be "bundled" automatically by Oracle.
2. Move the table into it's own Oracle tablespace file (*.DBS) and then put that file onto your fastest disk by itself.
3. SHADOW (for high READ activity) or STRIPE (for high UPDATE activity) or BOTH (to guard against reduced disk reliability caused by STRIPE'ing alone) your VMS disk(s) which contain the Oracle database file that contains the table in question.
4. If the table is in an Oracle file by itself then you can do your VMS tuning on the single file as you stated above.

Preference: start at 1 and see if it helps.

Bruce... pihlab_at_cbr.hhcs.gov.au    Received on Mon Jul 12 1993 - 06:33:37 CEST