22.执行计划

  SQL执行缓慢有很多原因,有时候是数据库本身原因,比如latch争用,或者某些参数设置不合理。有时候是SQL写法有问题,有时候是缺乏索引,可能是因为通过统计信息过期或者没收集直方图,可可能是优化器本身并不完善或者优化器自身BUG而导致的性能问题。

  如果是数据库自身原因导致SQL缓慢,需要通过分析等待事件,做出相依的处理

  这里主要介绍SQL的优化,侧重于分析SQL写法,分析SQL的执行计划。SQL调优就是通过各种手段和方法使优化器选在最佳执行计划,以最小的资源消耗获取想要的数据。

1获取执行计划常用的方法(几种方法)

  1.1 通过set autot语法查看

SQL> set autot;
Usage: SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]
SQL> 

  set autot on:该命令运行SQL并显示运行结果,执行计划和统计信息

  set autot trace:该命令会运行SQL,但不显示运行结果,会显示执行计划和统计信息。

  set autot trace exp:该命令查询语句不执行,DML语句会执行,只显示执行计划。

  set auto trace stat:该命令会运行SQL,只显示统计信息

  set auto off:关闭autotrace

复制代码
SQL> set autot trace;
SQL> select * from test;

62637 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 62637 |  8257K|   394   (1)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| TEST | 62637 |  8257K|   394   (1)| 00:00:01 |
--------------------------------------------------------------------------
Statistics
----------------------------------------------------------
        144  recursive calls
          0  db block gets
       5768  consistent gets
        304  physical reads
          0  redo size
    9668543  bytes sent via SQL*Net to client
      46305  bytes received via SQL*Net from client
       4177  SQL*Net roundtrips to/from client
         11  sorts (memory)
          0  sorts (disk)
      62637  rows processed
复制代码
  • recursive calls 表示递归调用的次数。一个 SQL 第一次执行就会发生硬解析,在硬解析的 时候,优化器会隐含地调用一些内部 SQL,因此当一个 SQL 第一次执行,recursive calls 会大 于 0;第二次执行的时候不需要递归调用,recursive calls 会等于 0。如果 SQL 语句中有自定义函数,recursive calls 永远不会等于 0,自定义函数被调用了多 少次,recursive calls 就会显示为多少次
  • db block gets 表示有多少个块发生变化,一般情况下,只有 DML 语句才会导致块发生变 化,所以查询语句中 db block gets 一般为 0。如果有延迟块清除,或者 SQL 语句中调用了返回 CLOB 的函数,db block gets 也有可能会大于 0,不要觉得奇怪。
  • consistent gets 表示逻辑读,单位是块。在进行 SQL 优化的时候,我们应该想方设法减少 逻辑读个数。通常情况下逻辑读越小,性能也就越好。需要注意的是,逻辑读并不是衡量 SQL 执行快慢的唯一标准,需要结合 I/O 等其他综合因素共同判断
  • 怎么通过逻辑读判断一个 SQL 还存在较大优化空间呢?如果 SQL 的逻辑读远远大于 SQL 语句中所有表的段大小之和(假设所有表都走全表扫描,表关联方式为 HASH JOIN), 那么该 SQL 就存在较大优化空间。
  • physical reads 表示从磁盘读取了多少个数据块,如果表已经被缓存在 buffer cache 中,没 有物理读,physical reads 等于 0。
  • redo size 表示产生了多少字节的重做日志,一般情况下只有 DML 语句才会产生 redo,查 询语句一般情况下不会产生 redo,所以这里 redo size 为 0。如果有延迟块清除,查询语句也会 产生 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 返回的行数判断整个 SQL 应该是走 HASH 连接还是走嵌套循环。 如果 rows processed 很大,一般走 HASH 连接;如果 rows processed 很小,一般走嵌套循环

  1.2.使用EXPLAIN PLAIN FOR 查看执行计划 

复制代码
SQL> explain plan for select * from test;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 62637 |  8257K|   394   (1)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| TEST | 62637 |  8257K|   394   (1)| 00:00:01 |
--------------------------------------------------------------------------
8 rows selected.
SQL> 
复制代码

  1.3 查看带有A-TIME的执行计划(真实的执行计划) 

alter session set statistics_level=all;   或者在SQL语句中添加hint:/*+ gather_plan_statistics */
select count(*) from test.test;
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
复制代码
SQL_ID  br5bhw1m4892h, child number 0
-------------------------------------
select count(*) from test.test
Plan hash value: 1950795681
-------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |      1 |00:00:00.01 |    1415 |
|   1 |  SORT AGGREGATE    |      |      1 |      1 |      1 |00:00:00.01 |    1415 |
|   2 |   TABLE ACCESS FULL| TEST |      1 |  62637 |  62637 |00:00:00.01 |    1415 |
-------------------------------------------------------------------------------------
 
复制代码
  • Starts 表示这个操作执行的次数
  • E-Rows 表示优化器估算的行数,就是普通执行计划中的 Rows。
  • A-Rows 表示真实的行数
  • A-Time 表示累加的总时间。与普通执行计划不同的是,普通执行计划中的 Time 是假的, 而 A-Time 是真实的。
  • Buffers 表示累加的逻辑读。
  • Reads 表示累加的物理读 

  上面介绍了 3 种方法查看执行计划。使用 AUTOTRACE 或者 EXPLAIN PLAN FOR 获取 的执行计划来自于 PLAN_TABLE。PLAN_TABLE 是一个会话级的临时表,里面的执行计划并 不是 SQL 真实的执行计划,它只是优化器估算出来的。真实的执行计划不应该是估算的,应 该是真正执行过的。 SQL 执行过的执行计划存在于共享池中, 具体存在于数据字典 V$SQL_PLAN 中,带有 A-Time 的执行计划来自于 V$SQL_PLAN,是真实的执行计划,而通 过 AUTOTRACE、通过 EXPLAIN PLAN FOR 获取的执行计划只是优化器估算获得的执行计 划。有读者会有疑问,使用 AUTOTRACE 查看执行计划,SQL 是真正运行过的,怎么得到的执行计划不是真实的呢?原因在于 AUTOTRACE 获取的执行计划来自于 PLAN_TABLE,而非 来自于共享池中的 V$SQL_PLAN。

  1.4 查看正在执行的SQL的执行计划

    select * from table(dbms_xplan.display_cursor('sql_id',child_number));  # 这里带入sql_id和child_number值即可。

    在一个会话中执行一个sql :select count(*) from a,b where a.owner=b.owner;

    在两一个会话中执行sql语句:  

  select a.sid, a.event, a.sql_id, a.sql_child_number, b.sql_text from v$session a, v$sql b where a.sql_address = b.address and a.sql_hash_value = b.hash_value and a.sql_child_number = b.child_number order by 1 desc;

  直接结果如下:

 

  带入得到:select * from table(dbms_xplan.display_cursor('czr9jwxv0xra6',0)); 

复制代码
SQL_ID  czr9jwxv0xra6, child number 0
-------------------------------------
select count(*) from a,b where a.owner=b.owner
 
Plan hash value: 1397777030
 
----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |       |       |  1354 (100)|          |
|   1 |  SORT AGGREGATE     |      |     1 |    10 |            |          |
|*  2 |   HASH JOIN         |      |   211M|  2014M|  1354  (42)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| A    | 72669 |   354K|   394   (1)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| B    | 72670 |   354K|   394   (1)| 00:00:01 |
----------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("A"."OWNER"="B"."OWNER")
复制代码

2.通过查看执行计划建立索引

  

  执行计划分为两部分,Plan hash value和predicate information这部分主要是表的访问路劲和表的连接方式。这里可以看一下Predicate information下面的部分信息,这部分就是谓词过滤信息。

  id这列有的前面有"*"号,这表示发生了谓词过滤,或者发生了HASH连接,或者是走了索引。id=1这个地方发生了hash连接(e表的deptno列和d表的deptno列进行hash连接的),id=3前面的"*"号,这里表示emp有谓词过滤,

  table access full 前面没有"*"号 -->如果表很小,那么不用在意,如果表很大,要询问一下开发是否忘记写了过滤条件,比如一个10G的表,没有过滤条件,那么它就会成为整个SQL性能瓶颈,这时需要查看该SQL语句中该表访问了多少列,如果访问的列的不多,可以把这些列组合起来,建立一个组合索引,索引的大小可能只有1GB,这是就可以利用 index fast full scan代替table access full。在访问列不多的情况,索引的(segment size)肯定比表大小(segment size)小。

  table access full前面有"*"号 --> 如果表很小,不用理会,如果表很大,可以查看一下这个表有多少行,然后在通过谓词过滤条件,查看返回多少行,如果返回的行数在表总总行数的5%以内,那么就可以在过滤列上建立索引。如果已经存在索引,但是没有走索引,这时就需要检查统计信息,特别是直方图,如果统计信息收集过了,那么可以通过hint强制走索引,如果有多个谓词过滤条件,需要建立组合索引并且将要选择性高的列放在前面,选择性低的列放在后面。

  table access by index rowid前面有"*"号, --> 表示回表再过滤,回表再过滤说明数据没有在索引中过滤干净。当table access by index rowid有"*"号时,可以将"*"号下面的过滤条件包含在索引中,这样就可以减少回表次数,提高查询性能。

示例:

这时创建一个联合索引,提高一下性能

从上面不能看出,consistent gets这个指标从332降到了3,说明查询效率提高了。

 

posted on   太白金星有点烦  阅读(63)  评论(0编辑  收藏  举报

相关博文:
阅读排行:
· [翻译] 为什么 Tracebit 用 C# 开发
· Deepseek官网太卡,教你白嫖阿里云的Deepseek-R1满血版
· 2分钟学会 DeepSeek API,竟然比官方更好用!
· .NET 使用 DeepSeek R1 开发智能 AI 客户端
· 刚刚!百度搜索“换脑”引爆AI圈,正式接入DeepSeek R1满血版
历史上的今天:
2021-09-03 3.Innodb体系架构之后台进程
2021-09-03 48.Mysql中的checkpoint机制

导航

< 2025年2月 >
26 27 28 29 30 31 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 1
2 3 4 5 6 7 8
点击右上角即可分享
微信分享提示