Oracle Optimizer: Migrating to using the cost-based optimizer-----series 1.1

oracle| optimization
Oracle Optimizer: Migrating to using the cost-based optimizer-----series 1.1



If you are developing an application in a previous version of Oracle (7.0 or earlier), the database uses the Rule-based optimizer (RBO), which will help you understand the Oracle optimizer and migrate to a number of efficient methods based on the cost optimizer (hereinafter called the CBO). Here is the first part of the five most



First part

1. What is an optimizer?

2. Why to optimize?

3. Available Optimizer.

4. Why remove Rbo?

5. Why migrate to the CBO?



Part II

1. Affect the initialization parameters of the CBO.

2. Internal Oracle parameters affecting the CBO.



Part III

1. Upgrade to the CBO settings change.

2. Generating statistical information

3. DML monitoring



Part Fourth

1. Hint.

2. Storage Overview

3. SYS mode statistical data



Part Fifth

1. New Permissions

2. How to analyze the implementation plan in the CBO

3. CBO details of Oracle application 11i

4. Conclusion



1. What is an optimizer?

In Oracle, there may be more than one way to execute a query, with the best level of execution plan, or the fastest minimum cost to return output and achieve optimal resource utilization. The optimizer builds an execution plan. The optimizer is the most efficient execution plan that runs in the database to obtain an engine that executes a list of paths based on different conditions and chooses to run the query. Once the plan build is executed, it executes the output. In Oracle, the optimizer is associated with a DML statement.



2. Why to optimize?

You know! Optimizing a query that takes advantage of minimal time and optimal resources for execution means fast and efficient. For resources, it means CPU utilization, disk input and output, memory consumption, and other range of network operations. Regardless of how well your server is on these resources, inappropriate or less desirable queries are always costly and slow down your task, or affect other processing on the server. The range of queries that depend on a variety of factors is expensive, including the size of the extracted result set, Scans the size of the data that retrieves the result set and the immediate load on the system. Proper syntax optimization will save users ' running time and unnecessary resource utilization.



3. Available Optimizer

Oracle has two modes of optimizer, based on rules and cost based, which determines the best execution plan.

This paper focuses on the CBO, briefly outlining Rbo.



3.1 rule-based optimization (RBO)

Rbo follows a simple hierarchical methodology. Rbo uses 15 levels of points, and when a query is received, the optimizer evaluates the number of points used and then selects the execution path of the optimal level (the fewest number) to run the query, with 15 key levels as follows:



. Use separate records for ROWID

. Use separate records for cluster connections.

Separate records using the primary key of a hash cluster.

. Use separate records for primary keys.

. Cluster connections.

. hash Cluster Connection

. index cluster PRIMARY key.

. Composite PRIMARY key.

. A single column primary key.

. The associative range lookup of indexed columns.

. The non-associative range lookup of indexed columns.

. sort merge Joins.

. Max or min for index columns

The order by of the indexed column.

. full table Scan.

For example, if you generate a query that matches a table with two columns exactly in a WHERE clause condition, a column has a primary key (corresponding to a separate record using the primary key). While the other column has a Non-key key (corresponding to a single-column primary key), Rbo prefers a primary key (corresponding to a separate record using a primary key) rather than a Non-key key (corresponding to a single-column primary key).

When it comes to accessing multiple tables in a query, the optimizer needs to make sure that the table is a driver table. Rbo generates a set of join orders, each table as the first table, the optimizer then selects the best plan from the result set of the execution plan. The optimizer evaluates different criteria such as (minimum nesting loops, minimal sort merge joins, best table access paths, and so on), and if you still can't compare the results, The optimizer selects the first table in the FROM clause of the query as the driver table. Therefore, the coding practice under normal conditions will put the driver table to the rightmost. Other tables are followed in the FROM clause in the order of access. That is, the order of the tables is the access order from right to left.

Note that the operators used to search for columns also play a role of decision level, sometimes even considering the time of the index as a level

For example, the following table shows the use of indexes on columns 1 and 2 if they are connected by "=" on the WHERE clause

Cases:

SELECT * from am79 where col1 = 1 and col2 = ' Amar ';

--Here both col1 and col2 are indexed.



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

Normal Index Types | Index used in RBO

Column1 (a) Column2 (b) column1+column2 (c) |

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

Non-unique Non-unique C

Non-unique Non-unique A + b

Non-unique Non-unique Non-unique C

Unique Non-unique A

Unique Non-unique A

Unique unique B (the most recent index created)

Unique unique unique C

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

-the above is tested on Oracle 8.1.7.1.

-in Case of non-unique single column indexes, both indexes are.

-in case of the unique indexes, they are not combined for execution to, any one is taken.

-preference is given to the index available with the ' = ' operator column, than with

others operators.

-don ' t create bitmap & function-based indexes, these won't be work in RBO.

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



Rbo preference for most of the previous versions of Oracle as execution plan paths, this choice is uniform. Queries always produce the same method for applications that run on different databases (to be continued).