1、优化器是oracle数据库中内置的一个核心子系统,也可以把它理解成是oracle数据库中的一个核心的模块或者一个核心功能组件,
优化器的目的是为了得到目标sql的执行计划
2、oracle数据库里的优化器又分为RBO和CBO这两种类型。
3、RBO所用的判断原则为一组内置的规则,这些规则是硬编码在oracle数据库的代码中的,RBO会根据这些规则从目标sql诸多可能的执行路径选择一条来作为执行计划;
CBO会从目标sql诸多可能的执行路径中选择成本最小的一条作为执行计划,成本值是根据目标sql语句所涉及的表、索引、列等相关对象的统计信息算出来的。
4、sql语句的执行过程
5、RBO准则:oracle会认为等级值低的执行路径效率会比等级高的执行效率要高。也就是在RBO眼里,等级1所对应的执行路径的执行效率最高,等级15对应的执行路径效率最低
效率最高的是通过ROWID,效率最低的是全表扫描。
6、出现以下的情形之一,那么即使你修改的优化器的模式或者使用rule hint,oracle依然不会使用RBO
索引组织表,分区表,并行查询,并行DML,星型连接,哈希连接,索引快速全扫描,函数索引
7、如果出现了两条或者两条以上的等级值相同的执行路径的情况,RBO如何选择?
会根据目标sql中所涉及的相关对象再数据字典缓存中的缓存顺序和目标sql中所涉及的各个对象在目标sql文本中出现的
先后顺序来综合判断。
我们来看一个使用RBO的情况下对目标sql的执行计划做调整的实例。
SQL> create index idx_mgr_temp on emp_temp(mgr);
索引已创建。
SQL> create index idx_mgr_temp on emp_temp(deptno);
create index idx_mgr_temp on emp_temp(deptno)
*
第 1 行出现错误:
ORA-00955: 名称已由现有对象使用
SQL> create index idx_deptno_temp on emp_temp(deptno);
索引已创建。
SQL> set linesize 1000 pagesize 1000
SQL> .
SQL> /
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-12月-80 800 20
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
7566 JONES MANAGER 7839 02-4月 -81 2975 20
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
7782 CLARK MANAGER 7839 09-6月 -81 2450 10
7788 SCOTT ANALYST 7566 19-4月 -87 3000 20
7839 KING PRESIDENT 17-11月-81 5000 10
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
7876 ADAMS CLERK 7788 23-5月 -87 1100 20
7900 JAMES CLERK 7698 03-12月-81 950 30
7902 FORD ANALYST 7566 03-12月-81 3000 20
7934 MILLER CLERK 7782 23-1月 -82 1300 10
已选择14行。
更改会话的执行计划的规则为RBO,并查看当前会话的执行计划。
SQL> alter session set optimizer_mode='RULE';
会话已更改。
SQL> set autotrace traceonly explain;
SQL> select * from emp_temp where mgr>100 and deptno>100;
执行计划
----------------------------------------------------------
Plan hash value: 1670750536
-------------------------------------------------------
| Id | Operation | Name |
-------------------------------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | TABLE ACCESS BY INDEX ROWID| EMP_TEMP |
|* 2 | INDEX RANGE SCAN | IDX_DEPTNO_TEMP |
-------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("MGR">100)
2 - access("DEPTNO">100)
Note
-----
- rule based optimizer used (consider using cbo)
SQL>
8、假如我们发现走索引IDX_DEPTNO_TEMP不如走索引IDX_MGR_TEMP的执行效率高,或者我们就想让RBO走索引IDX_MGR_TEXP
在使用RBO的情况下,我们通过等价改写目标sql(加0或者空字符的方式)来调整执行计划
select * from emp_temp
where mgr>100 and deptno+0>100;
执行计划
----------------------------------------------------------
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 operation id):
---------------------------------------------------
1 - filter("DEPTNO"+0>100)
2 - access("MGR">100)
Note
-----
- rule based optimizer used (consider using cbo)
9、调整两个索引在数据字典缓存顺序来改变执行计划。
SQL> drop index idx_mgr_temp;
索引已删除。
SQL> create index idx_mgr_temp on emp_temp(mgr);
索引已创建。
SQL> select * from emp_temp where mgr>100 and deptno>100;
执行计划
----------------------------------------------------------
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 operation id):
---------------------------------------------------
1 - filter("DEPTNO">100)
2 - access("MGR">100)
Note
-----
- rule based optimizer used (consider using cbo)
这也说明当目标sql有两个或者两个以上的执行路径的等级值相同时,我们确实可以通过调整相关对象在数据字典缓存中的顺序来影响RBO对于其执行计划的选择。
10、多表连接的驱动表,RBO会按照从右到左的顺序决定谁是驱动表,谁是被驱动表
select t1.mgr,t2.deptno from emp_temp t1,emp_temp1 t2
2 where t1.empno=t2.empno;
执行计划
----------------------------------------------------------
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 operation id):
---------------------------------------------------
4 - access("T1"."EMPNO"="T2"."EMPNO")
filter("T1"."EMPNO"="T2"."EMPNO")
Note
-----
- rule based optimizer used (consider using cbo)
表位置互换:
select t1.mgr,t2.deptno
2 from emp_temp1 t2,emp_temp t1
3 where t1.empno=t2.empno;
执行计划
----------------------------------------------------------
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 operation id):
---------------------------------------------------
4 - access("T1"."EMPNO"="T2"."EMPNO")
filter("T1"."EMPNO"="T2"."EMPNO")
Note
-----
- rule based optimizer used (consider using cbo)
11、如果RBO仅凭目标sql各条执行路径等级值的大小就可以选择出执行计划,那么无论怎么调整相关对象再该sql的sql文本中的位置,
对于该sql最终的执行计划都不会有任何影响。
优化器的目的是为了得到目标sql的执行计划
2、oracle数据库里的优化器又分为RBO和CBO这两种类型。
3、RBO所用的判断原则为一组内置的规则,这些规则是硬编码在oracle数据库的代码中的,RBO会根据这些规则从目标sql诸多可能的执行路径选择一条来作为执行计划;
CBO会从目标sql诸多可能的执行路径中选择成本最小的一条作为执行计划,成本值是根据目标sql语句所涉及的表、索引、列等相关对象的统计信息算出来的。
4、sql语句的执行过程
5、RBO准则:oracle会认为等级值低的执行路径效率会比等级高的执行效率要高。也就是在RBO眼里,等级1所对应的执行路径的执行效率最高,等级15对应的执行路径效率最低
效率最高的是通过ROWID,效率最低的是全表扫描。
6、出现以下的情形之一,那么即使你修改的优化器的模式或者使用rule hint,oracle依然不会使用RBO
索引组织表,分区表,并行查询,并行DML,星型连接,哈希连接,索引快速全扫描,函数索引
7、如果出现了两条或者两条以上的等级值相同的执行路径的情况,RBO如何选择?
会根据目标sql中所涉及的相关对象再数据字典缓存中的缓存顺序和目标sql中所涉及的各个对象在目标sql文本中出现的
先后顺序来综合判断。
我们来看一个使用RBO的情况下对目标sql的执行计划做调整的实例。
SQL> create index idx_mgr_temp on emp_temp(mgr);
索引已创建。
SQL> create index idx_mgr_temp on emp_temp(deptno);
create index idx_mgr_temp on emp_temp(deptno)
*
第 1 行出现错误:
ORA-00955: 名称已由现有对象使用
SQL> create index idx_deptno_temp on emp_temp(deptno);
索引已创建。
SQL> set linesize 1000 pagesize 1000
SQL> .
SQL> /
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-12月-80 800 20
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
7566 JONES MANAGER 7839 02-4月 -81 2975 20
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
7782 CLARK MANAGER 7839 09-6月 -81 2450 10
7788 SCOTT ANALYST 7566 19-4月 -87 3000 20
7839 KING PRESIDENT 17-11月-81 5000 10
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
7876 ADAMS CLERK 7788 23-5月 -87 1100 20
7900 JAMES CLERK 7698 03-12月-81 950 30
7902 FORD ANALYST 7566 03-12月-81 3000 20
7934 MILLER CLERK 7782 23-1月 -82 1300 10
已选择14行。
更改会话的执行计划的规则为RBO,并查看当前会话的执行计划。
SQL> alter session set optimizer_mode='RULE';
会话已更改。
SQL> set autotrace traceonly explain;
SQL> select * from emp_temp where mgr>100 and deptno>100;
执行计划
----------------------------------------------------------
Plan hash value: 1670750536
-------------------------------------------------------
| Id | Operation | Name |
-------------------------------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | TABLE ACCESS BY INDEX ROWID| EMP_TEMP |
|* 2 | INDEX RANGE SCAN | IDX_DEPTNO_TEMP |
-------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("MGR">100)
2 - access("DEPTNO">100)
Note
-----
- rule based optimizer used (consider using cbo)
SQL>
8、假如我们发现走索引IDX_DEPTNO_TEMP不如走索引IDX_MGR_TEMP的执行效率高,或者我们就想让RBO走索引IDX_MGR_TEXP
在使用RBO的情况下,我们通过等价改写目标sql(加0或者空字符的方式)来调整执行计划
select * from emp_temp
where mgr>100 and deptno+0>100;
执行计划
----------------------------------------------------------
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 operation id):
---------------------------------------------------
1 - filter("DEPTNO"+0>100)
2 - access("MGR">100)
Note
-----
- rule based optimizer used (consider using cbo)
9、调整两个索引在数据字典缓存顺序来改变执行计划。
SQL> drop index idx_mgr_temp;
索引已删除。
SQL> create index idx_mgr_temp on emp_temp(mgr);
索引已创建。
SQL> select * from emp_temp where mgr>100 and deptno>100;
执行计划
----------------------------------------------------------
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 operation id):
---------------------------------------------------
1 - filter("DEPTNO">100)
2 - access("MGR">100)
Note
-----
- rule based optimizer used (consider using cbo)
这也说明当目标sql有两个或者两个以上的执行路径的等级值相同时,我们确实可以通过调整相关对象在数据字典缓存中的顺序来影响RBO对于其执行计划的选择。
10、多表连接的驱动表,RBO会按照从右到左的顺序决定谁是驱动表,谁是被驱动表
select t1.mgr,t2.deptno from emp_temp t1,emp_temp1 t2
2 where t1.empno=t2.empno;
执行计划
----------------------------------------------------------
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 operation id):
---------------------------------------------------
4 - access("T1"."EMPNO"="T2"."EMPNO")
filter("T1"."EMPNO"="T2"."EMPNO")
Note
-----
- rule based optimizer used (consider using cbo)
表位置互换:
select t1.mgr,t2.deptno
2 from emp_temp1 t2,emp_temp t1
3 where t1.empno=t2.empno;
执行计划
----------------------------------------------------------
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 operation id):
---------------------------------------------------
4 - access("T1"."EMPNO"="T2"."EMPNO")
filter("T1"."EMPNO"="T2"."EMPNO")
Note
-----
- rule based optimizer used (consider using cbo)
11、如果RBO仅凭目标sql各条执行路径等级值的大小就可以选择出执行计划,那么无论怎么调整相关对象再该sql的sql文本中的位置,
对于该sql最终的执行计划都不会有任何影响。