22.执行计划

标签：00 22 sql 计划 SQL 执行 select

　　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，说明查询效率提高了。

标签：00,22,sql,计划,SQL,执行,select
From： https://www.cnblogs.com/zmc60/p/17675300.html

相关文章

赞助商

阅读排行