58-Oracle Autotrace功能和演进

每次做功能测试或是校验脚本，总是面临要收集性能参数和运行过程信息，次次开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= 10Plan 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 levelStatistics
-----------------------------------------------------------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 TraceOnlyExhibits 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.EMPLOYEESPlan 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 levelStatistics
-----------------------------------------------------------
--
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 evicted1  CPU used by this session1  CPU used when call started2  DB time1  HSC Heap Segment Block Changes1  Heap Segment Array Updates3  Requests to/from client3  SQL*Net roundtrips to/from client1  blocks cleaned out using minact3  buffer is not pinned count1  buffer is pinned count939  bytes received via SQL*Net from client64598  bytes sent via SQL*Net to client7  calls to get snapshot scn: kcmgss2  calls to kcmgcs5  consistent gets2  consistent gets examination2  consistent gets examination (fastpath)5  consistent gets from cache3  consistent gets pin3  consistent gets pin (fastpath)3  db block changes2  db block gets2  db block gets from cache2  db block gets from cache (fastpath)1  deferred (CURRENT) block cleanout applications1  enqueue releases3  enqueue requests3  execute count3  index range scans57344  logical read bytes from cache1  no work - consistent read gets10  non-idle wait count3  opened cursors cumulative1  opened cursors current1  parse count (hard)3  parse count (total)1  parse time elapsed3  recursive calls1  redo entries572  redo size7  session logical reads2  sorts (memory)1620  sorts (rows)1  sql area evicted188  undo change vector size4  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 性能瓶颈，为索引优化、执行路径调整提供可靠依据。