--
-- optimizer_index_cost_adj.sql
-- by Brian Peasland
-- 14 August 2002
-- 
-- This script is used to determine a better value
-- for the OPTIMIZER_INDEX_COST_ADJ parameter.
-- This parameter controls how the CBO treats index
-- lookups compared to full table scans. The default
-- for this parameter is 100. But that is frequently
-- an incorrect setting. This script computes a ratio
-- between the average wait times for index lookups and
-- the average wait times for full table scans. This 
-- ratio can be used as a guideline for setting this
-- parameter

PROMPT
PROMPT Use this value as a guideline for setting 
PROMPT the OPTIMIZER_INDEX_COST_ADJ parameter.

SELECT TRUNC((index_time/fts_time)*100) AS OPTIMIZER_INDEX_COST_ADJ
FROM (SELECT average_wait AS index_time FROM v$system_event
      WHERE event='db file sequential read') a,
     (SELECT average_wait AS fts_time FROM v$system_event
      WHERE event='db file scattered read') b;