在 Oracle 数据库的管理与开发中，了解 SQL 语句的执行计划对于优化数据库性能至关重要。SET AUTOTRACE 是 SQL*Plus 提供的一项强大功能，它能够自动跟踪并为 SQL 语句生成执行计划，同时提供与语句处理相关的统计信息。本文将深入探讨 SET AUTOTRACE 技术，帮助读者更好地利用它来优化 SQL 语句。
一、AUTOTRACE 功能概述
AUTOTRACE 是 SQL*Plus 的一项特性，其主要优势在于无需手动设置跟踪文件格式，便能自动为 SQL 语句展示执行计划。这一功能在分析 SQL 语句的性能瓶颈、优化查询效率等方面发挥着关键作用。通过 AUTOTRACE，开发人员和数据库管理员可以直观地了解 SQL 语句在数据库中的执行路径以及资源消耗情况，从而有针对性地进行优化。
二、AUTOTRACE 的启用与基本语法
任何通过 SQL*PLUS 连接的会话都可以使用 Autotrace，但在使用之前，需要进行一些必要的设置。首先，要确保相关用户具有使用 Autotrace 的权限。例如，给用户授予 PLUSTRACE 权限：
SQL>grant plustrace to hr;同时，被授权用户的 PLAN_TABLE 表必须存在。在完成上述设置后，就可以启用 Autotrace 功能了。启用的基本语法如下：
SET AUTOT(RACE) {OFF | ON | TRACE(ONLY)} (EXP(LAIN)) (STAT(ISTICS))其中，各参数的含义如下：
- SET AUTOTRACE OFF：这是默认模式，在此模式下，只显示 SQL 执行结果，不生成 AUTOTRACE 报告，即不会展示执行计划和相关统计信息。它用于关闭 Autotrace 功能，当不需要查看执行计划和统计数据时可使用此设置。
- SET AUTOTRACE ON：开启 Autotrace 功能，此时会显示 SQL 结果、执行路径以及资源消耗等信息。它会运行 SQL 语句，并全面展示执行计划和执行统计信息，适用于希望全面了解 SQL 语句执行情况的场景。
- SET AUTOTRACE TRACEONLY：与SET AUTOTRACE ON类似，但不显示 SQL 语句的执行结果，仅显示 AUTOTRACE 信息，即执行路径和资源消耗。当只关注执行计划和统计数据，而不关心查询返回的具体数据时，可选择此设置，这样可以避免大量数据输出对查看执行计划的干扰。
- SET AUTOTRACE TRACEONLY EXPLAINI：简写为SET AUTOT TRACE EXP。此设置下，Select 语句不会实际执行，但 DML 语句会被执行，且只显示 SQL 执行路径，即执行计划。对于一些查询数据量较大、执行时间较长的 Select 语句，使用此设置可以在不实际执行查询的情况下获取执行计划，节省时间。同时，对于 DML 语句，也能在执行前了解其执行计划，评估可能的影响。
- SET AUTOTRACE TRACEONLY STATISTICS：简写为SET AUTOT TRACE STAT。该设置会运行 SQL 语句，但只显示资源消耗的统计信息，不显示执行计划和 SQL 执行结果。当重点关注 SQL 语句执行过程中的资源消耗情况，如磁盘 I/O、内存使用等，而不需要查看执行计划和查询结果时，可使用此设置。
设置 AUTOTRACE 开关的相关命令存在简写惯例，具体如下：
关键字简写对应关系
- AUTOTRACE 可简写为 AUTOT。
- TRACEONLY 可简写为 TRACE。
- EXPLAIN 可简写为 EXP。
- STATISTICS 可简写为 STAT。
命令等价简写示例
- SET AUTOTRACE ON 等价于 SET AUTOT ON。
- SET AUTOTRACE OFF 等价于 SET AUTOT OFF。
- SET AUTOTRACE TRACEONLY 等价于 SET AUTOT TRACE。
- SET AUTOTRACE TRACEONLY EXPLAIN 等价于 SET AUTOT TRACE EXP。
- SET AUTOTRACE TRACEONLY STATISTICS 等价于 SET AUTOT TRACE STAT。
三、AUTOTRACE示例
3.1 set autot on
SQL> on
set autot on
col OBJECT_NAME for a20
set timing on
select object_id,object_name,owner from test1 where rownum<10;
OBJECT_ID OBJECT_NAME OWNER
---------- -------------------- ------------------------------
20 ICOL$ SYS
46 I_USER1 SYS
28 CON$ SYS
15 UNDO$ SYS
29 C_COBJ# SYS
3 I_OBJ# SYS
25 PROXY_ROLE_DATA$ SYS
41 I_IND1 SYS
54 I_CDEF2 SYS
9 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1430905904
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 864 | 2 (0)| 00:00:01 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | TABLE ACCESS FULL| TEST1 | 91074 | 8538K| 2 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<10)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
857 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9 rows processed可以看到，执行SET AUTOT ON再执行目标SQL，除了显示执行计划之外，还会显示该SQL的资源消耗及执行结果。这种方式只适用输出结果较少的情况。
3.2 set autot trace
SQL>
set autot trace
select object_id,object_name,owner from test1 where rownum<10;
9 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1430905904
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 864 | 2 (0)| 00:00:01 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | TABLE ACCESS FULL| TEST1 | 91074 | 8538K| 2 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<10)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
857 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9 rows processed可以看到，执行SET AUTOT ON再执行目标SQL，除了执行结果未显示，其它显示和set autot on一样。这种方式只适用输出结果较多的情况。
3.3 set autot trace exp
SQL>
set autot trace exp
select object_id,object_name,owner from test1 where rownum<10;
Execution Plan
----------------------------------------------------------
Plan hash value: 1430905904
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 864 | 2 (0)| 00:00:01 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | TABLE ACCESS FULL| TEST1 | 91074 | 8538K| 2 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<10)
Note
-----
- dynamic sampling used for this statement (level=2)
执行 set autot trace exp后再执行目前SQL，只显示该SQL的执行计划，并且和explan plan输出一样。
SQL>
set autot off
select object_id,object_name,owner from test1 where rownum<10;
OBJECT_ID OBJECT_NAME OWNER
---------- -------------------- ------------------------------
20 ICOL$ SYS
46 I_USER1 SYS
28 CON$ SYS
15 UNDO$ SYS
29 C_COBJ# SYS
3 I_OBJ# SYS
25 PROXY_ROLE_DATA$ SYS
41 I_IND1 SYS
54 I_CDEF2 SYS
9 rows selected.3.4 set autot trace stat
SQL>
set autot trace stat
select object_id,object_name,owner from test1 where rownum<10;
9 rows selected.
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
857 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9 rows processed执行set autot trace stat后再执行目前SQL，只显示该SQL的资源消耗情况。
3.5 set autot off
SQL>
set autot off
select object_id,object_name,owner from test1 where rownum<10;
OBJECT_ID OBJECT_NAME OWNER
---------- -------------------- ------------------------------
20 ICOL$ SYS
46 I_USER1 SYS
28 CON$ SYS
15 UNDO$ SYS
29 C_COBJ# SYS
3 I_OBJ# SYS
25 PROXY_ROLE_DATA$ SYS
41 I_IND1 SYS
54 I_CDEF2 SYS
9 rows selected.通过set autot off，关闭执行计划及其相关资源信息输出。
四、AUTOTRACE 资源信息详解
当使用 AUTOTRACE 获取执行计划和统计信息时，会得到一系列详细的数据，这些数据能够帮助我们深入分析 SQL 语句的执行情况。
分析上面的资源信息
0 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
857 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9 rows processed以下是一些常见的输出信息及其含义：
- recursive calls：递归调用次数为0，表示该SQL语句执行过程中没有发生递归调用。正常情况下，如果SQL语句首次执行且涉及硬解析，会有递归调用；缓存命中后再次执行，若无其他特殊操作（如调用自定义函数），该值应为0。本次为0说明语句执行较顺畅，没有额外的递归操作干扰。
- db block gets：从缓冲区缓存读取且发生变化的块数量为0，意味着在执行该SQL语句时，没有数据块在缓冲区中被修改，一般查询语句此值多为0，若在DML语句中出现非0值，代表有数据块更新操作。
- consistent gets：从缓冲区缓存读取的用于一致性读的undo数据块数量为5，说明在执行过程中，为保证数据一致性，从缓冲区读取了5个undo数据块。该值较高时可能意味着事务并发高或查询涉及大量历史数据版本，本次5个属于相对较低的数量，表明一致性读操作开销较小 。
- physical reads：物理读次数为0，即没有从磁盘读取数据块，所有数据都从内存缓冲区获取，这是比较理想的情况，说明数据在内存中命中率高，I/O开销小，SQL执行效率高。
- redo size：产生的重做日志大小为0，对于查询语句来说属于正常现象，因为查询一般不会产生重做日志；若为DML语句，该值应大于0，用于记录数据修改操作，以便在需要时进行数据恢复和回滚。
- bytes sent via SQL*Net to client：通过SQL*Net发送给客户端的字节数为857，体现了查询结果返回给客户端的数据量大小。数值越大，说明返回的数据越多，网络传输开销也越大，若性能受限时，可考虑减少不必要的返回数据。
- bytes received via SQL*Net from client：从客户端接收的字节数为523，代表客户端发送给数据库的请求数据量，包括SQL语句及相关参数等。该值可辅助分析客户端与数据库交互时的请求规模。
- SQL*Net roundtrips to/from client：客户端与数据库服务端之间的交互次数为2，交互次数越少越好，可通过调整`arraysize`等参数，减少频繁交互带来的网络开销。本次2次交互次数较低，说明数据传输相对高效。
- sorts (memory)和sorts (disk)：内存和磁盘排序次数均为0，说明该SQL语句执行过程中没有涉及排序操作，或者数据量小到无需排序就自然有序；若出现排序且磁盘排序次数非0，则需优化排序逻辑或增加内存资源。
- rows processed：处理的行数为9，即SQL语句执行过程中涉及处理的记录行数，可结合具体业务需求，判断该行数是否合理，若实际期望行数远大于此，可能存在查询条件过滤异常等问题 。
对它们的理解，有助于观察SQL的资源消耗。
五、使用 AUTOTRACE 的注意事项
- 权限问题：如前所述，使用 AUTOTRACE 功能需要用户具有相应的权限，即 PLUSTRACE 权限，并且相关用户的 PLAN_TABLE 表必须存在。如果权限不足或表不存在，在使用 AUTOTRACE 时可能会出现报错。
- 实际执行问题：当使用某些设置（如SET AUTOTRACE ON、SET AUTOTRACE TRACEONLY等）时，SQL 语句会实际执行。对于一些查询数据量巨大、执行时间很长的 SQL 语句，可能需要谨慎使用，以免耗费过多的时间和资源。在这种情况下，可以先使用SET AUTOTRACE TRACEONLY EXPLAN等不实际执行查询的设置来获取执行计划，评估执行风险。
- 统计信息准确性：AUTOTRACE 获取的执行计划和统计信息是基于当前数据库对象的统计信息生成的。如果数据库统计信息过时或不准确，可能会导致执行计划和统计信息与实际情况存在偏差。因此，定期更新数据库统计信息对于正确使用 AUTOTRACE 进行性能分析非常重要。
- 与其他工具的结合使用：虽然 AUTOTRACE 是一个强大的工具，但在实际应用中，它可能无法满足所有的性能分析需求。可以将 AUTOTRACE 与其他工具（如 Oracle SQL Developer 中的执行计划分析功能、DBMS_XPLAN 包等）结合使用，以获取更全面、准确的性能分析结果。例如，DBMS_XPLAN 包中的 DISPLAY_CURSOR 和 DISPLAY_AWR 函数可以获取真实的执行计划（而 AUTOTRACE 的执行计划可能依赖于统计信息估算），在某些情况下，结合这些函数可以更准确地评估 SQL 语句的性能。
六、总结
SET AUTOTRACE 技术是 Oracle 数据库中一项非常实用的功能，它为开发人员和数据库管理员提供了便捷的方式来获取 SQL 语句的执行计划和资源消耗统计信息。通过深入理解 AUTOTRACE 的功能、语法、输出信息以及应用场景，并注意使用过程中的各项事项，能够有效地利用该技术来优化 SQL 语句，提高数据库系统的性能。在实际的数据库开发和管理工作中，应根据具体需求灵活运用 AUTOTRACE，结合其他性能分析工具，不断提升数据库的性能和稳定性。