Oracle性能诊断与SQL优化：从9i到19c的技术演进与实践

在数据库运维中，性能稳定性与高效运行是核心诉求。Oracle作为企业级数据库的标杆，其性能诊断与SQL优化工具链随版本迭代持续升级。本文基于经典优化方法论，结合11g、19c版本的核心特性变化，系统梳理Oracle性能诊断的核心工具、优化方法及版本差异，为运维人员提供适配新一代数据库的实践指南。

一、AutoTrace功能的演进与实践

AutoTrace作为Oracle内置的SQL优化辅助工具，核心价值在于快速获取执行计划与统计信息，是SQL优化的入门级利器。其功能从9i的手动配置，逐步演进为高版本的自动化、精细化输出。

1. 基础原理与早期实践（9i/10g）

• 9i需手动创建PLAN_TABLE、PLUSTRACE角色并授权，步骤繁琐；10g简化配置，默认创建PLAN_TABLE$及公用同义词，无需手动初始化。
• 核心选项包括OFF/ON EXPLAIN/ON STATISTICS/TRACEONLY，支持输出执行计划与逻辑读、物理读等关键统计项。
• 内部通过双会话机制实现：一个执行SQL，一个记录执行计划与统计信息。

2. 版本迭代变化

（1）11g的优化

• 延续10g的自动初始化机制，新增对并行查询执行计划的完整展示，支持分区表的分区访问细节输出。
• 统计信息输出新增"consistent gets (optimized)"等细化指标，更精准反映SQL执行效率。
• 兼容之前版本的所有选项，保持使用习惯连续性。

（2）19c的重大升级

• 执行计划输出集成优化器自适应决策信息，标注"Adaptive Plan"标识及绑定变量自适应调整记录。
• 支持FORMAT='ADVANCED'选项，输出包含SQL Plan Directive、直方图使用情况等深度优化信息。
• 统计信息新增"buffer gets per execution"等聚合指标，简化性能瓶颈判断。
• 无需手动授权PLUSTRACE角色，普通用户默认拥有基础使用权限（高级功能需DBA授权）。

3. 19c环境下的实践步骤

-- 启用AutoTrace（包含执行计划与完整统计信息）
SET AUTOTRACE ON FORMAT ADVANCED;
-- 执行目标SQL
SELECT * FROM sys_user WHERE user_code = 'zhangyong' OR user_code IN (SELECT grp_code FROM sys_grp WHERE sys_grp.user_code = 'zhangyong');
-- 关闭AutoTrace
SET AUTOTRACE OFF;

二、SQL执行计划获取方法的版本迭代

获取准确的SQL执行计划是性能诊断的核心步骤。Oracle从9i的脚本查询，逐步演进为19c的自动化、多维度获取体系。

1. 基础方法（9i/10g）

• 核心方式：AutoTrace、EXPLAIN PLAN FOR+DBMS_XPLAN、V$SQL_PLAN查询。
• 10g新增DBMS_XPLAN.DISPLAY_AWR函数，支持从AWR仓库获取历史执行计划。
• 依赖手动编写脚本（如getplan_by_hashvalue.sh）获取缓存SQL的执行计划。

2. 版本迭代变化

（1）11g的关键增强

• DBMS_XPLAN功能扩展：新增DISPLAY_PLAN、DISPLAY_SQL_PLAN_BASELINE函数，支持SQL计划基线管理。
• V$SQL_PLAN新增"SQL_PLAN_BASELINE"字段，关联执行计划与基线信息。
• 支持通过SQL_ID直接获取执行计划，无需依赖HASH_VALUE，查询更精准。

（2）19c的全面升级

• 实时执行计划：通过V$SQL_PLAN_MONITOR视图或SQL Developer的"实时SQL监控"功能，动态查看SQL执行过程中的计划变化。
• 历史执行计划：DBMS_XPLAN.DISPLAY_AWR_HISTORY支持查询指定时间范围的执行计划演进，追踪计划退化原因。
• 自动执行计划捕获：开启Automatic SQL Tuning后，系统自动捕获高负载SQL的执行计划并存储至AWR。
• 支持JSON格式输出执行计划，便于自动化工具解析：

  SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(sql_id=>'1m225m1612xvg', format=>'JSON'));
  

3. 19c环境下的多场景执行计划获取

应用场景	实现方法
实时运行SQL	SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(sql_id=>'xxx', format=>'ALL'));
历史SQL（AWR存储）	SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR(sql_id=>'xxx'));
正在执行的长SQL	SELECT * FROM V$SQL_PLAN_MONITOR WHERE sql_id='xxx' ORDER BY plan_line_id;
SQL计划基线关联	SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE(plan_name=>'xxx'));

三、问题SQL捕获与CPU过度消耗优化

生产环境中，CPU过度消耗多由低效SQL导致。Oracle从早期的手动跟踪，演进为高版本的自动化识别与优化。

1. 基础诊断流程（9i/10g）

• 通过vmstat、top工具定位CPU瓶颈，结合v$session_wait查询等待事件（如db file scattered read）。
• 利用自定义脚本（getsqlbysid.sql）通过SID捕获问题SQL。
• 核心优化手段：创建索引、改写SQL（如OR转UNION ALL）、消除全表扫描。

2. 版本迭代变化

（1）11g的智能增强

• 新增Automatic Workload Repository (AWR) 自动捕获Top SQL，通过AWR报告直接定位高CPU消耗SQL。
• SQL Tuning Advisor可自动分析问题SQL，生成索引创建、SQL改写建议。
• v$session_wait新增"P3TEXT"字段，更清晰标注等待事件细节（如全表扫描的表名）。

（2）19c的自动化升级

• Automatic Indexing：系统自动识别重复全表扫描的SQL，创建临时索引并验证性能提升，经确认后可转为永久索引。
• Real-Time SQL Monitoring：通过SQL Developer或EM Express实时监控CPU消耗Top SQL，直观展示执行计划中的瓶颈步骤（如SORT AGGREGATE耗时占比）。
• 新增V$SQL_CPU_USAGE视图，按SQL_ID统计CPU消耗占比，快速定位元凶。
• 自适应SQL优化：系统自动改写低效SQL（如OR转UNION ALL、IN转EXISTS），无需人工干预。

3. 19c环境下CPU过度消耗优化实践

-- 1. 定位CPU消耗Top SQL
SELECT sql_id, sql_text, cpu_time/1000000 AS cpu_sec, executions 
FROM v$sql ORDER BY cpu_time DESC FETCH FIRST 10 ROWS ONLY;

-- 2. 查看SQL执行计划与优化建议
SELECT dbms_sqltune.report_sql_tuning_task(
  task_name => dbms_sqltune.create_tuning_task(sql_id=>'xxx')
) AS tuning_report FROM dual;

-- 3. 启用自动索引建议（可选）
ALTER SYSTEM SET optimizer_auto_indexing = ON;

-- 4. 手动优化示例（创建索引）
CREATE INDEX idx_hs_info_numcatalogguid ON hs_info(NUMCATALOGGUID);

四、SQL_TRACE/10046事件的跟踪与应用

SQL_TRACE/10046事件是Oracle深度诊断的核心工具，能捕获SQL执行的完整生命周期信息，其功能随版本迭代持续增强。

1. 基础原理与早期实践（9i/10g）

• 10046事件支持4个级别（1/4/8/12），分别对应基础跟踪、绑定变量、等待事件、全量信息。
• 需通过tkprof工具格式化跟踪文件，手动分析执行计划与等待事件。
• 全局启用需修改spfile，存在性能开销风险。

2. 版本迭代变化

（1）11g的功能增强

• 支持会话级动态启用10046事件，无需重启数据库：

  ALTER SESSION SET events '10046 trace name context forever, level 12';
  

• tkprof工具新增"AGGREGATE=YES"选项，聚合重复SQL的执行信息，简化分析。
• 跟踪文件新增绑定变量窥视信息，便于定位绑定变量导致的计划退化。

（2）19c的全面优化

• 新增10046事件级别16：包含SQL Plan Directive、自适应优化决策等深度信息。
• 跟踪文件支持JSON格式输出，便于自动化工具解析：

  ALTER SESSION SET events '10046 trace name context forever, level 16, format=json';
  

• 无需tkprof格式化，可通过DBMS_TRACE包直接查询跟踪信息：

  SELECT * FROM TABLE(dbms_trace.get_trace_data(trace_id=>'xxx'));
  

• 跟踪文件自动压缩存储，减少磁盘占用，支持按SQL_ID快速筛选跟踪记录。

3. 19c环境下10046事件诊断实践

-- 1. 启用10046事件（全量信息+JSON格式）
ALTER SESSION SET events '10046 trace name context forever, level 16, format=json';

-- 2. 执行目标操作（如慢查询、存储过程）
EXEC cmop_servdetail_d_eygle(sysdate);

-- 3. 关闭跟踪
ALTER SESSION SET events '10046 trace name context off';

-- 4. 查看跟踪文件路径
SELECT value||'/'||instance_name||'_ora_'||spid||'.trc' 
FROM v$parameter WHERE name='user_dump_dest'
CROSS JOIN v$instance
CROSS JOIN v$process WHERE addr=(SELECT paddr FROM v$session WHERE sid=sys_context('userenv','sid'));

五、物化视图在翻页查询中的优化升级

物化视图通过预计算与存储结果集，显著提升复杂查询性能，其功能在11g、19c中针对翻页查询场景进行了重点优化。

1. 基础优化逻辑（9i/10g）

• 针对翻页查询的全表扫描与排序瓶颈，创建物化视图预存储关联结果集。
• 支持定时刷新（COMPLETE/FAST），通过查询重写自动使用物化视图。
• 需手动创建降序索引，消除排序操作。

2. 版本迭代变化

（1）11g的关键改进

• 增量刷新优化：支持复杂关联查询的快速刷新（如多表JOIN+ORDER BY），减少刷新开销。
• 查询重写增强：自动识别翻页查询的ROWNUM过滤条件，优先使用物化视图数据。
• 支持创建基于函数的物化视图（如SUBSTR(a.vc2mid,0,4)），适配翻页查询中的字段截取需求。

（2）19c的智能化升级

• 自动刷新策略：支持基于数据变化量的自适应刷新（如数据变化超过10%时触发快速刷新）。
• 实时物化视图：对于翻页查询高频场景，可创建实时刷新的物化视图（REFRESH ON COMMIT），确保数据一致性的同时无需等待定时刷新。
• 集成分区功能：物化视图支持分区存储，翻页查询仅扫描目标分区，进一步降低IO开销。
• 自动索引：创建物化视图时，系统自动推荐并创建配套索引（如排序字段、查询条件字段），无需人工设计。

3. 19c环境下翻页查询物化视图优化实践

-- 创建物化视图（预存储翻页查询的关联结果集）
CREATE MATERIALIZED VIEW mv_hw_user4love
BUILD IMMEDIATE
REFRESH FAST ON COMMIT  -- 实时刷新
ENABLE QUERY REWRITE
PARTITION BY RANGE (numUserType)  -- 分区存储
AS
SELECT u.numuserid, u.vc2username, u.numusertype, u.numrank, u.numgender
FROM hw_user u, hw_userprofile p, hw_userscore s
WHERE u.numUserId = p.numUserId AND u.numUserId = s.numUserId AND s.numExperience > 100;

-- 系统自动创建排序索引（19c新特性）
-- 手动验证查询重写是否生效
EXPLAIN PLAN FOR
SELECT * FROM (SELECT t.*, ROWNUM i FROM (
  SELECT numuserid, vc2username FROM hw_user4love 
  WHERE numintention <> 99 ORDER BY numusertype DESC
) t WHERE ROWNUM <= 40) WHERE i > 20;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(format=>'ADVANCED'));

六、版本差异总结与实践建议

功能模块	9i/10g特性	11g增强点	19c核心升级
AutoTrace	手动配置，基础输出	并行查询支持，细化统计项	自适应决策输出，JSON格式，简化授权
执行计划获取	脚本查询，AWR基础支持	计划基线，DISPLAY_CURSOR增强	实时监控，自动捕获，多格式输出
问题SQL捕获	手动跟踪，vmstat+自定义脚本	AWR Top SQL，SQL Tuning Advisor	自动索引，实时监控，CPU消耗视图
SQL_TRACE/10046	4个级别，tkprof格式化	动态启用，绑定变量窥视	级别16，JSON格式，DBMS_TRACE直接查询
物化视图	定时刷新，手动索引	快速刷新，查询重写优化	实时刷新，分区支持，自动索引

实践建议

1. 11g环境：优先使用AWR报告定位问题SQL，结合SQL Tuning Advisor获取优化建议，通过DBMS_XPLAN.DISPLAY_CURSOR查看执行计划。
2. 19c环境：启用Automatic Indexing与Real-Time SQL Monitoring，减少人工干预；复杂查询优先使用物化视图+分区存储；深度诊断采用10046事件级别16，结合JSON格式跟踪文件快速分析。
3. 版本迁移注意：19c中无需手动创建PLAN_TABLE与PLUSTRACE角色，SQL_TRACE跟踪文件默认压缩，tkprof工具可兼容旧版本格式。