Other Recommendations
B-4
Oracle9
i
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 <tablename> cache
or
■
At the time of creation,
create table <tablename> ... 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.
