Application of CBO and RBO in Oracle

Oracle provides two types {
Tagshow (event, 'SQL ');
} "Href =" http://www.cnblogs.com/sopost/admin/javascript:; "target =" _ Self ">SQLOptimizer. CBO was introduced in oracle7, but it was only mature in Oracle8i. Oracle has clearly stated that in Versions later than Oracle9i (Oracle 10g), RBO will no longer support it. 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, they must be fully considered, this avoids the sharp decline in SQL statement performance. However, for new application systems, you can directly use CBO to write SQL statements, analyze execution plans, and perform performance tests in CBO mode, this requires developers to be familiar with CBO features.

The following are some notes for writing SQL statements in CBO:

1. RBO has been adopted since Oracle version 6 and has a set of strict rules for use. As long as you write SQL statements according to it, no matter what the 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, from which the lowest cost {
Tagshow (event, '% B7 % BD % B0 % b8 ');
} "Href =" http://www.cnblogs.com/sopost/admin/javascript:; "target =" _ Self ">SolutionAs the actual running solution. The Cost Calculation for 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, you must analyze the table and related indexes (using 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, it saves programmers the debugging time to select an optimal solution from multiple possible "execution plans", but in some cases there will also be problems.
Typical problems are: Sometimes, it indicates that an index is created, but the related index is obviously not used in the query process, resulting in a long query process, occupying a huge amount of resources, in this case, you need to carefully analyze the execution plan to find out the cause. For example, you can check whether related indexes are allowed in the connection sequence. Assume that the deptno column of the table EMP has an index, and the deptno column of the table dept has no index. The where statement has the condition EMP. deptno = Dept. deptno. During the NL connection, EMP is first accessed as the External table. Due to the connection mechanism, the External table data is accessed in full table scan and EMP. the index on deptno is obviously not used. A full index scan or quick full index scan can be performed on deptno.

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 "from" clause of the SQL statement must be analyzed using the analyze command. If the "from" clause is followed by a view, the basic tables in this view must also be analyzed using the analyze command. Otherwise, Oracle automatically performs the analyze command analysis before executing the SQL statement, this causes extremely slow SQL statement execution.

6. When using CBO, the number of tables following the "from" clause of the SQL statement should not be too large, because when CBO selects the table connection sequence, the factorial operation is performed on the table after the "from" clause, and the best join order is selected. If there are six tables in the "from" clause, the optional join sequence is 6*5*4*3*2*1 = 720, and CBO selects one of them, if the "from" clause has 12 tables, the optional connection sequence is 2*11*10*9*8*7*6*5*4*3*2*1 = 479001600, you can imagine how much CPU time will be consumed when selecting one type? 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 the 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 supports 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. Even if an index is built on the relevant column, smj can only avoid data sorting because of the existence of the index. 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. For tables with frequent data additions, deletions, and changes, it is best to analyze tables and indexes on a regular basis. The SQL statement "analyze {
Tagshow (event, 'table ');
} "Href =" http://www.cnblogs.com/sopost/admin/javascript:; "target =" _ Self ">TableXxx 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 determines that the values of the columns in the table are evenly distributed across all data rows by default. That is to say, each deptno value corresponds to 0.25 million data rows. Assuming that the SQL Search Condition deptno = 10, using the index on the eptno column for data search efficiency is often no higher than the full table scan. Oracle naturally turns a blind eye to the index ", the index is not highly selective.

In another case, if 1 million data rows are not evenly distributed among the four deptno values, 0.99 million rows correspond to values of 10, rows correspond to values of 20, rows correspond to values of 30, and rows correspond to values of 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 be 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, enables Oracle to index highly selective search values.

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

Show parameter optimizer_mode -- check the mode in which Oracle is located. The default setting since Oracle V7 is "choose", that is, if you want to query the analyzed table, select CBO and whether to select RBO. if this parameter is set to "rule", select RBO regardless of whether the table has been analyzed or not, unless it is forced by hint in the statement.

Analyze table xxx compute statistics for all indexes -- analyze the table.