当前位置： 首页 > news >正文

57-Oracle SQL Profile(23ai)实操

news 2025/6/22 12:02:49

在上一期中说到了SQL Tuning Advisor其中一个影响对象就是SQL Profile，同样在管理和应用开发中,SQL性能优化是个任重道远的工作，低效的SQL语句让应用响应缓慢,用户整体体验下降,拖垮搞蹦整个系统都有可能。Oracle数据库提供了多种组合工具，有的免费有的是需要许可，系统而全面地解决这些性能问题。SQL自动调优(Automatic SQL Tuning)与SQL Profile是从10g就引入的重要特性，提供解决SQL性能问题强大的支持。

一、SQL是否需要自动调优，什么情况下可以接受自动？

数据库的查询优化器(Optimizer)在执行SQL语句前,会根据统计信息、系统参数等因素生成一个或多个执行计划(Execution Plan),并选择一个它认为当前成本已经是最优的计划来执行。
  • 但是在一些因素的影响下，CBO会受影响出次优或是更差的选择，例如：
  1. 统计信息过时或缺失：当表的数据量、数据分布发生显著变化,而统计信息未能及时更新时,优化器基于旧信息做出的判断可能不再准确。
  2. 复杂的查询结构：对于包含多表连接、子查询、复杂谓词的SQL,优化器估算成本的难度会大大增加。（23ai之后有了机器学习是不是有提升）
  • 原生优化器局限性：优化器模型本身可能无法完美处理所有情况。
  • 传统的解决方法通常是在SQL语句中手动添加提示(Hints),以指导优化器选择特定的场景，但这种方式又降存在这些缺陷：
  1. 侵入式修改：需要修改应用程序代码,对于已封装的商业软件或第三方系统,几乎是不可能的任务，且不可延续。
  2. 维护困难：随着数据和业务需求需求调整和运行环境的变化,也许当前有效的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），强制锁定执行路径。
​ 核心原理​：
  1. 提取原始SQL的Outline Data。
  2. 通过Hint改写SQL生成目标Outline Data。
  3. 将脚改写的带有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);-- 收集统计信息
BEGINDBMS_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
DECLAREtask_name VARCHAR2(50);
BEGINtask_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 TraceOnlyExhibits 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=20Plan 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 levelStatistics
-----------------------------------------------------------1  CPU used by this session1  CPU used when call started5  Requests to/from client4  SQL*Net roundtrips to/from client4  buffer is not pinned count944  bytes received via SQL*Net from client67782  bytes sent via SQL*Net to client5  calls to get snapshot scn: kcmgss2  calls to kcmgcs4  consistent gets1  consistent gets examination1  consistent gets examination (fastpath)4  consistent gets from cache3  consistent gets pin3  consistent gets pin (fastpath)1  enqueue releases1  enqueue requests2  execute count1  index range scans32768  logical read bytes from cache3  no work - consistent read gets10  non-idle wait count2  opened cursors cumulative1  opened cursors current1  parse count (hard)2  parse count (total)169  process last non-idle time1  recursive calls4  session logical reads1  sorts (memory)1620  sorts (rows)1  table fetch by rowid5  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
BEGINDBMS_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');

五、使用体验​

  1. ​自动 Profile​ 适合解决统计信息不准导致的偶发性性能问题，动态适应数据变化。
  2. ​手动 Profile​ 用于锁定最优计划，尤其适合关键业务 SQL 或第三方系统不可改源码的场景。
  3. ​最佳实践​：
  • 优先尝试自动 Profile
  • 对核心事务 SQL 使用手动 Profile 强控计划
  • 通过 force_match 和 category 提升灵活性和安全性
http://www.lryc.cn/news/573345.html

相关文章：

  • 编程基础：耦合
  • 跨平台SEH实现的方法
  • Skrill是什么？中国用户能用吗？安全吗？完整指南
  • DAY 38 Dataset和Dataloader类
  • Python编程语言：2025年AI浪潮下的技术统治与学习红利
  • Python UDP Socket 实时在线刷卡扫码POS消费机服务端示例源码
  • 自动化立体仓库堆垛机控制系统STEP7 FC3功能块 I/O映射
  • `provide` 和 `inject` 组件通讯：实现跨组件层级通讯
  • 机器学习15-XGBoost
  • 微服务拆分——nacos/Feign
  • 华为云Flexus+DeepSeek征文 | 基于Flexus X实例的金融AI Agent开发：智能风控与交易决策系统
  • 李宏毅2025《机器学习》第三讲-AI的脑科学
  • 蓝牙数据通讯，实现内网电脑访问外网电脑
  • WPF调试三种工具介绍：Live Visual Tree、Live Property Explorer与Snoop
  • SylixOS 下的消息队列
  • Jupyter notebook调试：设置断点运行
  • Redis后端的简单了解与使用（项目搭建前置）
  • DeepEP开源MoE模型分布式通信库
  • 洛谷P3953 [NOIP 2017 提高组] 逛公园
  • 【DCS开源项目】—— Lua 如何调用 DLL、DLL 与 DCS World 的交互
  • day44-硬件学习之arm启动代码
  • 【Datawhale组队学习202506】零基础学爬虫 02 数据解析与提取
  • 【simulink】IEEE5节点系统潮流仿真模型（2机5节点全功能基础模型）
  • 【智能体】dify部署本地步骤
  • LeetCode第279题_完全平方数
  • 湖北理元理律师事务所企业债务纾困路径：司法重整中的再生之道
  • 蓝桥杯备赛篇（上） - 参加蓝桥杯所需要的基础能力 1（C++）
  • 华为OD机试_2025 B卷_判断一组不等式是否满足约束并输出最大差（Python，100分）（附详细解题思路）
  • 车载电子电器架构 --- 电子电气架构设计方案
  • QC -io 服务器排查报错方式/报错： Failed to convert string to integer of varId variable!“