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

The basis of the optimizer:

1. Optimizer in Oracle:

The optimizer is a core subsystem built into the Oracle database. The purpose of the optimizer is to get the most efficient execution path of the target sql in the current situation according to certain judgment principles, that is to say, to get the execution plan of the target sql.

The optimizer of Oracle database can be divided into two types: RBO and CBO:

RBO: rule-based optimizer (when obtaining the sql execution plan, RBO uses a set of built-in rules to judge)

CBO: cost-based optimizer (when obtaining the sql execution plan, CBO uses the judgment principle of cost, which selects the one with the lowest cost value from the many possible execution routes of the target sql to execute the plan)

Note:

①: since Oracle10G, RBO is no longer supported by Oracle, but the relevant implementation code of RBO has not been removed from the Oracle code, which means that RBO can still be used in Oracle11GR2 by modifying the optimizer pattern or using rule hint

②: RBO has many disadvantages, among which many good features and functions of Oracle can no longer be used under RBO because they are not supported by RBO.

There are situations where RBO will not be used even if the optimizer schema is modified or RULE hint Oracle is used (but CBO will be enforced)

①: the objects involved in the target sql are IOT (index organized table)

②: the objects involved in the target sql have partition tables

③: parallel query or parallel DML is used

④: star connection is used

⑤: hash links are used

⑥: uses a fast full scan of the index

⑦: functional index is used

SQL > set autotrace traceonly-enable sql tracking to view the execution plan

1.1.Rule-based optimizer-RBO

Change the optimizer mode to RULE in the current session, indicating that RBO is enabled in the current session

SQL > alter session set optimizer_mode='RULE'

Note: in the case of RBO, the execution plan of the sql can be adjusted by equivalently rewriting the target sql (by adding 0 or an empty string).

For example, select * from emp_temp where mgr > 100 and deptno+0 > 100; (deptno type is number)

Description:

①: when the target sql has two or more execution plans with the same level, we can indeed influence RBO's choice of execution plans by adjusting the caching order of related objects in the data dictionary cache

②: if the RBO can select the execution plan only based on the level values of each execution path of the target sql, then no matter how you adjust the position of the related objects in the sql text of the sql, it will have no effect on the final execution plan of the sql.

1.2.The cost-based optimizer-CBO (CBO is used by default when parsing the target sql from Oracle10G)

A cost-based optimizer is a value calculated by Oracle based on the statistical information of related objects, which is actually an estimate of the target sql corresponding to the O CPU of the execution step and the consumption of network resources.

2. Some basic concepts of CBO:

①: cardinality (advantages of sets): a concept unique to CBO, which refers to the number of records contained in a specified set; it actually represents an estimate of the number of records contained in the execution of a specific step of the target sql.

②: selectability: also a unique concept of CBO, it refers to the ratio of the number of records that return the result set after applying the specified predicate condition to the number of records of the original result set without any predicate condition. The range of selectable rate is 0: 1, and the smaller its value is, the better the selectivity of table name is. There is no doubt that the selectivity of 1 is the worst.

③: transitivity: it is a concept unique to CBO, which means that CBO may make a simple equivalent rewrite of the original target SQL, that is, adding new predicate conditions derived from the existing predicate conditions of sql to the original target SQL. The purpose of this is to provide more execution paths for CBO to choose, thereby increasing the possibility of getting a more efficient execution plan.

In Oracle, transitivity is divided into the following three situations:

①: simple predicate passing

For example, if the predicate condition of the original target sql is "t1.c1=t2.c1 and t1.c1=10", CBO may add "t2.c1=10" to the predicate condition, that is, it will be modified to

T1.c1=t2.c1 and t1.c1=10 and t2.c1=10

②: join predicate passing

For example, if the predicate condition in the original target sql is "t1.c1=t2.c1 and t2.c1=t3.c1", CBO may add "t1.c1=t3.c1" to the predicate condition, that is, it will be

Change it to "t1.c1=t2.c1 and t2.c1=t3.c1 and t1.c1=t3.c1"

③: external join predicate passing

For example, if the predicate condition in the original target sql is "t1.c1=t2.c1 (+) and t1.c1=10", CBO may add "t2.c1 (+) = 10" to the predicate condition, that is, it will be modified to

"t1.c1 (+) and t1.c1=10 and t2.c1 (+) = 10"

3. Limitations of CBO:

①: CBO defaults that the columns that appear in the where condition of the target sql statement are independent and have no relationship

②: CBO assumes that all target sql are executed separately and do not interfere with each other

③: CBO has many restrictions on histogram statistics

④: CBO may miss the correct execution plan when parsing the target sql associated with multiple tables

#

4. The mode of optimizer

In the Oracle database, the mode of the optimizer is determined by the value of the parameter optimizer_mode, and the value of optimizer_mode may be rule choose first_rows_n or all_rows

①: rule

Rule indicates that Oracle will use RBO to parse the target sql, and the statistics of the various objects involved in the target sql will have no effect on RBO.

②: choose

Choose is the default value for Oracle9i's optimizer_mode, which means that whether Oracle uses RBO or CBO when parsing the target sql depends on whether the table objects involved in the sql have statistics.

(as long as one of the table objects involved in the sql has statistics, Oracle will use CBO when parsing the sql. If none of the table objects in the sql have statistics, then Oracle will not use RBO)

③: first_rows_n (nasty 1, 10, 10, 100, 1, 000)

Optimizer_mode can be any of the above values, which means that when the value of optimizer_mode is first _ rows_n, Oracle will use CBO to parse the target sql, and the focus of CBO in calculating the cost values of each execution path of the sql is to return n records with the fastest response speed.

④: first_rows

First_rows is an obsolete parameter in Oracle9i that indicates that Oracle uses CBO and RBO together when parsing the target sql. The union here is that in most cases, first_rows still uses CBO to parse the target sql.

⑤: all_rows

All_rows is the default value of optimizer_mode in subsequent database versions of Oracle10G, which means that Oracle will use CBO to resolve the target sql, and at this time CBO focuses on the optimal throughput (that is, the minimum system Imax O and CPU resource consumption) when calculating the cost values of each execution path of the sql.

Result set

A result set is a set that contains a specified execution result. For the optimizer (whether RBO or CBO), the result set corresponds to the execution step of the target sql execution plan, and the execution result produced by an execution step is the output result set corresponding to the execution step.

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.