一个新特性是否值得使用，我们要知其利弊，方可去弊存利。
sql自动调优
从Oracle 11g开始，自动sql调优是一个预置的后台数据库作业，默认每天运行一次。
这个任务检查自动负载资料库(AWR)中资源消耗高的语句。然后它会调用sql调优顾问并为所分析的
每一条sql生成调优建议。
自动sql调优(Automatic SQL Tuning);
SQL调优工具集(SQL tuning sets STS);
SQL调优顾问(SQL Tuning Advisor);
自动数据库诊断监视器(Automatic Database Diagnostic Monitor ADDM)。
sql调优顾问是Oracle自动SQL调优功能的核心。这个定期运行，为awr中消耗大量资源的sql语句生成
调优建议。也可以手工运行sql优化顾问，并提供awr中特定时间段的快照、内存中消耗大量资源的sql，或者
是用户提供的SQL语句作为输入。
确定11g是否使用了自动sql调优作业
14:33:27 SQL>
select t.client_name,t.status,t.consumer_group,t.window_group from dba_autotask_client t;
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 rows selected
Executed in 1.14 seconds
14:33:31 SQL>
select task_name,TO_CHAR(execution_end,'YYYY-MM-DD HH24:MI') from dba_advisor_executions tt
where tt.task_name='SYS_AUTO_SQL_TUNING_TASK'
ORDER BY TT.execution_end;
TASK_NAME TO_CHAR(EXECUTION_END,'YYYY-MM
------------------------------ ------------------------------
SYS_AUTO_SQL_TUNING_TASK 2013-10-05 06:01
SYS_AUTO_SQL_TUNING_TASK 2013-10-06 06:07
SYS_AUTO_SQL_TUNING_TASK 2013-10-07 22:12
SYS_AUTO_SQL_TUNING_TASK 2013-10-08 22:08
SYS_AUTO_SQL_TUNING_TASK 2013-10-09 22:06
SYS_AUTO_SQL_TUNING_TASK 2013-10-10 22:05
SYS_AUTO_SQL_TUNING_TASK 2013-10-11 22:36
SYS_AUTO_SQL_TUNING_TASK 2013-10-12 06:00
SYS_AUTO_SQL_TUNING_TASK 2013-10-13 06:06
29 rows selected
Executed in 0.266 seconds
工作原理：
当在Oracle Database 11g或更高版本中创建一个数据库，Oracle就会自动实现三个自动
维护作业：
自动调优顾问
自动段顾问
自动优化器统计信息收集
这些任务自动配置在维护窗口时段运行。维护窗口时段是专门用来运行这些任务的时间段。
可以使用下面这个查询来查看维护窗口时段的详细信息
14:39:51 SQL>
select m.WINDOW_NAME,to_char(m.WINDOW_NEXT_TIME,'yyyy-mm-dd hh24:mi:ss'),m.SQL_TUNE_ADVISOR,m.OPTIMIZER_STATS,m.SEGMENT_ADVISOR
from dba_autotask_window_clients m;
WINDOW_NAME TO_CHAR(M.WINDOW_NEXT_TIME,'YY SQL_TUNE_ADVISOR OPTIMIZER_STATS SEGMENT_ADVISOR
------------------------------ ------------------------------ ---------------- --------------- ---------------
MONDAY_WINDOW 2013-10-14 22:00:00 ENABLED ENABLED ENABLED
TUESDAY_WINDOW 2013-10-15 22:00:00 ENABLED ENABLED ENABLED
WEDNESDAY_WINDOW 2013-10-16 22:00:00 ENABLED ENABLED ENABLED
THURSDAY_WINDOW 2013-10-17 22:00:00 ENABLED ENABLED ENABLED
FRIDAY_WINDOW 2013-10-18 22:00:00 ENABLED ENABLED ENABLED
SATURDAY_WINDOW 2013-10-19 06:00:00 ENABLED ENABLED ENABLED
SUNDAY_WINDOW 2013-10-20 06:00:00 ENABLED ENABLED ENABLED
7 rows selected
Executed in 0.203 seconds
如下几个视图与自动计划作业相关。
DBA_AUTOTASK_CLIENT 自动作业的统计信息。
DBA_AUTOTASK_CLIENT_HISTORY 作业执行的窗口历史信息。
DBA_AUTOTASK_CLIENT_job 当前运行的自动计划作业
DBA_AUTOTASK_JOB_HISTORY 自动计划作业运行历史
DBA_AUTOTASK_SCHEDULE 接下来的32天内自动任务的执行计划
DBA_AUTOTASK_TASK 与当前和过去的任务相关的信息
DBA_AUTOTASK_OPERATION 自动任务操作
DBA_AUTOTASK_WINDOW_CLIENTS 显示属于MAINTENANCE_WINDOW_GROUP的窗口
如何查看sql自动调优建议
11g r2之后版本
sql>set linesuze 80 pagesize 0 long 10000
sql>select dbms_auto_sqltune.report_auto_tuning_task from dual;
修改sql自动调优特性
我们从report中会发现自动sql调优建议对一条sql语句应用sql概要，调优建议作业的默认行为是不会自动接收sql概要建议。
使用dbms_auto_sqltune.set_auto_tuning_task_parameter过程来修改自动sql调优的默认行为。
启动接收sql概要
begin
dbms_auto_sqltune.set_auto_tuning_task_parameter(
parameter=>'ACCEPT_SQL_PROFILES',value=>'TRUE');
);
END;
/
--查询
SELECT *
FROM DBA_ADVISOR_PARAMETERS
WHERE TASK_NAME='SYS_AUTO_SQL_TUNING_TASK'
AND PARAMETER_NAME='ACCEPT_SQL_PROFILES';
禁用和启用sql自动调优
--禁用
BEGIN
DBMS_AUTO_TASK_ADMIN.disable(
client_name => 'sql tuning advisor',
operation => NULL,
window_name => NULL);
END;
/
--启用
BEGIN
DBMS_AUTO_TASK_ADMIN.enable(
client_name => 'sql tuning advisor',
operation => NULL,
window_name => NULL);
END;
/
可以通过下面查询来报告自动调优作业的状态
select client_name,status,consumer_group
from dba_autotask_client;
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29033984/viewspace-775292/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/29033984/viewspace-775292/
扫一扫
544
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
