Oracle rbo cbo SQL Optimizer

ORACLE provides two types of SQL optimizer: CBO and RBO. CBO was introduced in ORACLE7, but it was only mature in ORACLE8i. ORACLE has explicitly stated that
In later versions (ORACLE 10G), RBO will no longer be supported. Therefore, selecting CBO is an inevitable trend.

CBO and RBO, as different SQL optimizers, have a significant impact on the execution plan of SQL statements. to port existing applications from RBO to CBO
Consider these effects to avoid sharp decline in SQL statement performance. However, for new application systems, you can directly use CBO to write and analyze SQL statements in CBO mode.
The execution plan and performance test require developers to be familiar with the CBO features. The following are some notes for writing SQL statements in CBO:

1. RBO has been adopted since ORACLE 6 and has a set of strict rules for use. As long as you write SQL statements according to it, no matter what content in the data table is, it will not affect your
"Execution Plan", that is, it is not "sensitive" to data. CBO calculates the "cost" of various possible "execution plans", that is, cost, which selects the lowest cost solution as the actual
Running solution. The cost calculation of each "Execution Plan" depends on the statistical distribution of data in the data table. The ORACLE database itself is not clear about the statistical distribution and must be divided
ANALYZE tables and related indexes (use the ANALYZE command) to collect the data required by CBO.

2. When using CBO, when writing SQL statements, you do not need to consider the order of the tables or views behind the "FROM" clause and the conditional order behind the "WHERE" clause;
Many new technologies adopted by ORACLE since version 7 are based on CBO, such as star join arrangement queries, hash join queries, function indexes, and parallel queries.

3. Generally, the "Execution Plan" selected by CBO is not inferior to the "Execution Plan" of RBO, and CBO has less demanding requirements on programmers than RBO, saving the process
The time spent by the sequencer to select an optimal solution from multiple possible "execution plans", but problems may also occur in some cases.
Typical problems are: Sometimes, it indicates that an index is created, but the related index is obviously not used in the query process. As a result, the query process takes a long time and consumes a large amount of resources.
Analyze the execution plan carefully to find out the cause. For example, you can check whether related indexes are allowed in the connection sequence. Assume that the deptno column in the table emp has an index, and the deptno column in the table dept does not exist.
Index. The WHERE statement has the condition emp. deptno = dept. deptno. During NL connection, emp is first accessed as the External table. Because of the Connection Mechanism, the External table data access method is
During full table scan, the indexes on emp. deptno are obviously not used. You can perform full index scan or quick full index scan at most.

4. If the execution plan of a statement using RBO is indeed better than that of CBO, you can add the "rule" prompt to force the use of RBO.

5. When using CBO, all the tables after the SQL statement "FROM" clause must be analyzed using the ANALYZE command. If the "FROM" clause is followed by a view
Basic Tables must also be analyzed using the ANALYZE command. Otherwise, ORACLE will automatically ANALYZE the ANALYZE command before executing this SQL statement, which will greatly
This results in extremely slow SQL statement execution.

6. When using CBO, the number of tables after the SQL statement "FROM" clause should not be too large, because when selecting the table join sequence, CBO performs a factorial on the table after the "FROM" clause.
Operation. Select the best connection sequence. If the "FROM" clause has six tables, the optional join sequence is 6*5*4*3*2*1 = 720.
If the "FROM" clause has 12 tables, the optional connection sequence is 12*11*10*9*8*7*6*5*4*3*2*1 = 479001600.
How much CPU time will be consumed? If you really want to access many tables, it is best to use the ORDER prompt to forcibly use the "FROM" clause to set the access sequence of the table.

7. When using CBO, the SQL statement cannot reference the system data dictionary table or view, because the system data dictionary table has not been analyzed, which may lead to a very poor "Execution Plan ". However
Do not analyze data dictionary tables without authorization. Otherwise, deadlocks may occur or the system performance may seriously degrade.

8. When using CBO, pay attention to which type of table connection is used. ORACLE has Sort Merge Join (SMJ), Hash Join (HJ), and Nested Loop Join.
(NL ). CBO sometimes focuses on SMJ and HJ, but in OLTP systems, NL is generally better because it uses indexes efficiently.
When two tables are connected and the target column of the internal table has an index, only the Nested Loop can effectively use the index. SMJ, even if an index is built on the relevant column
To avoid data sorting. Due to HASH calculation, the existence of Indexes has almost no impact on the data query speed.

9. When using CBO, make sure that sufficient statistics are collected for tables and related indexes. It is recommended that you analyze tables and indexes on a regular basis for tables with frequent data additions, deletions, and changes. You can use SQL
Statement "analyze table xxx compute statistics for all indexes;" ORACLE can make the right choice only when it fully reflects the actual statistical data.

10. When using CBO, pay attention to the data distribution of the value of the indexed field, which will affect the execution plan of the SQL statement. For example:
The table emp contains 1 million rows of data, but the emp. deptno column has only four different values, such as 10, 20, 30, and 40. Although emp has many data rows, ORACLE defaults
The values of the columns in the table are evenly distributed across all data rows. That is to say, each deptno value corresponds to 0.25 million data rows. Assume that the SQL Search Condition DEPTNO = 10, and use
The efficiency of the index on the deptno column in data search is usually not higher than that of the full table scan. ORACLE naturally turns a blind eye to the index and considers the index to be less selective.

In another case, if 1 million data rows are not evenly distributed among the four types of deptno values, 0.99 million rows correspond to values of and rows correspond to 20 and rows.
The corresponding value is 30, and the value of line 2000 is 40. In this data distribution pattern, when searching for other deptno values except 10, there is no doubt that if the index can be applied, the efficiency will
It is much higher. We can analyze the index column separately, or use the analyze statement to create a histogram for the column to collect sufficient statistics for the column so that ORACLE can search
The value with higher cable selectivity can be indexed.

 

This article is from "one leaf in the wind"