完全掌握Oracle进阶学习之查看执行计划

星敏小哥_3768

星敏小哥_3768

2022-02-24

3249人浏览

转载

本篇文章给大家带来了关于oracle的相关知识,其中主要介绍了查看执行计划的相关问题,希望对大家有帮助。

推荐教程:《Oracle视频教程》

今天谈一谈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窗口

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<h2 id="3.2%E3%80%81%E6%9F%A5%E7%9C%8B%E6%89%A7%E8%A1%8C%E8%AE%A1%E5%88%92">3.2、查看执行计划</h2><p>查看这两个表关联的执行计划</p><pre class="brush:sql;toolbar:false">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 processed

3.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做出正确的执行计划来说至关重要。 如果CBO获得的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是什么呢?

请查阅大牛博文:Oracle arraysize 和 fetch size 参数 与 性能优化 说明

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

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

QuantOracle
QuantOracle

63个确定性量化金融计算器 + 10个通过MCP的复合工作流。期权定价、Greeks、奇异衍生品、风险指标、投资组合优化……

下载

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.

如果想深入学习,请参考大佬博文:Oracle 有关 Consistent gets 的测试 -- cnDBA.cn_中国DBA社区

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

oradebug工具介绍:oracle实用工具:oradebug

使用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 processed

00: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 processed

00: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     1000000

Misses 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     1000000
Misses 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。

推荐教程:《Oracle视频教程》

相关文章

PHP速学视频免费教程(入门到精通)
PHP速学视频免费教程(入门到精通)

PHP怎么学习?PHP怎么入门?PHP在哪学?PHP怎么学才快?不用担心,这里为大家提供了PHP速学教程(入门到精通),有需要的小伙伴保存下载就能学习啦!

下载

相关标签:

oracle

本站声明:本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系admin@php.cn

相关专题

更多
GoFrame框架Web开发与路由中间件
GoFrame框架Web开发与路由中间件

本专题聚焦GoFrame框架的Web开发,拆解路由注册与匹配规则、请求参数获取、参数校验与中间件执行顺序,说明统一响应、JWT鉴权、文件上传与接口文档生成的落地方式,并补充优雅重启与测试实践。

2026.11.17

0

15

GoFrame框架安装与gf命令行工具
GoFrame框架安装与gf命令行工具

本专题围绕GoFrame框架的安装与工具链配置展开,讲清Go版本要求、gf命令行的安装方式、代理镜像与依赖准备,说明项目初始化、热编译、交叉编译打包的做法,并整理安装阶段的常见报错与验证步骤。

2026.11.17

0

15

GoFrame框架零基础入门
GoFrame框架零基础入门

本专题整理GoFrame框架的入门知识,从模块化设计理念、环境准备到跑通第一个Web服务,讲清目录分层、配置加载、日志与中间件的用法,并对比Gin、Go-Zero的差异,帮助Go开发者建立整体认知。

2026.11.17

0

15

SQLAlchemy 一对多关系怎么建立
SQLAlchemy 一对多关系怎么建立

SQLAlchemy 一对多关系怎么建立?本专题整理父子表外键、relationship 双向映射、关联查询、级联删除和加载优化等相关文章,帮助你理解并实现常见的 ORM 关系建模。

2026.11.17

0

10

SQLAlchemy 如何增删改查
SQLAlchemy 如何增删改查

SQLAlchemy 如何增删改查?本专题整理 ORM 新增记录、条件查询、更新和删除、Session 事务提交与批量处理等实用文章,帮助你掌握 Python 数据库操作流程。

2026.11.17

0

11

SQLAlchemy ORM 模型怎么写
SQLAlchemy ORM 模型怎么写

SQLAlchemy ORM 模型怎么写?本专题整理模型类定义、Mapped 字段映射、主键与约束、表结构创建和常见报错的相关文章,帮助你从基础配置到实际建模逐步掌握 SQLAlchemy。

2026.11.17

0

16

Go-Zero API开发使用教程
Go-Zero API开发使用教程

本专题聚焦Go-Zero的API开发,拆解.api描述文件的语法结构、路由与分组定义、参数绑定与校验规则,说明统一响应体、JWT鉴权、跨域与接口文档生成的落地方式,并给出网关聚合与接口测试的实践建议。

2026.11.12

160

15

Go-Zero安装与goctl环境配置教程
Go-Zero安装与goctl环境配置教程

本专题围绕Go-Zero的安装与工具链配置展开,讲清Go版本要求、goctl安装方式、代理镜像、protoc依赖与环境变量设置,说明多版本共存、升级回退与CI流水线安装的做法,并整理安装阶段的常见报错与验证步骤。

2026.11.12

120

15

Go-Zero零基础入门教程
Go-Zero零基础入门教程

本专题整理Go-Zero的入门知识,从框架定位、环境准备到跑通第一个API服务,讲清目录分层、配置加载、日志与中间件的基本用法,并对比Gin、Kratos的差异,帮助Go开发者建立整体认知,判断它是否适合当前项目。

2026.11.12

100

15

热门下载

更多
网站特效
/
网站源码
/
网站素材
/
前端模板

精品课程

更多
相关推荐
/
热门推荐
/
最新课程