每次做功能测试或是校验脚本，总是面临要收集性能参数和运行过程信息，次次开trace，有时候各种简写，比如autot其实是autotrace traceonly，一次性一起回顾下Autotrace的功能和演进过程。
一、功能特点
自动化执行计划与统计信息
- Autotrace 是 SQL*Plus 提供的核心工具，自动生成 SQL 的执行计划（包括操作步骤、数据访问路径、成本估算）并收集资源消耗统计（逻辑读/物理读、排序操作、网络传输量等），无需手动解析跟踪文件。
多模式输出控制
- SET AUTOTRACE ON：显示查询结果、执行计划及统计信息。
- SET AUTOTRACE TRACEONLY：仅显示执行计划与统计，屏蔽结果集（适用于大数据量查询）。
- SET AUTOTRACE ON EXPLAIN：仅输出执行计划（不实际执行 DQL）。
- SET AUTOTRACE ON STATISTICS：仅输出资源统计。
实际执行分析与 EXPLAIN PLAN 仅解析语句不同，Autotrace 实际执行 SQL 并记录运行时数据，更贴近真实性能。
二、技术原理
双会话机制 Autotrace 启动后，Oracle 在后台创建 两个会话 ：
- 主会话：执行原始 SQL 语句。
- 监控会话：通过 v$sesstat、v$statname 等动态视图采集执行计划和资源统计。
依赖对象
- PLAN_TABLE：存储执行计划的结构（需通过 utlxplan.sql 创建）。
- PLUSTRACE 角色：授权访问性能视图（由 plustrce.sql 创建）。
统计项解析
- 逻辑读 (consistent gets)：Buffer Cache 中读取的数据块数，过高可能预示索引缺失。
- 物理读 (physical reads)：磁盘 I/O 次数，反映缓存命中率。
三、版本演进与验证脚本
Oracle 9i 及更早版本（那么远这么近）
- 特性：需手动创建 PLAN_TABLE 和 PLUSTRACE 角色。
- 配置脚本：
-SYS 用户执行
@?/rdbms/admin/utlxplan.sql -创建 PLAN_TABLE
@?/sqlplus/admin/plustrce.sql -创建 PLUSTRACE 角色
GRANT PLUSTRACE TO AUTOTRACE_USER; -授权用户- 验证命令：
SET AUTOTRACE ON;
SELECT * FROM emp WHERE deptno = 10; -- 显示结果、计划、统计
SET AUTOTRACE OFF；- 特性：
- 默认内置 PLAN_TABLE（同义词指向 SYS.PLAN_TABLE$），无需手动创建。输出格式化优化（集成 dbms_xplan.display）。
- 验证命令：
SET AUTOTRACE TRACEONLY EXPLAIN; -- 仅生成执行计划（不执行 DQL）
SELECT * FROM employees; -- 显示优化后的执行计划表格Oracle 10g R2+～19c～23ai
- 特性：
- 执行计划展示增强（包含 谓词信息、执行成本）。
- 支持 Plan hash value 唯一标识执行计划。
- 验证命令：（23ai free需要新建PLAN_TABLE ，依赖这个脚本@?/rdbms/admin/utlxplan.sql）
SET AUTOTRACE ON；
--Autotrace Enabled
--Shows the execution plan as well as statistics of the statement.
--显示结果、计划、统计
SELECT * FROM HR.EMPLOYEES WHERE EMPLOYEE_ID= 10;
--
SYS@CDB$ROOT> SELECT * FROM HR.EMPLOYEES WHERE EMPLOYEE_ID= 10;
no rows selected
SQL_ID d1h8xauy8q9bn, child number 0
-------------------------------------
SELECT * FROM HR.EMPLOYEES WHERE EMPLOYEE_ID= 10
Plan hash value: 2316499954
---------------------------------------------------------------------
| Id | Operation | Name | E-Rows |
---------------------------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| EMPLOYEES | 1 |
|* 2 | INDEX RANGE SCAN | IDX_EMP_DEPT | 1 |
---------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPLOYEE_ID"=10)
Note
-----
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
Statistics
-----------------------------------------------------------
3 CCursor + sql area evicted
---
SET AUTOTRACE TRACEONLY EXPLAIN; -仅生成执行计划（不执行 DQL）
SELECT * FROM HR.EMPLOYEES; -显示优化后的执行计划表格
--
SYS@CDB$ROOT> SET AUTOTRACE OFF;
Autotrace Disabled
SYS@CDB$ROOT> SET AUTOTRACE TRACEONLY EXPLAIN;
Autotrace TraceOnly
Exhibits the performance statistics with silent query output
SYS@CDB$ROOT> SELECT * FROM HR.EMPLOYEES;
107 rows selected.
SQL_ID gdf7tm42kzut1, child number 0
-------------------------------------
SELECT * FROM HR.EMPLOYEES
Plan hash value: 1445457117
------------------------------------------------
| Id | Operation | Name | E-Rows |
------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | TABLE ACCESS FULL| EMPLOYEES | 107 |
------------------------------------------------
Note
-----
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
Statistics
-----------------------------------------------------------
--
SET AUTOTRACE ON STATISTICS;
UPDATE HR.EMPLOYEES SET SALARY = SALARY+1000 WHERE employee_id = 100;
-显示 DML 统计（redo size、db block gets）
--
SYS@CDB$ROOT> SET AUTOTRACE ON STATISTICS;
Autotrace Enabled
Displays the statistics only.
SYS@CDB$ROOT> UPDATE HR.EMPLOYEES SET SALARY = SALARY+1000 WHERE employee_id = 100;
1 row updated.
Statistics
-----------------------------------------------------------
1 CCursor + sql area evicted
1 CPU used by this session
1 CPU used when call started
2 DB time
1 HSC Heap Segment Block Changes
1 Heap Segment Array Updates
3 Requests to/from client
3 SQL*Net roundtrips to/from client
1 blocks cleaned out using minact
3 buffer is not pinned count
1 buffer is pinned count
939 bytes received via SQL*Net from client
64598 bytes sent via SQL*Net to client
7 calls to get snapshot scn: kcmgss
2 calls to kcmgcs
5 consistent gets
2 consistent gets examination
2 consistent gets examination (fastpath)
5 consistent gets from cache
3 consistent gets pin
3 consistent gets pin (fastpath)
3 db block changes
2 db block gets
2 db block gets from cache
2 db block gets from cache (fastpath)
1 deferred (CURRENT) block cleanout applications
1 enqueue releases
3 enqueue requests
3 execute count
3 index range scans
57344 logical read bytes from cache
1 no work - consistent read gets
10 non-idle wait count
3 opened cursors cumulative
1 opened cursors current
1 parse count (hard)
3 parse count (total)
1 parse time elapsed
3 recursive calls
1 redo entries
572 redo size
7 session logical reads
2 sorts (memory)
1620 sorts (rows)
1 sql area evicted
188 undo change vector size
4 user calls
Commit complete.
SYS@CDB$ROOT>四、关键配置表
版本 |
依赖对象 |
初始化脚本 |
输出增强 |
|
≤ Oracle 9i |
手动创建 PLAN_TABLE |
utlxplan.sql+plustrce.sql |
基础文本格式 |
|
Oracle 10g |
系统自动管理 |
仅需授权PLUSTRACE角色 |
结构化表格（dbms_xplan） |
|
≥ 10gR2 |
系统自动管理 |
同上 |
谓词信息、Plan hash value |
五、典型问题与注意
- SP2-0613 错误：PLAN_TABLE 缺失或权限不足。方案：以 SYS 身份运行 utlxplan.sql 并授予用户 PLUSTRACE 角色。
- 全表扫描优化：若 physical reads 过高，需检查索引或调整 db_file_multiblock_read_count 参数。
- 隐式转换问题：执行计划中出现全扫描时，检查是否因数据类型隐式转换导致索引失效。
六、使用体验
Autotrace 通过自动化执行跟踪与资源统计，成为 Oracle SQL 优化的核心工具。其演进体现了 Oracle 对易用性的持续改进：从早期手动配置到版本 10g 的自动化，再到执行计划输出的结构化与精细化。结合版本特性正确配置和使用 Autotrace，可高效定位 SQL 性能瓶颈，为索引优化、执行路径调整提供可靠依据。