Oracle SQL 执行

最新推荐文章于 2024-04-23 21:54:10 发布

DB架构

最新推荐文章于 2024-04-23 21:54:10 发布

阅读量4.1k

点赞数

分类专栏： DBA 文章标签： oracle sql 数据库

本文链接：https://blog.csdn.net/u011868279/article/details/127139661

版权

DBA 专栏收录该内容

183 篇文章 12 订阅

订阅专栏

1.Oracle架构基础

Oracle数据库既用来指存储在硬盘上的内部存有数据的数据文件，也指用来管理这些文件的内存结构。术语“数据库”归属于数据文件，而“实例”则归属于内存结构。

实例由系统全局内存区域（SGA）以及一系列的后台进程组成。每个连接到数据库都是通过一个客户端进程来进行管理的。客户端进程是与服务器进程相联结的，每个服务器进程都会被分配一块私有的内存区域，称为程序共享内存区域或进程共享内存区域（Process Global Area , PGA）

2.SGA共享池

共享池是内存中最关键的部分之一，特别是对于SQL的执行来说。

共享池是Oracle缓存程序数据的地方。执行过的每一句SQL语句在共享池中都存有解析后的内容。

共享池中存储这些语句的地方称为库高速缓存。

算法最近最少使用（Least Recently Used, LRU）可以用来管理共享池中的对象。

基本思想：保留那些使用最频繁的以及最近使用的语句。

当你写SQL语句的时候，需要牢记于心的一点是：为了最高效地使用共享池，语句需要可以共享。如果你所写的每一句语句都是唯一的，基本上就违背了设立共享池的初衷。语句共享性越差，你将看到对于响应时间的影响也就会越大。

3.库高速缓存

库高速缓存是共享池中用来保存之前已经解析过的语句的区域。解析包括验证语句的语法、检验提及的对象，以及确认该对象的用户权限。

软解析： Oracle将取回之前解析过的信息并重用。

硬解析：语句之前没有执行过，Oracle将为当前的语句生成执行计划，并将它存在在缓存中以便将来重用。

要想知道Oracle在硬解析过程中都干了什么？最简单的方法：打开扩展SQL追踪，执行语句然后查看追踪数据。扩展的SQL追踪能抓取执行过程中发生的每个活动，不仅能看到所有执行的语句，而且还能看到Oracle执行的每一条语句。

4.完全相同的语句

确定一条语句是不是之前执行过，Oracle会去检查库高速缓存，看是不是存在同样的语句。

查询v$sql视图（列出了共享SQL区的统计信息，并且包含了一行最初输入的SQL文本每个子成员）查看当前存放在库高速缓存中的语句。

hr@orclpdb1:orclcdb> select * from employees where department_id = 60;

EMPLOYEE_ID FIRST_NAME           LAST_NAME                 EMAIL
----------- -------------------- ------------------------- -------------------------
PHONE_NUMBER         HIRE_DATE                     JOB_ID         SALARY COMMISSION_PCT MANAGER_ID
-------------------- ----------------------------- ---------- ---------- -------------- ----------
DEPARTMENT_ID
-------------
        103 Alexander            Hunold                    AHUNOLD
590.423.4567         03-JAN-2006 00:00:00          IT_PROG          9000                       102
           60

        104 Bruce                Ernst                     BERNST
590.423.4568         21-MAY-2007 00:00:00          IT_PROG          6000                       103
           60

        105 David                Austin                    DAUSTIN
590.423.4569         25-JUN-2005 00:00:00          IT_PROG          4800                       103
           60

        106 Valli                Pataballa                 VPATABAL
590.423.4560         05-FEB-2006 00:00:00          IT_PROG          4800                       103
           60

        107 Diana                Lorentz                   DLORENTZ
590.423.5567         07-FEB-2007 00:00:00          IT_PROG          4200                       103
           60


5 rows selected.

hr@orclpdb1:orclcdb> SELECT * FROM EMPLOYEES WHERE DEPARTMENT_ID = 60;

EMPLOYEE_ID FIRST_NAME           LAST_NAME                 EMAIL
----------- -------------------- ------------------------- -------------------------
PHONE_NUMBER         HIRE_DATE                     JOB_ID         SALARY COMMISSION_PCT MANAGER_ID
-------------------- ----------------------------- ---------- ---------- -------------- ----------
DEPARTMENT_ID
-------------
        103 Alexander            Hunold                    AHUNOLD
590.423.4567         03-JAN-2006 00:00:00          IT_PROG          9000                       102
           60

        104 Bruce                Ernst                     BERNST
590.423.4568         21-MAY-2007 00:00:00          IT_PROG          6000                       103
           60

        105 David                Austin                    DAUSTIN
590.423.4569         25-JUN-2005 00:00:00          IT_PROG          4800                       103
           60

        106 Valli                Pataballa                 VPATABAL
590.423.4560         05-FEB-2006 00:00:00          IT_PROG          4800                       103
           60

        107 Diana                Lorentz                   DLORENTZ
590.423.5567         07-FEB-2007 00:00:00          IT_PROG          4200                       103
           60


5 rows selected.

hr@orclpdb1:orclcdb> select /*a_comment*/ * from employees where department_id = 60;

EMPLOYEE_ID FIRST_NAME           LAST_NAME                 EMAIL
----------- -------------------- ------------------------- -------------------------
PHONE_NUMBER         HIRE_DATE                     JOB_ID         SALARY COMMISSION_PCT MANAGER_ID
-------------------- ----------------------------- ---------- ---------- -------------- ----------
DEPARTMENT_ID
-------------
        103 Alexander            Hunold                    AHUNOLD
590.423.4567         03-JAN-2006 00:00:00          IT_PROG          9000                       102
           60

        104 Bruce                Ernst                     BERNST
590.423.4568         21-MAY-2007 00:00:00          IT_PROG          6000                       103
           60

        105 David                Austin                    DAUSTIN
590.423.4569         25-JUN-2005 00:00:00          IT_PROG          4800                       103
           60

        106 Valli                Pataballa                 VPATABAL
590.423.4560         05-FEB-2006 00:00:00          IT_PROG          4800                       103
           60

        107 Diana                Lorentz                   DLORENTZ
590.423.5567         07-FEB-2007 00:00:00          IT_PROG          4200                       103
           60


5 rows selected.

hr@orclpdb1:orclcdb>
hr@orclpdb1:orclcdb> select sql_text,sql_id,child_number,hash_value,executions
  2  from v$sql where upper(sql_text) like '%EMPLOYEES%';

SQL_TEXT
----------------------------------------------------------------------------------------------------
SQL_ID        CHILD_NUMBER HASH_VALUE EXECUTIONS
------------- ------------ ---------- ----------
select /*a_comment*/ * from employees where department_id = 60
bt7fgxuf005ur            0 2617251671          1

select * from employees where department_id = 60
10jubat8pw6u9            0 1365121865          1

select sql_text,sql_id,child_number,hash_value,executions from v$sql where upper(sql_text) like '%EM
PLOYEES%'
10jv1b9ap3gxv            0 1431420859          1

SELECT * FROM EMPLOYEES WHERE DEPARTMENT_ID = 60
cq7t1xq95bpm8            0 2455098984          1


4 rows selected.

hr@orclpdb1:orclcdb>

尽管这3条语句是完全相同的结果，但Oracle认为它们是不同的，是因为，在执行一条语句时，Oracle会首先将字符串转换为散列值。这个散列值就作为该语句存放到库高速缓存时的关键字。当其他语句执行时，它们的散列值就会与已有的散列值做比较来寻找匹配。

为什么你在SQL中使用绑定变量而不是常量会如此重要，当使用绑定变量时，即使你改变了绑定变量的值，Oracle还是可以共享这个语句。

在解析过程中使用绑定变量的效果

hr@orclpdb1:orclcdb> 
hr@orclpdb1:orclcdb> variable v_dept number
hr@orclpdb1:orclcdb> exec :v_dept := 10

PL/SQL procedure successfully completed.

hr@orclpdb1:orclcdb> select * from employees where department_id = :v_dept;

EMPLOYEE_ID FIRST_NAME           LAST_NAME                 EMAIL
----------- -------------------- ------------------------- -------------------------
PHONE_NUMBER         HIRE_DATE                     JOB_ID         SALARY COMMISSION_PCT MANAGER_ID
-------------------- ----------------------------- ---------- ---------- -------------- ----------
DEPARTMENT_ID
-------------
        200 Jennifer             Whalen                    JWHALEN
515.123.4444         17-SEP-2003 00:00:00          AD_ASST          4400                       101
           10


1 row selected.

hr@orclpdb1:orclcdb> exec :v_dept := 20

PL/SQL procedure successfully completed.

hr@orclpdb1:orclcdb> select * from employees where department_id = :v_dept;

EMPLOYEE_ID FIRST_NAME           LAST_NAME                 EMAIL
----------- -------------------- ------------------------- -------------------------
PHONE_NUMBER         HIRE_DATE                     JOB_ID         SALARY COMMISSION_PCT MANAGER_ID
-------------------- ----------------------------- ---------- ---------- -------------- ----------
DEPARTMENT_ID
-------------
        201 Michael              Hartstein                 MHARTSTE
515.123.5555         17-FEB-2004 00:00:00          MK_MAN          13000                       100
           20

        202 Pat                  Fay                       PFAY
603.123.6666         17-AUG-2005 00:00:00          MK_REP           6000                       201
           20


2 rows selected.

hr@orclpdb1:orclcdb> exec :v_dept := 30

PL/SQL procedure successfully completed.

hr@orclpdb1:orclcdb> select * from employees where department_id = :v_dept;

EMPLOYEE_ID FIRST_NAME           LAST_NAME                 EMAIL
----------- -------------------- ------------------------- -------------------------
PHONE_NUMBER         HIRE_DATE                     JOB_ID         SALARY COMMISSION_PCT MANAGER_ID
-------------------- ----------------------------- ---------- ---------- -------------- ----------
DEPARTMENT_ID
-------------
        114 Den                  Raphaely                  DRAPHEAL
515.127.4561         07-DEC-2002 00:00:00          PU_MAN          11000                       100
           30

        115 Alexander            Khoo                      AKHOO
515.127.4562         18-MAY-2003 00:00:00          PU_CLERK         3100                       114
           30

        116 Shelli               Baida                     SBAIDA
515.127.4563         24-DEC-2005 00:00:00          PU_CLERK         2900                       114
           30

        117 Sigal                Tobias                    STOBIAS
515.127.4564         24-JUL-2005 00:00:00          PU_CLERK         2800                       114
           30

        118 Guy                  Himuro                    GHIMURO
515.127.4565         15-NOV-2006 00:00:00          PU_CLERK         2600                       114
           30

        119 Karen                Colmenares                KCOLMENA
515.127.4566         10-AUG-2007 00:00:00          PU_CLERK         2500                       114
           30


6 rows selected.

hr@orclpdb1:orclcdb> select sql_text,sql_id,child_number,executions
  2  from v$sql where sql_text like '%v_dept';

SQL_TEXT
----------------------------------------------------------------------------------------------------
SQL_ID        CHILD_NUMBER EXECUTIONS
------------- ------------ ----------
select * from employees where department_id = :v_dept
72k66s55jqk1j            0          3


1 row selected.

hr@orclpdb1:orclcdb>

利用绑定变量的优势及使用同样的SQL语句来编写SQL。需要更少的硬解析就意味着你的应用将会表现得更好并且可扩展性更强。

锁定器：锁定器是Oracle为了读取存放在库高速缓存或者其他内存结构中的信息时必须获得的一种锁。锁存器可以保护库高速缓存避免被两个同时进行的会话修改，或者一个会话正要读取的信息被另一个会话修改而导致的损坏。在读取库高速缓存中的任何信息前，Oracle都会获得一个锁存器，其他所有会话都必须等待，直到该锁存器被释放它们才能获得锁存器以完成工作。

互斥锁。它是一个序列化组件，用来阻止多个线程同时访问一个共享结构。与锁存器相比，互斥锁最大的优势在于：占用的内存较少，并且可以快速获取和释放。

互斥锁还可以直接修改游标的互斥锁引用计数，避免为已经打开的游标获取高速缓冲锁。互斥锁是比锁存器性能更好，也更具有可扩展性的机制。但要注意的是，语法解析仍然需要使用库高速缓存锁存器。

5.SGA缓冲区缓存

缓冲区缓存是系统全局内存区域（SGA）最大的部分之一。在数据库块从硬盘中读取出来后或写入硬盘之前，它用来存储数据库块。块是Oracle进行操作的最小单位。块中包含表数据行或索引条目，一些块还会包含用来排序的临时数据。Oracle必须读取块来获得SQL语句需要的数据行。块的典型大小有4KB，8KB或16KB.块大小的唯一限制因素取决于你所使用的操作系统。

6.查询转换

6.1 查询块

hr@orclpdb1:orclcdb> set autotrace on
hr@orclpdb1:orclcdb> select /*+qb_name(outer_employees)*/ * 
  2  from employees where department_id in
  3  (select /*+qb_name(inner_departments)*/ department_id from departments)
  4  ;
650.507.9822         23-MAY-2006 00:00:00          SH_CLERK         3000                       124
           50


106 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1445457117

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |   106 |  7314 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMPLOYEES |   106 |  7314 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("DEPARTMENT_ID" IS NOT NULL)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         13  consistent gets
          0  physical reads
          0  redo size
      10176  bytes sent via SQL*Net to client
        588  bytes received via SQL*Net from client
          9  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        106  rows processed

hr@orclpdb1:orclcdb>

查询可以有很多种转换方式。说明优化器是如何重构查询以生成更好的执行计划的。下面讲解转换方式：

视图合并
子查询解嵌套
联结消除
排序消除
谓语前推
使用物化视图重写查询

视图合并是一种将内嵌或存储方式视图展开为能独立分析或者查询剩余部分合并成总体执行计划的独立查询块的转换。改写后的语句基本上不包含视图。视图合并常常发生在当外部查询块的谓语包括下列项的时候。

能够在另一个查询块的索引中使用的列。
能够在另一个查询块的分区截断中使用的列。
在一个联结视图中能够限制返回行数的条件。

子查询解嵌套

子查询解嵌套与视图合并的相似之处在于通过一个单独的查询块来表示的。可合并的视图与可以解嵌套的子查询之间的主要区别在于，它们的位置是不同的：子查询位于WHERE子句，由转换器执行解嵌套审查。最典型的转换就是将子查询转变为表联结。

解嵌套实际上是首先将子查询转换为通过联结实现的内嵌视图，然后再与外层查询合并。有很多运算符都可以解嵌套：IN、NOT IN、EXISTS、NOT EXISTS、相关、不相关等等。

如果子查询没有被解嵌套，那么会为它单独生成子计划，并按照具有最优执行速度的总执行计划顺序执行。

当子查询不相关时，转换查询是非常直接的。

oe@orclpdb1:orclcdb> 
oe@orclpdb1:orclcdb> select * from employees
  2  where employee_id in 
  3  (select manager_id from departments);

EMPLOYEE_ID FIRST_NAME           LAST_NAME                 EMAIL
----------- -------------------- ------------------------- -------------------------
PHONE_NUMBER         HIRE_DATE                     JOB_ID         SALARY COMMISSION_PCT MANAGER_ID
-------------------- ----------------------------- ---------- ---------- -------------- ----------
DEPARTMENT_ID
-------------
        200 Jennifer             Whalen                    JWHALEN
515.123.4444         17-SEP-2003 00:00:00          AD_ASST          4400                       101
           10

        201 Michael              Hartstein                 MHARTSTE
515.123.5555         17-FEB-2004 00:00:00          MK_MAN          13000                       100
           20

        203 Susan                Mavris                    SMAVRIS
515.123.7777         07-JUN-2002 00:00:00          HR_REP           6500                       101
           40

        204 Hermann              Baer                      HBAER
515.123.8888         07-JUN-2002 00:00:00          PR_REP          10000                       101
           70

        205 Shelley              Higgins                   SHIGGINS
515.123.8080         07-JUN-2002 00:00:00          AC_MGR          12008                       101
          110

        100 Steven               King                      SKING
515.123.4567         17-JUN-2003 00:00:00          AD_PRES         24000
           90

        103 Alexander            Hunold                    AHUNOLD
590.423.4567         03-JAN-2006 00:00:00          IT_PROG          9000                       102
           60

        108 Nancy                Greenberg                 NGREENBE
515.124.4569         17-AUG-2002 00:00:00          FI_MGR          12008                       101
          100

        114 Den                  Raphaely                  DRAPHEAL
515.127.4561         07-DEC-2002 00:00:00          PU_MAN          11000                       100
           30

        121 Adam                 Fripp                     AFRIPP
650.123.2234         10-APR-2005 00:00:00          ST_MAN           8200                       100
           50

        145 John                 Russell                   JRUSSEL
011.44.1344.429268   01-OCT-2004 00:00:00          SA_MAN          14000             .4        100
           80


11 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 61425051

------------------------------------------------------------------------------------
| Id  | Operation            | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |    11 |   792 |     6   (0)| 00:00:01 |
|*  1 |  HASH JOIN RIGHT SEMI|             |    11 |   792 |     6   (0)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL  | DEPARTMENTS |    11 |    33 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL  | EMPLOYEES   |   107 |  7383 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("EMPLOYEE_ID"="MANAGER_ID")
   2 - filter("MANAGER_ID" IS NOT NULL)


Statistics
----------------------------------------------------------
        286  recursive calls
          6  db block gets
        411  consistent gets
         10  physical reads
       1016  redo size
       2154  bytes sent via SQL*Net to client
        664  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
         20  sorts (memory)
          0  sorts (disk)
         11  rows processed

oe@orclpdb1:orclcdb>

本例中的子查询就是通过转化为表联结来合并到主查询中的。这个联结运算是：HASH JOIN RIGHT SEMI运算，表明转换后选择进行半联结运算。该查询的执行计划就好像是按照下列语句得出来的。

oe@orclpdb1:orclcdb> select e.*
  2  from employees e, departments d
  3  where e.department_id = d.department_id;

EMPLOYEE_ID FIRST_NAME           LAST_NAME                 EMAIL
----------- -------------------- ------------------------- -------------------------
PHONE_NUMBER         HIRE_DATE                     JOB_ID         SALARY COMMISSION_PCT MANAGER_ID
-------------------- ----------------------------- ---------- ---------- -------------- ----------
DEPARTMENT_ID
-------------
        198 Donald               OConnell                  DOCONNEL
650.507.9833         21-JUN-2007 00:00:00          SH_CLERK         2600                       124
           50

        199 Douglas              Grant                     DGRANT
650.507.9844         13-JAN-2008 00:00:00          SH_CLERK         2600                       124
           50

        200 Jennifer             Whalen                    JWHALEN
515.123.4444         17-SEP-2003 00:00:00          AD_ASST          4400                       101
           10

        201 Michael              Hartstein                 MHARTSTE
515.123.5555         17-FEB-2004 00:00:00          MK_MAN          13000                       100
           20
        197 Kevin                Feeney                    KFEENEY
650.507.9822         23-MAY-2006 00:00:00          SH_CLERK         3000                       124
           50


106 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1445457117

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |   106 |  7314 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMPLOYEES |   106 |  7314 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("E"."DEPARTMENT_ID" IS NOT NULL)


Statistics
----------------------------------------------------------
          8  recursive calls
          0  db block gets
         15  consistent gets
          0  physical reads
          0  redo size
      10176  bytes sent via SQL*Net to client
        742  bytes received via SQL*Net from client
          9  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        106  rows processed

oe@orclpdb1:orclcdb>

使用NO_UNNEST提示

oe@orclpdb1:orclcdb> 
oe@orclpdb1:orclcdb> select *
  2  from employees
  3  where employee_id in 
  4            (select /*+NO_UNNEST*/ manager_id
  5               from departments);

EMPLOYEE_ID FIRST_NAME           LAST_NAME                 EMAIL
----------- -------------------- ------------------------- -------------------------
PHONE_NUMBER         HIRE_DATE                     JOB_ID         SALARY COMMISSION_PCT MANAGER_ID
-------------------- ----------------------------- ---------- ---------- -------------- ----------
DEPARTMENT_ID
-------------
        200 Jennifer             Whalen                    JWHALEN
515.123.4444         17-SEP-2003 00:00:00          AD_ASST          4400                       101
           10

        201 Michael              Hartstein                 MHARTSTE
515.123.5555         17-FEB-2004 00:00:00          MK_MAN          13000                       100
           20

        203 Susan                Mavris                    SMAVRIS
515.123.7777         07-JUN-2002 00:00:00          HR_REP           6500                       101
           40

        204 Hermann              Baer                      HBAER
515.123.8888         07-JUN-2002 00:00:00          PR_REP          10000                       101
           70

        205 Shelley              Higgins                   SHIGGINS
515.123.8080         07-JUN-2002 00:00:00          AC_MGR          12008                       101
          110

        100 Steven               King                      SKING
515.123.4567         17-JUN-2003 00:00:00          AD_PRES         24000
           90

        103 Alexander            Hunold                    AHUNOLD
590.423.4567         03-JAN-2006 00:00:00          IT_PROG          9000                       102
           60

        108 Nancy                Greenberg                 NGREENBE
515.124.4569         17-AUG-2002 00:00:00          FI_MGR          12008                       101
          100

        114 Den                  Raphaely                  DRAPHEAL
515.127.4561         07-DEC-2002 00:00:00          PU_MAN          11000                       100
           30

        121 Adam                 Fripp                     AFRIPP
650.123.2234         10-APR-2005 00:00:00          ST_MAN           8200                       100
           50

        145 John                 Russell                   JRUSSEL
011.44.1344.429268   01-OCT-2004 00:00:00          SA_MAN          14000             .4        100
           80


11 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2318085533

----------------------------------------------------------------------------------
| Id  | Operation          | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |             |     1 |    69 |   164   (0)| 00:00:01 |
|*  1 |  FILTER            |             |       |       |            |          |
|   2 |   TABLE ACCESS FULL| EMPLOYEES   |   107 |  7383 |     3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| DEPARTMENTS |     2 |     6 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter( EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM "HR"."DEPARTMENTS"
              "DEPARTMENTS" WHERE "MANAGER_ID"=:B1))
   3 - filter("MANAGER_ID"=:B1)


Statistics
----------------------------------------------------------
          8  recursive calls
          0  db block gets
        652  consistent gets
          0  physical reads
          0  redo size
       2154  bytes sent via SQL*Net to client
        702  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         11  rows processed

oe@orclpdb1:orclcdb>

这两种执行计划的主要区别就是，不进行查询转换将会选用FILTER运算而不是NESTED LOOPS连接。

相关子查询的解嵌套转换

oe@orclpdb1:orclcdb> 
oe@orclpdb1:orclcdb> select outer.employee_id, outer.last_name,
  2                       outer.salary, outer.department_id
  3                  from employees outer
  4                  where outer.salary >
  5                     (select avg(inner.salary)
  6                        from employees inner
  7                     where inner.department_id = outer.department_id);

EMPLOYEE_ID LAST_NAME                     SALARY DEPARTMENT_ID
----------- ------------------------- ---------- -------------
        201 Hartstein                      13000            20
        205 Higgins                        12008           110
        100 King                           24000            90
        103 Hunold                          9000            60
        104 Ernst                           6000            60
        108 Greenberg                      12008           100
        109 Faviet                          9000           100
        114 Raphaely                       11000            30
        120 Weiss                           8000            50
        121 Fripp                           8200            50
        122 Kaufling                        7900            50
        123 Vollman                         6500            50
        124 Mourgos                         5800            50
        137 Ladwig                          3600            50
        141 Rajs                            3500            50
        145 Russell                        14000            80
        146 Partners                       13500            80
        147 Errazuriz                      12000            80
        148 Cambrault                      11000            80
        149 Zlotkey                        10500            80
        150 Tucker                         10000            80
        151 Bernstein                       9500            80
        152 Hall                            9000            80
        156 King                           10000            80
        157 Sully                           9500            80
        158 McEwen                          9000            80
        162 Vishney                        10500            80
        163 Greene                          9500            80
        168 Ozer                           11500            80
        169 Bloom                          10000            80
        170 Fox                             9600            80
        174 Abel                           11000            80
        184 Sarchand                        4200            50
        185 Bull                            4100            50
        188 Chung                           3800            50
        189 Dilly                           3600            50
        192 Bell                            4000            50
        193 Everett                         3900            50

38 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2167610409

----------------------------------------------------------------------------------
| Id  | Operation            | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |           |    17 |   765 |     7  (15)| 00:00:01 |
|*  1 |  HASH JOIN           |           |    17 |   765 |     7  (15)| 00:00:01 |
|   2 |   VIEW               | VW_SQ_1   |    11 |   286 |     4  (25)| 00:00:01 |
|   3 |    HASH GROUP BY     |           |    11 |    77 |     4  (25)| 00:00:01 |
|   4 |     TABLE ACCESS FULL| EMPLOYEES |   107 |   749 |     3   (0)| 00:00:01 |
|   5 |   TABLE ACCESS FULL  | EMPLOYEES |   107 |  2033 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("ITEM_1"="OUTER"."DEPARTMENT_ID")
       filter("OUTER"."SALARY">"AVG(INNER.SALARY)")

Note
-----
   - this is an adaptive plan


Statistics
----------------------------------------------------------
        120  recursive calls
          5  db block gets
         52  consistent gets
          0  physical reads
        900  redo size
       1979  bytes sent via SQL*Net to client
        940  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         38  rows processed

oe@orclpdb1:orclcdb>

这个例子中子查询是如何转换为内嵌视图，然后与其外的查询合并且相联结的。相关列变成了联结条件而子查询的剩余部分用来生成内嵌视图。重写后的该查询会像下面这样：

oe@orclpdb1:orclcdb> select outer.employee_id, outer.last_name, outer.salary, outer.department_id
  2                    from employees outer,
  3                           (select department_id,avg(salary) avg_sal
  4                              from employees
  5                            group by department_id) inner
  6                  where outer.department_id = inner.department_id;

EMPLOYEE_ID LAST_NAME                     SALARY DEPARTMENT_ID
----------- ------------------------- ---------- -------------
        108 Greenberg                      12008           100
        109 Faviet                          9000           100
        117 Tobias                          2800            30
        156 King                           10000            80
        163 Greene                          9500            80
        165 Lee                             6800            80
        168 Ozer                           11500            80
        193 Everett                         3900            50

106 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2244571506

-----------------------------------------------------------------------------------------
| Id  | Operation           | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                   |   106 |  3604 |     4  (25)| 00:00:01 |
|   1 |  HASH GROUP BY      |                   |   106 |  3604 |     4  (25)| 00:00:01 |
|   2 |   NESTED LOOPS SEMI |                   |   106 |  3604 |     3   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| EMPLOYEES         |   107 |  3317 |     3   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN | EMP_DEPARTMENT_IX |   106 |   318 |     0   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("OUTER"."DEPARTMENT_ID"="DEPARTMENT_ID")


Statistics
----------------------------------------------------------
         44  recursive calls
          0  db block gets
         23  consistent gets
          0  physical reads
          0  redo size
       4129  bytes sent via SQL*Net to client
        797  bytes received via SQL*Net from client
          9  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        106  rows processed

oe@orclpdb1:orclcdb>

子查询解嵌套的行为是由隐藏参数_unnest_subquery控制的。

联结消除

用来从查询中消除冗余的表。冗余表的定义是：仅在联结谓语中出现的列，并且与这张表的联结不会增加或减少结果集的条数。

Oracle消除冗余表的第一种情况就是在主键-外键约束。通过employees和departments表的主键-外键列进行联结。

oe@orclpdb1:orclcdb> 
oe@orclpdb1:orclcdb> select e.* from employees e, departments d where 
  2  e.department_id = d.department_id;

EMPLOYEE_ID FIRST_NAME           LAST_NAME                 EMAIL
----------- -------------------- ------------------------- -------------------------
PHONE_NUMBER         HIRE_DATE                     JOB_ID         SALARY COMMISSION_PCT MANAGER_ID
-------------------- ----------------------------- ---------- ---------- -------------- ----------
DEPARTMENT_ID
-------------
        198 Donald               OConnell                  DOCONNEL
650.507.9833         21-JUN-2007 00:00:00          SH_CLERK         2600                       124
           50

        199 Douglas              Grant                     DGRANT
650.507.9844         13-JAN-2008 00:00:00          SH_CLERK         2600                       124
           50

106 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1445457117

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |   106 |  7314 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMPLOYEES |   106 |  7314 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("E"."DEPARTMENT_ID" IS NOT NULL)


Statistics
----------------------------------------------------------
          8  recursive calls
          0  db block gets
         15  consistent gets
          0  physical reads
          0  redo size
      10176  bytes sent via SQL*Net to client
        742  bytes received via SQL*Net from client
          9  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        106  rows processed

oe@orclpdb1:orclcdb>

departments表的联结是如何完全消除的。因为departments表中没有任何一列出现在查询列表中。

排序消除

与联结消除类似，排除消除也会移除不必要的运算。不必要的运算就是排序。如果在SQL语句中包含ORDER BY子句，执行计划中就会有SORT ORDER BY运算。

ORDER BY 消除

oe@orclpdb1:orclcdb> select count(*) from 
  2  (
  3  select d.department_name
  4  from departments d
  5  where d.manager_id = 201
  6  order by d.department_name
  7  );

  COUNT(*)
----------
         1

1 row selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1270001327

----------------------------------------------------------------------------------
| Id  | Operation          | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |             |     1 |     3 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |             |     1 |     3 |            |          |
|*  2 |   TABLE ACCESS FULL| DEPARTMENTS |     1 |     3 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("D"."MANAGER_ID"=201)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          6  consistent gets
          0  physical reads
          0  redo size
        550  bytes sent via SQL*Net to client
        703  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

oe@orclpdb1:orclcdb> select /*+no_query_transformation */ count(*) from 
  2  (
  3  select d.department_name
  4  from departments d
  5  where d.manager_id = 201
  6  order by d.department_name
  7  );

  COUNT(*)
----------
         1

1 row selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1233823892

------------------------------------------------------------------------------------
| Id  | Operation            | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |     1 |       |     4  (25)| 00:00:01 |
|   1 |  SORT AGGREGATE      |             |     1 |       |            |          |
|   2 |   VIEW               |             |     1 |       |     4  (25)| 00:00:01 |
|   3 |    SORT ORDER BY     |             |     1 |    15 |     4  (25)| 00:00:01 |
|*  4 |     TABLE ACCESS FULL| DEPARTMENTS |     1 |    15 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - filter("D"."MANAGER_ID"=201)


Statistics
----------------------------------------------------------
          9  recursive calls
          0  db block gets
         12  consistent gets
          0  physical reads
          0  redo size
        550  bytes sent via SQL*Net to client
        512  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

oe@orclpdb1:orclcdb>

第二部分使用了NO_QUERY_TRANSFORMATION提示，让优化器不要对查询进行移除排序转换。在这种情况下，SORT ORDER BY 运算就会出现在执行计划中，而进行了转换的版本中就不会出现排序运算。

总之，之所以做这样的转换，就是为了简化执行计划，移除不必要的或冗余的工作，使得计划执行效率更高，性能更好。

使用物化视图进行查询重写

查询重写是一种发生在当某些查询或查询的一部分已经被保存为物化视图，转换器重写该查询以使用预先计算好的物化视图数据而不需要执行当前查询的转换。

物化视图与普通视图的区别在于查询已经执行并将结果集存入了一张表中。这样做的话哦出就是预先计算了查询的结果并且在特定查询执行的时候可以直接调取该结果。

查询转换器会将查询与可用的物化视图相匹配，然后重写该查询以直接从物化结果集中选取查询数据。

7.确定执行计划

执行计划就是Oracle访问查询所使用的对象并返回相应结果数据将会采用的一系列步骤。为了确定该计划，Oracle将收集并使用很多信息。 Oracle确定执行计划所用到的最关键的信息之一就是统计信息。

由于Oracle在确定执行计划过程中的一个主要目标就是为所解析出的SQL语句选择一系列响应实际最短的运算，因而统计信息越准确，Oracle也就越可能计算出最佳执行计划。

优化器是Oracle内核中代码路径，负责为查询确定最佳执行计划。

8.执行计划并取得数据行

列大小设置是如何影响逻辑读取的

oe@orclpdb1:orclcdb> 
oe@orclpdb1:orclcdb> 
oe@orclpdb1:orclcdb> set arraysize 15
oe@orclpdb1:orclcdb> 
oe@orclpdb1:orclcdb> set autotrace traceonly statistics
oe@orclpdb1:orclcdb> 
oe@orclpdb1:orclcdb> select * from order_items;

665 rows selected.


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         51  consistent gets
          0  physical reads
          0  redo size
      24490  bytes sent via SQL*Net to client
        871  bytes received via SQL*Net from client
         46  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        665  rows processed

oe@orclpdb1:orclcdb> 
oe@orclpdb1:orclcdb> 
oe@orclpdb1:orclcdb> set arraysize 45
oe@orclpdb1:orclcdb> /

665 rows selected.


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         21  consistent gets
          0  physical reads
          0  redo size
      18670  bytes sent via SQL*Net to client
        545  bytes received via SQL*Net from client
         16  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        665  rows processed

oe@orclpdb1:orclcdb>

即使对于这个小的仅有665行的结果集，增大列大小所带来的不同也是显而易见的。将设置值从15增大到45.逻辑读取的次数就从51次降到21次，网络返回次数从46次降到16次。这个改变与SQL语句本身没有任何关系，而与Oracle如何能访问并返回数据行相关。