OPTIMIZER_PERMENTATIONS = 79,000—
This setting defaults to 80,000 and controls the number of various cost factors Oracle will consider before calling it quits and looking at what it has for cost estimates. For some reason, Oracle seems to make slightly better decisions with the cost-based optimizer if this is set slightly lower than its default value.
OPTIMIZER_INDEX_CACHING = 90—
The range for this parameter is 0 to 99, where 0 assumes that logical I/O is the same as physical I/O (equivalent of a 0% buffer hit cache ratio). It is recommended to set this to 90 to give the cost-based optimizer the thought that the index might already be in the buffer pool.
OPTIMIZER_INDEX_COST_ADJ = 10 through 50—
This parameter is similar to the prior mentioned optimizer_index_caching except that this setting (default setting is 100) assumes the service time on I/O requests is the same for indexed scans as full-table scans. 10 seems to be a good setting for OLTP type applications and 50 seems to be a good setting for decision support type applications. If you have the init.ora parameter timed_statistics set to true, the SQL statement in Listing 14.8 should help come up with a more accurate setting for this parameter for your particular system and applications. Set this parameter to the result of index scans divided by full table scans.
Listing 14.8. OPTIMIZER_INDEX_COST_ADJ Settings
SELECT event, average_wait
FROM v$system_event
WHERE event like 'db file s%';
EVENT AVERAGE_WAIT
--------- -------------------------
db file scattered read 1.12365 (full table scans)
db file sequential read .13254 (index scan)
|
HASH_JOIN = Enabled
Default setting is 'Disabled'
HASH_AREA_SIZE
This parameter should be twice SORT_AREA_SIZE.
SORT_AREA_SIZE
This parameter benefits both merge-joins and hash joins. Listing 14.9 shows a SQL query that will show if your sort operations are going out to the operating-system for additional space. This setting should be large enough so that this does not happen very frequently.
