Oracle Optimizer: Migrate to the cost-based optimizer ----- Series 1.1

zhaozj2021-02-16  57

Oracle Optimizer: Migrate to the cost-based optimizer ----- Series 1.1

If you develop an application in Oracle's previous version (7.0 or earlier), the database will use rule-based optimizer (translator Note: RBO), this article will help you understand the Oracle optimizer and migrate to cost optimization Several high efficiency methods (including CBO). The following is the first part of the five parts.

first part

1. What is an optimizer?

2. Why is it optimized?

3. Available optimizer.

4. Why remove RBO?

5. Why do you want to migrate to CBO?

the second part

1. Affect the initialization parameters of CBO.

2. Affect the internal parameters of the Oracle of CBO.

the third part

1. Upgrade to CBO settings.

2. Generate statistics

3. DML monitoring

fourth part

Tip.

2. Storage profile

3. Statistics of SYS mode

the fifth part

1. New permissions

2. How to analyze the implementation plan in CBO

3. Oracle Application 11i CBO Details

4 Conclusion

1. What is an optimizer?

In Oracle, executing a query may have not only one way, have the best level execution plan, or the fastest speed to return to the output and reach the best resource utilization implementation plan. Optimizer generates an execution plan. The optimizer is the most effective implementation plan that is running in the database to obtain the engine that executes the path list under different conditions and selects the most efficient execution plan to run the query. Once the plan is executed, it will execute the output. In Oracle, the optimizer is related to the DML statement.

2. Why is it optimized?

you know! Optimize a query for the minimum time and best resource utilization, meaning fast and efficient. For resources, it means that CPU utilization, disk input, memory consumption, and other range of network operations. Don't consider how plenty of these resources is on these resources, not appropriate, unmetive queries always pay expensive costs and slow down your task, or affect other processing on the server. Rely on various factors The query is expensive, including the extracted result set size, scanning the size of the data set and the instant load of the system. Appropriate syntax optimization will save user runtime and unnecessary resource utilization.

3. Available optimizer

Oracle has two modes of optimizer, based on rules and cost, which determines the best implementation plan.

This article focuses on CBO, briefly overview RBO.

3.1 Optimization based on rules (RBO)

RBO follows simple grading methodology. RBO uses 15 level points, when receiving a query, the optimizer will evaluate the number of points used, then select the best level (minimum number) execution path to run the query, 15 key points as follows:

. Use RowID's separate record

Use a separate record of the cluster connection.

. Use a separate record of the lacc cluster key.

Use the individual record of the primary key.

Cluster connection.

Hollow cluster connection

Index cluster key.

Composite primary key.

A single column primary key.

The combination range of the index column.

Non-binding range lookup of the index column.

Sort merge.

Maximum max or min of the index

ORDER BY of the index column.

Full table scan.

For example, if a query that exactly matches the two columns in the WHERE clause condition, a column has a primary key (corresponding to a separate record of the primary key.) And other non-main keys (corresponding to the single-listed primary key), RBO Make more primary keys (corresponding to separate records of the primary key.), Rather than non-primary keys (corresponding to the single-listed primary key).

When in a query involves accessing multiple tables, the optimizer needs to determine that the table is a drive table. Rbo generates a set of connection sequences, each table is the first table, then the optimizer selects from the results of the program The most ideal plan. Optimizer evaluates different conditions such as (minimum nested cycle, minimal sorting combined connection, best level table access path, etc.), if it is still unable to compare, the optimizer selects query The first table of the FROM clause acts as a drive table. Therefore, the coding practice under conventional conditions will put the drive surface on the right side. Other tables followed in the future in the future. That is, the order of the table is from Right to left access order. Please note that the operators used to search columns also play a role of the decision level, sometimes even considering the time of the index as a level.

For example, the following table demonstrates the index usage on column 1 and column 2, if they use "=" connection on the WHERE clause

example:

Select * from am79 where col1 = 1 and col2 = 'amar';

- Here Both col1 and col2 are indexed.

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

Normal INDEX TYPES | INDEX Used in Rbo

Column1 (a) Column2 (b) Column1 Column2 (C) |

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

Non-Unique Non-Unique C

NON-UNIQUE NON-UNIQUE A B

Non-Unique Non-Unique Non-Unique C

UNIQUE NON-UNIQUE A

UNIQUE NON-UNIQUE A

UNIQUE UNIQUE B (The Most Recent Index Created)

Unique Unique Unique C

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

-The Above is Tested on ORACLE 8.1.7.1.

-In Case of Non-Unique Single Column Indexes, Both INDEXES Are Used.

-In Case of Unique Indexes, They Are Not Combined for Execution Plan, Any One is Taken.

-Preference is given to the index available with the "=" Operator Column, Than With

Others Operators.

-Don't create Bitmap & Function-based Indexes, These Will Not Work In Rbo.

-------------------------------------------------- ----------------------------------- RBO preference Oracle Early Version of Early Version As the execution plan path, this The choice is unified. The query will eventually produce the same approach to the same application on different databases (to be continued).

转载请注明原文地址:https://www.9cbs.com/read-22228.html

New Post(0)