Other recommendations – Oracle Audio Technologies ORACLE9I B10508-01 User Manual

Page 136

background image

Other Recommendations


Oracle9i Installation Guide Release 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_


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.