How to optimize the Oracle database Optimizer

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 implemented 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, but you may
Rows has some doubts, because I used to do the same. At that time, I also wondered why the execution plan changed when I selected the different items above?

1. Oracle optimizer Optimization Method

Oracle optimizer has two optimization methods: Rule-Based Optimization (Rule-based
Optimization (RBO) and cost-based optimization (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 references the table and Cable
Statistical information. The statistical information shows the table size, the number of rows, and the length of each row. None of these statistics appear in the database at first after you perform analyze. In many cases, they expire in statistics.
Information will make the optimizer make an incorrect execution plan, 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, a full table scan (full
Table scan) is the best.

2. optimization mode of the Oracle optimizer (optermizer 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. Java Virtual Machine class loading process

All
Rows: This is what we call the cost method. When a table has statistical information, it returns all rows of the table in the fastest way, improving 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

You can set optimizer_mode = rule, optimizer_mode = choose,
Optimizer_mode = first_rows and optimizer_mode = all_rows select the four methods mentioned in 3. If you do not set
The optimizer_mode parameter uses the choose method by default.

B. Sessions level

Use SQL> alter session set optimizer_mode =.

C. Statement level

Hint is required.