Re: Q:Keeping a table in memory for speed

In article <336AA4C6.6A15_at_erols.com>,

        Martin Smith <mfsmith_at_erols.com> writes:
>
>Don't recall the specifics, but "cacheing" frequently-used tables was
>recommended in a recent article in Ora-something mag (little thin
>3rd-party rag--sorry to be so forgetful). I'd love to hear if that
>gives a big boost and what the best tables to do it with are.
>
>mfs
>
>Simon Spencer wrote:
>>
>> I dont suppose anyone can tell me how to make a table or group of tables
>> memory resident?
>>
>> (Oracle 7.3, Under NT 4.0)
>>
>> Many thanks.

There can be a massive improvement in performance if you understand the server load adequately to make good decisions about caching (Just use 'alter table tablename cache', and then preload it with select * from tablenm)

Be sure that you understand your systems use of buffers well enough to identify what shouldn't be cached in order to leave adequate room for indices. Of course, if you have enough ram for all tables and indices (per dba_segments), then simply cache all tables. In the 64-bit ORACLE, there are several additional ORACLE and OS parameters to be set (see the white papers from DEC). Using DEC UNIX and 64-bit ORACLE (VLM), with VLM activated, orders of magnitude improvements can be gained. The profile of queries that gain the most benefit appears to be very complex joins ... i.e. > 10 tables outer joined on 8 or more of the criteria.

Other parameters that help are around BOB, and maximizing parallel query on SMP boxes. Put them all together, and it is easy to see 1 to 2 orders of magnitude improvement in speed. BTW, this isn't as good on tablescans ... there is substantial system overhead in managing a lot of ram.

Bill

--