Shulou(Shulou.com)05/31 Report--

This article mainly introduces the ORACLE optimizer RBO and CBO what is useful, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let the editor take you to understand it.

Basic concepts of RBO and CBO

The optimizer in the Oracle database is also called query optimizer (Query Optimizer). It is an optimization tool for SQL analysis and execution, and it is responsible for generating and formulating the implementation plan of SQL. There are two kinds of optimizers for Oracle, 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 until ORACLE 9i. ORACLE 10g, ORACLE has completely abandoned RBO, it has a set of strict rules, as long as you write SQL statements according to it, no matter what the contents of the data table, it will not affect your "execution plan", that is to say, RBO is not "sensitive" to data; it chooses the execution plan for the specified table according to the priority rules specified by ORACLE. For example, in the rules, the priority of the index is higher than that of the full table scan; RBO selects the execution plan based on the available access paths and the level of access paths. In RBO, the writing of SQL often affects the execution plan, which requires developers to know the details of RBO very well. The performance of SQL scripts written by rookies may be very poor. With the abandonment of RBO, it gradually became unknown. Perhaps only the older generation of DBA knows more about it. The official document describes the access path to RBO in detail:

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 matured gradually in ORACLE 9i. It completely replaced RBO in ORACLE 10g. CBO is to calculate the "cost" of various possible "execution plans", that is, COST, from which the lowest implementation scheme of COST is selected as the actual operation scheme. It depends on the statistical information of database objects, and whether the statistical information is accurate or not will affect CBO to make the best choice. If it is found that the objects (tables, indexes, etc.) involved in a SQL execution have not been analyzed and counted, then ORACLE will use a technique called dynamic sampling to dynamically collect some data information on the table and index.

With regard to RBO and CBO, I have a vivid analogy: before the arrival of big data's era, business may be able to make good decisions and follow market changes with years of accumulated experience (RBO). However, in the era of big data, if business decisions were made on the basis of previous experience, rather than on big data, data analysis and data mining, it would be possible to make wrong decisions. This is why more and more companies pay more and more attention to BI and data mining, such as e-commerce, games, telecommunications and other industries have been applied on a large scale. They used to do BI analysis in the database department of a game company to mine potential consumers. I have a deep impression so far.

The advantages and disadvantages of CBO and RBO

CBO is superior to RBO because RBO is a rigid, outdated optimizer that only recognizes rules and is insensitive to data. After all, the rules are dead and the data are changing, so the resulting execution plan is often unreliable and not optimal, CBO because RBO can be reflected in many ways. Let's take a look at an example, which 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 can be seen from the above, the data distribution of the test table is extremely uneven, with only one record for ID=100 and 50314 records for ID=1. Let's first take a look at the implementation plans of the two SQL 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

As can be seen from the execution plan, the execution plan of RBO is a bit disappointing. For ID=1, almost all the data meets the predicate conditions, and walking the index can only add additional overhead (because ORACLE first has to access the index data block, find the corresponding key value on the index, and then access the corresponding data in the table according to the ROWID on the key value), since we have to access almost all the data in the table. Then full table scanning is naturally the best choice. RBO chose the wrong execution plan. You can compare the execution plan of SQL under CBO, it is obviously sensitive to data, and the execution plan is adjusted according to the amount of data in time. When the query condition is 1, it scans the whole table; when the query condition is 100, it scans the interval index. 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 can see why ORACLE highly recommends using CBO and does not support RBO since ORACLE 10g. As the saying goes, the back wave of the Yangtze River pushes the front wave, and the front wave dies on the beach.

Summary of CBO knowledge points

The query optimizer consists of a query converter (Query Transform), a cost estimator (Estimator) and a plan generator (Plan Generator).

CBO optimizer component

CBO consists of the following components:

Query converter (Query Transformer)

The function of the query converter is to change the form of the query statement equivalently in order to produce a better execution plan. It determines whether to rewrite the user's query (including view merge, predicate advance, 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.

Cost evaluator (Estimator)

The evaluator uses a complex combination of algorithms to estimate the overall cost of each execution plan by combining three values of information: Selectivity, Cardinality, and Cost.

The plan generator considers possible access paths (Access Path), association methods, and association order, generates different execution plans, and lets the query optimizer select the one with the least execution cost from these plans.

Plan Generator (Plan Generator)

The plan generator is to generate a large number of execution plans and then select the one with the lowest overall cost or total cost.

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

The following picture is a picture drawn by myself with tools in order to deepen my understanding.

View the 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 from system level, session level and statement level, which is very convenient and flexible.

The values that can be selected by optimizer_mode are: first_rows_n,all_rows. First_rows_n also has first_rows_1000, first_rows_100, first_rows_10 and first_rows_1.

In Oracle 9i, the optimizer mode can choose first_rows_n,all_rows, choose, rule, and so on:

Rule: a rule-based approach.

Choolse: refers to when a table or index has statistical information, then take the CBO way, if the table or index has no statistical information, the table is not particularly small, and the corresponding column has an index, then take the index, take the RBO way.

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 approach, except that when a table has statistics, it will return the first few rows of the query in the fastest way, reducing response time overall.

All Rows: the default value in 10g, which is what we call Cost, when a table has statistics, it will return all rows of the table in the fastest way, improving the query throughput as a whole.

Although RBO,Oracle 10g official documentation about optimizer_mode parameters is no longer supported in Oracle 10g, only first_rows and all_rows. However, you can still set optimizer_mode to rule or choose, probably for the sake of transition or downward compatibility with ORACLE. 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, which 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 achieved by using the prompt hints.

SQL > select / * + rule * / * from dba_objects where rownum

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.