Shulou(Shulou.com)06/01 Report--

RBO and CBO methods of Oracle's optimizer

1. Rule-based optimization (Rule-Based Optimization, abbreviated as RBO)

When analyzing SQL statements, the optimizer follows some predetermined rules within Oracle and is insensitive to data. It uses only a small amount of information to determine the execution plan of an sql statement, including:

1) the sql statement itself

2) basic information about table, view, index, etc. involved in sql

3) the information in the data dictionary in the local database (the remote database data dictionary information is not valid for RBO)

For example, it is common to walk the index when a column in a where clause has an index. However, it should be noted that going through the index is not necessarily excellent. For example, if a table has only two rows of data, one IO can complete the retrieval of the whole table, while twice IO is needed when walking the index. In this case, the efficiency of full table scan (full table scan) is better.

2. Cost-based optimization (Cost-Based Optimization, referred to as CBO)

It looks at the cost of statements (Cost), which estimates the cost of each execution plan through the cost engine, which quantifies the resources consumed by each execution plan, and CBO selects the optimal execution plan according to this cost. The resources consumed by a query can be divided into three parts: I CPU cost, NETWORK cost. CPU O refers to the cost of reading data from disk to memory (this cost is the most important for the query, so one of the basic principles of optimization is to reduce the total number of times). CPU cost refers to the cost of processing data in memory, once the data is read into memory, when we identify the data we want, we will perform sort or join operations on the data, which consumes CPU resources The cost of network is also high for accessing remote nodes.

When determining whether to use this method, the optimizer mainly refers to the statistical information of tables and indexes. Statistics give information about the size of the table, the number of rows, the length of each row, and so on. These statistics are not available in the library at first and appear only after analyze. A lot of out-of-date statistics will cause the optimizer to make a wrong execution plan, so the information should be updated in time (dbms_stat.analyze).

Some technologies such as star join permutation query, hash join query, functional index, and parallel query are all based on CBD.

3. The optimization mode includes four modes: Rule, Choose, First rows and All rows:

Rule: a rule-based approach.

Choolse: this is how Oracle works by default. Refers to when a table or index has statistical information, then take 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 take the index, take the RBO way.

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: in what we call Cost, when a table has statistics, it returns all the rows of the table in the fastest way, improving the overall query throughput. If there is no statistical information, it will be in the RBO way.

4. Set which optimization mode to choose:

A. Set OPTIMIZER_MODE=RULE/CHOOSE/FIRST_ROWS/ALL_ROWS in initSID.ora (default is Choose)

B, Sessions level passed: ALTER SESSION SET OPTIMIZER_MODE=RULE/CHOOSE/FIRST_ROWS/ALL_ROWS

C, the statement level is set by Hint (/ * +... * /).

5. Some common problems:

A. Why is there an index in a certain field of the table, but not in the execution plan?

1. Optimization mode is the way of all_rows

2. The table has been analyze and has statistical information.

3. The table is very small, and as mentioned above, Oracle's optimizer doesn't think it's worth indexing.

B. When using CBO, why can't system data dictionary tables or views be referenced in SQL statements?

1. Because the system data dictionary tables have not been analyzed, it may lead to a very poor "execution plan".

2. Unauthorized analysis of data dictionary tables may lead to deadlock or serious degradation of system performance.

C. How to choose the table join mode when using CBO?

1. CBO sometimes focuses on SMJ and HJ, but in OLTP systems, NL is generally better because it uses indexes efficiently.

2. SMJ, even if there is an index on the relevant column, the data sorting process can only be avoided because of the existence of the index.

3. Because HJ has to do HASH operation, the existence of index has almost no effect on the speed of data query.

D. Is there anything you need to pay attention to when using CBO?

1. It is necessary to ensure that sufficient statistical data are collected for tables and related indexes, and it is best to analyze tables and indexes on a regular basis for tables that often add, delete or change data.

2. Available SQL statement: analyze table xxx compute statistics for all indexes

Why is it slow to use CBO sometimes?

1. No Analyze is performed on the table or view

2. When SQL performs CBO, it will automatically Analyze objects without Analyze, resulting in slow operation.

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.