(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?