Oracle SQL Tuning Advisor调优工具一、概述在Oracle 10g及更高版本中SQL Tuning Advisor是优化SQL性能的核心工具之一。它的任务是分析指定的SQL语句并提出一系列改进建议包括统计信息建议发现缺失或过时的对象统计信息。SQL Profile 建议发现优化器估算错误建议创建 Profile 来修正估算。索引建议建议创建新索引以提升性能。SQL 重构建议建议改写 SQL 语句如去重、改写子查询。二、为什么SQL Tuning Advisor能找到更好的执行计划2.1 工作原理SQL Tuning Advisor将给定的SQL语句委派给自动调优优化器Automatic Tuning Optimizer进行处理。该组件是Oracle查询优化器的一部分但拥有与普通优化器不同的工作模式特性普通优化器Normal Optimizer自动调优优化器Automatic Tuning Optimizer目标在毫秒级内生成执行计划花较长时间秒到分钟级寻找更优计划技术常规成本估算、统计信息假设分析What-if、强化动态采样验证无可实际执行计划中的部分步骤比较估算值与实际值用途日常SQL解析SQL调优顾问的专用引擎关键结论两者职责不同——优化器必须快速响应而调优顾问可以投入更多时间进行深度探索因此有时能找到优化器在第一时刻未能发现的更优执行计划。2.2 输入方式SQL Tuning Advisor的核心接口是DBMS_SQLTUNE程序包它可以接受以下四种形式的SQL输入SQL文本直接提供完整的SQL语句。共享池中的SQL通过指定SQL_ID引用已在共享池中解析的SQL。AWR中的SQL通过指定SQL_ID引用存储在AWR资料库中的历史SQL。SQL调优集SQL Tuning Set一组SQL语句的集合适用于批量调优。三、创建自定义存储过程封装在实际工作中最常用的是通过SQL_ID进行调优。为了简化操作流程我们可以创建一个存储过程。该过程接收SQL_ID自动创建调优任务、执行任务并输出查看报告的命令。脚本说明输入p_sql_id(目标 SQL 的 ID)。输出调优任务名称及查询报告的命令。-- 以 SYS 用户运行CREATEORREPLACEPROCEDUREp_create_sqltuning_task(p_sql_id VARCHAR2)ISv_tuning_task VARCHAR2(30);v_sql_id v$session.sql_id%TYPE;BEGINv_sql_id :p_sql_id;-- 1. 创建调优任务v_tuning_task :DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_idv_sql_id);-- 2. 执行调优任务DBMS_SQLTUNE.EXECUTE_TUNING_TASK(v_tuning_task);-- 3. 输出任务名称和查看报告的SQL命令DBMS_OUTPUT.PUT_LINE(This Tuning task name is : ||v_tuning_task);DBMS_OUTPUT.PUT_LINE(-------------Please using follow command query SQL tuning report!------------);DBMS_OUTPUT.PUT_LINE(set linesize 200 pagesize 9999);DBMS_OUTPUT.PUT_LINE(set long 100000);DBMS_OUTPUT.PUT_LINE(select dbms_sqltune.report_tuning_task(||v_tuning_task||) from dual;);END;/四、案例1、准备工作-- 准备表和数据 SQL create table tab_1 as select * from dba_objects; Table created. SQL insert into tab_1 select * from tab_1; 72357 rows created. SQL commit; Commit complete. -- 收集统计信息 BEGIN DBMS_STATS.GATHER_TABLE_STATS(OWNNAMEdbmon, TABNAMEtab_1, ESTIMATE_PERCENT1, METHOD_OPTFOR ALL COLUMNS SIZE 1, NO_INVALIDATEFALSE, CASCADETRUE, DEGREE 1); END; / set linesize 200 pagesize 999 col owner format a20 col object_Name format a20 col sql_text format a40 -- 发起待调优的SQL语句 SELECT /* sql_tuning_test */ object_id,object_name,owner FROM tab_1 WHERE object_id50; OBJECT_ID OBJECT_NAME OWNER ---------- -------------------- -------------------- 50 I_COL3 SYS 50 I_COL3 SYS -- 查询SQL语句的SQL_ID SELECT sql_text, sql_id, hash_value, child_number FROM v$sql WHERE sql_text LIKE %sql_tuning_test% AND sql_text NOT LIKE %v$sql%; SQL_TEXT SQL_ID HASH_VALUE CHILD_NUMBER ---------------------------------------- ------------- ---------- ------------ SELECT /* sql_tuning_test */ object_id,o 58x341t6ddxzk 1289156594 0 bject_name,owner FROM tab_1 WHERE object _id502、执行调优任务假设我们有一个SQL_ID为58x341t6ddxzk的SQL语句调用存储过程来启动调优任务-- 执行调优任务setserveroutputonEXECp_create_sqltuning_task(58x341t6ddxzk);-- 输出内容-------------Please using follow command query SQL tuning report!------------setlinesize200pagesize9999setlong100000selectdbms_sqltune.report_tuning_task(TASK_13)fromdual;3、查看生成的调优报告按照存储过程输出的提示执行以下命令查看详细的调优报告setlinesize200pagesize9999setlong100000selectdbms_sqltune.report_tuning_task(TASK_13)fromdual;4、调优报告分析下面我们逐段分析这份报告的内容。第一部分基本信息DBMS_SQLTUNE.REPORT_TUNING_TASK(TASK_13) -------------------------------------------------------------------------------- GENERAL INFORMATION SECTION ------------------------------------------------------------------------------- Tuning Task Name : TASK_13 Tuning Task Owner : SYS Workload Type : Single SQL Statement Scope : COMPREHENSIVE Time Limit(seconds): 1800 Completion Status : COMPLETED Started at : 08/14/2026 16:43:58 Completed at : 08/14/2026 16:43:58 ------------------------------------------------------------------------------- Schema Name : DBMON Container Name: PDB1 SQL ID : 58x341t6ddxzk SQL Text : SELECT /* sql_tuning_test */ object_id,object_name,owner FROM tab_1 WHERE object_id50信息解读调优任务在1秒内完成16:43:58 - 6:43:58远低于1800秒的时间限制。目标SQL的具体语句。第二部分发现的问题与建议共1个发现1建议创建索引FINDINGS SECTION (1 finding) ------------------------------------------------------------------------------- 1- Index Finding (see explain plans section below) -------------------------------------------------- The execution plan of this statement can be improved by creating one or more indices. Recommendation (estimated benefit: 99.62%) ------------------------------------------ - Consider running the Access Advisor to improve the physical schema design or creating the recommended index. create index DBMON.IDX$$_000D0001 on DBMON.TAB_1(OBJECT_ID,OBJECT_NAME, OWNER); Rationale --------- Creating the recommended indices significantly improves the execution plan of this statement. However, it might be preferable to run Access Advisor using a representative SQL workload as opposed to a single statement. This will allow to get comprehensive index recommendations which takes into account index maintenance overhead and additional space consumption.解读调优顾问发现创建索引可以将响应时间提升99.62%。建议创建复合索引三列组合 ——OBJECT_ID、OBJECT_NAME、OWNER报告特别指出虽然为单条 SQL 创建索引能显著改善执行计划但更推荐的做法是使用Access Advisor基于一个有代表性的 SQL 工作负载来获取综合性的索引建议。这是因为单条 SQL 的索引建议可能与其他 SQL 的需求冲突。Access Advisor会综合考虑索引维护开销DML 时的额外写入和存储空间消耗给出全局最优方案。第三部分执行计划对比原始执行计划1- Original ----------- Plan hash value: 2157271952 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 2 | 90 | 795 (1)| 00:00:01 | |* 1 | TABLE ACCESS FULL| TAB_1 | 2 | 90 | 795 (1)| 00:00:01 | --------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(OBJECT_ID50)问题诊断执行了全表扫描TABLE ACCESS FULL代价795。创建索引后的执行计划2- Using New Indices -------------------- Plan hash value: 1688321801 ----------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 2 | 90 | 3 (0)| 00:00:01 | |* 1 | INDEX RANGE SCAN| IDX$$_000D0001 | 2 | 90 | 3 (0)| 00:00:01 | ----------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access(OBJECT_ID50) -------------------------------------------------------------------------------对比分析执行索引范围扫描代价3。5、清理调优任务-- 调优完成后及时删除任务EXECDBMS_SQLTUNE.DROP_TUNING_TASK(TASK_13); Oracle SQL Tuning Advisor调优工具
Oracle SQL Tuning Advisor调优工具一、概述在Oracle 10g及更高版本中SQL Tuning Advisor是优化SQL性能的核心工具之一。它的任务是分析指定的SQL语句并提出一系列改进建议包括统计信息建议发现缺失或过时的对象统计信息。SQL Profile 建议发现优化器估算错误建议创建 Profile 来修正估算。索引建议建议创建新索引以提升性能。SQL 重构建议建议改写 SQL 语句如去重、改写子查询。二、为什么SQL Tuning Advisor能找到更好的执行计划2.1 工作原理SQL Tuning Advisor将给定的SQL语句委派给自动调优优化器Automatic Tuning Optimizer进行处理。该组件是Oracle查询优化器的一部分但拥有与普通优化器不同的工作模式特性普通优化器Normal Optimizer自动调优优化器Automatic Tuning Optimizer目标在毫秒级内生成执行计划花较长时间秒到分钟级寻找更优计划技术常规成本估算、统计信息假设分析What-if、强化动态采样验证无可实际执行计划中的部分步骤比较估算值与实际值用途日常SQL解析SQL调优顾问的专用引擎关键结论两者职责不同——优化器必须快速响应而调优顾问可以投入更多时间进行深度探索因此有时能找到优化器在第一时刻未能发现的更优执行计划。2.2 输入方式SQL Tuning Advisor的核心接口是DBMS_SQLTUNE程序包它可以接受以下四种形式的SQL输入SQL文本直接提供完整的SQL语句。共享池中的SQL通过指定SQL_ID引用已在共享池中解析的SQL。AWR中的SQL通过指定SQL_ID引用存储在AWR资料库中的历史SQL。SQL调优集SQL Tuning Set一组SQL语句的集合适用于批量调优。三、创建自定义存储过程封装在实际工作中最常用的是通过SQL_ID进行调优。为了简化操作流程我们可以创建一个存储过程。该过程接收SQL_ID自动创建调优任务、执行任务并输出查看报告的命令。脚本说明输入p_sql_id(目标 SQL 的 ID)。输出调优任务名称及查询报告的命令。-- 以 SYS 用户运行CREATEORREPLACEPROCEDUREp_create_sqltuning_task(p_sql_id VARCHAR2)ISv_tuning_task VARCHAR2(30);v_sql_id v$session.sql_id%TYPE;BEGINv_sql_id :p_sql_id;-- 1. 创建调优任务v_tuning_task :DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_idv_sql_id);-- 2. 执行调优任务DBMS_SQLTUNE.EXECUTE_TUNING_TASK(v_tuning_task);-- 3. 输出任务名称和查看报告的SQL命令DBMS_OUTPUT.PUT_LINE(This Tuning task name is : ||v_tuning_task);DBMS_OUTPUT.PUT_LINE(-------------Please using follow command query SQL tuning report!------------);DBMS_OUTPUT.PUT_LINE(set linesize 200 pagesize 9999);DBMS_OUTPUT.PUT_LINE(set long 100000);DBMS_OUTPUT.PUT_LINE(select dbms_sqltune.report_tuning_task(||v_tuning_task||) from dual;);END;/四、案例1、准备工作-- 准备表和数据 SQL create table tab_1 as select * from dba_objects; Table created. SQL insert into tab_1 select * from tab_1; 72357 rows created. SQL commit; Commit complete. -- 收集统计信息 BEGIN DBMS_STATS.GATHER_TABLE_STATS(OWNNAMEdbmon, TABNAMEtab_1, ESTIMATE_PERCENT1, METHOD_OPTFOR ALL COLUMNS SIZE 1, NO_INVALIDATEFALSE, CASCADETRUE, DEGREE 1); END; / set linesize 200 pagesize 999 col owner format a20 col object_Name format a20 col sql_text format a40 -- 发起待调优的SQL语句 SELECT /* sql_tuning_test */ object_id,object_name,owner FROM tab_1 WHERE object_id50; OBJECT_ID OBJECT_NAME OWNER ---------- -------------------- -------------------- 50 I_COL3 SYS 50 I_COL3 SYS -- 查询SQL语句的SQL_ID SELECT sql_text, sql_id, hash_value, child_number FROM v$sql WHERE sql_text LIKE %sql_tuning_test% AND sql_text NOT LIKE %v$sql%; SQL_TEXT SQL_ID HASH_VALUE CHILD_NUMBER ---------------------------------------- ------------- ---------- ------------ SELECT /* sql_tuning_test */ object_id,o 58x341t6ddxzk 1289156594 0 bject_name,owner FROM tab_1 WHERE object _id502、执行调优任务假设我们有一个SQL_ID为58x341t6ddxzk的SQL语句调用存储过程来启动调优任务-- 执行调优任务setserveroutputonEXECp_create_sqltuning_task(58x341t6ddxzk);-- 输出内容-------------Please using follow command query SQL tuning report!------------setlinesize200pagesize9999setlong100000selectdbms_sqltune.report_tuning_task(TASK_13)fromdual;3、查看生成的调优报告按照存储过程输出的提示执行以下命令查看详细的调优报告setlinesize200pagesize9999setlong100000selectdbms_sqltune.report_tuning_task(TASK_13)fromdual;4、调优报告分析下面我们逐段分析这份报告的内容。第一部分基本信息DBMS_SQLTUNE.REPORT_TUNING_TASK(TASK_13) -------------------------------------------------------------------------------- GENERAL INFORMATION SECTION ------------------------------------------------------------------------------- Tuning Task Name : TASK_13 Tuning Task Owner : SYS Workload Type : Single SQL Statement Scope : COMPREHENSIVE Time Limit(seconds): 1800 Completion Status : COMPLETED Started at : 08/14/2026 16:43:58 Completed at : 08/14/2026 16:43:58 ------------------------------------------------------------------------------- Schema Name : DBMON Container Name: PDB1 SQL ID : 58x341t6ddxzk SQL Text : SELECT /* sql_tuning_test */ object_id,object_name,owner FROM tab_1 WHERE object_id50信息解读调优任务在1秒内完成16:43:58 - 6:43:58远低于1800秒的时间限制。目标SQL的具体语句。第二部分发现的问题与建议共1个发现1建议创建索引FINDINGS SECTION (1 finding) ------------------------------------------------------------------------------- 1- Index Finding (see explain plans section below) -------------------------------------------------- The execution plan of this statement can be improved by creating one or more indices. Recommendation (estimated benefit: 99.62%) ------------------------------------------ - Consider running the Access Advisor to improve the physical schema design or creating the recommended index. create index DBMON.IDX$$_000D0001 on DBMON.TAB_1(OBJECT_ID,OBJECT_NAME, OWNER); Rationale --------- Creating the recommended indices significantly improves the execution plan of this statement. However, it might be preferable to run Access Advisor using a representative SQL workload as opposed to a single statement. This will allow to get comprehensive index recommendations which takes into account index maintenance overhead and additional space consumption.解读调优顾问发现创建索引可以将响应时间提升99.62%。建议创建复合索引三列组合 ——OBJECT_ID、OBJECT_NAME、OWNER报告特别指出虽然为单条 SQL 创建索引能显著改善执行计划但更推荐的做法是使用Access Advisor基于一个有代表性的 SQL 工作负载来获取综合性的索引建议。这是因为单条 SQL 的索引建议可能与其他 SQL 的需求冲突。Access Advisor会综合考虑索引维护开销DML 时的额外写入和存储空间消耗给出全局最优方案。第三部分执行计划对比原始执行计划1- Original ----------- Plan hash value: 2157271952 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 2 | 90 | 795 (1)| 00:00:01 | |* 1 | TABLE ACCESS FULL| TAB_1 | 2 | 90 | 795 (1)| 00:00:01 | --------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(OBJECT_ID50)问题诊断执行了全表扫描TABLE ACCESS FULL代价795。创建索引后的执行计划2- Using New Indices -------------------- Plan hash value: 1688321801 ----------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 2 | 90 | 3 (0)| 00:00:01 | |* 1 | INDEX RANGE SCAN| IDX$$_000D0001 | 2 | 90 | 3 (0)| 00:00:01 | ----------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access(OBJECT_ID50) -------------------------------------------------------------------------------对比分析执行索引范围扫描代价3。5、清理调优任务-- 调优完成后及时删除任务EXECDBMS_SQLTUNE.DROP_TUNING_TASK(TASK_13);