查看执行计划mysql 查看执行计划的方法

转载

mob6454cc64e36b 2023-12-16 22:30:38

文章标签 查看执行计划mysql oracle SQL 执行计划 sed 文章分类 MySQL 数据库

一、查看执行计划的方式

1.1、设置autotrace

1.2、使用第三方工具

1.3、EXPLAIN PLAN FOR

二、清除SGA缓存

三、分析执行计划

3.1、创建测试表

3.2、查看执行计划

3.2.1、执行计划

3.2.2、谓词说明：

3.2.3、统计信息

四、部分信息解释

4.1、SQL*Net roundtrips to/from client的计算方式

4.2、consistent gets

今天谈一谈Oracle查看执行计划的方式，以及怎样看执行计划。

一、查看执行计划的方式

1.1、设置autotrace

autotrace命令如下

序号	命令	解释
1	SET AUTOTRACE OFF	此为默认值，即关闭Autotrace
2	SET AUTOTRACE ON EXPLAIN	只显示执行计划
3	SET AUTOTRACE ON STATISTICS	只显示执行的统计信息
4	SET AUTOTRACE ON	包含2,3两项内容
5	SET AUTOTRACE TRACEONLY	与ON相似，但不显示语句的执行结果

1.2、使用第三方工具

比如PL/SQL Develop的explain窗口

查看执行计划mysql 查看执行计划的方法_oracle

1.3、EXPLAIN PLAN FOR

据说在执行的SQL前加上 EXPLAIN PLAN FOR 可以查看执行计划，还没搞明白，后续补上

举例：

SQL> EXPLAIN PLAN FOR SELECT * FROM EMP;
已解释。
SQL> SELECT plan_table_output FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE'));
或者：
SQL> select * from table(dbms_xplan.display);

二、清除SGA缓存

因为在sql执行时，sql的执行计划、从磁盘读取的数据库等信息会在SGA的某些缓存中保存一段时间，为了查看语句第一次执行的效果，就需要清空这些缓存。

ALTER SYSTEM FLUSH SHARED_POOL;
 ALTER SYSTEM FLUSH BUFFER_CACHE;
 ALTER SYSTEM FLUSH GLOBAL CONTEXT;

三、分析执行计划

3.1、创建测试表

新建两张表cust_info、cst_tran（单纯用来测试，没有实际意义）
CREATE TABLE CUST_INFO
 (CST_NO NUMBER,
 CST_NAME VARCHAR2(50),
 AGE SMALLINT);CREATE TABLE CST_TRAN
 (
 CST_NO NUMBER,
 TRAN_DATE VARCHAR2(8),
 TRAN_AMT NUMBER(19,3)
 );插入一些数据，CUST_INFO表1万，CST_TRAN表100万。
INSERT INTO CUST_INFO
 SELECT 100000+LEVEL,
        'test'||LEVEL,
        ROUND(DBMS_RANDOM.VALUE(1,100))
 FROM DUAL
 CONNECT BY LEVEL<=10000; INSERT INTO CST_TRAN
 WITH AA AS
 (SELECT LEVEL FROM DUAL CONNECT BY LEVEL<=100)
 SELECT T.CST_NO,
        TO_CHAR(SYSDATE - DBMS_RANDOM.VALUE(1,1000),'yyyymmdd'),
        ROUND(DBMS_RANDOM.VALUE(1,999999999),3)
 FROM CUST_INFO T
 INNER JOIN AA
 ON 1=1;

3.2、查看执行计划

查看这两个表关联的执行计划
SQL> SELECT T.CST_NO, T.CST_NAME, G.TRAN_DATE, G.TRAN_AMT FROM CUST_INFO T INNER JOIN CST_TRAN G ON G.CST_NO = T.CST_NO;
1000000 rows selected.
 Execution Plan
 ----------------------------------------------------------
 Plan hash value: 2290587575--------------------------------------------------------------------------------
 | Id  | Operation                        | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
 --------------------------------------------------------------------------------
 |   0 | SELECT STATEMENT   |                     |   996K|    68M|  1079     (2)| 00:00:13 |
 |*  1 |  HASH JOIN                   |                     |   996K|    68M|  1079     (2)| 00:00:13 |
 |   2 |   TABLE ACCESS FULL | CUST_INFO | 10000 |   390K|    11     (0)| 00:00:01 |
 |   3 |   TABLE ACCESS FULL | CST_TRAN  |  1065K|    32M|  1064     (1)| 00:00:13 |
 --------------------------------------------------------------------------------Predicate Information (identified by operation id):
 ---------------------------------------------------   1 - access("G"."CST_NO"="T"."CST_NO")
Note
 -----
    - dynamic sampling used for this statement (level=2) Statistics
 ----------------------------------------------------------
     561  recursive calls
       0  db block gets
       70483  consistent gets
        4389  physical reads
       0  redo size
    45078003  bytes sent via SQL*Net to client
      733845  bytes received via SQL*Net from client
       66668  SQL*Net roundtrips to/from client
      10  sorts (memory)
       0  sorts (disk)
     1000000  rows processed3.2.1、执行计划
首先我们看一下第一部分
--------------------------------------------------------------------------------
 | Id  | Operation                        | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
 --------------------------------------------------------------------------------
 |   0 | SELECT STATEMENT   |                     |   996K|    68M|  1079     (2)| 00:00:13 |
 |*  1 |  HASH JOIN                   |                     |   996K|    68M|  1079     (2)| 00:00:13 |
 |   2 |   TABLE ACCESS FULL | CUST_INFO | 10000 |   390K|    11     (0)| 00:00:01 |
 |   3 |   TABLE ACCESS FULL | CST_TRAN  |  1065K|    32M|  1064     (1)| 00:00:13 |
 --------------------------------------------------------------------------------

执行计划中字段解释：

ID: 一个序号，但不是执行的先后顺序。执行的先后根据缩进来判断。
Operation：当前操作的内容。
Rows：当前操作的Cardinality，Oracle估计当前操作的返回结果集。
Cost（CPU）：Oracle 计算出来的一个数值（代价），用于说明SQL执行的代价。
Time：Oracle 估计当前操作的时间。

说明：

1、Operation

记录每一步的操作，按照缩进的程度判断执行的先后顺序。

在OLAP数据库中，HASH JOIN连接较多，特别是返回数据集大的时候，基本都是HASH JOIN。

2、Rows

rows值表示CBO预期从一个行源（row source）返回的记录数，这个行源可能是一个表，一个索引，也可能是一个子查询。在Oracle 9i中的执行计划中，Cardinality缩写成Card。在10g中，Card值被rows替换。

rows值对于CBO做出正确的执行计划来说至关重要。

在多表关联查询或者SQL中有子查询时，每个关联表或子查询的rows的值对主查询的影响都非常大，甚至可以说，CBO就是依赖于各个关联表或者子查询rows值计算出最后的执行计划。

对于多表查询，CBO使用每个关联表返回的行数（rows）决定用什么样的访问方式来做表关联（如Nested loops Join 或 hash Join）

3、Cost（CPU）和Time是执行计划的重要参考值

3.2.2、谓词说明：

Predicate Information (identified by operation id):
 ---------------------------------------------------   1 - access("G"."CST_NO"="T"."CST_NO")
Note
 -----
    - dynamic sampling used for this statement (level=2)

Access: 表示这个谓词条件的值将会影响数据的访问路劲（表还是索引）。

Filter：表示谓词条件的值不会影响数据的访问路劲，只起过滤的作用。（此例中没有）

注意：在谓词中主要注意access，要考虑谓词的条件，使用的访问路径是否正确。

3.2.3、统计信息

Statistics
 ----------------------------------------------------------
     561  recursive calls
       0  db block gets
       70483  consistent gets
        4389  physical reads
       0  redo size
    45078003  bytes sent via SQL*Net to client
      733845  bytes received via SQL*Net from client
       66668  SQL*Net roundtrips to/from client
      10  sorts (memory)
       0  sorts (disk)
     1000000  rows processed

参数说明：

recursive calls :递归调用。一般原因：dictionary cache未命中；动态存储扩展；PL/SQL语句
db block gets :bufer中读取的block数量，用于insert,update,delete,selectfor update
consistent gets :这里是一致读次数（一个block可能会被读多次），bufer中读取的用于查询（除掉select forupdate）的block数量。
physical reads :从磁盘上读取的block数量,敬请关注每周五晚免费网络公开课。
redo size :bytes，写到redo logs的数据量
bytes sent via SQL*Net to client ：发送给客户端的字节数
bytes received via SQL*Net from client ：从客户端接收的字节数
SQL*Net roundtrips to/from client ：与客户端的交互次数（个人理解接收一条SQL语句，执行结果分多次发送给客户端，如有问题请指正）
sorts (memory) :内存排序次数
sorts (disk) :磁盘排序次数；与sort_area_size有关
rows processed ：执行完SQL后返回结果集的行数

四、部分信息解释

4.1、SQL*Net roundtrips to/from client的计算方式

这个指标的计算方式和一个参数息息相关，arraysize。

arraysize是什么呢？

arraysize定义了一次返回到客户端的行数，取值范围【1-5000】，默认15。

使用命令在数据库中查看arraysize的值。

show arraysize

还可以修改这个值

set arraysize 5000;

明白了arraysize这个参数就可以计算SQL*Net roundtrips to/from client的值了。上例中，返回客户端结果集的行数是1000000，默认arraysize值是15，1000000/15向上取整等于66667。

为啥要向上取整？

举个栗子，如果有10个苹果，一个只能拿3个，几次可以拿完，3次可以拿9个，还剩1个，所以还需要再拿一次，共4次。

统计分析中的值是66668，为什么我们计算的值是66667？

就要看这个指标本身了，再粘贴一次：SQL*Net roundtrips to/from client 重点看from，意思是我们还要接受一次客户端发来的SQL语句，因此是：66667+1，本问题纯属个人臆断，无真凭实据，受限于本人的知识水平，如有误，请指出。

将arraysize的值修改为5000后，再观察SQL*Net roundtrips to/from client的变化，结果为201。

前面提到 arraysize的取值范围是【1-5000】，我们可以试一下改为不在这个区间的值，比如改为0，结果报错了

SQL> set arraysize 0;
SP2-0267: arraysize option 0 out of range (1 through 5000)

4.2、consistent gets

译为中文就是：一致性读，好抽象的一个指标，啥叫一致性读，心中无数羊驼驼在大海中狂奔。

官网对consistent gets 的解释：

consistent gets：Number of times a consistent read wasrequested for a block.

通常我们执行SQL查询时涉及的每一block都是Consistent Read, 只是有些CR(Consistent Read)需要使用undo 来进行构造, 大部分CR(Consistent Read)并不涉及到undo block的读.

还有就是每次读这个block都是一次CR(可能每个block上有多个数据row), 也就是如果某个block被读了10次, 系统会记录10个Consistent Read.

接来下测试下， consistent gets是从哪来的，需要使用有sysdba权限的用户，因为oradebug工具需要sysdba权限。

使用10046对同一条数据跟踪两次，注意观察 consistent gets的不同

为了不影响测试结果，首先清空缓存
SQL> ALTER SYSTEM FLUSH SHARED_POOL;
System altered.
SQL> ALTER SYSTEM FLUSH BUFFER_CACHE;
System altered.
SQL> ALTER SYSTEM FLUSH GLOBAL CONTEXT;
System altered.
第一次执行 
SQL> set tim on timing on
 00:42:30 SQL> set autot trace stat
 00:42:36 SQL> oradebug setmypid
 Statement processed.
 00:42:42 SQL> alter session set tracefile_identifier='chf1';Session altered.
Elapsed: 00:00:00.01
 00:42:50 SQL> oradebug event 10046 trace name context forever,level 12;
 Statement processed.
 00:42:57 SQL> SELECT T.CST_NO, T.CST_NAME, G.TRAN_DATE, G.TRAN_AMT FROM CHF.CUST_INFO T INNER JOIN CHF.CST_TRAN G ON G.CST_NO = T.CST_NO;1000000 rows selected.
Elapsed: 00:00:22.71
Statistics
 ----------------------------------------------------------
     547  recursive calls
       0  db block gets
       70368  consistent gets
        3898  physical reads
       0  redo size
    45078003  bytes sent via SQL*Net to client
      733845  bytes received via SQL*Net from client
       66668  SQL*Net roundtrips to/from client
      10  sorts (memory)
       0  sorts (disk)
     1000000  rows processed00:44:24 SQL> oradebug event 10046 trace name context off;
 Statement processed.
 00:45:54 SQL> oradebug tracefile_name
 /u01/app/oracle/diag/rdbms/orcl/bpas/trace/bpas_ora_7715_chf1.trc第二次执行 
00:46:04 SQL> alter session set tracefile_identifier='chf2';
Session altered.
Elapsed: 00:00:00.00
 00:46:35 SQL> oradebug event 10046 trace name context forever,level 12;
 Statement processed.
 00:46:43 SQL> SELECT T.CST_NO, T.CST_NAME, G.TRAN_DATE, G.TRAN_AMT FROM CHF.CUST_INFO T INNER JOIN CHF.CST_TRAN G ON G.CST_NO = T.CST_NO;1000000 rows selected.
Elapsed: 00:00:21.62
Statistics
 ----------------------------------------------------------
       0  recursive calls
       0  db block gets
       70301  consistent gets
        3850  physical reads
       0  redo size
    45078003  bytes sent via SQL*Net to client
      733845  bytes received via SQL*Net from client
       66668  SQL*Net roundtrips to/from client
       0  sorts (memory)
       0  sorts (disk)
     1000000  rows processed00:47:11 SQL> oradebug event 10046 trace name context off;
 Statement processed.
 00:49:03 SQL> oradebug tracefile_name
 /u01/app/oracle/diag/rdbms/orcl/bpas/trace/bpas_ora_7715_chf2.trc

通过对比两次执行，发现consistent gets、physical reads、sorts (memory)都有变化，这是因为SGA中已经缓存了部分数据块。

再对比下我们刚才生产的两个跟踪日志，为方便查看，先将其格式转换以下
[oracle@localhost ~]$ tkprof /u01/app/oracle/diag/rdbms/orcl/bpas/trace/bpas_ora_7715_chf1.trc /u01/chf1.trc
TKPROF: Release 11.2.0.1.0 - Development on Wed Dec 8 00:53:37 2021
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
 [oracle@localhost ~]$ tkprof /u01/app/oracle/diag/rdbms/orcl/bpas/trace/bpas_ora_7715_chf2.trc /u01/chf2.trc
TKPROF: Release 11.2.0.1.0 - Development on Wed Dec 8 00:53:48 2021
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
打开 /u01/chf1.trc，下面贴出部分重要信息
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call     count       cpu    elapsed       disk      query    current        rows
 ------- ------  -------- ---------- ---------- ---------- ----------  ----------
 Parse        1      0.03       0.03          8         67          0           0
 Execute      1      0.00       0.00          0          0          0           0
 Fetch    66668      0.76       3.24       3890      70301          0     1000000
 ------- ------  -------- ---------- ---------- ---------- ----------  ----------
 total    66670      0.79       3.28       3898      70368          0     1000000Misses in library cache during parse: 1
Elapsed times include waiting on following events:
   Event waited on                             Times   Max. Wait  Total Waited
   ----------------------------------------   Waited  ----------  ------------
   SQL*Net message to client                   66670        0.01          0.14
   SQL*Net message from client                 66670       64.54         79.11
   db file sequential read                         5        0.00          0.00
   Disk file operations I/O                        1        0.00          0.00
   db file scattered read                          5        0.00          0.00
   asynch descriptor resize                        4        0.00          0.00
   direct path read                               69        0.00          0.02 OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call     count       cpu    elapsed       disk      query    current        rows
 ------- ------  -------- ---------- ---------- ---------- ----------  ----------
 Parse       12      0.00       0.00          0          0          0           0
 Execute     24      0.01       0.01          0          0          0           0
 Fetch       30      0.00       0.00          8         67          0          18
 ------- ------  -------- ---------- ---------- ---------- ----------  ----------
 total       66      0.02       0.02          8         67          0          18打开 /u01/chf2.trc，下面贴出部分重要信息
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call     count       cpu    elapsed       disk      query    current        rows
 ------- ------  -------- ---------- ---------- ---------- ----------  ----------
 Parse        1      0.00       0.00          0          0          0           0
 Execute      1      0.00       0.00          0          0          0           0
 Fetch    66668      1.57       3.73       3850      70301          0     1000000
 ------- ------  -------- ---------- ---------- ---------- ----------  ----------
 total    66670      1.57       3.73       3850      70301          0     1000000Misses in library cache during parse: 0
Elapsed times include waiting on following events:
   Event waited on                             Times   Max. Wait  Total Waited
   ----------------------------------------   Waited  ----------  ------------
   SQL*Net message to client                   66670        0.00          0.10
   SQL*Net message from client                 66670        6.83         19.93
   asynch descriptor resize                        4        0.00          0.00
   direct path read                               69        0.00          0.01 OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call     count       cpu    elapsed       disk      query    current        rows
 ------- ------  -------- ---------- ---------- ---------- ----------  ----------
 Parse        0      0.00       0.00          0          0          0           0
 Execute      0      0.00       0.00          0          0          0           0
 Fetch        0      0.00       0.00          0          0          0           0
 ------- ------  -------- ---------- ---------- ---------- ----------  ----------
 total        0      0.00       0.00          0          0          0           0

比较发现，第一次执行解析SQL语句，生产执行计划时，consistent gets发生67次，执行SQL语句时发生70301。第一次执行解析SQL语句，生产执行计划时，因已经有缓存，所以consistent gets发生0次，执行SQL语句时发生70301。

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。

上一篇：android 按压特效安卓系统叫ar 特效

下一篇：大数据分析与挖掘数据的维数是什么意思大数据维度建模技术

提问和评论都可以，用心的回复会被更多人看到评论

发布评论

相关文章

官方博客	全部文章	热门标签	班级博客
了解我们	网站地图	意见反馈

鸿蒙开发者社区	51CTO学堂
51CTO	软考资讯