Summary of ORACLE optimizer RBO and CBO, and oracle rbocbo Optimization

Summary of ORACLE optimizer RBO and CBO, and oracle rbocbo Optimization

RBOAnd basic CBO concepts

The Optimizer in the Oracle database is also called the Query Optimizer ). It is an optimization tool for SQL analysis and execution. It is responsible for generating and developing SQL Execution plans. There are two types of Oracle optimizer: Rule-Based optimizer (RBO) and cost-based optimizer (CBO)

RBO: Rule-Based Optimization Rule-Based Optimizer

CBO: Cost-Based Optimization Optimizer

RBO has been used since ORACLE 6 and has been used since ORACLE 9i. at the beginning of ORACLE 10 Gb, ORACLE has completely discarded RBO, which has a set of strict rules for use, as long as you follow it to write SQL statements, regardless of the content in the data table, it does not affect your "Execution Plan", that is, RBO is not "sensitive" to data. It selects the execution plan for the specified table based on the ORACLE-specified priority rules. For example, in a rule, the index priority is greater than full table scan. RBO selects an execution plan based on the available access path and access path level. In RBO, the SQL statement often affects the execution plan. It requires developers to have a good understanding of the RBO rules. The SQL script written by cainiao may have very poor performance. As RBO is abandoned, it is gradually unknown. Maybe only the DBA of the older generation has a deep understanding of it. The official document details the RBO access path:

RBO Path 1: Single Row by Rowid

RBO Path 2: Single Row by Cluster Join

RBO Path 3: Single Row by Hash Cluster Key with Unique or Primary Key

RBO Path 4: Single Row by Unique or Primary Key

RBO Path 5: Clustered Join

RBO Path 6: Hash Cluster Key

RBO Path 7: Indexed Cluster Key

RBO Path 8: Composite Index

RBO Path 9: Single-Column Indexes

RBO Path 10: Bounded Range Search on Indexed Columns

RBO Path 11: Unbounded Range Search on Indexed Columns

RBO Path 12: Sort Merge Join

RBO Path 13: MAX or MIN of Indexed Column

RBO Path 14: order by on Indexed Column

RBO Path 15: Full Table Scan

CBO is a more reasonable and reliable optimizer than RBO. It was introduced from ORACLE 8, but it has gradually matured in ORACLE 9i and completely replaced RBO in ORACLE 10g, CBO is the "COST" for calculating various possible "execution plans", namely, COST. It selects the lowest COST execution scheme as the actual running scheme. It depends on the statistical information of database objects. Whether the statistical information is accurate or not affects CBO to make the best choice. If you find that the objects (tables, indexes, and so on) involved in an SQL statement are not analyzed or counted, ORACLE uses a dynamic sampling technique, dynamically collect some data information on tables and indexes.

With regard to RBO and CBO, I have an image analogy: before the age of big data, business may be able to make good decisions and follow market changes through years of accumulated experience (RBO. However, in the big data era, if we rely on previous experience to make decisions, rather than making decisions based on big data, data analysis, and data mining, we may make wrong decisions. This is why more and more companies are paying more and more attention to BI and data mining. E-commerce, gaming, telecom, and other industries have been widely applied, I used to perform BI Analysis in the database department of a game company to discover potential consumer users. So far, the image is quite deep.

CBOAdvantages and disadvantages of RBO

CBO is better than RBO because RBO is a rigid and outdated optimizer that only recognizes rules and is not sensitive to data. After all, the rule is dead, and the data is changed. The execution plan generated in this way is often unreliable and not optimal. The CBO can be reflected in many aspects. Let's take an example. This case comes from making Oracle run faster.

SQL> create table test as select 1 id ,object_name from dba_objects;

Table created.

SQL> create index idx_test on test(id);

Index created.

SQL> update test set id=100 where rownum =1;

1 row updated.

SQL> select id, count(1) from test group by id;

        ID   COUNT(1)

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

       100          1

         1      50314

As shown above, the data distribution in the test table is extremely uneven. ID = 100 has only one record, and ID = 1 has 50314 records. Let's first look at the execution plans of the two SQL statements under RBO.

SQL> select /*+ rule */ * from test where id =100;

Execution Plan

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

Plan hash value: 2473784974

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

| Id  | Operation                   | Name     |

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

|   0 | SELECT STATEMENT            |          |

|   1 |  TABLE ACCESS BY INDEX ROWID| TEST     |

|*  2 |   INDEX RANGE SCAN          | IDX_TEST |

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

Predicate Information (identified by operation id):

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

   2 - access("ID"=100)

Note

-----

   - rule based optimizer used (consider using cbo)

Statistics

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

          1  recursive calls

          0  db block gets

          3  consistent gets

          0  physical reads

          0  redo size

        588  bytes sent via SQL*Net to client

        469  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

SQL> 

 

SQL> select /*+ rule */ * from test where id=1;

50314 rows selected.

Execution Plan

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

Plan hash value: 2473784974

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

| Id  | Operation                   | Name     |

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

|   0 | SELECT STATEMENT            |          |

|   1 |  TABLE ACCESS BY INDEX ROWID| TEST     |

|*  2 |   INDEX RANGE SCAN          | IDX_TEST |

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

Predicate Information (identified by operation id):

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

   2 - access("ID"=1)

Note

-----

   - rule based optimizer used (consider using cbo)

Statistics

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

          1  recursive calls

          0  db block gets

       7012  consistent gets

         97  physical reads

          0  redo size

    2243353  bytes sent via SQL*Net to client

      37363  bytes received via SQL*Net from client

       3356  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

      50314  rows processed

From the execution plan, we can see that the execution plan of RBO is a little disappointing. For ID = 1, almost all the data meets the predicate conditions, indexing only adds additional overhead (because ORACLE first needs to access the index data block and finds the corresponding key value on the index, then access the corresponding data in the table according to the ROWID on the key value.) since we want to access almost all the data in the table, full table scan is naturally the best choice. RBO selects an incorrect execution plan. We can compare the SQL Execution Plan under CBO. Obviously, it is sensitive to data. The execution plan is adjusted based on the data volume in a timely manner. When the query condition is 1, it performs a full table scan; when the query condition is 100, it performs a range index scan. As follows:

SQL> select * from test where id=1;

50314 rows selected.

Execution Plan

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

Plan hash value: 1357081020

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

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT  |      | 49075 |  3786K|    52   (2)| 00:00:01 |

|*  1 |  TABLE ACCESS FULL| TEST | 49075 |  3786K|    52   (2)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

   1 - filter("ID"=1)

Note

-----

   - dynamic sampling used for this statement

Statistics

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

         32  recursive calls

          0  db block gets

       3644  consistent gets

          0  physical reads

          0  redo size

    1689175  bytes sent via SQL*Net to client

      37363  bytes received via SQL*Net from client

       3356  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

      50314  rows processed

SQL> select * from test where id =100;

Execution Plan

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

Plan hash value: 2473784974

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

| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT            |          |     1 |    79 |     2   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| TEST     |     1 |    79 |     2   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | IDX_TEST |     1 |       |     1   (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

   2 - access("ID"=100)

Note

-----

   - dynamic sampling used for this statement

Statistics

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

          9  recursive calls

          0  db block gets

         73  consistent gets

          0  physical reads

          0  redo size

        588  bytes sent via SQL*Net to client

        469  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

SQL> 

This alone shows why ORACLE strongly recommends the use of CBO, which is not supported since ORACLE 10 Gb. The waves in the Yangtze River lie on the beach.

CBOSummary of knowledge points

The CBO optimizer generates a set of possibly used execution plans based on SQL statements, estimates the cost of each execution Plan, and calls the Plan Generator to generate the execution Plan, compare the cost of the Execution Plan, and finally select an execution plan with the minimum cost. The Query optimizer consists of the Query Transform, Estimator, and Plan Generator.

CBOOptimizer Components

CBO consists of the following components:

·Query Transformer)

The function of the query converter is to change the query statement form to generate a better execution plan. It determines whether to rewrite the user's query (including view merging, predicate advancing, non-nested subquery/subquery anti-nesting, materialized view rewriting) to generate a better query plan.

The input to the query transformer is a parsed query, which is represented by a set of

query blocks. The query blocks are nested or interrelated to each other. The form of the

query determines how the query blocks are interrelated to each other. The main

objective of the query transformer is to determine if it is advantageous to change the

form of the query so that it enables generation of a better query plan. Several different

query transformation techniques are employed by the query transformer, including:

■ View Merging

■ Predicate Pushing

■ Subquery Unnesting

■ Query Rewrite with Materialized Views

Any combination of these transformations can be applied to a given query.

· Estimator)

The evaluators use a combination of complex algorithms to calculate the three values of information to evaluate the overall Cost of each execution plan: Selectivity, Cardinality, and Cost)

The scheduler generator generates different execution plans based on the possible Access Path, association method, and association sequence, let the query optimizer select a plan with the minimum execution cost from these plans.

· Plan Generator)

The scheduler generator generates a large number of execution plans, and then selects an execution plan with the lowest overall cost or the lowest overall cost.

Because different access paths, connection methods, and connection sequence can be combined, although data is accessed and processed in different ways, the same results can be produced.

I used tools to draw images for better understanding.

View ORACLE optimizer

SQL> show parameter optimizer_mode;

NAME                           TYPE        VALUE

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

optimizer_mode                 string      ALL_ROWS

Modify the ORACLE optimizer

The ORACLE 10g optimizer can modify the optimizer mode in three ways: system level, session level, and statement level, which is convenient and flexible.

The optional values of optimizer_mode are first_rows_n and all_rows. Among them, first_rows_n has first_rows_1000, first_rows_100, first_rows_10, first_rows_1.

In Oracle 9i, you can select first_rows_n, all_rows, choose, and rule as the optimizer mode:

Rule:Rule-based.

Choolse: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.

If OPTIMIZER_MODE=CHOOSE, if statistics do not exist, and if you do not add hints to SQL statements, then SQL statements use the RBO. You can use the RBO to access both relational data and object types. If OPTIMIZER_MODE=FIRST_ROWS, FIRST_ROWS_n, or ALL_ROWS and no statistics exist, then the CBO uses default statistics. Migrate existing applications to use the cost-based approach.

First Rows: It is similar to the Choose method. The difference is that when a table has statistical information, it will return the first few rows of the query in the fastest way, the overall response time is reduced.

All Rows:The default value in 10 Gb is the Cost method we call. When a table has statistics, it returns all rows of the table in the fastest way, improves query throughput in general

Although Oracle 10g does not support RBO, Oracle 10g Official documentation only supports first_rows and all_rows for optimizer_mode parameters. however, you can still set optimizer_mode to rule or choose. It is estimated that ORACLE is considered for transition or backward compatibility. As shown below.

System Level

SQL> alter system set optimizer_mode=rule scope=both;

System altered.

SQL> show parameter optimizer_mode

NAME                                 TYPE        VALUE

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

optimizer_mode                       string      RULE

Session level

Modify the optimizer mode at the session level. This mode is only valid for the current session. Other sessions still use the system optimizer mode.

SQL> alter session set optimizer_mode = first_rows_100;

Session altered.

Statement level

The statement level is achieved by using the hints prompt.

SQL> select/* + rule */* from dba_objects where rownum <= 10;

Oracle index optimization hiding Tool

Where do you see the optimizer. Oracle optimizer is based on two principles: cost-based (CBO) and rule-based (RBO ). If the data volume to be queried is less than 5% and an appropriate index is available, oracle uses the CBO by default. it chooses to query indexes to improve the query speed (performance ).
Of course, sometimes the optimizer is not so intelligent, and the index may be appropriate. for various reasons (such as inaccurate table statistics), the optimizer does not take the index, at this time, we can use oracle hints (mandatory index) to let the optimizer go through the RBO plan to improve performance.
 
In oracle, is the update statistics optimization function similar to informix?

Oracle optimizer has two options: CBO and RBO. CBO is cost-based, that is, statistics-based, and RBO is rule-based.
Starting from 10 Gb, oracle recommends CBO instead of RBO. CBO should be similar to update statistics optimization.
You can refer to "oracle cbo rbo" on Baidu.