RBO and CBO of Oracle are described in detail and optimized mode setting method

  • 2021-12-13 17:35:10
  • OfStack

The optimizer of Oracle has two optimization modes, namely, rule-based optimization mode (Rule-Based Optimization, abbreviated as RBO) and cost-based optimization mode (Cost-Based Optimization, abbreviated as CBO). In Oracle8 and later versions, CBO is recommended in the strong column of Oracle

RBO mode: When the optimizer analyzes the SQL statement, it follows some rules predetermined in Oracle. For example, it is common for us to go to the index when 1 column in an where clause has an index.

CBO mode: It is the cost of looking at statements (Cost), where the cost mainly refers to Cpu and memory. When deciding whether to use this method, the optimizer mainly refers to the statistics of tables and indexes. Statistics give information such as the size of the table, the number of rows, and the length of each row. These statistics are not available in the library at first, but appear after analyze. Many out-of-date statistics will cause the optimizer to make an incorrect execution plan, so these information should be updated in time.

Note: Indexing is not one fixed is excellent, for example, a table has only two rows of data, one IO can complete the retrieval of the whole table, while indexing at this time requires two IO, and then full table scanning (full table scan) is the best

The optimization modes include Rule, Choose, First rows and All rows:

Rule: Rules-based approach.

Choolse: This is the way Oracle is used by default. It refers to the way of CBO when a table or index has statistical information, and the way of RBO when the table or index has no statistical information, the table is not particularly small, and the corresponding column has indexes.

First Rows: It is similar to Choose, except that when a table has statistics, it returns the first few rows of the query in the fastest way, reducing overall response time.

All Rows: This is what we call Cost. When a table has statistics, it will return all the rows of the table in the fastest way, improving query throughput as a whole. If there is no statistical information, go RBO.

Set which optimization mode to choose:

A, Instance level We can set OPTIMIZER_MODE=RULE/CHOOSE/FIRST_ROWS/ALL_ROWS in initSID.ora file. If OPTIMIZER_MODE parameter is not set, Choose mode is used by default.

B and Sessions levels are set by ALTER SESSION SET OPTIMIZER_MODE=RULE/CHOOSE/FIRST_ROWS/ALL_ROWS.

C, the statement level is set with Hint (/* +... */)
Why is it that a field in a table is obviously indexed, but the execution plan is not indexed?
1. The optimization mode is the mode of all_rows
2. The table has been analyze and has statistical information (most likely, the statistical information is wrong)
3. The tables are small and, as mentioned above, the Oracle optimizer does not think it is worth indexing.

We can see if 1 table or index under 1 is statistics

SELECT * FROM user_tables 
     WHERE table_name=<table_name>
     AND num_rows is not null;
     SELECT * FROM user_indexes
     WHERE table_name=<table_name>
      AND num_rows is not null;

When we use CBO, we should update the statistics of tables and indexes in time to avoid creating unrealistic execution plans.
ANALYZE table table_name COMPUTE STATISTICS; 
     ANALYZE INDEX index_name ESTIMATE STATISTICS;


Related articles: