Listen Software Solutions' "How To" Series:
SQL Tuning - Optimizer Hints
By David Nishimoto
Optimizer
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
Back to the LSS "How To" Series Main Page
|
