57-Oracle SQL Profile(23ai)实操-EW帮帮网

在上一期中说到了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 提升灵活性和安全性

57-Oracle SQL Profile(23ai)实操

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

二、SQL Profile 分类与原理

1. Auto SQL Profile（基于SQL Tuning Advisor）

2. 手工SQL Profile

三. 实操演示：23 ai上HR.T4SQLPROFILE 表实验

步骤1：创建测试环境

步骤2：自动 Profile 实验

四、 SQL Profile 管理指南

常用操作

关键管理场景

管理SQL Profile

五、使用体验

网站公告

今日签到

热门文章

最新发布

57-Oracle SQL Profile(23ai)实操

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

二、SQL Profile 分类与原理​

​1. Auto SQL Profile（基于SQL Tuning Advisor）​​

2. 手工SQL Profile​

三. 实操演示：23 ai上HR.T4SQLPROFILE 表实验​

​步骤1：创建测试环境

步骤2：自动 Profile 实验

四、 SQL Profile 管理指南​

​常用操作

关键管理场景​

管理SQL Profile

五、使用体验​

网站公告

今日签到

热门文章

最新发布

二、SQL Profile 分类与原理

1. Auto SQL Profile（基于SQL Tuning Advisor）

2. 手工SQL Profile

三. 实操演示：23 ai上HR.T4SQLPROFILE 表实验

步骤1：创建测试环境

四、 SQL Profile 管理指南

常用操作

关键管理场景

五、使用体验