出品丨TeacherWhat
题图：Hands@Photo by Toa Heftiba on Unsplash
关键字：Oracle、SQL、调优、诊断、手把手数据库入门、SQL*PLUS、Autotrace
正文约4000字，建议阅读时间5分钟
目录结构：
1. AUTOTRACE使用方法
2. AUTOTRACE的使用例
3. AUTOTRACE报告含义解析
4. EXPLAIN PLAN使用例
5. 注意事项
6. 本文要点&思考
本公众号文章仅代表个人观点，与任何公司无关。
其他系列文章：
AUTOTRACE
在SQL*PLUS上，可以通过AUTOTRACE来进行SQL调优和查看执行计划以及执行时候的性能统计信息。
AUTOTRACE使用方法
AUTOTRACE具体使用方法方法如下：
1.要使用AUTOTRACE命令，也需要首先创建执行计划表PLAN_TABLE（一般数据库中默认已经建好）。
@$ORACLE_HOME/rdbms/admin/utlxplan.sql
2.赋予执行用户相关权限
CONNECT AS SYSDBA
@$ORACLE_HOME/SQLPLUS/ADMIN/PLUSTRCE.SQL
GRANT PLUSTRACE TO <执行用户>;
3.AUTOTRACE的使用方法
SQL> set autotrace help
Usage: SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]
AUTOTRACE命令详细语法
※关于AUTOTRACE命令的更详细语法可以参考在线文档：
▲SQL*Plus® User’s Guide and Reference
Controlling the Autotrace Report
https://docs.oracle.com/en/database/oracle/oracle-database/19/sqpug/tuning-SQL-Plus.html#GUID-C8FBE008-B2D6-4049-A095-0747E5328A50
| 序号 | 命令 | 解释 |
|---|---|---|
| 1 | SET AUTOTRACE ON | 打开Autotrace，输出SQL查询结果和执行计划，以及性能统计 |
| 2 | SET AUTOTRACE ON EXPLAIN | 打开Autotrace，输出SQL查询结果和执行计划，但不输出性能统计 |
| 3 | SETAUTOTRACE TRACEONLY | 打开Autotrace，输出执行计划和性能统计，但不输出SQL查询结果 |
| 4 | SET AUTOTRACE TRACEONLY STATISTICS | 打开Autotrace，仅输出性能统计，但不输出SQL查询结果和执行计划 |
| 5 | SET AUTOTRACE OFF | 此为默认值，即关闭Autotrace |
AUTOTRACE的使用例
下面通过AUTOTRACE查看执行计划的例子。
例1(11.2.0.4)：set autotrace on
SQL> set autotrace on
SQL> select * from dual;
D
-
X
Execution Plan
----------------------------------------------------------
Plan hash value: 272002086
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| DUAL | 1 | 2 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
538 bytes sent via SQL*Net to client
551 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
例2：set autotrace traceonly explain
SQL> set autotrace traceonly explain
SQL> select * from dual;
Execution Plan
----------------------------------------------------------
Plan hash value: 272002086
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| DUAL | 1 | 2 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------
AUTOTRACE报告含义解析
以如下的输出报告为例，介绍一下各部分含义。
输出例：
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> set autotrace on
SQL> select * from dual;
DU -----①
--
X
执行计划
----------------------------------------------------------
Plan hash value: 272002086 -----②
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -----③
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| DUAL | 1 | 2 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------
统计信息 -----④
----------------------------------------------------------
1 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
554 bytes sent via SQL*Net to client
380 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
报告各部分含义
①：SQL语句的执行结果
②：SQL执行计划的哈希值，用于标识不同的执行计划
③：SQL的执行计划内容
| 项目 | 解释 |
|---|---|
| Id | 各步骤的序号，注:非执行顺序 |
| Operation | 操作内容 |
| Name | 操作对象 |
| Rows | 操作行数 |
| Bytes | 操作bytes大小（预估） |
| Cost (%CPU) | 当前操作成本估算 |
| Time | 当前操作需要时间估算 |
④：SQL的执行性能统计
| 序号 | 数据库统计名称 | 说明 |
|---|---|---|
| 1 | recursive calls | 递归调用SQL的数量；执行SQL语句时生成的内部SQL语句，称为递归调用。 |
| 2 | db block gets | 以当前模式读取（CURRENT）的数据块数。即读取最新的块内容，通常在数据修改时发生。 |
| 3 | consistent gets | 一致性读的数据块数。可能包含数据块（data）的读取和回滚数据块（undo）的读取。 |
| 4 | physical reads | 物理读的数据块数。包括磁盘 "physical reads direct"和从磁盘读入缓存的数据块. |
| 5 | redo size | 生成的redo的大小（单位：bytes） |
| 6 | bytes sent via SQL*Net to client | 前台进程发送到客户端的字节总数。 |
| 7 | bytes received via SQL*Net from client | 从客户端收到的字节总数。 |
| 8 | SQL*Net roundtrips to/from client | 发送到客户端和从客户端接收的Oracle Net消息总数 |
| 9 | sorts (memory) | 不需要写入磁盘，在内存中完成的排序操作数 |
| 10 | sorts (disk) | 至少需要写入一次磁盘的排序操作数 |
| 11 | rows processed | 操作中处理的行数 |
注意点
当指定参数TRACEONLY时：
SQL语句会真正执行；
不会显示执行结果；
只会显示统计信息和执行计划当指定参数TRACEONLY EXPLAIN时：
SQL语句不会真正执行；
不会显示执行结果；
只会显示执行计划,不会显示统计信息。
相关问题
SP2-0613: PLAN_TABLE错误
如果执行计划表PLAN_TABLE不存在的话，执行set autotrace可能会发生SP2-0613: PLAN_TABLE错误。
解决：用执行set autotrace的用户执行下面操作。
$ORACLE_HOME/rdbms/admin/utlxplan.sql
本文要点
本文介绍了在SQL*PLUS上查看执行计划以及执行时候的性能统计信息方法，AUTOTRACE命令。
思考
AUTOTRACE命令和EXPLAIN PLAN命令有什么相似之处和不同之处？
——End——
专注于技术不限于技术！
用碎片化的时间，一点一滴地提高数据库技术和个人能力。
欢迎关注！
获取SQL执行计划最基础的方法是啥？
SQL调优和诊断从哪入手？
2020年11月 数据库流行度排名
你知道Oracle数据库除了SGA和PGA，还有MGA么？
读了这些数据库经典书，你已经超过了90%的Oracle技术者（文末彩蛋）
手把手教你在Windows 10安装Oracle 19c（详细图文附踩坑指南）
