Rule based optimizer (learning notes)

Tri Hua "Oracle-based SQL Optimization" Learning notes

1.1 The rule based optimizer

(1) CBO

(2) RBO

Compared with the CBO, RBO has a clear mandate. In the case of using RBO, it is difficult to adjust the execution plan once it has gone wrong. In addition, if RBO is used, even the order in which the objects involved in the target SQL appear in the SQL text may affect the choice of the Rbo execution plan I am, worse, the good features of Oracle databases, Features can no longer be used in RBO because they cannot be supported by Rbo locks.

As long as one of the following occurs, the RBO is not used even if the optimizer mode is modified or the rule hnint,oracle is used (instead of the CBO).

(1) This IoT (Indexorganized Table) is designed in the target SQL.

(2) The objects designed in the target SQL have partitioned tables.

(3) A parallel query or a parallel DML is used.

(4) using a star connection

(5) using a hash connection

(6) using the index fast full scan

(7) Using a function index

(8) ...

In this case, it is difficult for us to adjust the rbo choice of the implementation plan, one of the most important reason is that hint can not be used. Because hint is used in the target SQL, it means that the CBO is automatically started, where Oracle resolves hint's target SQL with a CBO. There are only two exceptions here, the rule Hint and Sriving_site Hint, which he can use under Rbo and do not automatically enable the CBO.

Then there is no way to adjust the implementation plan in the case of using Rbo ...

Of course not, but in this case we have very limited means of adjustment. One possible approach is to override the target SQL equivalently, such as adding 0 to a number or date type column in the Where condition of the target SQL (if it is varchar2 or char type, you can add a null character, such as | | , so that the index that could have been left could not go now. For target SQL that contains multiple table joins, this change can even affect the order of table joins, thereby making it possible to adjust the execution plan of the target SQL with Rbo.

But if there are two or two of the same ranking values of the same execution path, then rbo how to choose at this time. Quite simply, rbo the sequence of objects involved in the target SQL in the database cache (data Dictionary cache) and the order in which they appear in the target SQL text, based on the related objects involved in the destination. This means that we can adjust the execution plan by adjusting the order of the objects in the data dictionary, changing the order in which the objects involved in the target SQL appear in the SQL text.

Experiment: The execution plan for target SQL is adjusted using RBO.

CREATE TABLE Emp_temp as SELECT * from  EMP;
 
Create INDEX idx_mgr_temp on emp_temp (MGR);
 
Create INDEX idx_deptno_temp on emp_temp (DEPTNO);
 
 
SELECT * from Emp_temp where  mgr>100 and deptno>100;

# # #在当前session中配置优化器模式为RULE

Alter session set optimizer_mode= ' rule ';

SET Autot TRACE EXP

If we find that go index idx_deptno_temp than go index idx_mgr_temp execution efficiency is high, or we want to let Rbo go index idx__mgr_temp, then how do we let do.

Can add a 0 don't let it Go index:

Select * from Emp_temp where mgr>100 anddeptno+0>100;
 
08:28:11 Scott@felix Sql>select * from emp_temp where mgr>100 anddeptno+0>100; 
 
No rows selected Execution plan----------------------------------------------------------plan hash value:2973289657 ----------------------------------------------------
| Id | Operation |
Name |   ----------------------------------------------------
| 0 |              SELECT STATEMENT |  | |* 1 | TABLE ACCESS by INDEX rowid|   emp_temp | |* 2 | INDEX RANGE SCAN |
Idx_mgr_temp | ----------------------------------------------------predicate information (identified by OperationID):------------ ---------------------------------------1-filter ("DEPTNO" +0>100) 2-access ("MGR" >100) Note------Rul
         ebased Optimizer used (consider using CBO) Statistics---------------------------------------------------------- 1 Recursive calls 0 db block Gets 2 consistent gets 0 physical reads 0 Redo size 799 Bytes sent via sql*net Tocl Ient Bytes received via Sql*netfrom Client 1 sql*net roundtrips to/fromclient 0 Sorts (mem
  Ory) 0 Sorts (disk) 0 rows processed 09:58:53 scott@felix sql>

We can see that the implementation plan has been changed.

Just now I created the index idx_mgr_temp, and then created the index idx_deptno_temp, so idx_mgr_temp cache first, in the case of caching idx_deptno_temp Rbo choose to go to the index Idx_deptno_ Temp's index range sweeps, if that's the reverse.

Delete Index First iddx_mgr_temp

09:58:53 Scott@felix Sql>drop indexidx_mgr_temp;
Index dropped.
 
Create the index again: 10:15:20 scott@felix sql>create indexidx_mgr_temp on emp_temp (MGR);
 
 
Index created.
 
10:16:05 Scott@felix Sql>select * from Emp_temp where mgr>100 and deptno>100; 
 
No rows selected Execution plan----------------------------------------------------------plan hash value:2973289657 ----------------------------------------------------
| Id | Operation |
Name |   ----------------------------------------------------
| 0 |              SELECT STATEMENT |  | |* 1 | TABLE ACCESS by INDEX rowid| emp_temp | |*<span style= "COLOR: #FF0000;"   > 2 | INDEX RANGE SCAN | Idx_mgr_temp |</span>----------------------------------------------------predicate information (identified by OperationID):---------------------------------------------------1-filter ("DEPTNO" >100) 2-access ("MGR" ) Note------rulebased OptimIzer used (consider using CBO) Statistics----------------------------------------------------------3 recur Sive calls 0 db block gets 6 consistent gets 0 physical reads 0 redo size 7 Bytes sent via sql*net toclient bytes received via Sql*netfrom Client 1 sql*net roundtrips to/fr Omclient 0 Sorts (memory) 0 sorts (disk) 0 rows processed 10:17:48 scott@felix-sql>

As the experiment shows, when the target SQL has two or more than two of the execution path of the registration phase, we can indeed adjust the relevant objects in the data dictionary cache order to affect the RBO for the implementation of the plan choice.

Create table EMP_TEMP1 as SELECT * from EMP;
 
10:25:30 Scott@felix sql>selectt1.mgr,t2.deptno from emp_temp t1,emp_temp1 T2 10:26:27 2 Where t1.empno=t2.empno;
 
Rows selected. Execution Plan----------------------------------------------------------The plan hash value:1323777565--------------- --------------------------
| Id | Operation |
Name |   -----------------------------------------
| 0 |           SELECT STATEMENT |   |
|  1 |           MERGE JOIN |   |
|   2 |           SORT JOIN |   |
|    3 | TABLE ACCESS full|   EMP_TEMP1 | |* 4 |           SORT JOIN |   |
|    5 | TABLE ACCESS full|
Emp_temp | -----------------------------------------predicate information (identified by OperationID):----------------------- ----------------------------4-access ("T1".) EMPNO "=" T2 "." EMPNO ") filter (" T1 "). EMPNO "=" T2 "." EMPNO ") Note------rulebased optimizer used (consider using CBO) Statistics----------------------------------------------------------1 Recursive calls 0 DB block gets 6 consistent gets 4 Physical reads 0 redo size 778 bytes sent via sql*net toclient 524 bytes received via SQL *netfrom Client 2 sql*net roundtrips to/fromclient 2 sorts (memory) 0 sorts (disk) 1 4 rows processed 10:31:02 Scott@felix sql>

We found emp_tem1p as the driving table,

Note: The sort merge join, in the strictest sense, does not have the concept of a driver table and a driven table, just for the sake of elaboration and for the concept of a sorted merge join.

So what about changing the position in the SQL text of the SQL?

 
Select T1.mgr,t2.deptno from emp_temp1 t2,emp_temp T1 Where t1.empno=t2.empno;
 
      10:31:02 Scott@felix Sql>selectt1.mgr,t2.deptno from emp_temp1 t2,emp_temp T1 10:36:15 2 Where t1.empno=t2.empno;         MGR DEPTNO--------------------7902 20 7698 30 7698 30 7839          20 7698 30 7839 30 7839 10 7566 20 10 7698
 
 
7788 7698 7566 7782, selected. Execution Plan----------------------------------------------------------The plan hash value:2135683657--------------- --------------------------
| Id | Operation |
Name |   -----------------------------------------
| 0 |           SELECT STATEMENT |   |
|  1 |           MERGE JOIN |   |
|  2 |           SORT JOIN |   |
|   3 | TABLE ACCESS full|  emp_temp | |* 4 |       SORT JOIN |    |
|   5 | TABLE ACCESS full|
EMP_TEMP1 | -----------------------------------------predicate information (identified by OperationID):----------------------- ----------------------------4-access ("T1".) EMPNO "=" T2 "." EMPNO ") filter (" T1 "). EMPNO "=" T2 "." EMPNO ") Note------rulebased optimizer used (consider using CBO) Statistics----------------------------------- -----------------------1 Recursive calls 0 DB block gets 6 consistent gets 0 phys ical reads 0 redo size 778 bytes sent via sql*net toclient 524 bytes received via Sql*netfrom CL Ient 2 sql*net roundtrips to/fromclient 2 sorts (memory) 0 sorts (disk) Rows Pro cessed 10:36:15 Scott@felix sql>

We found that the driver table changed to Emp_temp.

Note that when the target SQL has a level value of two or two of the execution paths, we determine that the Rbo's choice of execution plan can be affected by changing the order in which the objects involved in the target SQL appear in that SQL text.

Note: The above several experiments are on the RBO mode. Hash join is not supported by Rbo.

The EMP has a primary key and there is no index on the empno column on Emp_temp.

10:43:50 Scott@felix sql>select t1.mgr,t2.deptno from emp t1,emp_temp T2 2 Where 10:44:00;


Rows selected. Execution Plan----------------------------------------------------------The plan hash value:367190759---------------- ---------------------------------
| Id | Operation |
Name |   -------------------------------------------------
| 0 |          SELECT STATEMENT |   |
|  1 |          NESTED LOOPS |   |
|   2 |          NESTED LOOPS |   |
|    3 | TABLE ACCESS Full |    emp_temp | |* 4 | INDEX UNIQUE SCAN |   Pk_emp | |   5 | TABLE ACCESS by INDEX rowid|
EMP | -------------------------------------------------predicate information (identified by Operation ID):--------------- ------------------------------------4-access ("T1".) EMPNO "=" T2 "." EMPNO ") Note------the rule based optimizer used (consider using CBO) Statistics----------------------------------------------------------0 Recursive calls 0 db block gets consistent gets 0 p  Hysical reads 0 Redo size 778 bytes sent via sql*net to client 524 bytes via received
         From client 2 sql*net roundtrips to/from client 0 sorts (memory) 0 sorts (disk)
 Rows processed 10:44:00 Scott@felix sql>

Modify the table order for this SQL text:

Select T1.mgr,t2.deptno from emp_temp t2,emp T1 Where t1.empno=t2.empno;
 
10:44:00 Scott@felix Sql>selectt1.mgr,t2.deptno from Emp_temp T2, emp T1 10:49:57 2 Where t1.empno=t2.empno;
 
 
Rows selected. Execution Plan----------------------------------------------------------The plan hash value:367190759---------------- ---------------------------------
| Id | Operation |
Name |   -------------------------------------------------
| 0 |          SELECT STATEMENT |   |
|  1 |          NESTED LOOPS |   |
|   2 |         NESTED LOOPS |   |
|   3 | TABLE ACCESS Full |    emp_temp | |* 4 | INDEX UNIQUE SCAN |   Pk_emp | |   5 | TABLE ACCESS by INDEX rowid|
EMP | -------------------------------------------------predicate information (identified by OperationID):--------------- ------------------------------------4-access ("T1".) EMPNO "=" T2 "." EMPNO ") Note------rulebased OptimizerUsed (consider using CBO) Statistics----------------------------------------------------------1 recursive Calls 0 db block gets consistent gets 0 physical reads 0 redo size 778 b Ytes sent via sql*net toclient 524 bytes received via Sql*netfrom Client 2 sql*net roundtrips ENT 0 Sorts (memory) 0 sorts (disk) rows processed 10:49:57 Scott@felix

As you can see, the execution plan goes through a nested loop, and the driver table is still table emp_temp, which proves that the execution plan can be selected if the Rbo is based on the size of the path level value of the target SQL bar. Then no matter how you adjust the position of the related object in the SQL text of the SQL, there will be no effect on the final execution plan for that SQL.