• oracle之执行计划

    oracle之执行计划

    sql调优就是通过各种手段和方法是优化器选择最佳执行计划，以最小的资源消耗获取到想要的数据。

    3.1获取执行计划常用方法

    3.1.1 使用SUTOTRACE查看执行计划

    SQL> set autot
用法: SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]
*方括号内的参数可以省略
SET AUTOT ON：该命令会运行SQL并且显示运行结果，执行计划和统计信息；
set autot trace：该命令会运行sql，但不显示运行结果，会显示执行计划和统计信息；
set autot trace exp：运行改命令查询语句不执行，dml语句会执行，只显示执行计划；
set autot trace stat：该命令会运行sql，只显示统计信息。
set autot off：关闭autotrace

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    ①使用SET AUTOT ON 查看执行计划
    SQL> set autot on
SQL> select count(1) from user_info;

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

        10


执行计划
----------------------------------------------------------

Plan hash value: 2714159753

------------------------------------------------------------------------

| Id  | Operation          | Name      | Rows  | Cost (%CPU)| Time     |
------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |           |     1 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |           |     1 |            |          |

|   2 |   TABLE ACCESS FULL| USER_INFO |    10 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------


统计信息
----------------------------------------------------------
    211  recursive calls
      0  db block gets
     32  consistent gets
      6  physical reads
      0  redo size
    527  bytes sent via SQL*Net to client
    520  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
      5  sorts (memory)
      0  sorts (disk)
      1  rows processed
   
--使用SET AUTOT ON：会运行SQL并且显示运行结果，执行计划和统计信息，如果返回大量结果集，可以使用set autot trace查看执行计划，set autot trace不会输出sql执行结果；   

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    ②使用set autot trace查看执行计划
    SQL> select count(1) from user_info;


执行计划
----------------------------------------------------------

Plan hash value: 2714159753

------------------------------------------------------------------------

| Id  | Operation          | Name      | Rows  | Cost (%CPU)| Time     |
------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |           |     1 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |           |     1 |            |          |

|   2 |   TABLE ACCESS FULL| USER_INFO |    10 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------


统计信息
----------------------------------------------------------

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

--使用autotrace查看执行计划会带来一个额外的好处，当sql执行完毕后，会在执行计划的末尾显示sql在运行过程中耗费的一些统计信息；
--recursive calls表示递归调用的次数。一个sql第一次执行就会发生硬解析，在硬解析的时候，优化器会隐含地调用一些内部sql，因此当一个sql第一次执行，recursive calls会大于0（上面那个执行计划可以看出来），第二次执行的时候不需要递归调用，就会等于0；
*--如果sql语句中有自定义函数，recursive calls永远不会等于0，自定义函数被调用了多少次，recursive calls就会显示多少次；
--db block gets表示有多少块发生变化，一般情况下，只有dml语句才会导致块发生变化，所以查询语句中一般为0。如果有延迟块清除，或者sql语句中调用CLOB函数的时候，也会有可能大于0；
--consistent gets 表示逻辑读，单位是块。通常情况下逻辑读越小，性能就越好，逻辑读并不衡量sql执行快慢的唯一标准，还需要结合I/O等其他综合因素来共同判断。
--physical reads 表示从磁盘读取了多少数据块,如果表已经被缓存在 buffer cache 中,没有物理读,physical reads等于0;
--redo size 表示产生了多少字节的重做日志,一般情况下只有dml才会产生redo,查询语句一般不会产生redo,如果有延迟块清除,查询语句也会产生redo;
--bytes sent via SQL*Net to client 表示从数据库服务器发送了多少字节到客户端;
--bytes received via SQL*Net from client 表示从客户端发送了多少字节到服务端;
--SQL*Net roundtrips to/from client 表示客户端与数据库服务端交互的次数,可以通过设置arraysize减少交互次数;
--sorts (memory)和sorts (disk)分别表示内存排序和磁盘排序的次数;
--rows processed 表示sql一共返回多少行数据;(可以通过sql返回的行数判断整个sql应该走HASH连接还是走嵌套循环,如果返回行数很大,一般走hash连接;如果返回行数很少,一般走嵌套循环)


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48

    3.1.2 使用EXPLAIN PLAN FOR 查看执行计划

    SQL> explain plan for select ename,empno from emp where job = 'gashier';

已解释。

SQL> select * from table
  2  (dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

Plan hash value: 3956160932

--------------------------------------------------------------------------

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |      |     1 |    18 |     3   (0)| 00:00:01 |

|*  1 |  TABLE ACCESS FULL| EMP  |     1 |    18 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

   1 - filter("JOB"='gashier')

已选择13行。

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31

    查看高级(ADVANCED)执行计划

    SQL> select * from table(dbms_xplan.display(NULL,NULL,'advanced -projection'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

Plan hash value: 3956160932

--------------------------------------------------------------------------

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |      |     1 |    18 |     3   (0)| 00:00:01 |

|*  1 |  TABLE ACCESS FULL| EMP  |     1 |    18 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

   1 - SEL$1 / EMP@SEL$1

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      FULL(@"SEL$1" "EMP"@"SEL$1")
      OUTLINE_LEAF(@"SEL$1")
      ALL_ROWS

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

      DB_VERSION('11.2.0.1')
      OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA

  */

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

   1 - filter("JOB"='gashier')

已选择32行。
--高级执行计划比普通执行计划多了Query Block Name / Object Alias和Outline Data(当控制半连接/反连接执行计划的时候,就可能需要查看高级执行计划)
--Query Block Name表示查询块名称,Object Alias表示对象别名;Outline Data表示sql内部hint

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52

    3.1.3 查看带有 A-TIME 的执行计划

    alter session set statistics_level=all;或者在sql语句中添加hint:/*+ gather_plan_statistics */

    eg. select /*+ gather_plan_statistics full(emp) */ ename,empno from emp where job = ‘gashier’;

    SQL>  select /*+ gather_plan_statistics full(emp) */ ename,empno from emp where job = 'gashier';

ENAME           EMPNO
---------- ----------
东方             8888

SQL> select * from table(dbms_xplan.display_cursor(NULL,NULL,'allstats last'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  44crudbx35dhr, child number 0
-------------------------------------
 select /*+ gather_plan_statistics full(emp) */ ename,empno from emp
where job = 'gashier'

Plan hash value: 3956160932

--------------------------------------------------------------------------------
----

| Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffe

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
rs |

--------------------------------------------------------------------------------
----

|   0 | SELECT STATEMENT  |      |      1 |        |      1 |00:00:00.01 |
 8 |

|*  1 |  TABLE ACCESS FULL| EMP  |      1 |      1 |      1 |00:00:00.01 |
 8 |


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
----


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

   1 - filter("JOB"='gashier')


已选择19行。
--Starts 表示这个操作执行的次数;
--E-Rows 表示优化器估算的行数,就是普通执行计划中的ROWS;
--A-Rows 表示真实的行数;
--A-Time 表示累加的总时间;与普通执行计划不同的是,普通执行计划的Time是假的;而A-Time是真的;
--Buffers 表示累加的逻辑读;
--Reads 表示累加的物理读;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52
    • 53
    • 54
    • 55

    小结:演示了三种查看执行计划;其中使用AUTOTRACE 或者 EXPLAIN PLAN FOR获取的执行计划来自于PLAN_TABLE。PLAN_TABLE是一个会话级的临时表，里面的执行计划并不是sql真是的执行计划，他只是优化器估算出来的。真是的执行计划，应该是真正执行过的而不是估算出来的。sql执行过的执行计划存在于共享池中，具体在于数据字典V S Q L P L A N 中 ， 带 有 A − T i m e 的 执 行 计 划 来 自 于 V SQL_PLAN中，带有A-Time的执行计划来自于V SQLP​LAN中，带有A−Time的执行计划来自于VSQL_PLAN,是真实的执行计划,而通过AUTOTRACE、通过EXPLAN PLAN FOR获取的执行计划只有优化器估算获得的执行计划；

  • 相关阅读:
    yolox小计
    前端网页隐藏滚动条
    基于springboot实现医院急诊平台系统项目【项目源码】
    webpack配置自动压缩图片
    防止鱼叉式网络钓鱼的4个步骤
    基于MATLAB的迭代学习控制(Iterative Learning Control，ILC)算法的仿真与分析
    2316. 统计无向图中无法互相到达点对数（leetcode）并查集-------------------Java实现
    汇川伺服【选型目录】
    二十三种设计模式全面解析-适配器模式的妙用：异构数据库和不同版本API的完美兼容！
    Web大学生网页作业成品——城市环卫管理系统后台模板(HTML+CSS+JavaScript)
  • 原文地址：https://blog.csdn.net/chw65897/article/details/125511632