Oracle Audio Technologies ORACLE9I B10508-01 - Manual (Page 136)

Other Recommendations
B-4 Oracle9i Installation Guide Release 2 (9.2.0.2) for HP Alpha OpenVMS
Check statistics, such as V$SYSSTAT, to see if the number of sorts to disk is high
compared to in-memory sorts
. If it is, then increase the value of SORT_AREA_
SIZE.
Other Recommendations
Check the size in number of rows of the tables involved in the query, and translate
this size into total number of blocks
. Based on the query, try to fit as many of the
hard hit table blocks in DB_BLOCK_BUFFERS.
For example, if there are four tables involved in the query, but columns from one of
the tables are used repeatedly in the "where" clause in joins, "in", etc.; try to fit as
many blocks from this table as possible into the cache to see if
DB_BLOCK_BUFFERS can be increased
. To ensure the hard hit tables are cached
and stay in the most recently used (MRU) end of the cache, perform either of the
following steps:
■
Type (using SQLPLUS),
alter table cache
or
■
At the time of creation,
create table ... cache
If there are enough buffers to accommodate all blocks from all tables involved in the
query, use the alter command to cache all the blocks
.
The purpose is to cache most
blocks into memory to ensure that I/O to disks is eliminated or remains low.