What is Optimization?

The optimizer considers a number of factors to make what is usually the best choice among its alternatives. However, an application designer usually knows more about a particular application's data than the optimizer could know. Despite the best efforts of the optimizer, in some situations a developer can choose a more effective way to execute a SQL statement than the optimizer can.

Note: The optimizer may not make the same decisions from one version of Oracle to the next. In future versions of Oracle, the optimizer may make different decisions based on better, more sophisticated information available to it.

Execution Plans

Example

This example shows an execution plan for this SQL statement:

SELECT ename, job, sal, dname 

	FROM emp, dept 

  WHERE emp.deptno = dept.deptno 

	AND NOT EXISTS 

		(SELECT * 

			FROM salgrade

		  WHERE emp.sal BETWEEN losal AND hisal);

This statement selects the name, job, salary, and department name for all employees whose salaries do not fall into any recommended salary range.

Figure 5 - 1 shows a graphical representation of the execution plan.

Figure 5 - 1. An Execution Plan

Steps of Execution Plan

Each step of the execution plan either retrieves rows from the database or accepts rows from one or more row sources as input:

• Steps indicated by black boxes physically retrieve data from an object in the database. Such steps are called access paths:

• Steps 3 and 6 read all the rows of the EMP and SALGRADE tables, respectively.

• Step 5 looks up each DEPTNO value returned by step 3 in the PK_DEPTNO index. There it finds the ROWIDS of the associated rows in the DEPT table.

• Step 4 retrieves from the DEPT table the rows whose ROWIDs were returned by Step 5.

• Steps indicated by white boxes operate on row sources:

• Step 2 performs a nested loops operation, accepting row sources from Steps 3 and 4, joining each row from Step 3 source to its corresponding row in Step 4, and returning the resulting rows to Step 1.

• Step 1 performs a filter operation. It accepts row sources from Steps 2 and 6, eliminates rows from Step 2 that have a corresponding row in Step 6, and returns the remaining rows from step 2 to the user or application issuing the statement.

Order of Performing Execution Plan Steps

To execute the statement for Figure 5 - 1, for example, Oracle performs the steps in this order:

• First, Oracle performs Step 3, and returns the resulting rows, one by one, to Step 2.

• For each row returned by Step 3, Oracle performs these steps:

• Oracle performs Step 5 and returns the resulting ROWID to Step 4.

• Oracle performs Step 4 and returns the resulting row to Step 2.

• Oracle performs Step 2, accepting a single row from Step 3 and a single row from Step 4, and returning a single row to Step 1.

• Oracle performs Step 6 and returns the resulting row, if any, to Step 1.

• Oracle performs Step 1. If a row is returned from Step 6, Oracle returns the row from Step 2 to the user issuing the SQL statement.

Oracle performs the parent step and all cascaded steps once for each row in turn retrieved by the child step. The parent steps that are triggered for each row returned by a child step include table accesses, index accesses, nested loops joins, and filters.

Some parent steps require all rows from their child steps before they can be performed. For such a parent step, Oracle cannot perform the parent step until all rows have been returned from the child step. Such parent steps include sorts, sort-merge joins, group functions, and aggregates.

The EXPLAIN PLAN Command

ID		OPERATION		OPTIONS		OBJECT_NAME 

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

0		SELECT STATEMENT 

1		  FILTER 

2		    NESTED LOOPS 

3		      TABLE ACCESS	FULL		EMP 

4		      TABLE ACCESS	BY ROWID	DEPT 

5		        INDEX	UNIQUE SCAN	PK_DEPTNO 

6		    TABLE ACCESS	FULL		SALGRADE 

You can obtain such a listing by using the EXPLAIN PLAN command and then querying the output table. For information on how to use this command and produce and interpret its output, see Appendix A, "Performance Diagnostic Tools".

Each box in the figure and each row in the output table corresponds to a single step in the execution plan. For each row in the listing, the value in the ID column is the value shown in the corresponding box in the figure.

For a complete description of the EXPLAIN PLAN command, see Appendix A, "Performance Diagnostic Tools".

Oracle's Approaches to Optimization

rule-based	Using the rule-based approach, the optimizer chooses an execution plan based on the access paths available and the ranks of these access paths in Table 5 - 1.
cost-based	Using the cost-based approach, the optimizer considers available access paths and factors in information based on statistics in the data dictionary for the objects (tables, clusters, or indexes) accessed by the statement to determine which execution plan is most efficient. The cost-based approach also considers hints, or optimization suggestions in the statement placed in a comment.

The Rule-Based Approach

The Cost-Based Approach

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

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

The cost is an estimated value proportional to the expected resource usage needed to execute the statement using the execution plan. The optimizer calculates the cost based on the estimated computer resources, including but not limited to I/O, CPU time, and memory, required to execute the statement using the plan.

Non-parallel execution plans with greater costs take more time to execute than those with smaller costs. (When using the parallel query option, resource usage is not related to elapsed time.)

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

Oracle can also optimize a statement with the goal of best response time, or minimal resource usage necessary to process the first row accessed by a SQL statement. For information on how the optimizer chooses an optimization approach and goal, see the section "Choosing an Optimization Approach and Goal" .

Note: Throughput and response times apply only to non-parallel statements. When using the parallel query option, resource usage is not related to elapsed time. See Chapter 6, "Parallel Query Option", for more information about the parallel query option.

Statistics Used for the Cost-Based ApproachThe cost-based approach uses statistics to estimate the cost of each execution plan. These statistics quantify the data distribution and storage characteristics of tables, columns, and indexes. The ANALYZE command generates these statistics. Using these statistics, the optimizer estimates how much I/O, CPU time, and memory are required to execute a SQL statement using a particular execution plan.

The following data dictionary views display the statistics:

• USER_TABLES, ALL_TABLES, and DBA_TABLES

• USER_TAB_COLUMNS, ALL_TAB_COLUMNS, and DBA_TAB_COLUMNS

• USER_INDEXES, ALL_INDEXES, and DBA_INDEXES

• USER_CLUSTERS and DBA_CLUSTERS