Optimizer_mode Optimizer mode

The main task of the query optimizer is to accept input SQL and various environment parameters and configuration parameters to generate an appropriate SQL execution plan (execution plan).

Query Optimizer has gone through two historical stages:

• rbo:rule-based Optimization rule-based Optimizer
• cbo:cost-based optimization cost-based optimizer

With regard to RBO and the CBO, an image metaphor: Before the Big Data era, business may be able to make good decisions and follow market changes with years of accumulated experience rules (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. That's why more and more companies are paying more attention to bi and data mining.

First, Rbo Optimizer

RBO (Rule Based Optimizer, rules-based optimizer) is the embedding of 15 rules within the optimizer, which performs a plan selection of the specified table according to the precedence rules specified by Oracle. The execution of the SQL statement conforms to which rule, and the corresponding SQL execution plan is customized according to the rules. 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. Since it is an outdated and inflexible optimizer, Oracle no longer supports RBO services.

15 types of rules and ranking:

• 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

Second, CBO optimizer

The CBO is a more reasonable and reliable optimizer than Rbo, which was introduced from Oracle 8, but matured in Oracle 9i. After entering Oracle 10g, Query Optimizer has used the CBO (cost Based Optimizer, price-based optimizer) as the default optimizer, and Oracle no longer supports RBO services.

The idea is to get Oracle to get all the information about the execution plan, use system statistics to perform various execution path trials, calculate the "cost" of various possible "execution plans", that is cost, and get the execution plan with the lowest relative costs as the actual running scenario. 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 at the time of SQL execution, then Oracle uses a technique called dynamic sampling,

Dynamic statistics collection is a feature of the Oracle CBO optimizer. The optimizer generates an execution plan that is calculated based on the costing cost formula, and if the relevant data table does not collect statistics and uses the CBO mechanism, it will cause dynamic sampling (sampling).

Dynamic sampling (sampling) is the generation of execution plans that are based on a small adoption rate that is now being collected for statistics, dynamic collection tables and some data information on the index. Because of the low sampling rate, the sampling process is fast but imprecise, and the sampled results are not entered into the data dictionary.

The CBO query optimizer consists of the query Transform, the Cost Estimator (estimator), and the plan Generator.

The CBO optimizer has two selectable operating modes:

2.1 first_rows (n)

When you set the optimizer mode to: First_rows (n), it means that Oracle will prioritize the first n records in the result set as soon as possible when executing the SQL statement, while the other results do not require simultaneous feedback, that is, when processing the data, the subsequent data may not be extracted. , the previous data has been returned to the user, this requirement is often seen on site search or BBS paging. For example, each time only the first 20 of the query information is displayed, then setting First_rows (20) is very appropriate. For paging operations, the more front pages, the shorter the time it takes to display the results.

It is important to note that the X used by the sort must be created with an index, otherwise the CBO ignores First_rows (n) and uses All_rows.

2.2 all_rows

When the CBO pattern is all_rows, it means that we need Oracle to execute the SQL as quickly as we can and return the result set. The difference between it and First_rows (n) is that all_rows emphasizes the overall efficiency of execution, while First_rows (N) emphasizes returning the first N records at the fastest speed. All_rows is used more in OLAP systems and is designed to quickly get the last record of execution results.

Iii. choose--Transition in two eras

For a period of Oracle 9i, the default parameters for Optimizer_mode are choose. That period was the time when query optimizer switched from Rbo to CBO, and Oracle opted for a gradual transition. Since there was no dedicated nightly statistics collection, many times there were no statistics on the data tables. In addition, the dynamic sampling technology is immature, so the application of CBO has some obstacles. At this time, Oracle presented the Choose as the optimizer default mode.

Choose is a switch between auto-select Rbo and CBO. If there is a statistic in the data table that SQL involves, then the SQL is using the CBO optimizer. Otherwise it is with Rbo. Dynamic sampling technology is a technical solution under the condition of CBO optimizer.

[Email protected]> Alter sessionSetOptimizer_mode=Choose; The session has changed. [Email protected]>SetAutotrace traceonly; [Email protected]> Exec dbms_stats.delete_table_stats ('Scott','EMP', Cascade_columns =true, cascade_indexes =true);P L/The SQL process has completed successfully. [Email protected]>SelectEname fromEMP, selected -line. Execution Plan----------------------------------------------------------Plan Hash Value:3956160932----------------------------------| Id | Operation | Name |----------------------------------|0|      SELECT STATEMENT | ||1| TABLE ACCESS full| EMP |----------------------------------Note------rule based optimizer used (considerusingCBO) statistical information----------------------------------------------------------1Recursive calls0db block gets8consistent gets0Physical reads0Redo Size732Bytes sent via sql*Net to client544Bytes received via Sql*net fromClient2Sql*net roundtrips to/ fromClient0sorts (memory)0sorts (disk) -rows Processed[email protected]> Exec dbms_stats.gather_table_stats (ownname ='Scott', tabname ='EMP');P L/The SQL process has completed successfully. [Email protected]>SelectEname fromEMP, selected -line. Execution Plan----------------------------------------------------------Plan Hash Value:3956160932--------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |--------------------------------------------------------------------------|0|      SELECT STATEMENT | | -| -|3(0)|xx:xx: on||1| TABLE ACCESS full| EMP | -| -|3(0)|xx:xx: on|--------------------------------------------------------------------------Statistical Information----------------------------------------------------------1Recursive calls0db block gets8consistent gets0Physical reads0Redo Size732Bytes sent via sql*Net to client544Bytes received via Sql*net fromClient2Sql*net roundtrips to/ fromClient0sorts (memory)0sorts (disk) -rows Processed[email protected]>

View Code

Iv. View and modification of Optimizer_mode

With the optimizer parameter Optimizer_mode, we can control the Oracle optimizer to generate execution plans in different modes.

4.1 Views Optimizer_mode

Parameter Optimizer_mode

SCOTT@PDBORCL>Show parameter Optimizer_modename TYPE VALUE------------------------------------ ----------- ------------------------------Optimizer_mode string All_rowsscott@PDBORCL> SelectName, value fromV$parameterwhereName='Optimizer_mode'; NAME VALUE-------------------------------------------------------------------------------- ---------------------------- -----------Optimizer_mode All_rowsscott@PDBORCL>

4.2 Modifying Optimizer_mode

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

System level

You can modify the Optimizer_mode by using the following statement

Alter system set Optimizer_mode=all_rows Scope=both;

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.

Alter session set OPTIMIZER_MODE=FIRST_ROWS_100;

Statement level

The statement level is implemented by using hint hints.

Select/*+ Rule */* from EMP;

Reference:

Oracle Optimizer RBO and CBO introduction summary

Talking about Optimizer_mode optimizer mode

Optimizer_mode Optimizer mode