Oracle Optimizer and execution plan

1. The optimizer (Optimizer) is an optimization tool for SQL analysis and execution that is responsible for making the SQL execution plan, which is responsible for ensuring that SQL performs the most efficiently, such as determining how Oracle accesses the data, full table scan, or index range Range scan), or full index quick Scan (index fast, index_ffs), and how the query is associated with the table. There are 2 kinds of optimizer, Rbo and CBO, starting with Oracle 10g, RBO has been deprecated, but can still be used in hint way.

2. Rbo execution mechanism: embed several rules in the optimizer, execute the SQL to conform to what rules, then follow the rules to develop the execution plan, the rules are prioritized. Select/*+ Rule */* from t where id=1; Use the hint method to force execution with the Rbo optimizer.

3. CBO implementation mechanism: Access to all the execution plan information, through the analysis of this information, and finally a cost-minimization of the implementation plan as the final implementation plan.

4. Even when the index is not analyzed, Oracle will still use the CBO (starting with Oracle 10g, RBO has been deprecated), at which point Oracle uses dynamic sampling, when parsing sql, dynamic collection tables, some blocks of data on the index, Use the information from these blocks and the information in the dictionary about these objects to calculate the cost of the execution plan, and then choose the optimal execution plan. Dynamic sampling is used only for the first time in SQL execution, the hard analysis phase, and subsequent soft analysis no longer uses dynamic sampling, directly using the first SQL hard-to-parse execution plan.

5. The CBO optimizer has 2 selectable operating modes:

• First_rows (n): When Oracle executes SQL, priority is given to returning the first N records in the result set to the fastest speed, and the other results do not need to be returned at the same time. This requirement is often seen in search or pagination.

Select/*+ first_rows (Ten) */b.x, B.Y

From (select/*+ first_rows () */a.*, rownum rnum

From (select/*+ first_rows (TEN) */* from T ORDER by X) a where rownum<=20)

b where rnum>=10;

• All_rows: Completes SQL execution to return all result sets.

Select/*+ all_rows */*

From (select/*+ all_rows */a.*, rownum R

From (select/*+ all_rows */owner, object_name, created from T where owner= ' sys ' ORDER by object_name) a where rownum< =20)

where r>=10;

6. Cardinality in the execution plan (cardinality): Execution plan in each step, the card value indicates the number of records that the CBO expects to return from a row source, a row source that may be a table, an index, or a subquery.

The card in the execution plan is the abbreviation of cardinality, after 10g, the card is replaced by rows, indicating that the CBO estimates the number of records that the current operation expects to get. The value of cardinality is critical for the CBO to make the right execution plan.

/*+ dynamic_sampling (t 0) cardinality (t 10000) */Without analyzing the data, disable the dynamic sampling and tell the CBO to obtain 10,000 records from the T table.

7. The implementation plan can be obtained using the following methods:

• Explain plan for example: 1.explain plan for SELECT * from T; 2. Select * FROM table (dbms_xplan.display);
• Sqlplus command set autotrace on; Set Autotrace trace exp, stat;
• Tools provided by third parties, such as Toad,pl/sql developer

8. Access in the execution plan indicates that the value of this predicate condition will affect the access path (table or index) of the data, and filter indicates that the value of the predicate condition does not affect the access path of the data, only the role of filtering.

9. If the table is not analyzed, the CBO can obtain the analysis data by dynamic sampling, and can get an accurate execution plan, if the table is analyzed, but the analysis information is too old, the CBO will not use dynamic sampling, but use these old analysis data, which may lead to the wrong execution plan.