8.2 What is the Optimizer?

 < Day Day Up > 



The purpose of the Optimizer is to optimize the performance of SQL statements. The Optimizer decides the best way to execute an SQL code statement in the fastest way possible, based on information available to it. The Optimizer can use various methods of assessing the best path of performance for an SQL code statement. These methods include:

  • Rule-based approach.

    • Outlines.

  • Cost-based approach.

    • Statistics.

    • Hints.

    • Histograms.

    Note 

     Oracle Database 10 Grid   Rule-based Optimization is desupported.

Indexes against tables may or may not be used as the better option to execute an SQL statement. Sometimes the Optimizer will decide that reading an entire table is more efficient than reading a small part of an index, to access a ROWID pointer accessing a small number of table rows.

Through the years various versions of the Oracle SQL code Optimizer have become more and more intelligent. In Oracle9i Database it can be relied on fairly heavily to produce the best performance for SQL code.

Note 

 Oracle Database 10 Grid   Oracle Database 10g has Optimizer improvements such as less of a need for SQL code statements to be case sensitive.

In general the capabilities of the Optimizer will deteriorate as the complexity of SQL code increases and particularly the quality of SQL code decreases. The Optimizer should not be affected by database size unless SQL code or data model are poorly tuned and constructed. Database size merely exacerbates problems caused by poor design. Unfortunately these circumstances happen often. In OLTP systems SQL code can sometimes become so complex and convoluted that the Optimizer simply cannot cope and executes full table scans on all tables. In data warehouses tables are so large sometimes that special attention is needed because scale and poor coding are simply way beyond the capabilities of the Optimizer.

The rule-based approach is out of date and will probably be deprecated in a future version of Oracle Database.

Note 

 Oracle Database 10 Grid   Rule-based Optimization is desupported.

Statistics are very important to the Optimizer and the cost-based approach. The more up to date those statistics, the more accurately the Optimizer can predict the best way to execute an SQL statement. In very large or very active databases continual update to statistics is impractical, likely to cause contention and is time consuming. Thus the more out of date statistics are, the less accurate the Optimizer will be.

8.2.1 What are Statistics?

Statistics are a computation or estimation of the exact size and placement of data in tables and indexes. Statistics can be used by the Optimizer to make a better assessment of actual data and thus produce a better query plan, more accurately matching the data as it actually is in the database.

Statistics are used by cost-based optimization and not rule-based optimization.



 < Day Day Up > 



Oracle High Performance Tuning for 9i and 10g
Oracle High Performance Tuning for 9i and 10g
ISBN: 1555583059
EAN: 2147483647
Year: 2003
Pages: 164

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net