ITPub博客

首页 > Linux操作系统 > Linux操作系统 > SQL TUNING ADVISOR

SQL TUNING ADVISOR

Linux操作系统 作者:xiaoyan5686670 时间:2015-11-29 18:56:47 0 删除 编辑
Normal 0 7.8 磅 0 2 false false false EN-US ZH-CN X-NONE</w:LidThemeComplexScript. MicrosoftInternetExplorer4 <style. /* Style. Definitions */ table.MsoNormalTable {mso-style-name:普通表格; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-priority:99; mso-style-parent:""; mso-padding-alt:0cm 5.4pt 0cm 5.4pt; mso-para-margin:0cm; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman","serif";} </style.

SQL TUNING ADVISOR

一个新特性是否值得使用,我们要知其利弊,方可去弊存利。

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/22569416/viewspace-1849170/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2012-07-25

  • 博文量
    108
  • 访问量
    196794