Query Optimizer

Source : Link

1 Optimizer Operations

A SQL statement can be executed in many different ways, such as
 full table scans,  index scans, nested loops, and hash joins.
The query optimizer determines the most efficient way to execute a SQL statement after considering many factors related to the objects referenced and the conditions specified in the query.
This determination is an important step in the processing of any SQL statement and can greatly affect execution time.

Note:

The optimizer might not make the same decisions from one version of Oracle to the next. In recent versions, the optimizer might make different decisions, because better information is available.

For any SQL statement processed by Oracle, the optimizer performs the operations listed below:
-------------------------------------------------------

Operation : Description

Evaluation of expressions and conditions

The optimizer first evaluates expressions and conditions containing constants as fully as possible.

Statement transformation

For complex statements involving, for example, correlated subqueries or views, the optimizer might transform the original statement into an equivalent join statement

Choice of optimizer goals

The optimizer determines the goal of optimization.

Choice of access paths

For each table accessed by the statement, the optimizer chooses one or more of the available access paths to obtain table data.

Choice of join orders

For a join statement that joins more than two tables, the optimizer chooses which pair of tables is joined first, and then which table is joined to the result, and so on..

--------------------------------------------------------

You can influence the optimizer's choices by setting the optimizer goal, and by gathering representative statistics for the query optimizer. The optimizer goal is either throughput or response time.

The application designer can use hints in SQL statements to instruct the optimizer about how a statement should be executed.

2 Choosing an Optimizer Goal

By default, the goal of the query optimizer is the best throughput. This means that it chooses the least amount of resources necessary to process all rows accessed by the statement.

Oracle can also optimize a statement with the goal of best response time. This means that it uses the least amount of resources necessary to process the first row accessed by a SQL statement.

Choose a goal for the optimizer based on the needs of your application:

·         For applications performed in batch,  optimize for best throughput.

·         For interactive applications, such as Oracle Forms applications or SQL*Plus queries, optimize for best response time. Usually, response time is important in interactive applications, because the interactive user is waiting to see the first row or first few rows accessed by the statement.

The optimizer's behavior when choosing an optimization approach and goal for a SQL statement is affected by the following factors:

·         OPTIMIZER_MODE Initialization Parameter

·         Optimizer SQL Hints for Changing the Query Optimizer Goal

·         Query Optimizer Statistics in the Data Dictionary

2.1 OPTIMIZER_MODE Initialization Parameter

The OPTIMIZER_MODE initialization parameter establishes the default behavior for choosing an optimization approach for the instance.

OPTIMIZER_MODE Initialization Parameter Values

 ------------------------------------------------------------------------

Value: Description

ALL_ROWS

The optimizer uses a cost-based approach for all SQL statements in the session regardless of the presence of statistics and optimizes with a goal of best throughput (minimum resource use to complete the entire statement). This is the default value.

FIRST_ROWS_n

The optimizer uses a cost-based approach, regardless of the presence of statistics, and optimizes with a goal of best response time to return the first n number of rows; n can equal 1, 10, 100, or 1000.

FIRST_ROWS

The optimizer uses a mix of cost and heuristics to find a best plan for fast delivery of the first few rows.

Note: Using heuristics sometimes leads the query optimizer to generate a plan with a cost that is significantly larger than the cost of a plan without applying the heuristic.

FIRST_ROWS is available for backward compatibility and plan stability; use FIRST_ROWS_n instead.

-------------------------------------------------------------
You can change the goal of the query optimizer for all SQL statements in a session by changing the parameter value in initialization file or by the ALTER SESSION SET OPTIMIZER_MODE statement.
For example:

·         The following statement in an initialization parameter file establishes the goal of the query optimizer for all sessions of the instance to best response time:

          OPTIMIZER_MODE = FIRST_ROWS_1

·         The following SQL statement changes the goal of the query optimizer for the current session to best response time:

   ALTER SESSION SET OPTIMIZER_MODE = FIRST_ROWS_1;

2.2 Optimizer SQL Hints for Changing the Query Optimizer Goal

To specify the goal of the query optimizer for an individual SQL statement, use one of the hints mentioned below.
 Any of these hints in an individual SQL statement can override the OPTIMIZER_MODE initialization parameter for that SQL statement.

-----------------------------------------------------

Hint: Description

FIRST_ROWS(n)

This hint instructs Oracle to optimize an individual SQL statement with a goal of best response time to return the first n number of rows, where n equals any positive integer. The hint uses a cost-based approach for the SQL statement, regardless of the presence of statistic.

ALL_ROWS

This hint explicitly chooses the cost-based approach to optimize a SQL statement with a goal of best throughput.

-----------------------------------------------------

2.3 Query Optimizer Statistics in the Data Dictionary

The statistics used by the query optimizer are stored in the data dictionary.
 You can collect exact or estimated statistics by using the DBMS_STATS package.
To maintain the effectiveness of the query optimizer, you must have statistics that are representative of the data.
For table columns that contain values with large variations in number of duplicates, called skewed data, you should collect histograms.
The resulting statistics provide the query optimizer with information about data uniqueness and distribution.
Using this information, the query optimizer is able to compute plan costs with a high degree of accuracy.
This enables the query optimizer to choose the best execution plan based on the least cost.
If no statistics are available when using query optimization, the optimizer will do dynamic sampling depending on the setting of the OPTMIZER_DYNAMIC_SAMPLING initialization parameter.

This may cause slower parse times so for best performance, the optimizer should have representative optimizer statistics.

3 Enabling and Controlling Query Optimizer Features

This section contains some of the initialization parameters specific to the optimizer.
The following sections are especially useful when tuning Oracle applications.

3.1 Enabling Query Optimizer Features

You enable optimizer features by setting the OPTIMIZER_FEATURES_ENABLE initialization parameter.

OPTIMIZER_FEATURES_ENABLE Parameter

The OPTIMIZER_FEATURES_ENABLE parameter acts as an umbrella parameter for the query optimizer.
This parameter can be used to enable a series of optimizer-related features, depending on the release.
 It accepts one of a list of valid string values corresponding to the release numbers, such as 8.0.4, 8.1.7, and 9.2.0.
For example, the following setting enables the use of the optimizer features in generating query plans in Oracle 10g, Release 1.

OPTIMIZER_FEATURES_ENABLE=10.0.0; 

The OPTIMIZER_FEATURES_ENABLE parameter was introduced with the main goal to allow customers to upgrade the Oracle server, yet preserve the old behavior of the query optimizer after the upgrade.
For example, when you upgrade the Oracle server from release 8.1.5 to release 8.1.6, the default value of the OPTIMIZER_FEATURES_ENABLE parameter changes from 8.1.5 to 8.1.6.
This upgrade results in the query optimizer enabling optimization features based on 8.1.6, as opposed to 8.1.5.
For plan stability or backward compatibility reasons, you might not want the query plans to change because of new optimizer features in a new release.
 In such a case, you can set the OPTIMIZER_FEATURES_ENABLE parameter to an earlier version.
For example, to preserve the behavior of the query optimizer to release 8.1.5, set the parameter as follows:

OPTIMIZER_FEATURES_ENABLE=8.1.5;

This statement disables all new optimizer features that were added in releases following release 8.1.5.

Note:

If you upgrade to a new release and you want to enable the features available with that release, then you do not need to explicitly set the OPTIMIZER_FEATURES_ENABLE initialization parameter.

Oracle Corporation does not recommend explicitly setting the OPTIMIZER_FEATURES_ENABLE parameter to an earlier release. Instead, execution plan or query performance issues should be resolved on a case-by-case basis.

3.2 Controlling the Behavior of the Query Optimizer

This section lists some initialization parameters that can be used to control the behavior of the query optimizer.
These parameters can be used to enable various optimizer features in order to improve the performance of SQL execution.

CURSOR_SHARING

This parameter converts literal values in SQL statements to bind variables. Converting the values improves cursor sharing and can affect the execution plans of SQL statements. The optimizer generates the execution plan based on the presence of the bind variables and not the actual literal values.

DB_FILE_MULTIBLOCK_READ_COUNT

This parameter specifies the number of blocks that are read in a single I/O during a full table scan or index fast full scan. The optimizer uses the value of DB_FILE_MULTIBLOCK_READ_COUNT to cost full table scans and index fast full scans. Larger values result in a cheaper cost for full table scans and can result in the optimizer choosing a full table scan over an index scan. If this parameter is not set explicitly (or is set is 0), the optimizer will use a default value of 8 when costing full table scans and index fast full scans.

OPTIMIZER_INDEX_CACHING

OPTIMIZER_INDEX_COST_ADJ

OPTIMIZER_MODE

This initialization parameter sets the mode of the optimizer at instance startup.
The possible values are ALL_ROWS, FIRST_ROWS_n, and FIRST_ROWS.

PGA_AGGREGATE_TARGET

This parameter automatically controls the amount of memory allocated for sorts and hash joins. Larger amounts of memory allocated for sorts or hash joins reduce the optimizer cost of these operations.

STAR_TRANSFORMATION_ENABLED

4 Understanding the Query Optimizer

The query optimizer determines which execution plan is most efficient by considering available access paths and by factoring in information based on statistics for the schema objects (tables or indexes) accessed by the SQL statement.
The query optimizer also considers hints, which are optimization instructions placed in a comment in the statement.
The query optimizer performs the following steps:

1.      The optimizer generates a set of potential plans for the SQL statement based on available access paths and hints.

2.      The optimizer estimates the cost of each plan based on statistics in the data dictionary for the data distribution and storage characteristics of the tables, indexes, and partitions accessed by the statement.

The cost is an estimated value proportional to the expected resource use needed to execute the statement with a particular plan.

The optimizer calculates the cost of access paths and join orders based on the estimated computer resources, which includes I/O, CPU, and memory.

Serial plans with higher costs take more time to execute than those with smaller costs.

When using a parallel plan, however, resource use is not directly related to elapsed time.

3.      The optimizer compares the costs of the plans and chooses the one with the lowest cost.

4.1 Components of the Query Optimizer

The query optimizer operations include:

·         Transforming Queries

·         Estimating

·         Generating Plans

Estimating

The estimator generates three different types of measures:

·         Selectivity

·         Cardinality

·         Cost

These measures are related to each other, and one is derived from another.
The end goal of the estimator is to estimate the overall cost of a given plan.
If statistics are available, then the estimator uses them to compute the measures.
The statistics improve the degree of accuracy of the measures.

Cardinality

Cardinality represents the number of rows in a row set. Here, the row set can be a base table, a view, or the result of a join or GROUP BY operator.

Cost

The cost represents units of work or resource used. The query optimizer uses disk I/O, CPU usage, and memory usage as units of work. So, the cost used by the query optimizer represents an estimate of the number of disk I/Os and the amount of CPU and memory used in performing an operation. The operation can be scanning a table, accessing rows from a table by using an index, joining two tables together, or sorting a row set. The cost of a query plan is the number of work units that are expected to be incurred when the query is executed and its result produced.
The access path determines the number of units of work required to get data from a base table. The access path can be a table scan, a fast full index scan, or an index scan. During table scan or fast full index scan, multiple blocks are read from the disk in a single I/O operation. Therefore, the cost of a table scan or a fast full index scan depends on the number of blocks to be scanned and the multiblock read count value. The cost of an index scan depends on the levels in the B-tree, the number of index leaf blocks to be scanned, and the number of rows to be fetched using the rowid in the index keys. The cost of fetching rows using rowids depends on the index clustering factor.
The join cost represents the combination of the individual access costs of the two row sets being joined, plus the cost of the join operation.

Generating Plans

The main function of the plan generator is to try out different possible plans for a given query and pick the one that has the lowest cost.
Many different plans are possible because of the various combinations of different access paths, join methods, and join orders that can be used to access and process data in different ways and produce the same result.
A join order is the order in which different join items, such as tables, are accessed and joined together.
For example, in a join order of table1, table2, and table3, table table1 is accessed first. Next, table2 is accessed, and its data is joined to table1 data to produce a join of table1 and table2. Finally, table3 is accessed, and its data is joined to the result of the join between table1 and table2.

4.2 Reading and Understanding Execution Plans

To execute a SQL statement, Oracle might need to perform many steps. Each of these steps either retrieves rows of data physically from the database or prepares them in some way for the user issuing the statement. The combination of the steps Oracle uses to execute a statement is called an execution plan. An execution plan includes an access path for each table that the statement accesses and an ordering of the tables (the join order) with the appropriate join method.

Overview of EXPLAIN PLAN

You can examine the execution plan chosen by the optimizer for a SQL statement by using the EXPLAIN PLAN statement. When the statement is issued, the optimizer chooses an execution plan and then inserts data describing the plan into a database table. Simply issue the EXPLAIN PLAN statement and then query the output table.
These are the basics of using the EXPLAIN PLAN statement:

·         Use the SQL script UTLXPLAN.SQL to create a sample output table called PLAN_TABLE in your schema.

·         Include the EXPLAIN PLAN FOR clause prior to the SQL statement

·         After issuing the EXPLAIN PLAN statement, use one of the scripts or package provided by Oracle to display the most recent plan table output.

·         The execution order in EXPLAIN PLAN output begins with the line that is the furthest indented to the right. The next step is the parent of that line. If two lines are indented equally, then the top line is normally executed first.

Example 13-1 (http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/optimops.htm#i73885)

 uses EXPLAIN PLAN to examine a SQL statement that selects the employee_id, job_title, salary, and department_name for the employees whose IDs are less than 103.

Source : Link

>With Oracle 10g, CBO will be the only optimizer as RBO will be dead.

Most important task

The most important task for the CBO is to design an execution plan for an SQL statement.

Permutations

The CBO takes an SQL statement and tries to weigh different ways (plan) to execute it.

It assigns a cost to each plan and chooses the plan with the smallest cost.

The number of permutations of plans the cbo tries can be set with optimizer_max_permutations.

Costs

The cost for a statement is calculated like: physical io + logical io / 1000 + net io.

Tuning the CBO

One of the most important parameters to tune the CBO is optimizer_index_cost_adj.

Actually, optimizer_index_cost_adj doesn't tune CBO itself but is important for the CBO in deciding for an execution plan which affects the speed of the SQL query.

Other important parameters are: optimizer_search_limit and optimizer_max_permutations.

Gathering statistics

CBO needs some statistics in order to assess the cost of the different access plans. These statistics include values such as

• size of tables
• size of indexes
• number of rows in the tables
• number of distinct keys in an index
• number of levels in a B* index
• average number of blocks for a value
• average number of leaf blocks in an index

These statistics can be gathered with dbms_stats and the monitoring feature.

Setting OPTIMIZER_MODE

The optimizer_mode determines if Oracle will use CBO or RBO.

As with Oracle 10g, there is only CBO, there is no imperative need to set this parameter to use the CBO.

However, it will influence the the plan either to have

         the first rows as soon as possible (first_rows) or

                                              all rows (all_rows).

Watching CBO do its decisions

Setting diagnostic event 10053 allows to watch the CBO how it decides for an execution plan.

>By default, the CBO weighs logical io as expensive as physical io.

 

This behaviour can be changed by adjusting

optimizer_index_caching and

optimizer_index_cost_adj.

optimizer_index_cost_adj

The default value of 100 means that an index access to a table is just as expensive as a full table scan.

On most systems, the default value of 100 should be lowered.

optimizer_index_caching

This value is a percentage that indicates how many blocks are found in the buffer cache when an index is read.
This value is only for index blocks, not for blocks in the table that the index points to.

On most systems, the default value of 0 is too low and should be altered.

Optimizer plan stability

Plan stability allows to maintain the same query execution plan for the same SQL statements.

OUTLN is the schema to store metadata for stored outlines.

Query execution plan

Whenever an SQL statement is executed, Oracle (that is the RBO or CBO) designs an execution plan for it.

This execution plan is basically a step by step instruction for how the statement must be executed.

That is, the order in which tables are read,

 if indexes are used, which join methods are used to join tables and so on.

The execution plan for an SQL statement can be viewed with the explain plan statement.

The query execution plan is elaborated during an SQL statement's parse phase.

Join methods in Oracle

When a select statement gets data from multiple tables (or, more accuratly called: row sources), the table are said to be joined.

Different join methods are possible in Oracle. Such a join method determines the mechanism (or the algorithm, if you will) how such row sources are joined.

Merge join

Also called sort merge join.

A merge join basically sorts all relevant rows in the first table by the join key , and also sorts the relevant rows in the second table by the join key, and then merges these sorted rows.

The behaviour of merge joins is influenced by the initialization parameters

sort_area_size and

db_file_mutliblock_read_count.

Nested loops

The nested loop iterates over all rows of the outer table.

If there are conditions in the where clause of the SQL statement that apply to the outer table only, it checks whether those apply.

If they do, the corresponding rows (from the where condition) in the joined inner table are searched.

These rows from the inner table are either found using an index (if a suitable exists) or by doing a full table scan.

Hash join

A hash join (ideally) takes the smaller table (or row source), iterates over its rows and performs a hash algorithm on the columns for the where conditions between the tables and stores the result.

After it has finished, it iterates over the other table and performs the same hashing algorithm on the joined columns.

It then searches the previously built hashed values and if they match, it returns the row.

Cluster join

Anti join

Used for queries with a not in.

Hints for Changing the Query Optimizer Goal