When query is submitted to oracle server, various processing steps are performed to execute query. Following main steps are involved in this process.
1- Query Parsing
2- Optimizer determines most efficient way to execute query and generates an execution plan.
3- Finally SQL engine executes query.
In 1st step, SQL server checks syntax of query and outputs error if found.
After parsing of query, optimizer generates execution plan. Execution plan produced by optimizer depends on two main possible goals
- Throughput: Throughput means to consume least amount of resources needed to process all rows accessed by query.
- Response Time : Means to consume least amount of resources to process first row.
Application in which users views all results of query, best throughput is preferred. To achieve best Throughput oracle server uses Cost Based Optimization (CBO).
In web like application in which users generally tends to view only first few results, best response time is preferred and Rule Based Optimization (RBO) is used in this case.
By default optimizer behavior depends on OPTIMIZER_MODE initialization parameter. Possible values for OPTIMIZER_MODE include
CHOOSE: if data dictionary statistics for at least one of tables in query are available, CBO is used otherwise RBO is used.
All_ROWS: CBO is used.
FIRST_ROWS_N: CBO is used to get best response time for first n rows where n can be any positive number.
FIRST_ROWS: A mixed approach of CBO and RBO is used.
RULE: RBO is used.
Some new features like Partitioned Table and Indexes, Index Organized Table, Function Based Index require CBO. In this case optimizer chooses CBO even if the value for OPTIMIZER_MODE is set to RULE. Hints can also be used to override default value of OPTIMIZER_MODE.
Oracle strongly recommends CBO as RBO is obsolete in Oracle 10g and future releases. Official documentation for 10g do not include CHOOSE and RULE options. But these are still supported for backward compatibility.
When new database is released, the new execution plan may change because more information is now available to optimizer, this may affect the performance of previously tuned query. OPTIMIZER_FEATURES_ENABLE initialization parameter can be used to set optimization according to previous release. For example to keep the behavior of optimizer to 8.1.5 release, set parameter as
Optimizer consists of following key components
- Query Transformer: Query transformer analysis the query and makes necessary changes to query for example query transformer unnest a sub-query or view
- Estimator: Estimator estimates the cost of the different execution plan
- Plan Generator: Plan Generator calculates the plan with least cost.
The output of the CBO or RBO is an execution plan which tells execution engine following
- Access Paths
Access path means how table will be accessed, possibilities include
- Full Table Scan
- Rowid Scan
- Index Scan
- Cluster Scan
- Hash Scan
- Sample Table Scan
- Join Orders
For a query joining more than two tables, optimizer decides which two table will be joined first and which will be joined to result and so on.
- Joins Methods
Tables can be joined using following methods
- Nested Loop joins
- Hash joins
- Sort Merge Joins
- Cartesian joins
- Outer joins
SQL execution engine use all above information to output results.