Oracle Optimizer RBO and CBO introduction summary

RBO and the basic concept of the CBO

The optimizer in the Oracle database is also called the query Optimizer. It is an optimization tool for SQL analysis and execution, and it is responsible for generating and developing SQL execution plans. Oracle's optimizer has two types, rule-based optimizer (RBO) and cost-based optimizer (CBO)

rbo:rule-based Optimization rule-based Optimizer

cbo:cost-based optimization cost-based optimizer

Rbo has been adopted since Oracle 6 and has been used in Oracle 9i. Oracle 10g, Oracle has completely discarded RBO, it has a set of strict rules of use, as long as you follow it to write SQL statements, regardless of the contents of the data table, will not affect your "execution plan", that is, RBO data is not "sensitive" It chooses the execution plan for the specified table based on the precedence rules specified by Oracle. For example, in the rules, the priority of the index is greater than the full table scan; Rbo is based on the available access path and access path level to select the execution plan, in Rbo, SQL is often written to affect the execution plan, it requires developers to understand the rbo of the details, the novice wrote SQL script performance may be very poor. With the abandonment of Rbo, it is becoming unknown. Perhaps only the older generation of DBAs knows it more deeply. The official documentation on RBO's access path is detailed:

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

The CBO is a more reasonable and reliable optimizer than Rbo, which was introduced from Oracle 8, but matured in Oracle 9i, completely replacing Rbo in Oracle 10g, which is the "cost" of calculating various possible "execution plans" Choose the lowest cost execution plan, as the actual operation scheme. It relies on the statistical information of database objects, the accuracy of statistics will affect the CBO to make the best choice. If the objects involved (tables, indexes, and so on) are not analyzed and counted once the SQL is executed, Oracle uses a technique called dynamic sampling to dynamically collect some data on the tables and indexes.

With regard to RBO and the CBO, I have an image of metaphor: Before the advent of the Big Data era, business may be able to make good decisions and follow market changes with years of accumulated experience (RBO). But in the big data age, it's possible to make wrong decisions if you're doing business on a previous basis, rather than relying on big data, data analysis, and data mining to make decisions. This is more and more companies on BI, data mining for the sake of more and more, like e-commerce, gaming, telecommunications and other industries have been large-scale applications, previously in a game company database department to do BI analysis, mining potential consumer is simply no less than. So far the image is quite deep.

CBO and the pros and cons of Rbo

The CBO is better than Rbo because Rbo is a dull, outdated optimizer that only rules and is insensitive to data. After all, the rules are dead, the data is changed, and the resulting execution plan is often unreliable, not optimal, and the CBO can be represented in many ways because of the rbo. Let's take a look at an example, which comes from "getting Oracle to 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 the test group by ID;

        ID   COUNT (1)

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

                 1

         1      50314

As can be seen from the above, the data distribution of the test table is extremely uneven, id=100 records only one, and the id=1 record has 50,314. Let's take a look at the Rbo two SQL execution plan first.

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> 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

           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

As can be seen from the execution plan, Rbo's execution plan is a bit disappointing, for id=1, almost all of the data conforms to the predicate condition, the index can only increase the additional cost (because Oracle first accesses the index data block, the index found the corresponding key value, Then follow the ROWID on the key value to access the corresponding data in the table, since we almost have to access all the data in the table, then the full table scan is naturally the best choice. And Rbo chose the wrong execution plan. Can be compared to the CBO under the SQL execution plan, obviously it is sensitive to data, the execution plan timely based on the amount of data adjusted, when the query condition is 1 o'clock, it goes full table scan, when the query condition is 100, it walks the interval index scan. As shown below:

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|   (2) | 00:00:01 |

|*  1 |  TABLE ACCESS full| TEST | 49075 |  3786k|   (2) | 00:00:01 |

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

predicate information (identified by Operation ID):

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

   1-filter ("ID" =1)

Note

-----

   -Dynamic sampling used for this statement

Statistics

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

           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 |    |     2   (0) | 00:00:01 |

|   1 |  TABLE ACCESS by INDEX rowid| TEST     |     1 |    |     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

           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

This alone shows why Oracle strongly recommends using the CBO, which does not support Rbo from Oracle 10g. The so-called superseding, before the waves died on the beach.

CBO Summary of Knowledge points

The CBO optimizer generates a set of possible execution plans based on the SQL statement, estimates the cost of each execution plan, and calls the Plan Builder to generate the execution plan, compare the cost of the execution plan, and ultimately choose a Generator execution plan. The query optimizer consists of the query Transform, the Cost Estimator (estimator), and the plan Generator.

CBO Optimizer Components

The CBO is comprised of the following components:

· Query Converter (Transformer)

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

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

Query blocks. The query blocks is nested or interrelated to all other. The form of the

Query determines how the query blocks is interrelated to all other. The main

Objective of the query transformer is to determine if it's advantageous to change the

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

Query transformation techniques is employed by the query transformer, including:

View Merging

predicate pushing

Subquery unnesting

Query Rewrite with materialized views

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

· Cost Evaluator (Estimator)

The evaluator evaluates the overall cost of each execution plan by combining a complex algorithm with three values of statistical information: selectivity (selectivity), cardinality (cardinality), cost

The Plan builder takes into account possible access paths (access path), association methods, and Association order, and generates different execution plans, allowing the query optimizer to choose from these plans a plan with the lowest execution cost.

· Planning Builder (Plan Generator)

The Plan builder is to generate a large number of execution plans, and then choose an execution plan whose overall cost or lowest overall costs.

Because different access paths, connection methods, and connection sequences can be combined, while data is accessed and processed in different ways, it can produce the same results

It's my own drawing for a deeper understanding, with tools.

View Oracle Optimizer

Sql> Show parameter Optimizer_mode;

NAME                           TYPE        VALUE

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

Optimizer_mode                 string      all_rows

Modifying the Oracle Optimizer

The ORACLE 10g optimizer makes it easy and flexible to modify the optimizer mode from the system level, session level, and statement level three ways.

where optimizer_mode can choose the values are: First_rows_n,all_rows. Among them First_rows_n and first_rows_1000, first_rows_100, first_rows_10, First_rows_1

in Oracle 9i, the optimizer mode can choose First_rows_n,all_rows, choose, Rule and other modes:

Rule : rules-based approach.

Choolse: refers to when a table or index has statistics, then walk the CBO way, if the table or index does not have statistical information, the table is not particularly small, and the corresponding column has an index, then go index, Walk Rbo way.

If optimizer_mode=choose, if statistics do not exist, and if you don't add hints to SQL statements, then SQL statements U Se 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 approach, unlike when a table has statistics, it will be the quickest way to return the top lines of the query, reducing the overall response time.

All rows: The default value in 10g, which is what we call cost, when a table has statistics, it returns all rows of the table in the quickest way, increasing the throughput of the query as a whole

Although Rbo,oracle 10g is no longer supported in Oracle 10g, the official documentation for the Optimizer_mode parameter is only first_rows and all_rows. But you can still set optimizer_mode to rule or choose, It is estimated that Oracle is considered for transition or backwards 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

The session level modifies the optimizer mode and is valid only for the current session, while 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 implemented by using hint hints.

Sql> Select/*+ Rule */* from dba_objects where rownum <= 10;

Oracle Optimizer RBO and CBO introduction summary