Summary of Oracle optimizer Optimization Methods

Oracle is the world's leading information management software developer and is well known for its complex relational database products. This article introduces the Oracle optimizer, which is a very useful tool. Before executing an SQL statement, Oracle needs to analyze the statement execution plan and then execute it according to the execution plan. The execution plan of the Analysis Statement is completed by the Optimizer.

In different cases, an SQL statement may have multiple execution plans, but at a certain point in time, there must be only one execution plan that is optimal and takes the least time. I believe you will use Pl/SQL Developer, Toad and other tools to view the execution plan of a statement. However, you may have questions about Rule, Choose, First rows, and All rows, because I did the same thing at the beginning. At that time, I also wondered why the above different items were selected and the execution plan changed?

1. Oracle optimizer Optimization Method

Oracle optimizer has two types of Optimization Methods: Rule-Based Optimization (RBO) and Cost-Based Optimization (Cost-Based Optimization, (hereinafter referred to as CBO ).

A. RBO: when analyzing SQL statements, the optimizer follows some internal Oracle rules. For example, we usually use indexes when a column in a where clause has an index.

B. CBO: According to the meaning of the word, it refers to the Cost of the statement (Cost). The Cost here mainly refers to the Cpu and memory. When determining whether this method is used, the optimizer mainly refers to the statistical information of tables and indexes. The statistical information shows the table size, the number of rows, and the length of each row. These statistics are not available in the database at first, but are only available after you perform analyze. In many cases, the Optimizer may make an incorrect execution plan when the statistics expire, because we should update this information in a timely manner. In Oracle8 and later versions, CBO is recommended for Oracle columns.

We need to make it clear that indexes are not necessarily optimal. For example, if a table has only two rows of data, I/O can complete the entire table search, at this time, the index requires two I/O operations. At this time, it is best to perform full table scan on the table.

2. Oracle optimizer optimization Mode)

The optimization modes include Rule, Choose, First rows, and All rows, which we mentioned above. I will explain it as follows:

Rule: a Rule-based approach.

Choolse: This is what we should note. By default, Oracle uses this method. When a table or index has statistical information, it adopts the CBO method. If the table or index does not have statistical information, the table is not particularly small, in addition, when the corresponding column has an index, the index is adopted and the RBO method is adopted.

First Rows: It is similar to the Choose method. The difference is that when a table has statistics, it returns the First few Rows of the query in the fastest way, the overall response time is reduced.

All Rows: This is what we call the Cost method. When a table has statistics, it returns All Rows of the table in the fastest way, improves the query throughput in general. If no statistical information is available, the rule-based approach is adopted.

3. How to set which optimization mode to choose

◆ A. Instance level

In init. set OPTIMIZER_MODE = RULE, OPTIMIZER_MODE = CHOOSE, OPTIMIZER_MODE = FIRST_ROWS, and OPTIMIZER_MODE = ALL_ROWS in the ora file to CHOOSE the four methods mentioned in 3, if you do not set the OPTIMIZER_MODE parameter, Choose is used by default.

◆ B. Sessions level

Use SQL> ALTER SESSION SET OPTIMIZER_MODE =.

◆ C. Statement level

Hint is required.

1. Description of Oracle database index and SQL Server
2. Code and actual operations for Oracle to change the tablespace size
3. Three different types of Oracle optimizer
4. Introduction to three types of Oracle optimizer
5. 26 parameters of the Oracle optimizer