在上一期中说到了SQL Tuning Advisor其中一个影响对象就是SQL Profile，同样在管理和应用开发中,SQL性能优化是个任重道远的工作，低效的SQL语句让应用响应缓慢,用户整体体验下降,拖垮搞蹦整个系统都有可能。Oracle数据库提供了多种组合工具，有的免费有的是需要许可，系统而全面地解决这些性能问题。SQL自动调优(Automatic SQL Tuning)与SQL Profile是从10g就引入的重要特性，提供解决SQL性能问题强大的支持。
一、SQL是否需要自动调优，什么情况下可以接受自动？
数据库的查询优化器(Optimizer)在执行SQL语句前,会根据统计信息、系统参数等因素生成一个或多个执行计划(Execution Plan),并选择一个它认为当前成本已经是最优的计划来执行。
- 但是在一些因素的影响下，CBO会受影响出次优或是更差的选择，例如：
- 统计信息过时或缺失：当表的数据量、数据分布发生显著变化,而统计信息未能及时更新时,优化器基于旧信息做出的判断可能不再准确。
- 复杂的查询结构：对于包含多表连接、子查询、复杂谓词的SQL,优化器估算成本的难度会大大增加。（23ai之后有了机器学习是不是有提升）
- 原生优化器局限性：优化器模型本身可能无法完美处理所有情况。
- 传统的解决方法通常是在SQL语句中手动添加提示(Hints),以指导优化器选择特定的场景，但这种方式又降存在这些缺陷：
- 侵入式修改：需要修改应用程序代码,对于已封装的商业软件或第三方系统,几乎是不可能的任务，且不可延续。
- 维护困难：随着数据和业务需求需求调整和运行环境的变化,也许当前有效的Hint，就在不远的下一次失效甚至产生负面影响。
基于这样的场景和选择，Oracle引入了自动SQL调优机制,提供更智能、非侵入的方式改善SQL执行效率的更优雅的方式。
二、SQL Profile 分类与原理
1. Auto SQL Profile（基于SQL Tuning Advisor）
本质：优化器辅助统计信息（如基数校正因子 SCALE_ROWS），动态修正执行计划成本计算。
工作原理：
- 分析阶段：校验统计信息、识别异常访问路径（如缺失索引或全表扫描成本失真）。
- 生成阶段：自动创建校正因子（如将表基数从100修正为100万）。
- 生效机制：优先使用Profile中的统计信息覆盖默认值，优化器据此重新计算成本。
优点：
- 自动化高效：一键生成，适合批量优化AWR中的高频低效SQL。
- 动态适应：随数据变化自动调整，避免计划固化（如新增索引时自动启用）。
- 低风险：内置成本对比验证，规避极端路径（如不会强制禁用所有索引）。
缺点：
- 优化局限：无法突破优化器规则（如不能强制嵌套循环替代哈希连接）。
- 环境依赖：严重依赖当前统计信息，跨环境迁移（测试→生产）可能失效。
- 黑盒操作：修正逻辑存储在隐藏表 SQLPROF$，问题追溯困难。
2. 手工SQL Profile
本质：Hint组合（从目标执行计划提取Outline Data），强制锁定执行路径。
核心原理：
- 提取原始SQL的Outline Data。
- 通过Hint改写SQL生成目标Outline Data。
- 将脚改写的带有Hint的替换原始的版本，生成最终Profile。
优点：
- 精准控制：绕过优化器决策，强制固定最优路径（如索引扫描、连接顺序）。
- 优先级碾压：覆盖SQL中的硬编码Hint（如 /*+ FULL(T) */ 无效）。
- 灵活生效：通过 CATEGORY 按会话/环境隔离（如测试环境用 DEV 类别、生产用PROD）。
缺点：
- 技术门槛高：需深入理解成本模型，错误设置引发性能恶化（如基数误判导致笛卡尔积）。
- 维护成本大：表结构变更（如索引删除）需人工重新验证Profile有效性。
- 过优化风险：强制计划可能失效（如索引失效仍强制扫描，引发全表扫描被禁用）。
三. 实操演示：23 ai上HR.T4SQLPROFILE 表实验
步骤1：创建测试环境
SYS@CDB$ROOT> alter session set container=FREEPDB1;
-- 建表并模拟插入不均衡数据
CREATE TABLE HR.T4SQLPROFILE AS
SELECT * FROM DBA_OBJECTS WHERE ROWNUM <= 10000;
INSERT INTO HR.T4SQLPROFILE
SELECT * FROM DBA_OBJECTS
WHERE object_id = 100 AND ROWNUM <= 9000; -- 90% 数据集中
CREATE INDEX HR.IDX_T4SQLPROFILE ON HR.T4SQLPROFILE(object_id);
-- 收集统计信息
BEGIN
DBMS_STATS.GATHER_TABLE_STATS('HR','T4SQLPROFILE');
END;
/
--PL/SQL procedure successfully completed.
步骤2：自动 Profile 实验
官方文档DBMS_SQLTUNE.ACCEPT_SQL_PROFILE 的 FORCE_MATCH 参数默认值为 FALSE，即必须在 SQL 文本完全匹配时应用 SQL Profile才会被使用，若目标 SQL 文本有改动即使是where 后面的字句查询不一致，原有 SQL Profile 就失效。。所以必须设置“DBMS_SQLTUNE.ACCEPT_SQL_PROFILE(task_name, force_match=>TRUE);”
-- 低效全表扫描（因 NO_INDEX Hint）
SELECT /*+ NO_INDEX(T) */ * FROM HR.T4SQLPROFILE T WHERE object_id=10;
-- 创建调优任务并接受 Profile
DECLARE
task_name VARCHAR2(50);
BEGIN
task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_text => 'SELECT /*+ NO_INDEX(T) */ * FROM HR.T4SQLPROFILE T WHERE object_id=10',
scope => 'COMPREHENSIVE',
task_name => 'AUTO_TUNE_TASK'
);
DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name);
DBMS_SQLTUNE.ACCEPT_SQL_PROFILE(task_name, force_match=>TRUE);
END;
/
-- 验证：计划转为索引扫描
--验证SQL Profile被接受，同时查询走index
SELECT * FROM HR.T4SQLPROFILE WHERE object_id=10;
--
SYS@CDB$ROOT> SELECT * FROM HR.T4SQLPROFILE WHERE object_id=10;
SYS C_USER# 10 10 CLUSTER 24-APR-25
--
SYS@CDB$ROOT> SET AUTOTRACE ON
Autotrace TraceOnly
Exhibits the performance statistics with silent query output
SELECT * FROM HR.T4SQLPROFILE WHERE object_id=20;
SYS@CDB$ROOT> SELECT * FROM HR.T4SQLPROFILE WHERE object_id=20;
1 row selected.
SQL_ID cy30yj4480y2q, child number 0
-------------------------------------
SELECT * FROM HR.T4SQLPROFILE WHERE object_id=20
Plan hash value: 2117281514
-------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T4SQLPROFILE | 1 |
|* 2 | INDEX RANGE SCAN | IDX_T4SQLPROFILE | 1 |
-------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=20)
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
-----------------------------------------------------------
1 CPU used by this session
1 CPU used when call started
5 Requests to/from client
4 SQL*Net roundtrips to/from client
4 buffer is not pinned count
944 bytes received via SQL*Net from client
67782 bytes sent via SQL*Net to client
5 calls to get snapshot scn: kcmgss
2 calls to kcmgcs
4 consistent gets
1 consistent gets examination
1 consistent gets examination (fastpath)
4 consistent gets from cache
3 consistent gets pin
3 consistent gets pin (fastpath)
1 enqueue releases
1 enqueue requests
2 execute count
1 index range scans
32768 logical read bytes from cache
3 no work - consistent read gets
10 non-idle wait count
2 opened cursors cumulative
1 opened cursors current
1 parse count (hard)
2 parse count (total)
169 process last non-idle time
1 recursive calls
4 session logical reads
1 sorts (memory)
1620 sorts (rows)
1 table fetch by rowid
5 user calls
SYS@CDB$ROOT>
四、 SQL Profile 管理指南
常用操作
|
操作 |
命令/视图 |
|
查看 Profile |
SELECT name, category, status FROM dba_sql_profiles; |
|
禁用/启用 |
DBMS_SQLTUNE.ALTER_SQL_PROFILE(name=>'PROF1', attribute_name=>'STATUS', value=>'DISABLED'); |
|
删除 Profile |
DBMS_SQLTUNE.DROP_SQL_PROFILE('PROF1'); |
|
迁移 Profile |
使用DBMS_SQLTUNE.PACK_STGTAB_SQLPROF导出导入 |
关键管理场景
- 环境隔离：通过 CATEGORY 控制 Profile 生效范围（如测试环境用 DEV ，生产用PROD）。
- 版本升级：使用数据泵导出 SQLPROF$ 表实现跨版本迁移。
- 性能监控：结合 AWR 报告检查 SQL Profile 的使用效果。
管理SQL Profile
-- 查看所有 Profile
SELECT name, category, status, sql_text
FROM dba_sql_profiles；
--
SYS@CDB$ROOT> SELECT name, category, status, sql_text FROM dba_sql_profiles;
SYS_SQLPROF_0197914a1f230000 DEFAULT ENABLED SELECT /*+ NO_INDEX(T) */ * FROM HR.T4SQLPROFILE T WHERE object_id=10
-- 禁用/启用 Profile
BEGIN
DBMS_SQLTUNE.ALTER_SQL_PROFILE(
name => 'SYS_SQLPROF_0197914a1f230000',
attribute_name => 'STATUS',
value => 'DISABLED' -- 或 'ENABLED'
);
END;
/
-- 删除 Profile
EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE('SYS_SQLPROF_0197914a1f230000');
五、使用体验
- 自动 Profile 适合解决统计信息不准导致的偶发性性能问题，动态适应数据变化。
- 手动 Profile 用于锁定最优计划，尤其适合关键业务 SQL 或第三方系统不可改源码的场景。
- 最佳实践：
- 优先尝试自动 Profile
- 对核心事务 SQL 使用手动 Profile 强控计划
- 通过 force_match 和 category 提升灵活性和安全性
扫一扫
492
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
