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

57-Oracle SQL Profile(23ai)实操

ops 2025/6/22 11:14:15

在上一期中说到了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.xdnf.cn/news/14543.html

相关文章：

  • nginx服务器配置时遇到的一些问题
  • Mac电脑-触摸板增强工具-BetterTouchTool
  • 文本分类与聚类：让信息“各归其位”的实用方法
  • 力扣网C语言编程题：多数元素
  • MCPServer编程与CLINE配置调用MCP
  • 1.23Node.js 中操作 mongodb
  • 【Linux-shell】探索Dialog 工具在 Shell 图形化编程中的高效范式重构
  • 让大模型“更懂人话”：对齐训练（RLHF DPO）全流程实战解析
  • 蓝牙 5.0 新特性全解析：传输距离与速度提升的底层逻辑（面试宝典版）
  • 下载链接记录
  • 【Flink实战】 Flink SQL 中处理字符串 `‘NULL‘` 并转换为 `BIGINT`
  • Vue学习笔记
  • 二分查找----1.搜索插入位置
  • C/C++ 高频八股文面试题1000题(一)
  • ROS1/ROS2中工作空间和工作包创建详解
  • 服务网格安全（Istio）：用零信任架构重构微服务通信安全
  • 5.3 LED字符设备驱动
  • 深度学习笔记27-LSTM实现糖尿病探索与预测(Pytorch)
  • 实验问题记录：PyTorch Tensor 也会出现 a = b 赋值后，修改 a 会影响 b 的情况
  • 解锁决策树：数据挖掘的智慧引擎
  • IT运维面试常问问题答案
  • QEMU学习之路（10）— RISCV64 virt 使用Ubuntu启动
  • 【C++】哈希表的实现（开放定址法）
  • 服务器手动安装并编译R环境库包：PROJ→RGDAL
  • C++ 11 智能指针 std::weak_ptr
  • 项目开发中途遇到困难的解决方案
  • RISC-V物联网关，支持鸿蒙，T-Thread实时系统
  • 关于Seata的一个小issue...
  • 【蓝牙】Qt4中向已配对的手机发送PDF文件
  • html和css实现文本打断换行、自动换行