Listen Software's How To: SQL Tuning--Optimizer Hints

July 8, 2002

Optimizer Steps: Each step of the execution plan returns a set of rows that are used either by the next step or in the last step.

The optimizer calculates the cost based on the estimated computer resources, including but not limited to I/O, CPU time, and memory.

The goal of the cost-based approach is the best throughput, or minimal resource usage necessary to process all rows accessed by the statement.

alter system set TIMED_STATISTICS=TRUE; 
execute sys.dbms_utility.analyze_schema
('APPLICATION_USER','COMPUTE');

Cost-Based Approach

The optimizer generates a set of execution plans based on the possible join orders, join operations, and available access paths. The optimizer estimates the costs of each plan and chooses the one with the lowest cost.

A smaller sort area size is likely to increase the cost for sort-merge join. Increase the SORT_AREA_SIZE variable in the initialization file.

Indexes

Indexes improve the performance of queries that select a small percentage of rows from the table (2 to 4 percent).

Only index columns with good selectivity. An index's selectivity is good if few rows have the same value.

Selectivity = number of row / number of distinct rows