大家好，我是 JiekeXu,江湖人称“强哥”，青学会 MOP 技术社区主席，荣获 Oracle ACE Pro 称号，墨天轮 MVP，墨天轮年度“墨力之星”，拥有 Oracle OCP/OCM 认证,MySQL 5.7/8.0 OCP 认证以及 PCA、PCTA、OBCA、OGCA、金仓KCA、KCP 等众多国产数据库认证证书,今天和大家一起来看看 Oracle SQL Profile：优化查询性能的神奇 “魔法棒”！欢迎关注我的微信公众号“JiekeXu DBA之路”，然后点击右上方三个点“设为星标”置顶，更多干货文章才能第一时间推送，谢谢！
前 言
SQL Profile 是 Oracle 10g 中引入的一项功能，通过 DBMS_SQLTUNE 包或通过 Oracle Enterprise Manager 作为自动 SQL 调优过程的一部分进行管理。
SQL Profile
SQL Profile 是存储在数据字典中的信息集合，它使查询优化器能够为 SQL 语句创建最佳执行计划。SQL Profile 包含在 Automatic SQL Tuning 期间发现的优化器估计不佳的更正。此信息可以提高优化器基数和选择性估计，从而引导优化器选择更好的计划。
下图显示了 SQL 语句与此语句的 SQL Profile 之间的关系。优化器使用 SQL Profile 和环境生成执行计划。在此示例中，计划位于语句的 SQL 计划基线中。
如果优化器环境或 SQL Profile 发生变化，优化器就会创建新的计划。随着表数据的增长，或者随着索引的创建或删除，SQL Profile 的计划也会发生变化。即使相应语句的数据分布或访问路径发生更改，配置文件仍保持相关。
一般来说，不需要刷新 SQL Profile。但是，随着时间的推移，配置文件内容可能会过时。在这种情况下，SQL 语句的性能可能会下降。语句可能会显示为高负载或 TOP SQL。在这种情况下，自动 SQL 调整任务会再次将语句捕获为高负载 SQL。您可以为语句执行新的 SQL Profile。
SQL Profile 不包含有关单个执行计划的信息。相反，优化程序在选择计划时具有以下信息源：
- 环境，其中包含数据库配置、绑定变量值、优化器统计信息、数据集等
- SQL Profile 配置文件中的补充统计信息
请务必注意，SQL Profile 不会冻结 SQL 语句的执行计划，就像存储的大纲那样。随着表的增长或索引的创建或删除，执行计划可能会使用相同的 SQL Profile 发生变化。即使相应语句的数据分发或访问路径发生变化，其中存储的信息仍然相关。但是，在很长一段时间内，其内容可能会过时，并且必须重新生成。这可以通过在同一语句上再次运行自动 SQL 调优以重新生成 SQL 配置文件来完成。
SQL Profile 文件是一个数据库对象，其中包含特定于 SQL 语句的辅助统计信息。
从概念上讲，SQL Profile 文件之于 SQL 语句就像对象级统计信息之于表或索引。SQL Profile 文件是在 DBA 调用 SQL Tuning Advisor 时创建的。SQL Profile 以内部格式存储在数据字典 DBA_SQL_PROFILES 中。优化器在优化期间使用此信息来确定最佳计划。
SQL Profile 文件的范围可由 CATEGORY 配置文件属性控制。此属性确定哪些用户会话可以应用配置文件。您可以在 DBA_SQL_PROFILES 视图的 CATEGORY 列中查看 SQL 配置文件的 CATEGORY 属性。
COLUMN category FORMAT a10 COLUMN sql_text FORMAT a20 SELECT NAME, SQL_TEXT, CATEGORY, STATUS FROM DBA_SQL_PROFILES;复制
SQL 配置文件和 SQL 计划基线之间不存在严格的关系。如果语句在 SQL 计划基线中有多个计划，则 SQL 配置文件非常有用，因为它使优化程序能够在基线中选择成本最低的计划。
如果有必要，SQL Tuning Advisor 会推荐使用 SQL Profile 文件。你可以使用 DBMS_SQLTUNE 来执行、更改、删除和传输 SQL 配置文件。
使用 DBMS_SQLTUNE 包
要使用 SQL PROFILE API，您需要 CREATE ANY SQL_PROFILE、DROP ANY SQL_PROFILE 和 ALTER ANY SQL_PROFILE 系统权限。
DBMS_SQLTUNE.CREATE_TUNING_TASK
--Syntax SQL text format: DBMS_SQLTUNE.CREATE_TUNING_TASK ( sql_text IN CLOB, bind_list IN sql_binds := NULL, user_name IN VARCHAR2 := NULL, scope IN VARCHAR2 := SCOPE_COMPREHENSIVE, time_limit IN NUMBER := TIME_LIMIT_DEFAULT, task_name IN VARCHAR2 := NULL, description IN VARCHAR2 := NULL, con_name IN VARCHAR2 := NULL, database_link_to IN VARCHAR2 := NULL) RETURN VARCHAR2; --sqlid DBMS_SQLTUNE.CREATE_TUNING_TASK ( sql_id IN VARCHAR2, plan_hash_value IN NUMBER := NULL, scope IN VARCHAR2 := SCOPE_COMPREHENSIVE, time_limit IN NUMBER := TIME_LIMIT_DEFAULT, task_name IN VARCHAR2 := NULL, description IN VARCHAR2 := NULL, con_name IN VARCHAR2 := NULL, database_link_to IN VARCHAR2 := NULL) RETURN VARCHAR2; --AWR DBMS_SQLTUNE.CREATE_TUNING_TASK ( begin_snap IN NUMBER, end_snap IN NUMBER, sql_id IN VARCHAR2, plan_hash_value IN NUMBER := NULL, scope IN VARCHAR2 := SCOPE_COMPREHENSIVE, time_limit IN NUMBER := TIME_LIMIT_DEFAULT, task_name IN VARCHAR2 := NULL, description IN VARCHAR2 := NULL, con_name IN VARCHAR2 := NULL, dbid IN NUMBER := NULL, database_link_to IN VARCHAR2 := NULL) RETURN VARCHAR2; --SQL tuning set format --SQL Performance Analyzer format: --https://docs.oracle.com/en/database/oracle/oracle-database/23/arpls/DBMS_SQLTUNE.html#GUID-02C23DDE-6304-49E4-9DFC-EA39D44F943C复制
1) 接受 sql profile 使用 DBMS_SQLTUNE.ACCEPT_SQL_PROFILE 接受 SQL 优化顾问建议的 SQL 配置文件的过程。这将在数据库中创建和存储 SQL 配置文件。 DECLARE my_sqlprofile_name VARCHAR2(30); BEGIN my_sqlprofile_name := DBMS_SQLTUNE.ACCEPT_SQL_PROFILE ( task_name => 'my_sql_tuning_task', name => 'my_sql_profile'); END; / --my_sql_tuning_task 是 SQL 调优任务的名称。可以在 DBA_SQL_PROFILES 视图中查看有关 SQL 配置文件的信息。 2) 更改 sql profile 使用 ALTER_SQL_PROFILE 过程更改现有 SQL 配置文件的 STATUS、NAME、DESCRIPTION 和 CATEGORY 属性。 BEGIN DBMS_SQLTUNE.ALTER_SQL_PROFILE( name => 'my_sql_profile', attribute_name => 'STATUS', value => 'DISABLED'); END; / 3) 删除 sql profile 可以使用 DROP_SQL_PROFILE 过程删除 SQL profile。 begin DBMS_SQLTUNE.DROP_SQL_PROFILE(name => 'my_sql_profile'); end; /复制
SQL PROFILE 操作示例
JIEKEXU 普通用户创建表、插入数据、创建索引和收集统计信息
执行带有 no_index 提示的查询，查看执行计划使用全表扫描
drop table test; create table test (n number ); declare begin for i in 1 .. 10000 loop insert into test values(i); commit; end loop; end; / create index test_idx on test(n); exec dbms_stats.gather_table_stats('','TEST'); SQL>select /*+ no_index(test test_idx) */ * from test where n=1; N ---------- 1 Execution Plan ---------------------------------------------------------- Plan hash value: 1357081020 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 4 | 4 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| TEST | 1 | 4 | 4 (0)| 00:00:01 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("N"=1)复制
使用 SYS 用户创建并执行优化任务，并运行报告优化任务，
接受建议的 SQL 配置文件。
declare my_task_name VARCHAR2(30); my_sqltext CLOB; begin my_sqltext := 'select /*+ no_index(test test_idx) */ * from test where n=1'; my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK( sql_text => my_sqltext, user_name => 'JIEKEXU', scope => 'COMPREHENSIVE', time_limit => 60, task_name => 'my_sql_tuning_task_2', description => 'Task to tune a query on a specified table'); end; / --scope: 取值 LIMITED： SQL Tuning Advisor 根据统计检查、访问路径分析和 SQL 结构分析生成建议。不生成 SQL 配置文件建议。取值 COMPREHENSIVE： SQL Tuning Advisor 执行其在有限范围内执行的所有分析以及 SQL 配置文件分析。 --time_limit: 指定调整会话的最长持续时间（以秒为单位）。 --执行任务 begin DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'my_sql_tuning_task_2'); end; / --查看报告 set long 10000 set longchunksize 1000 set linesize 100 set heading off SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'my_sql_tuning_task_2') from DUAL; set heading on GENERAL INFORMATION SECTION ------------------------------------------------------------------------------- Tuning Task Name : my_sql_tuning_task_2 Tuning Task Owner : SYS Workload Type : Single SQL Statement Scope : COMPREHENSIVE Time Limit(seconds): 60 Completion Status : COMPLETED Started at : 03/28/2025 16:47:00 Completed at : 03/28/2025 16:47:04 ------------------------------------------------------------------------------- Schema Name: JIEKEXU SQL ID : apxdba96mcfqt SQL Text : select /*+ no_index(test test_idx) */ * from test where n=1 ------------------------------------------------------------------------------- FINDINGS SECTION (1 finding) ------------------------------------------------------------------------------- 1- SQL Profile Finding (see explain plans section below) -------------------------------------------------------- A potentially better execution plan was found for this statement. Recommendation (estimated benefit: 90.9%) ----------------------------------------- - Consider accepting the recommended SQL profile. execute dbms_sqltune.accept_sql_profile(task_name => 'my_sql_tuning_task_2', task_owner => 'SYS', replace => TRUE); Validation results ------------------ The SQL profile was tested by executing both its plan and the original plan and measuring their respective execution statistics. A plan may have been only partially executed if the other could be run to completion in less time. Original Plan With SQL Profile % Improved ------------- ---------------- ---------- Completion Status: COMPLETE COMPLETE Elapsed Time (s): .000182 .000019 89.56 % CPU Time (s): .00017 .000014 91.76 % User I/O Time (s): 0 0 Buffer Gets: 22 2 90.9 % Physical Read Requests: 0 0 Physical Write Requests: 0 0 Physical Read Bytes: 0 0 Physical Write Bytes: 0 0 Rows Processed: 1 1 Fetches: 1 1 Executions: 1 1 Notes ----- 1. Statistics for the original plan were averaged over 10 executions. 2. Statistics for the SQL profile plan were averaged over 10 executions. ------------------------------------------------------------------------------- EXPLAIN PLANS SECTION ------------------------------------------------------------------------------- 1- Original With Adjusted Cost ------------------------------ Plan hash value: 1357081020 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 4 | 4 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| TEST | 1 | 4 | 4 (0)| 00:00:01 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("N"=1) Hint Report (identified by operation id / Query Block Name / Object Alias): Total hints for statement: 3 (U - Unused (3)) --------------------------------------------------------------------------- 0 - STATEMENT U - IGNORE_OPTIM_EMBEDDED_HINTS / hint overridden by another in parent query block U - OPTIMIZER_FEATURES_ENABLE(default) / hint overridden by another in parent query block 1 - SEL$1 / TEST@SEL$1 U - no_index(test test_idx) / rejected by IGNORE_OPTIM_EMBEDDED_HINTS 2- Using SQL Profile -------------------- Plan hash value: 2882402178 ----------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 4 | 1 (0)| 00:00:01 | |* 1 | INDEX RANGE SCAN| TEST_IDX | 1 | 4 | 1 (0)| 00:00:01 | ----------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("N"=1) Hint Report (identified by operation id / Query Block Name / Object Alias): Total hints for statement: 1 (U - Unused (1)) --------------------------------------------------------------------------- 1 - SEL$1 / TEST@SEL$1 U - no_index(test test_idx) / rejected by IGNORE_OPTIM_EMBEDDED_HINTS ------------------------------------------------------------------------------- --接受 SQL Profile 建议 DECLARE my_sqlprofile_name VARCHAR2(30); begin my_sqlprofile_name := DBMS_SQLTUNE.ACCEPT_SQL_PROFILE ( task_name => 'my_sql_tuning_task_2', name => 'my_sql_profile'); end; /复制
使用普通用户再次运行查询,即使有 no_index 提示，也使用索引。
注意：在 Explain Plan Note 中，我们看到 “SQL profile “my_sql_profile” ” 用于此语句。
SQL>set autot on SQL>select /*+ no_index(test test_idx) */ * from test where n=1; N ---------- 1 Execution Plan ---------------------------------------------------------- Plan hash value: 2882402178 ----------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 4 | 1 (0)| 00:00:01 | |* 1 | INDEX RANGE SCAN| TEST_IDX | 1 | 4 | 1 (0)| 00:00:01 | ----------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("N"=1) Hint Report (identified by operation id / Query Block Name / Object Alias): Total hints for statement: 1 (U - Unused (1)) --------------------------------------------------------------------------- 1 - SEL$1 / TEST@SEL$1 U - no_index(test test_idx) / rejected by IGNORE_OPTIM_EMBEDDED_HINTS Note ----- - SQL profile "my_sql_profile" used for this statement复制
如何生成每个优化集的报告？
SELECT 'SELECT d.id , d.owner , d.description , d.created , d.last_modified , d.statement_count, ss.* FROM TABLE(DBMS_SQLTUNE.select_sqlset ('''||name||''')) ss, dba_sqlset d WHERE d.name='''||name||''';' FROM dba_sqlset d ORDER BY d.last_modified DESC;复制
参考链接
https://docs.oracle.com/en/database/oracle/oracle-database/23/tgsql/managing-sql-profiles.html#GUID-EE6AA78D-A7BA-4F27-8BE4-FAE4E1C7499C https://docs.oracle.com/en/database/oracle/oracle-database/23/arpls/DBMS_SQLTUNE.html#GUID-821462BF-1695-41CF-AFF7-FD23E9999C6A复制
全文完，希望可以帮到正在阅读的你，如果觉得有帮助，可以分享给你身边的朋友，同事，你关心谁就分享给谁，一起学习共同进步~~~
❤️ 欢迎关注我的公众号【JiekeXu DBA之路】，一起学习新知识！
——————————————————————————
公众号：JiekeXu DBA之路
墨天轮：https://www.modb.pro/u/4347
CSDN ：https://blog.csdn.net/JiekeXu
ITPUB：https://blog.itpub.net/69968215
腾讯云：https://cloud.tencent.com/developer/user/5645107
——————————————————————————
最后修改时间：2025-03-31 09:38:39
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
评论
怀念和想念
10小时前
评论
0Oracle SQL Profile：优化查询性能的神奇“魔法棒”
10小时前
点赞 评论
...
1天前
评论
0Oracle SQL Profile：优化查询性能的神奇“魔法棒”
1天前
点赞 评论
luyingjun
1天前
评论
0Oracle SQL Profile：优化查询性能的神奇“魔法棒”
1天前
点赞 评论
鲁鲁
1天前
评论
0Oracle SQL Profile：优化查询性能的神奇“魔法棒”
1天前
点赞 评论
相关阅读
【专家有话说第五期】在不同年龄段，DBA应该怎样规划自己的职业发展？
墨天轮编辑部
1271次阅读
2025-03-13 11:40:53
Oracle RAC ASM 磁盘组满了，无法扩容怎么在线处理？
Lucifer三思而后行
767次阅读
2025-03-17 11:33:53
Oracle+Deepseek+Dify 实现数据库数据实时分析
bicewow
687次阅读
2025-03-06 09:41:49
【ORACLE】ORACLE19C在19.13版本前的一个严重BUG-24761824
DarkAthena
565次阅读
2025-03-04 14:33:31
Oracle避坑指南｜同名表导出难题：如何精准排除指定用户下的表？
szrsu
514次阅读
2025-03-05 00:42:34
2月“墨力原创作者计划”获奖名单公布
墨天轮编辑部
454次阅读
2025-03-13 14:38:19
Ogg23ai高手必看-MySQL Innodb Cluster跟oracle的亲密接触
曹海峰
449次阅读
2025-03-04 21:56:13
【ORACLE】char类型和sql优化器发生的“错误”反应
DarkAthena
405次阅读
2025-03-04 23:05:01
什么，oracle 主机用户被删了？原来是虚惊一场！
Lucifer三思而后行
401次阅读
2025-03-03 21:12:09
Oracle 如何修改 db_unique_name？强迫症福音！
Lucifer三思而后行
341次阅读
2025-03-12 21:27:56
