Computer Accessories User Manual

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 <tablename> cache

■ 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.