数据库教程FGMT09‑Oracle性能优化之执行计划与基线管理

数据库教程FGMT09‑Oracle性能优化之执行计划与基线管理

前言

在Oracle数据库运维与SQL性能调优工作当中，SQL语句执行计划直接决定SQL运行的资源消耗、响应时间与业务吞吐量。生产环境中经常遇到相同SQL在统计信息变更、版本升级、索引增减之后发生执行计划突变，引发慢查询、数据库CPU冲高、业务接口超时等线上故障。风哥教程本文围绕Oracle SQL完整处理流程、软解析硬解析、绑定变量、游标、CBO优化器、访问路径、表连接方式、hint干预手段、执行计划查看、SPM基线管理、SQL Profile、SQL Patch等核心技术开展完整讲解。风哥 itpux‑com

本套风哥教程面向数据库运维工程师、DBA、后端开发人员，全部实验环境统一标准化配置：主机名称fgedu‑net‑cn，硬件规格为64G物理内存、8颗CPU，数据库实例名fgedudb，数据库名fgedudb，业务测试用户名fgedu，文件根目录统一替换为/fgedudb，整套实验基于Oracle19c企业版完成。风哥教程本文分为前言与大纲介绍、核心理论知识、实战操作演练、总结四大模块，实战章节包含大量可直接复制执行的SQL脚本、操作系统操作步骤，读者可以在测试环境完整复现全部实验现象，掌握执行计划阅读、计划异常排查、执行计划固化的全套生产运维手段。

内容大纲

1. Oracle性能优化基础概念与SQL完整处理流程
2. Oracle软解析、硬解析原理，绑定变量与游标底层机制
3. CBO优化器工作原理，成本计算逻辑，统计信息对计划的影响
4. SQL访问路径、表连接方式、驱动表选择核心原理
5. Oracle多种执行计划查看手段，执行计划阅读规则
6. hint提示原理、正确使用方式与线上常见使用误区
7. SQL执行计划基线SPM基础概念与组件架构
8. SQL Profile原理、适用场景与实操流程
9. SQL Patch原理、适用场景与实操流程
10. 完整实验环境搭建，复现计划漂移、基线捕获、基线演化、基线禁用与删除
11. 生产环境运维规范、风险点、应急处置注意事项

一、核心理论知识

本章节为本套风哥教程理论基础，只有理解底层理论，才可以读懂执行计划，定位线上SQL性能抖动根因。风哥教程 113257174

1.1 Oracle SQL性能优化基础概念

SQL调优的本质，是在硬件资源（CPU、内存、IO）约束之下，让SQL以最小化逻辑读、物理读、CPU消耗完成业务逻辑。一条SQL从客户端提交到数据库，到最终返回结果集，会依次经过语法解析、语义校验、库缓存查找、优化器计算、行源生成、执行返回结果整套链路。执行计划就是优化器输出的一套操作步骤，定义数据库以何种顺序读取表、索引，完成过滤、关联、排序、聚合运算。

生产环境会出现执行计划漂移：统计信息收集、数据库版本升级、系统参数变更、索引新增删除、数据分布倾斜，都会造成CBO生成完全不一样的执行计划。同一条业务SQL，计划发生变化之后，逻辑读可能提升数十上百倍，直接造成业务雪崩。
为了解决计划不稳定问题，Oracle自11g版本引入SPM（SQL Plan Management，SQL计划管理）基线框架，将经过验证性能优良的执行计划保存为接受基线，SQL再次运行优先选用已经验证的计划，同时支持后台安全评估接纳更优的新计划，兼顾计划稳定性与优化器迭代能力。网上搜索风哥教程可以学习全套数据库教程

1.2 SQL语句完整内部处理流程

当业务客户端向fgedudb实例提交SELECT、UPDATE、DELETE等SQL语句，数据库内部处理分为六大阶段：

1. 语法解析阶段：校验SQL语法书写合法性，语法错误直接向客户端抛出报错；
2. 语义检查阶段：校验访问的表、索引、视图对象是否存在，当前fgedu用户是否具备对象访问权限，字段名是否合法；
3. 库缓存查找阶段：对SQL文本做哈希运算生成唯一sql_id，在SGA共享池Library Cache查找是否已经存在解析树、父游标、子游标。如果找到匹配游标进入软解析流程；找不到匹配游标触发硬解析；
4. CBO优化阶段：读取对象统计信息、系统统计信息，生成多套候选执行计划，计算每一套计划的COST成本值，选取成本最低的候选计划；
5. 行源生成阶段：将选中的执行计划转换为数据库可执行的行源树；
6. 执行阶段：按照行源树步骤访问Buffer Cache、磁盘存储，完成过滤、表连接、排序聚合运算，向客户端返回结果集。

1.3 硬解析、软解析、游标、绑定变量

游标（Cursor）是会话处理SQL的内存句柄。私有SQL区域存放于PGA内存；父游标、子游标存放在SGA共享池Library Cache当中。

1. 硬解析：库缓存不存在匹配游标，数据库完整执行语法校验、语义校验、CBO成本计算，会消耗大量CPU资源与latch闩锁资源。高并发业务大量硬解析，会严重压垮数据库。触发硬解析典型场景：SQL文本存在空格、大小写差异；统计信息大规模变更；绑定变量窥探产生大量子游标；游标被共享池老化刷出内存。
2. 软解析：SQL文本完全一致，在共享池命中父游标与匹配子游标，跳过完整优化流程，仅做少量权限校验，CPU开销远低于硬解析。
3. 软软解析：会话PGA本地缓存游标信息，不需要访问共享池，进一步降低解析开销。

绑定变量是抑制硬解析爆炸的核心手段，业务代码应当避免拼接字面量，使用:var绑定变量形式提交SQL。但是绑定变量也存在缺陷：当字段数据分布严重倾斜，不同传入绑定变量值，数据选择性差异巨大，同一条SQL需要完全不同的执行计划。为此Oracle引入bind‑sensitive绑定敏感游标、bind‑aware绑定感知游标，针对倾斜数据自动生成多个子游标适配不同输入值的最优计划。

理论重点：绑定变量可以抑制硬解析，但不能保证每次都输出最优执行计划，这也是生产环境需要SPM基线、SQL Profile介入调优的重要场景。

1.4 CBO优化器与统计信息

Oracle10g之后已经废弃RBO基于规则的优化器，全部默认使用CBO基于成本优化器。CBO做成本计算两大核心输入：对象统计信息、系统统计信息。

1. 对象统计信息：表总行数、数据块数量、行平均长度；索引叶子块数量、索引键值、直方图信息；直方图用来记录字段数据倾斜分布。
2. 系统统计信息：CPU运算开销、单块读IO成本、多块读IO成本。针对主机fgedu‑net‑cn（64G内存8CPU），系统统计信息会采集本机真实硬件IO与CPU指标，为成本计算提供硬件基准。

统计信息过时、缺失、直方图丢失，会直接导致CBO计算出错误COST，生成劣质执行计划。很多线上SQL性能故障根源就是统计信息异常。

1.5 SQL访问路径、表连接、驱动表

访问路径

访问路径代表读取表数据的方式，分为全表扫描、索引范围扫描、索引唯一扫描、索引快速全扫描等。

• 全表扫描：读取表所有数据块，适合小表，或者返回大比例数据的查询；
• 索引扫描：通过索引定位rowid再回表读取行数据，适合过滤条件选择性高，返回少量行的查询。

表连接三种方式

1. 嵌套循环 NESTED LOOPS：驱动表返回少量行，每一行去被驱动表做索引查找。驱动表行数越少性能越好；被驱动表连接列必须存在索引。
2. 哈希连接 HASH JOIN：对驱动表构建哈希表，被驱动表做哈希探测。适合大表与大表关联，消耗PGA内存。
3. 排序合并连接 SORT MERGE JOIN：两张表分别按照连接字段排序，之后合并结果集。适合排序字段已有排序结果，或者不等值连接场景。

驱动表

驱动表也叫外部表，是表连接操作的输入源。嵌套循环中驱动表返回行数直接决定循环次数；哈希连接驱动表用来构建哈希桶。CBO依靠统计信息评估，选择行数少的表作为驱动表。统计信息失真会造成驱动表选择颠倒，SQL执行时间暴涨。

1.6 hint提示、SPM基线、SQL Profile、SQL Patch概念

风哥数据库教程 itpux‑com

1. hint提示：嵌入SQL语句中的指令，干预CBO优化器的访问路径、连接方式、并行度等行为。hint写在SQL内部，业务代码变更、应用版本迭代hint会丢失，不适合大规模线上生产环境。
2. SPM SQL计划基线：将经过验证的良好执行计划持久化存储在数据字典，不需要修改业务SQL文本。SQL再次解析优先选用接受状态基线；新生成计划标记为未接受，经过演化任务验证性能更优之后才会启用，是19c生产环境首选计划固化方案。
3. SQL Profile：不修改SQL文本，不直接锁定计划，而是向优化器补充校正后的统计信息，修正CBO成本计算偏差。适合统计信息失真，无法收集正确直方图的业务SQL。
4. SQL Patch：数据库后台给指定SQL追加hint集合，业务SQL本身不需要修改。适合不方便修改应用源码，又需要hint干预执行计划的场景。

四者对比：hint写死在业务SQL；SQL Profile校正统计；SQL Patch后台追加hint；SPM基线直接保存整套执行计划，具备计划演化验证能力。

二、实战操作演练

本套风哥教程全部实战操作，执行主机fgedu‑net‑cn，数据库fgedudb，业务用户fgedu，目录/fgedudb，硬件规格64G内存8CPU。

环境说明：操作系统用户oracle，登录服务器后设置环境变量，指向fgedudb实例；数据库操作优先使用sysdba身份，业务测试对象使用fgedu用户。

2.1 操作系统与数据库环境校验

2.1.1 操作系统层面检查（主机fgedu‑net‑cn）

登录操作系统oracle用户执行：

#确认主机名称
hostname
#确认oracle软件目录，全部使用/fgedudb
echo $ORACLE_HOME
#查看内存硬件 64G内存
free -h
#查看CPU 8CPU
lscpu

输出校验：hostname输出为fgedu‑net‑cn；内存总大小64G；CPU总逻辑核8颗。

2.1.2 数据库实例参数确认（64G内存8CPU标准配置）

登录sqlplus / as sysdba，查看内存参数配置，64G物理内存主机，Oracle分配内存48G，预留内存给操作系统。

show parameter memory_target;
show parameter sga_target;
show parameter pga_aggregate_target;
show parameter processes;
show parameter optimizer_use_sql_plan_baselines;
show parameter optimizer_capture_sql_plan_baselines;

参考标准spfile参数（适配64G内存8CPU）：

alter system set memory_max_target=48G scope=spfile;
alter system set memory_target=48G scope=spfile;
alter system set processes=800 scope=spfile;
--SPM基线默认开启
alter system set optimizer_use_sql_plan_baselines=TRUE scope=spfile;
alter system set optimizer_capture_sql_plan_baselines=FALSE scope=spfile;

说明：optimizer_capture_sql_plan_baselines设置FALSE，不开启自动捕获基线；生产环境建议手工加载基线，避免系统自动捕获大量劣质计划进入基线库。

2.1.3 创建测试用户fgedu，授权实验权限

create user fgedu identified by fgedudb default tablespace users temporary tablespace temp;
grant connect,resource to fgedu;
--调优相关权限
grant select_catalog_role to fgedu;
grant administer sql management object to fgedu;
grant execute on dbms_xplan to fgedu;
grant execute on dbms_spm to fgedu;
grant execute on dbms_sqltune to fgedu;
grant execute on dbms_sqldiag to fgedu;

2.2 构建测试业务数据表（fgedu用户）

切换到fgedu用户，创建测试大表、索引，制造数据倾斜场景，用来复现执行计划漂移现象。

conn fgedu/fgedudb@fgedudb

--创建测试大表t_big
create table t_big(id number,code number,name varchar2(100));

--插入测试数据，制造数据倾斜，code=100 10万行，其他code各10行
declare
begin
  for i in 1..100000 loop
    insert into t_big values(i,100,'testdata'||i);
  end loop;
  for j in 200..300 loop
    for k in 1..10 loop
      insert into t_big values(100000+j*10+k,j,'test'||j);
    end loop;
  end loop;
  commit;
end;
/

--创建普通B‑tree索引
create index idx_tbig_code on t_big(code);

--收集表统计信息，不收集直方图，人为制造统计信息缺陷
exec dbms_stats.gather_table_stats(ownname=>'FGEDU',tabname=>'T_BIG',method_opt=>'for all columns size 1');

上面method_opt size 1，直方图关闭，CBO无法识别code字段严重倾斜，会生成错误的执行计划，模拟线上统计信息缺失故障场景。

2.3 Oracle查看执行计划多种方式实战

风哥教程 113257174

方式1：explain plan for 生成执行计划（仅预估计划，不真实执行SQL）

conn fgedu/fgedudb@fgedudb

explain plan for
select /*+ test_sql */ * from t_big where code=100;

select * from table(dbms_xplan.display());

dbms_xplan.display读取plan_table里面预估执行计划。注意：explain plan不会真实运行SQL，无法看到运行时统计信息，不能看到实际行号。

方式2：dbms_xplan.display_cursor读取内存中游标真实执行计划

SQL已经真实执行，游标驻留在library cache，读取真实运行执行计划，包含A‑Rows实际返回行数，是生产最常用方式。

--执行目标SQL
select /*+ test_sql */ * from t_big where code=100;

--找到这条SQL的sql_id
select sql_id,sql_text from v$sql where sql_text like '%test_sql%';

--替换为实际查询得到的sql_id值
select * from table(dbms_xplan.display_cursor(sql_id=>'&sql_id',cursor_child_no=>0,format=>'ALLSTATS LAST'));

format参数说明：

• ALLSTATS LAST：展示实际行、逻辑读、物理读等运行时指标；线上故障排查优先使用该格式。

方式3：读取AWR历史保存的执行计划

SQL已经从shared pool老化刷出内存，从AWR历史视图读取历史执行计划。

select * from table(dbms_xplan.display_awr(sql_id=>'&sql_id'));

执行计划阅读要点

1. 执行计划缩进层级：缩进最多的行最先执行；同一缩进从上往下执行。
2. COST是优化器估算成本；A‑ROWS是SQL实际返回行数；E‑ROWS是优化器预估行数。
3. 如果E‑ROWS预估行数与A‑ROWS实际行数差异巨大，代表统计信息失真，这是SQL性能问题高频根因。

2.4 hint提示实操与常见误区演示

重要提醒：hint写死在业务SQL源码中，应用版本迭代会丢失，线上业务尽量优先使用SPM基线、SQL Patch，不要大规模依赖hint。

--使用index hint强制走索引访问路径
explain plan for
select /*+ index(t idx_tbig_code) */ * from t_big t where code=100;

select * from table(dbms_xplan.display());

hint常见错误：表别名写错，hint失效；hint语法拼写错误，Oracle不会抛出报错，直接忽略hint。
演示hint别名写错导致失效案例：

explain plan for
select /*+ index(idx_tbig_code) */ * from t_big t where code=100;
select * from table(dbms_xplan.display());

这里hint没有写表别名t，hint失效，优化器不使用索引。很多开发DBA踩坑，hint无效却没有任何ORA报错。

2.5 SPM SQL计划基线完整实战

本实验完整演示捕获基线、查看基线、演化基线、启用禁用基线、删除基线完整流程。

2.5.1 执行目标SQL，让游标加载至共享池

conn fgedu/fgedudb@fgedudb
select /*+ spm_test */ * from t_big where code=220;
--查询v$sql获取sql_id
select sql_id,sql_text,plan_hash_value from v$sql where sql_text like '%spm_test%';

记录返回的sql_id与plan_hash_value。

2.5.2 使用dbms_spm，从cursor cache手动加载计划到基线

生产环境不建议开启自动捕获，推荐手工加载已经验证性能良好的执行计划进入基线库。
以sysdba身份执行：

conn / as sysdba
set serveroutput on
declare
v_ret number;
begin
  v_ret := dbms_spm.load_plans_from_cursor_cache(
    sql_id => '&input_sql_id',
    plan_hash_value => '&input_phv',
    enabled => 'YES',
    fixed => 'NO'
  );
  dbms_output.put_line('加载基线成功数量:'||v_ret);
end;
/

2.5.3 查询dba_sql_plan_baselines视图查看基线信息

select sql_handle,plan_name,enabled,accepted,sql_text
from dba_sql_plan_baselines
where sql_text like '%spm_test%';

字段说明：

• ENABLED：是否启用该计划；
• ACCEPTED：是否为已经接受可用基线；
• SQL_HANDLE：SQL全局唯一标识，后续修改、删除基线需要使用该字段。

2.5.4 制造执行计划漂移，生成未接受基线

人为重新收集统计信息，制造计划变更。

exec dbms_stats.gather_table_stats(ownname=>'FGEDU',tabname=>'T_BIG',method_opt=>'for all columns size auto');

再次反复执行目标SQL，优化器生成一套新的不同执行计划，新计划会加入基线，但是accepted=NO未接受，不会被数据库使用。

select /*+ spm_test */ * from t_big where code=220;
select sql_handle,plan_name,enabled,accepted,sql_text from dba_sql_plan_baselines where sql_text like '%spm_test%';

2.5.5 基线演化，验证新计划性能是否更优

执行演化任务，数据库后台测试未接受计划性能，判断是否接纳为可用基线。

declare
v_task_name varchar2(100);
v_report clob;
begin
  v_task_name := dbms_spm.create_evolve_task(sql_handle=>'&input_sql_handle');
  dbms_spm.execute_evolve_task(task_name=>v_task_name);
  v_report := dbms_spm.report_evolve_task(task_name=>v_task_name);
  dbms_output.put_line(v_report);
end;
/

执行完演化任务，读取报告，可以看到新计划性能对比结果，满足条件自动变为accepted=YES。

2.5.6 禁用、启用、删除SQL计划基线

--禁用基线
declare
v_ret number;
begin
v_ret:=dbms_spm.alter_sql_plan_baseline(
sql_handle=>'&sql_handle',
plan_name=>'&plan_name',
attribute_name=>'ENABLED',
attribute_value=>'NO');
end;
/

--删除指定基线
declare
v_ret number;
begin
v_ret:=dbms_spm.drop_sql_plan_baseline(
sql_handle=>'&sql_handle',
plan_name=>'&plan_name');
dbms_output.put_line('删除基线数量：'||v_ret);
end;
/

2.6 SQL Profile实战演练

SQL Profile用于校正优化器统计估算偏差，不需要修改业务SQL文本。需要ADMINISTER SQL MANAGEMENT OBJECT权限。

conn / as sysdba
VARIABLE tsk_name VARCHAR2(64);
BEGIN
:tsk_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_text=>'select * from fgedu.t_big where code=100',
scope=>'COMPREHENSIVE',
task_name=>'TUNE_TASK_FGEDU_01'
);
END;
/

--执行调优任务
exec DBMS_SQLTUNE.EXECUTE_TUNING_TASK(:tsk_name);

--接受生成SQL Profile，force_match=true，弱化SQL文本空格差异匹配
DECLARE
pro_name varchar2(60);
BEGIN
pro_name := DBMS_SQLTUNE.ACCEPT_SQL_PROFILE(
task_name=>'TUNE_TASK_FGEDU_01',
force_match=>TRUE
);
dbms_output.put_line('生成profile名称：'||pro_name);
END;
/

--查询已存在SQL Profile
select name,signature,category,status from dba_sql_profiles;

--禁用SQL Profile
exec dbms_sqltune.alter_sql_profile(name=>'&profile_name',attribute_name=>'STATUS',attribute_value=>'DISABLED');

--删除SQL Profile
exec dbms_sqltune.drop_sql_profile(name=>'&profile_name');

2.7 SQL Patch实战演练

SQL Patch后台给指定SQL附加hint集合，业务SQL源码无需改动，适合不方便修改应用程序的调优场景，调用包DBMS_SQLDIAG实现。

conn / as sysdba
declare
patch_name varchar2(128);
begin
patch_name := dbms_sqldiag.create_sql_patch(
sql_text => 'select * from fgedu.t_big where code=100',
hint_text => 'INDEX(T IDX_TBIG_CODE)',
name => 'FGEDU_PATCH_01',
description => '测试强制索引patch'
);
dbms_output.put_line('创建sql patch名称：'||patch_name);
end;
/

--查看sql patch
select name,sql_text,hint_text,status from dba_sql_patches;

--禁用patch
exec dbms_sqldiag.alter_sql_patch(name=>'FGEDU_PATCH_01',attribute_name=>'STATUS',attribute_value=>'DISABLED');

--删除sql patch
exec dbms_sqldiag.drop_sql_patch(name=>'FGEDU_PATCH_01');

2.8 生产环境迁移基线、profile导出导入简要操作

当数据库迁移，需要把调优对象迁移到目标库，SPM基线可以通过staging中转表导出导入。

--创建基线中转存储表
exec dbms_spm.create_stgtab_baseline(table_name=>'SPM_STG_TAB',tablespace_name=>'USERS');

--源库：把基线导出到中转表
declare
v_cnt number;
begin
v_cnt:=dbms_spm.pack_stgtab_baseline(stgtab_name=>'SPM_STG_TAB',sql_handle=>'&sql_handle');
dbms_output.put_line('导出基线行数：'||v_cnt);
end;
/
--expdp导出SPM_STG_TAB表，传输到目标库impdp导入
--目标库：从中转表导入基线
declare
v_cnt number;
begin
v_cnt:=dbms_spm.unpack_stgtab_baseline(stgtab_name=>'SPM_STG_TAB');
dbms_output.put_line('导入基线行数：'||v_cnt);
end;
/

2.9 线上故障排查完整操作流程总结（实战步骤）

1. 业务反馈SQL变慢，抓取慢SQL的sql_id；
2. 通过dbms_xplan.display_cursor获取真实执行计划，对比E‑ROWS预估行数与A‑ROWS实际行数，判断统计信息是否失真；
3. 查看dba_sql_plan_baselines，确认是否存在基线；查看dba_sql_profiles、dba_sql_patches确认是否存在调优对象；
4. 如果是统计信息问题，优先收集正确统计信息；
5. 统计信息无法修复，优先使用SPM基线固化已知良好执行计划；特殊场景选用SQL Profile、SQL Patch；
6. 变更完成之后观察业务响应时间，保留回退脚本（禁用、删除基线/profile/patch脚本），线上操作必须准备回退方案。

三、风哥针对本文总结

本套风哥教程完整覆盖Oracle执行计划与基线管理整套知识，从SQL内部处理原理、软/硬解析、游标绑定变量、CBO优化器，到访问路径、表连接、hint使用，再到三大计划固化工具SPM基线、SQL Profile、SQL Patch理论与完整实操命令。风哥数据库教程 itpux‑com

1. 线上排查SQL性能故障，优先看真实运行的执行计划（display_cursor），不要依赖explain plan预估计划；重点对比预估行数E‑ROWS和实际行数A‑ROWS，二者差距巨大基本指向统计信息异常。
2. hint嵌入业务SQL，版本迭代容易丢失；生产环境优先采用SPM SQL计划基线做执行计划固化，不要大规模依赖业务代码写hint。
3. SPM基线生产环境不建议开启自动捕获参数optimizer_capture_sql_plan_baselines，避免大量劣质计划被捕获进入基线库；推荐手工加载经过业务验证性能优良的执行计划。
4. SQL Profile用于校正优化器统计估算偏差；SQL Patch适合不方便修改业务源码，后台追加hint的场景；二者都需要对应的调优包许可，上线前需要确认数据库许可合规。网上搜索风哥教程可以学习全套数据库教程
5. 所有线上调优操作，上线前必须准备完整回退脚本：禁用、删除基线/profile/patch脚本，一旦调优引发业务异常可以快速回滚。
6. 基线只是应急保障手段，不能替代根本优化。优先修复统计信息、增加合理索引、改写业务SQL；基线用于防止已经优化完成的SQL后续发生计划漂移。

整套实验所有脚本建议读者在测试环境完整复现，理解每一条命令的输出含义，再应用到真实生产运维工作。