Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Jul 8, 2002

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

By David Nishimoto

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

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 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

Oracle Archives

Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM