RULE-based optimizer (learning notes) and rule learning notes

RULE-based optimizer (learning notes) and rule learning notes
Cui Hua's Oracle-based SQL optimization learning notes
1.1 RULE-based Optimizer

(1) CBO

(2) RBO

Compared with CBO, RBO has obvious permissions. When RBO is used, once a problem occurs in the execution plan, it is difficult to adjust it. In addition, if RBO is used, the writing of the target SQL statement, or even the sequence of the objects involved in the target SQL statement in the SQL text, may affect the selection of the RBO execution plan, even worse, good features and functions in Oracle databases cannot be used in RBO because they cannot be supported by RBO locks.

 

As long as one of the following situations occurs, Oracle still does not use RBO (Instead, CBO) even if the optimizer mode is modified or RULE Hnint is used ).

(1) IOT (indexOrganized Table) is designed in the target SQL ).

(2) the objects in the target SQL are partitioned tables.

(3) Parallel queries or parallel DML are used.

(4) use a Star Connection

(5) use hash connection

(6) fast and full index Scanning

(7) function indexes used

(8 )......

In this case, it is difficult to adjust the execution plan selected by RBO. One of the key reasons is that hint cannot be used. If Hint is used in the target SQL statement, it means that CBO is automatically started, that is, Oracle will use CBO to parse the target SQL statement of the Hint. There are only two exceptions: RULE Hint and SRIVING_SITE Hint, which can be used in RBO and cannot be used automatically.

 

So is there no way to adjust the execution plan when RBO is used ??

Of course this is not the case, but in this case, our adjustment methods are very limited. One feasible method is to rewrite the target SQL equivalent. For example, add 0 to the column of the NUMBER or DATE type in the where condition of the target SQL statement (if it is varchar2 or char type, you can add an empty character, for example, | ''), so that you can leave the indexes that can be taken away. For target SQL statements that contain multi-Table connections, this change can even affect the order of table connections, then, the execution plan of the target SQL statement can be adjusted when RBO is used.

 

 

But if there are two or more execution paths with the same level value, how should RBO choose? In this case, RBO caches the Data Dictionary based on The objects involved in the target SQL statement) the cache sequence in and the sequence of objects involved in the target SQL statement in the target SQL text. This means that we can adjust the cache sequence of related objects in the data dictionary, and change the order in which each object involved in the target SQL statement appears in the SQL text to adjust its execution plan.

 

Experiment: Use RBO to adjust the execution plan of the target SQL statement.

 

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;

 

### Set the optimizer mode to RULE in the current session

 

Alter session set optimizer_mode = 'rule ';

 

SET AUTOT TRACE EXP

 

 

If we find that the index IDX_DEPTNO_TEMP is less efficient than the index IDX_MGR_TEMP, or we want RBO to take the index idx1_mgr_temp, how can we make it happen ??

 

You can add 0 to prevent it from indexing:

 

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-----   - rulebased 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 toclient       512  bytes received via SQL*Netfrom client         1  SQL*Net roundtrips to/fromclient         0  sorts (memory)         0  sorts (disk)         0  rows processed 09:58:53 scott@felix SQL> 

We can see that the execution plan has changed.

 

 

Just now, I first created an index IDX_MGR_TEMP and then an index IDX_DEPTNO_TEMP. Therefore, IDX_MGR_TEMP is first cached. In the case of IDX_DEPTNO_TEMP cache, RBO chooses to scan the index range of IDX_DEPTNO_TEMP, what if this is the opposite ??

Delete index IDDX_MGR_TEMP first

 

At 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 bytes | Id | Operation | Name | percent | 0 | select statement | * 1 | table access by index rowid | EMP_TEMP | * 2 | index range scan | IDX_MGR_TEMP | percent ---------------------------------------------------- predicate Information (identified by operationid): ----------------------------------------------- 1-filter ("DEPTNO"> 100) 2-access ("MGR"> 100) Note ------rulebased optimizer used (consider using cbo) statistics limit 3 recursive cballs 0 db block gets 6 consistent gets 0 physical reads 0 redo size 799 bytes sent via SQL * Net toclient 512 bytes encoded ed via SQL * Netfrom client 1 SQL * Net roundtrips /fromclient 0 sorts (memory) 0 sorts (disk) 0 rows processed 10:17:48 scott @ felix SQL>

 

The above experiment shows that when two or more execution paths of the target SQL statement are registered simultaneously, we can adjust the cache sequence of related objects in the data dictionary cache to influence the selection of RBO execution plans.

 

 

Create table emp_temp1 as select * from emp; 10:25:30 scott@felix SQL>Selectt1.mgr,t2.deptno from emp_temp t1,emp_temp1 t210:26:27  2  Where t1.empno=t2.empno; 14 rows selected. Execution Plan----------------------------------------------------------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)        14  rows processed 10:31:02 scott@felix SQL>

We found that emp_tem1p is used as the driver table,

Note: In strict sense, there is no concept of a drive table or a drive table. Here, we just add a concept to the sort merge join for convenience.

 

 

So what if I change the position in the SQL Text of the SQL statement ??

 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  t110: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         30     7788         20     7698         30     7566         20     7782         10 14 rows selected.  Execution Plan----------------------------------------------------------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  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)        14  rows processed 10:36:15 scott@felix SQL>

 

We found that the driver table is changed to emp_temp.

 

Note: When the target SQL has two or more execution paths with the same level value, we are sure that we can influence the selection of RBO execution plans by changing the order in which each object involved in the target SQL appears in the SQL text.

 

 

Note: The above experiments are based on the RBO model. RBO does not support HASH join.

 

 

Emp has a primary key, and the empno column on emp_temp has no index.

10:43:50 scott@felix SQL>Select t1.mgr,t2.deptno from emp  t1,emp_temp t210:44:00   2   Where t1.empno=t2.empno;14 rows selected.Execution Plan----------------------------------------------------------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-----   - rule based optimizer used (consider using cbo)Statistics----------------------------------------------------------          0  recursive calls          0  db block gets         22  consistent gets          0  physical reads          0  redo size        778  bytes sent via SQL*Net to client        524  bytes received via SQL*Net from client          2  SQL*Net roundtrips to/from client          0  sorts (memory)          0  sorts (disk)         14  rows processed10:44:00 scott@felix SQL>

 

Modify the table sequence of the 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 t110:49:57  2    Where t1.empno=t2.empno; 14 rows selected.  Execution Plan----------------------------------------------------------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 optimizer used (consider using cbo)  Statistics----------------------------------------------------------         1  recursive calls         0  db block gets        22  consistent gets         0  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         0  sorts (memory)         0  sorts (disk)        14  rows processed 10:49:57 scott@felix SQL>

 

We can see that the execution plan follows a nested loop and the driver table is still EMP_TEMP, which proves that: if RBO selects an execution plan based on the level value of each execution path of the target SQL statement. No matter how you adjust the position of the related object in the SQL Text of the SQL statement, the final execution plan of the SQL statement will not be affected.