暂无图片
暂无图片
1
暂无图片
暂无图片
暂无图片
首页 / Oracle-SQL性能优化（5）-查询转换

Oracle-SQL性能优化（5）-查询转换

原创 大柏树 2022-11-03
898

查询转换 Oracle Query Transformation 又称查询重写，它是Oracle在解析目标SQL过程中非常重要的一步，Oracle解析器会把目标SQL的每个查询部分都标记为内部查询块，可能会对其进行等价改写，目的是为了能够更高效的执行目标SQL。
例如下面的SQL，我们通常把内部查询块称为子查询。

SQL> select first_name,last_name from hr.employees
  2  where department_id in
  3  (select department_id from hr.departments where location_id = 1800);
FIRST_NAME	     LAST_NAME
-------------------- -------------------------
Michael 	     Hartstein
Pat		     Fay

复制

查询转换主要包含以下四种情况：

  • 视图合并 View Merging
  • 谓词推入 Predicate Pushing
  • 子查询展开 Subquery Unnesting
  • 物化视图重写 Query Rewrite with Materialized Views

1.视图合并

SQL语句中包含select子查询或者视图，CBO会展开子查询或视图，进行相应的等价改写，这就是视图合并。
示例如下：

--先创建如下视图：
SQL> create view hr.employees_50_vw as 
  2  select employee_id,last_name,job_id,salary,commission_pct,department_id
  3  from hr.employees
  4  where department_id = 50;

View created.

SQL> 
--查询视图
SQL> select EMPLOYEE_ID from hr.employees_50_vw where EMPLOYEE_ID=150;
Execution Plan
----------------------------------------------------------
Plan hash value: 1833546154
---------------------------------------------------------------------------
| Id  | Operation		    | Name	    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT	    |		    |	  1 |	  7 |	  1   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES     |	  1 |	  7 |	  1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN	    | EMP_EMP_ID_PK |	  1 |	    |	  0   (0)| 00:00:01 |
---------------------------------------------------------------------------
可以看到，当我们对employees_50_vw 视图进行检索时优化器对其进行视图合并操作。在执行计划中，视图名和view关键字也都没有了。

复制

视图合并，并不一定能够提升性能，对于一些复杂的视图，内部已经单独进行了优化，如果发生了视图合并，则会打乱其原来的执行顺序，导致整体性能下降，我们可以通过添加HINT:no_merge禁止视图合并，命令如下：

SQL> select /*+ no_merge(v) */ v.employee_id from hr.employees_50_vw v where v.employee_id > 150;
Execution Plan
----------------------------------------------------------
Plan hash value: 4085831017
---------------------------------------------------------------------------
| Id  | Operation	    | Name		| Rows	| Bytes | Cost (%CPU)| Time	|
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |			|    24 |   312 |     2   (0)| 00:00:01 |
|   1 |  VIEW		    | EMPLOYEES_50_VW	|    24 |   312 |     2   (0)| 00:00:01 |
|*  2 |   VIEW		    | index$_join$_002	|    24 |   168 |     2   (0)| 00:00:01 |
|*  3 |    HASH JOIN	    |			|	|	|	     |		|
|*  4 |     INDEX RANGE SCAN| EMP_DEPARTMENT_IX |    24 |   168 |     1   (0)| 00:00:01 |
|*  5 |     INDEX RANGE SCAN| EMP_EMP_ID_PK	|    24 |   168 |     2  (50)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("DEPARTMENT_ID"=50 AND "EMPLOYEE_ID">150)
   3 - access(ROWID=ROWID)
   4 - access("DEPARTMENT_ID"=50)
   5 - access("EMPLOYEE_ID">150)
添加HINT后，执行计划中出现了VIEW关键字，说明未发生视图合并。

复制

在Oracle中，当SQL中所包含的视图定义中出现以下内容时（包括但不限于）不能合并视图，UNION/UNION ALL/MINUS/ROWNUM等。

SQL> select * from
  2  (select e.*,d.dname
  3  from scott.emp e,scott.dept d
  4  where d.deptno = e.deptno
  5  and d.dname = 'ACCOUNTING'
  6  and rownum < 10)
  7  where ename='CLARK';


Execution Plan
----------------------------------------------------------
Plan hash value: 61981215

------------------------------------------------------------------------------------------
| Id  | Operation		       | Name	 | Rows  | Bytes | Cost (%CPU)| Time	 |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	       |	 |     5 |   480 |     6  (17)| 00:00:01 |
|*  1 |  VIEW			       |	 |     5 |   480 |     6  (17)| 00:00:01 |
|*  2 |   COUNT STOPKEY 	       |	 |	 |	 |	      | 	 |
|   3 |    MERGE JOIN		       |	 |     5 |   255 |     6  (17)| 00:00:01 |
|*  4 |     TABLE ACCESS BY INDEX ROWID| DEPT	 |     1 |    13 |     2   (0)| 00:00:01 |
|   5 |      INDEX FULL SCAN	       | PK_DEPT |     4 |	 |     1   (0)| 00:00:01 |
|*  6 |     SORT JOIN		       |	 |    14 |   532 |     4  (25)| 00:00:01 |
|   7 |      TABLE ACCESS FULL	       | EMP	 |    14 |   532 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------------
SQL> select * from                   
  2  (select e.*,d.dname
  3  from scott.emp e,scott.dept d
  4  where d.deptno=e.deptno
  5  and d.dname='ACCOUNTING')
  6  where ename='CLARK';


Execution Plan
----------------------------------------------------------
Plan hash value: 3625962092

----------------------------------------------------------------------------------------
| Id  | Operation		     | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	     |	       |     1 |    51 |     4	 (0)| 00:00:01 |
|   1 |  NESTED LOOPS		     |	       |     1 |    51 |     4	 (0)| 00:00:01 |
|   2 |   NESTED LOOPS		     |	       |     1 |    51 |     4	 (0)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL	     | EMP     |     1 |    38 |     3	 (0)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN	     | PK_DEPT |     1 |       |     0	 (0)| 00:00:01 |
|*  5 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    13 |     1	 (0)| 00:00:01 |
----------------------------------------------------------------------------------------

复制

2.谓词推入

如果涉及到的SQL未发生视图合并，那么优化器就会把SQL中的视图或者子查询当做一个独立的处理单元，将原本处于外部查询和视图之间的连接条件推入视图或者子查询内部，这个过程称为谓词推入。谓词推入的目的是尽可能的过滤掉无用的数据，以提升性能。
示例如下：

--创建视图，加上union all，防止视图合并，从而展示谓词推入。
SQL> create or replace view scott.push_vw as
  2  select e.ename,e.job,e.mgr,e.deptno,d.dname
  3  from scott.dept d,scott.emp e
  4  where d.deptno=e.deptno and d.dname='ACCOUNTING'
  5  union all
  6  select e.ename,e.job,e.mgr,e.deptno,d.dname
  7  from scott.dept d,scott.emp e
  8  where d.deptno=e.deptno and d.dname='SALES';
View created.
SQL>

SQL> select * from scott.push_vw v where v.ename = 'CLARK';
Execution Plan
----------------------------------------------------------
Plan hash value: 2727022673
---------------------------------------------------------------------------
| Id  | Operation		       | Name	 | Rows  | Bytes | Cost (%CPU)| Time	 |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT	       |	 |     2 |    96 |     8   (0)| 00:00:01 |
|   1 |  VIEW			       | PUSH_VW |     2 |    96 |     8   (0)| 00:00:01 |
|   2 |   UNION-ALL		       |	 |	 |	 |	      | 	 |
|   3 |    NESTED LOOPS 	       |	 |     1 |    34 |     4   (0)| 00:00:01 |
|   4 |     NESTED LOOPS	       |	 |     1 |    34 |     4   (0)| 00:00:01 |
|*  5 |      TABLE ACCESS FULL	       | EMP	 |     1 |    21 |     3   (0)| 00:00:01 |
|*  6 |      INDEX UNIQUE SCAN	       | PK_DEPT |     1 |	 |     0   (0)| 00:00:01 |
|*  7 |     TABLE ACCESS BY INDEX ROWID| DEPT	 |     1 |    13 |     1   (0)| 00:00:01 |
|   8 |    NESTED LOOPS 	       |	 |     1 |    34 |     4   (0)| 00:00:01 |
|   9 |     NESTED LOOPS	       |	 |     1 |    34 |     4   (0)| 00:00:01 |
|* 10 |      TABLE ACCESS FULL	       | EMP	 |     1 |    21 |     3   (0)| 00:00:01 |
|* 11 |      INDEX UNIQUE SCAN	       | PK_DEPT |     1 |	 |     0   (0)| 00:00:01 |
|* 12 |     TABLE ACCESS BY INDEX ROWID| DEPT	 |     1 |    13 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   5 - filter("E"."ENAME"='CLARK')
   6 - access("D"."DEPTNO"="E"."DEPTNO")
   7 - filter("D"."DNAME"='ACCOUNTING')
  10 - filter("E"."ENAME"='CLARK')
  11 - access("D"."DEPTNO"="E"."DEPTNO")
  12 - filter("D"."DNAME"='SALES')
--因为有union all，所以优化器不能进行视图合并，而是将where条件ename = 'CLARK'推送到视图中，如上述执行计划中的第5和第10步。
--当执行计划中view关键字前面带有 * 号，且谓词信息部分为外部过滤条件时，说明谓词没有推入到视图中。

复制

一般情况下，常量的谓词推入有利于提升性能，例如ename=‘CLARK’.
当然了，也可以通过添加hint强制优化器将谓词推入视图中。
hint: push_pred(name) name:子查询或视图的别名

3.子查询展开

优化器将嵌套查询转换为等效的join语句，然后优化整个SQL连接，这个过程称为子查询展开。
以下面的SQL为例，在子查询不展开的情况下，需要从emp表中获取每一行，代入子查询中进行匹配，类似于嵌套循环。而子查询展开之后emp和dept表就可以做一个哈希连接或合并连接。

--子查询展开
SQL> select * from scott.emp where deptno in (select deptno from scott.dept where dname='ACCOUNTING');
SQL> select * from table(dbms_xplan.DISPLAY_CURSOR(null,null,'ADVANCED ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID	dczzs001pyx6h, child number 1
-------------------------------------
select * from scott.emp where deptno in (select deptno from scott.dept
where dname='ACCOUNTING')
Plan hash value: 844388907
-------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation		     | Name    | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time	 | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	     |	       |      1 |	 |	 |     6 (100)| 	 |	3 |00:00:00.01 |      10 |    |	 |	    |
|   1 |  MERGE JOIN		     |	       |      1 |      5 |   255 |     6  (17)| 00:00:01 |	3 |00:00:00.01 |      10 |    |	 |	    |
|*  2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |      1 |      1 |    13 |     2   (0)| 00:00:01 |	1 |00:00:00.01 |       4 |    |	 |	    |
|   3 |    INDEX FULL SCAN	     | PK_DEPT |      1 |      4 |	 |     1   (0)| 00:00:01 |	4 |00:00:00.01 |       2 |    |	 |	    |
|*  4 |   SORT JOIN		     |	       |      1 |     14 |   532 |     4  (25)| 00:00:01 |	3 |00:00:00.01 |       6 |  2048 |  2048 | 2048  (0)|
|   5 |    TABLE ACCESS FULL	     | EMP     |      1 |     14 |   532 |     3   (0)| 00:00:01 |     14 |00:00:00.01 |       6 |    |	 |	    |
-------------------------------------------------------------------------------------------------------------------------------------------------------------
--添加hint禁用子查询展开
SQL> select * from scott.emp where deptno in (select /*+ no_unnest */ deptno from scott.dept where dname='ACCOUNTING');
SQL> select * from table(dbms_xplan.DISPLAY_CURSOR(null,null,'ADVANCED ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID	68s87y6y08qjb, child number 0
-------------------------------------
select * from scott.emp where deptno in (select /*+ no_unnest */ deptno
from scott.dept where dname='ACCOUNTING')
Plan hash value: 2809975276
----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation		     | Name    | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time	 | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	     |	       |      1 |	 |	 |     6 (100)| 	 |	3 |00:00:00.01 |      13 |
|*  1 |  FILTER 		     |	       |      1 |	 |	 |	      | 	 |	3 |00:00:00.01 |      13 |
|   2 |   TABLE ACCESS FULL	     | EMP     |      1 |     14 |   532 |     3   (0)| 00:00:01 |     14 |00:00:00.01 |       7 |
|*  3 |   TABLE ACCESS BY INDEX ROWID| DEPT    |      3 |      1 |    13 |     1   (0)| 00:00:01 |	1 |00:00:00.01 |       6 |
|*  4 |    INDEX UNIQUE SCAN	     | PK_DEPT |      3 |      1 |	 |     0   (0)| 	 |	3 |00:00:00.01 |       3 |
----------------------------------------------------------------------------------------------------------------------------------
SQL> select * from scott.emp where deptno in (select /*+ no_unnest */ deptno from scott.dept where dname='ACCOUNTING');

     EMPNO ENAME      JOB	       MGR HIREDATE	    SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7782 CLARK      MANAGER	      7839 09-JUN-81	   2450 		   10
      7839 KING       PRESIDENT 	   17-NOV-81	   5000 		   10
      7934 MILLER     CLERK	      7782 23-JAN-82	   1300 		   10

SQL> select * from table(dbms_xplan.DISPLAY_CURSOR(null,null,'ADVANCED ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID	68s87y6y08qjb, child number 0
-------------------------------------
select * from scott.emp where deptno in (select /*+ no_unnest */ deptno
from scott.dept where dname='ACCOUNTING')

Plan hash value: 2809975276

----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation		     | Name    | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time	 | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	     |	       |      1 |	 |	 |     6 (100)| 	 |	3 |00:00:00.01 |      13 |
|*  1 |  FILTER 		     |	       |      1 |	 |	 |	      | 	 |	3 |00:00:00.01 |      13 |
|   2 |   TABLE ACCESS FULL	     | EMP     |      1 |     14 |   532 |     3   (0)| 00:00:01 |     14 |00:00:00.01 |       7 |
|*  3 |   TABLE ACCESS BY INDEX ROWID| DEPT    |      3 |      1 |    13 |     1   (0)| 00:00:01 |	1 |00:00:00.01 |       6 |
|*  4 |    INDEX UNIQUE SCAN	     | PK_DEPT |      3 |      1 |	 |     0   (0)| 	 |	3 |00:00:00.01 |       3 |
----------------------------------------------------------------------------------------------------------------------------------
由于添加了hint /*+ no_unnest */ 禁用了子查询展开，进而出现了FILTER，类似于嵌套循环，此处的驱动表为主表emp，由于emp中deptno有3个不同的值，因此需要代入子查询遍历3次。

复制

语句中出现exists和not exists时，容易产生filter。
filter类似于一种天然的嵌套循环，优化可以参考嵌套循环，如果满足以下条件，那么filter的出现不一定会影响性能。

  • 驱动表筛选后返回少量数据。
  • 被驱动表的关联字段需要有索引（连接列基数较大或选择性较高）。
  • 两表关联后返回少量数据。
    若以上条件都不满足，则可以尝试使用 hint /*+ unnest */ 来消除FILTER，当hint无效时就需要对SQL语句进行等价改写。

4.物化视图查询重写

物化视图的查询结果存储在表中，查询物化视图的基表时，Oracle会自动判断能否通过查询物化视图来得到结果，如果可以，则应避免表之间的关联操作，直接从物化视图中读取数据，这个过程就是物化视图查询重写。由于大多数查询已经预先计算过了，因此利用该特性可以有效提高查询速度。
示例如下：

--首先创建物化视图
SQL> create materialized view sh.cal_month_sales_mv1
  2  enable query rewrite
  3  as
  4  select t.calendar_month_desc,sum(s.amount_sold) as dollars
  5  from sh.sales s,sh.times t
  6  where s.time_id = t.time_id
  7  group by t.calendar_month_desc;
Materialized view created.
SQL>
--检查系统查询重写参数是否打开
SQL> show parameter query_rewrite_enabled

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
_mmv_query_rewrite_enabled	     boolean	 TRUE
query_rewrite_enabled		     string	 TRUE
SQL>
--尝试查询以上基表，查看执行计划
SQL> select t.calendar_month_desc,sum(s.amount_sold)   
  2  from sh.sales s,sh.times t
  3  where s.time_id = t.time_id
  4  group by t.calendar_month_desc;
SQL> select * from table(dbms_xplan.DISPLAY_CURSOR(null,null,'ADVANCED ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID	6xv4juuycxk13, child number 0
-------------------------------------
select t.calendar_month_desc,sum(s.amount_sold) from sh.sales
s,sh.times t where s.time_id = t.time_id group by t.calendar_month_desc

Plan hash value: 3220541711

----------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation		     | Name		   | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	     |			   |	  1 |	     |	     |	   3 (100)|	     |	   48 |00:00:00.01 |    7 |
|   1 |  MAT_VIEW REWRITE ACCESS FULL| CAL_MONTH_SALES_MV1 |	  1 |	  48 |	 912 |	   3   (0)| 00:00:01 |	   48 |00:00:00.01 |    7 |
----------------------------------------------------------------------------------------------------------------------------------------------
--可以看到，直接从物化视图中获得了结果
SQL> select * from table(dbms_xplan.DISPLAY_CURSOR(null,null,'ADVANCED ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID	6xv4juuycxk13, child number 1
-------------------------------------
select t.calendar_month_desc,sum(s.amount_sold) from sh.sales
s,sh.times t where s.time_id = t.time_id group by t.calendar_month_desc

Plan hash value: 2607197432

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation		| Name	   | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | Pstart| Pstop | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	|	   |	  1 |	     |	     |	 567 (100)|	     |	     |	     |	   48 |00:00:00.37 | 1690 |	 1619 |       |       | 	 |
|   1 |  HASH GROUP BY		|	   |	  1 |	  60 |	2220 |	 567   (6)| 00:00:07 |	     |	     |	   48 |00:00:00.37 | 1690 |	 1619 |  1148K|  1148K| 1357K (0)|
|*  2 |   HASH JOIN		|	   |	  1 |	1460 | 54020 |	 566   (6)| 00:00:07 |	     |	     |	 1460 |00:00:00.37 | 1690 |	 1619 |  1557K|  1557K| 1603K (0)|
|   3 |    VIEW 		| VW_GBC_5 |	  1 |	1460 | 30660 |	 548   (6)| 00:00:07 |	     |	     |	 1460 |00:00:00.37 | 1635 |	 1619 |       |       | 	 |
|   4 |     HASH GROUP BY	|	   |	  1 |	1460 | 18980 |	 548   (6)| 00:00:07 |	     |	     |	 1460 |00:00:00.37 | 1635 |	 1619 |    64M|  6122K| 3067K (0)|
|   5 |      PARTITION RANGE ALL|	   |	  1 |	 918K|	  11M|	 525   (2)| 00:00:07 |	   1 |	  28 |	  918K|00:00:00.25 | 1635 |	 1619 |       |       | 	 |
|   6 |       TABLE ACCESS FULL | SALES    |	 28 |	 918K|	  11M|	 525   (2)| 00:00:07 |	   1 |	  28 |	  918K|00:00:00.19 | 1635 |	 1619 |       |       | 	 |
|   7 |    TABLE ACCESS FULL	| TIMES    |	  1 |	1826 | 29216 |	  18   (0)| 00:00:01 |	     |	     |	 1826 |00:00:00.01 |   55 |	    0 |       |       | 	 |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--我们禁用物化视图查询重写之后，查询重新关联。

复制

利用物化视图查询重写的功能，我们可以有效提高查询的速度，但该功能需要额外的存储空间，且对于前端完全透明。物化视图查询重写实际上是以空间换性能，因此对于查询较为频发且性能较差的SQL，可以考虑使用物化视图。
注：本文参考于：《DBA攻坚指南》

墨力计划oracle
最后修改时间：2022-11-04 09:12:43
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

文章被以下合辑收录

Oracle 性能优化（持续更新中）（共15篇）
性能优化

评论

目录
  • 1.视图合并
  • 2.谓词推入
  • 3.子查询展开
  • 4.物化视图查询重写