1.STA简介
SQL tuning的基本步骤
a、鉴别需要调整的高负载SQL或者Top SQL
b、寻找可改进的执行计划
c、实施能够改进的执行计划以提高SQL效率
如何tuning SQL
a、检查是否为优化器设置了合理的参数(optimizer_mode,optimizer_index_caching,optimizer_index_cost_adj,以及相关cache size)
b、检查SQL语句所涉及的对象是否存在过时的统计信息或者倾斜列是否缺少直方图等
c、通过添加提示来引导SQL语句使用正确的访问路径，以及连接方式等
d、重构等价的SQL语句以使得SQL更高效(如最小化基表及中间结果集，避免列运算，列上的函数，null值，不等运算使得索引失效)
e、添加合理的索引或物化视图以及移除冗余索引,分散I/O等
Automatic Tuning Optimizer 做什么?
a、分析统计信息
优化器执行计划产生期间记录当前SQL语句涉及对象的统计信息的类型以及哪些被使用或哪些是需要的当统计信息记录完成后自动调整优化器会比对与查询相关的这些对象的统计信息是否可用或过时或非均衡列缺少直方图等针对上述的操作之后得到哪些对象没有统计信息以及哪些对象缺少统计信息以及额外的统计信息用于生成report
b、分析访问路径
优化器会分析当前SQL所使用的访问路径是否合理，也就是分析基于表的访问方式，如全表扫描，索引扫描等自动调整优化器会基于谓词尝试假设性的推断来创建合理的索引，也就是建议通过添加或修改相应的索引来提高性能
c、SQL结构分析
优化器会建议对于一些具有较大影响的SQL语句作结构性调整及转换(基于内部规则)，如未嵌套的子查询，重写物化视图，视图合并等基于语法以及语义结构的分析与调整，如谓词列上的运算，UNION与UNION ALL的使用，NOT IN, NOT EXIST之间替换等对中间结果集以及连接方式等实现一些预估的分析
d、SQL profiling
SQL profiling 内置于优化器，就是一个剖析工具，基于上述得到的信息对当前的SQL进行剖析，以检查出导致性能糟糕的故障点所有上述分析得到的结果以及辅助信息最后以sql profile的形式表现出来，供用户来判断是否接受当用户接受这些profile，下次处于normal模式时，相同的sql语句会使用这个profile
可以对profile进行启用，停用，以及修改，因此即使表发生较大的变化，profile依旧能使得SQL受益
STA可tuning的方式
STA提供OEM图形界面以及API方式进行tuning，本文主要描述API即dbms_sqltune.create_tuning_task方式，下面是可被create_tuning_task接受的API方式
a、直接提供SQL语句文本
b、引用共享池中的SQL语句(sql_id)
c、引用awr自动工作负载中的SQL语句(sql_id)
d、建议SQL调优集(批量tuning)
2. 检查sql调优是否打开
set line 200
set pagesize 1000
col client_name for a40
col status for a10
col consumer_group for a40
col window_group for a30
select client_name,status,consumer_group,window_group from dba_autotask_client order by client_name;
CLIENT_NAME STATUS CONSUMER_GROUP WINDOW_GROUP
---------------------------------------- ---------- ---------------------------------------- ------------------------------
auto optimizer stats collection ENABLED ORA$AUTOTASK_STATS_GROUP ORA$AT_WGRP_OS
auto space advisor ENABLED ORA$AUTOTASK_SPACE_GROUP ORA$AT_WGRP_SA
sql tuning advisor ENABLED ORA$AUTOTASK_SQL_GROUP ORA$AT_WGRP_SQ
3. 启用和禁用自动SQL优化：
启动sql自动优化
exec dbms_auto_task_admin.enable(client_name => 'sql tuning advisor',operation =>NULL, window_name => NULL);
SQL> exec dbms_auto_task_admin.enable(client_name => 'sql tuning advisor',operation =>NULL, window_name => NULL);
PL/SQL procedure successfully completed.
SQL> select client_name,status,consumer_group,window_group from dba_autotask_client order by client_name;
CLIENT_NAME STATUS CONSUMER_GROUP WINDOW_GROUP
---------------------------------------- ---------- ---------------------------------------- ------------------------------
auto optimizer stats collection ENABLED ORA$AUTOTASK_STATS_GROUP ORA$AT_WGRP_OS
auto space advisor ENABLED ORA$AUTOTASK_SPACE_GROUP ORA$AT_WGRP_SA
sql tuning advisor ENABLED ORA$AUTOTASK_SQL_GROUP ORA$AT_WGRP_SQ
SQL>
关闭sql自动优化
exec dbms_auto_task_admin.disable(client_name => 'sql tuning advisor', operation=> NULL,window_name => NULL);
SQL> exec dbms_auto_task_admin.disable(client_name => 'sql tuning advisor', operation=> NULL,window_name => NULL);
PL/SQL procedure successfully completed.
SQL> select client_name,status,consumer_group,window_group from dba_autotask_client order by client_name;
CLIENT_NAME STATUS CONSUMER_GROUP WINDOW_GROUP
---------------------------------------- ---------- ---------------------------------------- ------------------------------
auto optimizer stats collection ENABLED ORA$AUTOTASK_STATS_GROUP ORA$AT_WGRP_OS
auto space advisor ENABLED ORA$AUTOTASK_SPACE_GROUP ORA$AT_WGRP_SA
sql tuning advisor DISABLED ORA$AUTOTASK_SQL_GROUP ORA$AT_WGRP_SQ
4.配置自动SQL优化：
exec dbms_sqltune.set_tuning_task_parameter (task_name =>'SYS_AUTO_SQL_TUNING_TASK', parameter => 'ACCEPT_SQL_PROFILES', value => 'TRUE');
以下是控制自动SQL优化任务的dbms_sqltune选项：
INTERRUPT_TUNING_TASK：中断正在执行的任务，得到中间结果并正常退出。
RESUME_TUNING_TASK：恢复先前被中断的任务。
CANCEL_TUNING_TASK：取消正在执行的任务，清除任务的所有结果。
RESET_TUNING_TASK：重置正在执行的任务，清除任务的所有结果并返回到其初始状态。
DROP_TUNING_TASK：删除一个任务，清除所有与该任务相关的结果。
4.查看SQL调优顾问最近几次的运行情况
set line 200
set pagesize 1000
col task_name for a30
col status for a15
col execution for a40
select task_name,status,to_char(execution_end,'DD-MON-YY HH24:MI') execution from
dba_advisor_executions where task_name='SYS_AUTO_SQL_TUNING_TASK' order by
execution_end;
5.查看SQL自动调优建议
set linesize 3000 PAGESIZE 0 LONG 100000
select DBMS_SQLTUNE.REPORT_AUTO_TUNING_TASK FROM DUAL;
6.生成SQL调优脚本
根据SQL建议，可以生成相应的调优的SQL语句脚本。
select DBMS_SQLTUNE.SCRIPT_TUNING_TASK('SYS_AUTO_SQL_TUNING_TASK') from dual;
--查看系统自动优化建议(报告前一天的)，'SYS_AUTO_SQL_TUNING_TASK'是系统自定义的
select dbms_sqltune.report_tuning_task('SYS_AUTO_SQL_TUNING_TASK') from dual;
--查看一个范围内的报告，begin_exec和end_exec取自execution_name 字段的值
select dbms_sqltune.report_auto_tuning_task(begin_exec => 'EXEC_5815',end_exec => 'EXEC_6166') from dual;
--查看execution_name的调优报告
select dbms_sqltune.report_tuning_task(task_name => 'SYS_AUTO_SQL_TUNING_TASK', execution_name => 'EXEC_6166') from dual;
--查看某个对象的调优报告，取object_id 的值
select dbms_sqltune.report_auto_tuning_task(object_id => '33075') from dual;
执行过程
检查书库版本
select * from v$version where rownum<2;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
创建示例表
create table t1 as select * from dba_objects;
收集统计信息
execute dbms_stats.gather_table_stats(ownname => 'SCOTT', tabname => 'T1',estimate_percent => 100);
查看执行计划
explain plan for SELECT object_id,object_name FROM t1 WHERE object_id = 1000;
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 30 | 223 (1)| 00:00:03 |
|* 1 | TABLE ACCESS FULL| T1 | 1 | 30 | 223 (1)| 00:00:03 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
--------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
1 - filter("OBJECT_ID"=1000)
13 rows selected.
下面就用DBMS_SQLTUNE优化该SQL
如无权限，需要赋予用户ADVISOR权限
grant ADVISOR to scott;
创建sql tuning任务
DECLARE
huo_task_name VARCHAR2(30);
huo_sqltext CLOB;
BEGIN
huo_sqltext := 'SELECT object_id,object_name FROM t1 WHERE object_id = 1000';
huo_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_text => huo_sqltext,
scope => 'COMPREHENSIVE',
time_limit => 120,
task_name => 'huo_sql_tuning',
description => 'Task to tune a query on table t1');
END;
/
PL/SQL procedure successfully completed.
参数说明：
bind_list：多个绑定变量以','逗号分隔。参数值一定要根据绑定变量对应的列的类型书写.
如：emp.ename类型是VARCHAR2(10)，那么就要写成 bind_list =>sql_binds(anydata.convertvarchar2(10)),
time_limit：执行的最长时间，默认是60。
scope：
LIMITED，用大概1秒时间去优化SQL语句，但是并不进行SQL Profiling分析。
COMPREHENSIVE，进行全面分析，包含SQL Profiling分析；比LIMITED用时更长。
查看任务
set line 200
set pagesize 100
col owner for a16
col task_name for a30
col status for a12
SELECT OWNER,TASK_ID,TASK_NAME,STATUS FROM DBA_ADVISOR_LOG WHERE TASK_NAME='huo_sql_tuning';
OWNER TASK_ID TASK_NAME STATUS
---------------- ---------- ------------------------------ ------------
SCOTT 366 huo_sql_tuning INITIAL
执行sql tuning任务
BEGIN
DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'huo_sql_tuning' );
END;
/
查看SQL TUNING状态
SELECT status FROM USER_ADVISOR_TASKS WHERE task_name = 'huo_sql_tuning';
STATUS
------------
COMPLETED
展示sql tunning结果
SET LONG 10000
SET LONGCHUNKSIZE 1000
SET LINESIZE 100
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('huo_sql_tuning') FROM DUAL;
DBMS_SQLTUNE.REPORT_TUNING_TASK('HUO_SQL_TUNING')
----------------------------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : huo_sql_tuning
Tuning Task Owner : SCOTT
Workload Type : Single SQL Statement
Scope : COMPREHENSIVE
Time Limit(seconds): 120
Completion Status : COMPLETED
Started at : 06/17/2021 10:48:42
Completed at : 06/17/2021 10:48:42
-------------------------------------------------------------------------------
Schema Name: SCOTT
SQL ID : 5x14tdu9p8n2y
SQL Text : SELECT object_id,object_name FROM t1 WHERE object_id = 1000
-------------------------------------------------------------------------------
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: 98.65%)
------------------------------------------
- Consider running the Access Advisor to improve the physical schema design
or creating the recommended index.
create index SCOTT.IDX$$_016E0001 on SCOTT.T1("OBJECT_ID","OBJECT_NAME");
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.
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
------------------------------------------------------------------------------
1- Original
-----------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 30 | 223 (1)| 00:00:03 |
|* 1 | TABLE ACCESS FULL| T1 | 1 | 30 | 223 (1)| 00:00:03 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=1000)
2- Using New Indices
--------------------
Plan hash value: 3777207039
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 30 | 3 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| IDX$$_016E0001 | 1 | 30 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("OBJECT_ID"=1000)
-------------------------------------------------------------------------------7.完成后删除sql tunning任务
EXEC DBMS_SQLTUNE.DROP_TUNING_TASK('huo_sql_tuning');
8.查看SQL Tuning Advisor的进展（task执行很久）
col opname for a20
col ADVISOR_NAME for a20
SELECT SID,SERIAL#,USERNAME,OPNAME,ADVISOR_NAME,TARGET_DESC,START_TIME SOFAR, TOTALWORK
FROM V$ADVISOR_PROGRESS
WHERE USERNAME = 'SCOTT';
参考：
350
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
