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

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Oracle

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




Oracle Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




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