oracle数据库优化-11G自动维护任务autotask

通过自动创建job来自动维护任务。

SQL> select client_name,window_group ,a.status from dba_autotask_client a;
CLIENT_NAME                        WINDOW_GROUP              STATUS
--------------------------------- ------------------------- --------
auto optimizer stats collection     ORA$AT_WGRP_OS           ENABLED
auto space advisor                  ORA$AT_WGRP_SA           DISABLED
sql tuning advisor                  ORA$AT_WGRP_SQ           DISABLED

新的管理包
SQL> desc sys.dbms_auto_task_admin
Element               Type      
--------------------- --------- 
OPTFLG_DEFERRED       CONSTANT  
OPTFLG_IMMEDIATE      CONSTANT  
PRIORITY_MEDIUM       CONSTANT  
PRIORITY_HIGH         CONSTANT  
PRIORITY_URGENT       CONSTANT  
PRIORITY_CLEAR        CONSTANT  
LIGHTWEIGHT           CONSTANT  
HEAVYWEIGHT           CONSTANT  
VOLATILE              CONSTANT  
STABLE                CONSTANT  
SAFE_TO_KILL          CONSTANT  
DO_NOT_KILL           CONSTANT  
ATTRVAL_TRUE          CONSTANT  
ATTRVAL_FALSE         CONSTANT  
GET_P1_RESOURCES      PROCEDURE 
SET_P1_RESOURCES      PROCEDURE 
SET_CLIENT_SERVICE    PROCEDURE 
GET_CLIENT_ATTRIBUTES PROCEDURE 
DISABLE               PROCEDURE 
ENABLE                PROCEDURE 
OVERRIDE_PRIORITY     PROCEDURE 
SET_ATTRIBUTE         PROCEDURE 

SQL> 

窗口组包含的窗口如下:

SQL> select * From dba_scheduler_wingroup_members c where c.WINDOW_GROUP_NAME='ORA$AT_WGRP_SA';
WINDOW_GROUP_NAME              WINDOW_NAME
------------------------------ ------------------------------
ORA$AT_WGRP_SA                 WEEKNIGHT_WINDOW
ORA$AT_WGRP_SA                 WEEKEND_WINDOW
ORA$AT_WGRP_SA                 MONDAY_WINDOW
ORA$AT_WGRP_SA                 TUESDAY_WINDOW
ORA$AT_WGRP_SA                 WEDNESDAY_WINDOW
ORA$AT_WGRP_SA                 THURSDAY_WINDOW
ORA$AT_WGRP_SA                 FRIDAY_WINDOW
ORA$AT_WGRP_SA                 SATURDAY_WINDOW
ORA$AT_WGRP_SA                 SUNDAY_WINDOW
9 rows selected

select  client_name,window_name,to_char(e.WINDOW_START_TIME,'yyyy-mm-dd hh24:mi:ss') stime,job_name,job_status from dba_autotask_job_history e
order by stime desc 

1. ORACLE 11G自动维护任务

相关查询：

 

select window_name,

       a1.RESOURCE_PLAN,

       a1.start_date,

       a1.end_date,

       a1.duration,

       a1.ENABLED

  From dba_scheduler_windows a1;

select a2.CLIENT_NAME, a2.STATUS, a2.CONSUMER_GROUP

  From dba_autotask_client a2;

select a3.WINDOW_name,

       a3.WINDOW_NEXT_TIME,

       a3.AUTOTASK_STATUS,

       a3.OPTIMIZER_STATS,

       a3.SEGMENT_ADVISOR,

       a3.SQL_TUNE_ADVISOR

  From dba_autotask_window_clients a3;

select *

  From dba_rsrc_plan_directives

 where plan = 'DEFAULT_MAINTENANCE_PLAN'

    or plan like '%AUTOTASK%'

 

REASON="Stop job called because associated window was closed"

 

SQL> SELECT CLIENT_NAME,JOB_STATUS,JOB_DURATION FROM DBA_AUTOTASK_JOB_HISTORY WHERE CLIENT_NAME='auto optimizer stats collection';

 

CLIENT_NAME

----------------------------------------------------------------

JOB_STATUS

------------------------------

JOB_DURATION

---------------------------------------------------------------------------

auto optimizer stats collection

SUCCEEDED

+000 02:02:28

1  ORACLE 11G 自动维护任务：

自动维护任务是一种按规则自动启动的数据库维护操作任务。比如自动收集为查询优化器使用的统计信息。自动维护任务按维护窗口自动运

行。所谓自动维护窗口是按照预定义的间隔时间窗口。

oracle11g数据库有三种预定义的自动维护任务：

■ Automatic Optimizer Statistics Collection：

■ Automatic Segment Advisor

■ Automatic SQL Tuning Advisor（这个是oracle 11g新添加的自动维护任务）

缺省情况下，这三个任务配置为在所有维护窗口运行。

维护窗口：维护窗口是一个连续的时间间隔，用于管理自动维护任务所用。维护窗口是oracle 调度窗口，属于窗口组MAINTENANCE_WINDOW_GROUP。

1.2 配置自动维护任务：

1：启用和禁止维护任务：

使用DBMS_AUTO_ADMIN pl/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;

这里client_name参数可以通过数据字典视图DBA_AUTOTASK_CLIENT来查询。

sys@ORCL11> select client_name from dba_autotask_client;

CLIENT_NAME

------------------------------------------------------------

auto optimizer stats collection

auto space advisor

sql tuning advisor

如果想启用或禁用所有窗口自动维护任务，调用ENABLE或DISABLE过程：

EXECUTE DBMS_AUTO_TASK_ADMIN.DISABLE;

1.3 为某一个维护窗口启用或禁用维护TASK

缺省情况下，所有维护任务在所有预定义的维护窗口都运行。可以对某一个维护窗口启用或禁用自动化任务。

BEGIN

dbms_auto_task_admin.disable(

client_name => 'sql tuning advisor', 

operation   => NULL, 

window_name => 'MONDAY_WINDOW');

END;

上面的例子是对维护窗口monday_window进行禁用sql tuning advisor。

1.4 配置维护窗口：

对于当前打开的窗口，你需要首先禁用，然后修改再启用，配置立即生效，如果你不通过这三个过程来修改属性，属性是不会生效的，直到下一次窗口打开。

1.4.1 修改维护窗口

可是使用DBMS_SCHEDULER 包来修改窗口属性。

--先禁用维护窗口

BEGIN

dbms_scheduler.disable(

    name  => 'SATURDAY_WINDOW');

--修改维护窗口属性：

dbms_scheduler.set_attribute(

    name      => 'SATURDAY_WINDOW',

    attribute => 'DURATION',

    value     => numtodsinterval(4, 'hour'));

--启用维护窗口

dbms_scheduler.enable(

    name => 'SATURDAY_WINDOW');

END;

/

1.4.2 创建新窗口：

BEGIN

dbms_scheduler.create_window(

    window_name     => 'EARLY_MORNING_WINDOW',

    duration        =>  numtodsinterval(1, 'hour'),

    resource_plan   => 'DEFAULT_MAINTENANCE_PLAN',

    repeat_interval => 'FREQ=DAILY;BYHOUR=5;BYMINUTE=0;BYSECOND=0');

dbms_scheduler.add_window_group_member(

    group_name  => 'MAINTENANCE_WINDOW_GROUP',

    window_list => 'EARLY_MORNING_WINDOW');

END;

/

1.4.3 删除窗口

BEGIN

DBMS_SCHEDULER.REMOVE_WINDOW_GROUP_MEMBER(

    group_name  => 'MAINTENANCE_WINDOW_GROUP',

    window_list => 'EARLY_MORNING_WINDOW');

END;

/

1.4.4 跟踪job运行情况

可以通过查询视图DBA_AUTOTASK_HISTORY追踪job运行情况：

SQL> select client_name,job_name,job_start_time

     from dba_autotask_job_history;

CLIENT_NAME          JOB_NAME             JOB_START_TIME

-------------------- -------------------- ------------------------------------

auto optimizer stats ORA$AT_OS_OPT_SY_1   09-APR-12 10.00.02.039000 PM +08:00

auto space advisor   ORA$AT_SA_SPC_SY_2   09-APR-12 10.00.02.050000 PM +08:00

sql tuning advisor   ORA$AT_SQ_SQL_SW_3   09-APR-12 10.00.02.015000 PM +08:00

自动sql优化

Oracle 11g引入了自动SQL优化。这本书的许多章节都详细涵盖了这个主题，因此本节只作概述。Oracle 11g根据AWR(Automatic Workload Repository，自动负载信息库)的统计信息针对产生较大性能影响的SQL语句运行SQL 优化顾问(SQL Tuning Advisor)。AWR的统计信息用来生成一个SQL语句列表并根据它们过去一周对系统性能的影响进行排序，这个SQL列表会自动排除所有被断定为不太容易优化的SQL语句，比如并行查询、DML、DDL和任何由并发问题引起的性能问题。SQL优化顾问生成优化SQL的建议，这类建议中可能有SQL配置文件(Profile)(利用统计信息)。当建议使用SQL配置文件的时候，这些配置文件会进行性能测试，如果测试结果显示至少有三倍的改进，并且SQL优化任务参数ACCEPT_SQL_PROFILES设置为true，这个建议就会被接受；如果ACCEPT_SQL_PROFILES被设置为false，这个建议会被报告。您也可以针对任何查询单独执行这些步骤。
通过调用dbms_auto_task_admin程序包的enable和disable过程就可以控制自动SQL优化任务的执行。当启用时，SQL 优化顾问就在定义好的维护时间窗口里运行，默认的维护时间窗口参考下面的表1-2。
表1-2  Oracle 11g默认维护窗口

窗 口 名 称	描    述
MONDAY_WINDOW	星期一晚上10点开始到第二天临晨2点
TUESDAY_WINDOW	星期二晚上10点开始到第二天临晨2点
WEDNESDAY_WINDOW	星期三晚上10点开始到第二天临晨2点
THURSDAY_WINDOW	星期四晚上10点开始到第二天临晨2点
FRIDAY_WINDOW	星期五晚上10点开始到第二天临晨2点
SATURDAY_WINDOW	星期六晚上10点开始并持续20小时
SUNDAY_WINDOW	星期日晚上10点开始并持续20小时

 

 
启用和禁用自动SQL优化：
exec dbms_auto_task_admin.enable(client_name => 'sql tuning advisor',operation =>
NULL, window_name => NULL);
 
exec dbms_auto_task_admin.disable(client_name => 'sql tuning advisor', operation
=> NULL,window_name => NULL);
配置自动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：删除一个任务，清除所有与该任务相关的结果。
第5章将展示如何在企业管理器中通过简单的“点击运行”来使用自动SQL优化功能

可手工激活或禁止

BEGIN

  DBMS_AUTO_TASK_ADMIN.ENABLE(

    client_name => 'sql tuning advisor', 

    peration => NULL, 

    window_name => NULL);

END;

/

BEGIN

  DBMS_AUTO_TASK_ADMIN.DISABLE(

    client_name => 'sql tuning advisor', 

    peration => NULL, 

    window_name => NULL);

END;

/

有一系列参数可供定制，比如是否自动接受profile

Parameter	Description
ACCEPT_SQL_PROFILE	Specifies whether to accept SQL profiles automatically.
EXECUTION_DAYS_TO_EXPIRE	Specifies the number of days for which to save the task history in the advisor framework schema. By default, the task history is saved for 30 days before it expires.
MAX_SQL_PROFILES_PER_EXEC	Specifies the limit of SQL profiles that are accepted for each automatic SQL tuning task. Consider setting the limit of SQL profiles that are accepted for each automatic SQL tuning task based on the acceptable level of changes that can be made to the system on a daily basis.
MAX_AUTO_SQL_PROFILES	Specifies the limit of SQL profiles that are accepted in total.

BEGIN

  DBMS_AUTO_SQLTUNE.SET_AUTO_TUNING_TASK_PARAMETER(

    parameter => 'ACCEPT_SQL_PROFILES', value => 'TRUE');

END;

/

可通过DBMS_AUTO_SQLTUNE.REPORT_AUTO_TUNING_TASK查看报告

VARIABLE my_rept CLOB;

BEGIN

  :my_rept :=DBMS_AUTO_SQLTUNE.REPORT_AUTO_TUNING_TASK(

    begin_exec   => NULL,

    end_exec     => NULL,

    type         => 'TEXT',

    level        => 'TYPICAL',

    section      => 'ALL',

    object_id    => NULL,

    result_limit => NULL);

END;

/

 

 

 

案例1 SGEADV 锁表

今日应用反馈存在锁表的情况，锁表的信息为：WRi$SEGADV_OBJLIST.

select t2.username,
       t2.sid,
       t2.serial#,
       t3.object_name,
       t1.LOCKED_MODE
  from v$locked_object t1, v$session t2, dba_objects t3
 where t1.session_id = t2.sid
   and t1.object_id = t3.object_id
 order by t2.logon_time;

查询锁表对应的会话，查询会话正在等待的事件resmgr:cpu quantum;

等待事件 ‘resmgr:cpu quantum’ 是资源管理器用来控制 CPU 分配给进程的标准事件。当会话等待 ‘resmgr:cpu quantum’ 时，会话正在等待分配一个CPU时间额度 。

当启用资源管理器并限制CPU消耗时会发生此等待。为了减少此等待事件的发生，可以增加会话当前消费组的CPU分配，因此下边方法中的第三种不要一味去禁用该任务，要了解瓶颈是否是cpu足够只是被限制了才出现“resmgr:cpu quantum”事件的 。

 

Symptoms

High waits on event 'resmgr:cpu quantum' might be noticed even when resource manager is disabled.     You already have confirmed parameter RESOURCE_MANAGER_PLAN is set to null but still noticing the above wait events.

Top 5 Timed Foreground Events:

Event                    Waits   Time(s)  Avg wait(ms) % DB time Wait Class

resmgr:cpu quantum         1,596  346,281       216968          89.19 Scheduler

db file scattered read   171,071   14,778           86           3.81 User I/O

log file sync             28,575   10,810          378           2.78 Commit

db file sequential read  943,457   6,569             7           1.69 User I/O

DB CPU                     2,133   0.55

Cause

This could be due to DEFAULT_MAINTENANCE_PLAN. From 11g onwards every weekday window has a pre-defined Resource Plan called DEFAULT_MAINTENANCE_PLAN, which will become active once the related window opens.
Following entries can also be noted in alert log at the time of issue.
 

Wed Sep 16 02:00:00 2009
Clearing Resource Manager plan via parameter：
Wed Sep 16 22:00:00 2009
Setting Resource Manager plan SCHEDULER[0x2C55]:DEFAULT_MAINTENANCE_PLAN via scheduler window
Setting Resource Manager plan DEFAULT_MAINTENANCE_PLAN via parameter
Wed Sep 16 22:00:05 2009
Begin automatic SQL Tuning Advisor run for special tuning task "SYS_AUTO_SQL_TUNING_TASK"

Solution

To disable the DEFAULT_MAINTENANCE_PLAN you can use the below steps as suggested inNote 786346.1
1. Set the current resource manager plan to null (or another plan that is not restrictive):
 

alter system set resource_manager_plan='';

2. Change the active windows to use the null resource manager plan (or other nonrestrictive plan)
using:3. Then, for each window_name (WINDOW_NAME from DBA_SCHEDULER_WINDOWS), run:

Execute dbms_scheduler.set_attribute('WEEKNIGHT_WINDOW','RESOURCE_PLAN','');

execute dbms_scheduler.set_attribute('WEEKEND_WINDOW','RESOURCE_PLAN','');

3. Then, for each window_name (WINDOW_NAME from DBA_SCHEDULER_WINDOWS), run:

execute dbms_scheduler.set_attribute('<window name>','RESOURCE_PLAN','');

execute dbms_scheduler.set_attribute('<window name>','RESOURCE_PLAN','');

References

NOTE:392037.1 - Database Hangs. Sessions wait for 'resmgr:cpu quantum'
NOTE:756734.1 - 11g: Scheduler Maintenance Tasks or Autotasks
NOTE:786346.1 - Resource Manager and Sql Tunning Advisory DEFAULT_MAINTENANCE_PLAN
NOTE:806893.1 - Large Waits With The Wait Event "Resmgr:Cpu Quantum"
注意：

但是很多用户会发现禁用资源计划很多时候没有作用.可以禁用Oracle缺省启用的资源调度,最后通过以下参数设置解决问题:
_resource_manager_always_on = false
关闭

ALTER system SET resource_manager_plan='';

EXECUTE dbms_scheduler.set_attribute('WEEKNIGHT_WINDOW','RESOURCE_PLAN','');

EXECUTE dbms_scheduler.set_attribute('WEEKEND_WINDOW','RESOURCE_PLAN','');

EXECUTE dbms_scheduler.set_attribute('MONDAY_WINDOW','RESOURCE_PLAN','');

EXECUTE dbms_scheduler.set_attribute('TUESDAY_WINDOW','RESOURCE_PLAN','');

EXECUTE dbms_scheduler.set_attribute('WEDNESDAY_WINDOW','RESOURCE_PLAN','');

EXECUTE dbms_scheduler.set_attribute('THURSDAY_WINDOW','RESOURCE_PLAN','');

EXECUTE dbms_scheduler.set_attribute('FRIDAY_WINDOW','RESOURCE_PLAN','');

EXECUTE dbms_scheduler.set_attribute('SATURDAY_WINDOW','RESOURCE_PLAN','');

EXECUTE dbms_scheduler.set_attribute('SUNDAY_WINDOW','RESOURCE_PLAN','');

 

修正跑脚本的时间

关闭指定的client 

BEGIN

  dbms_auto_task_admin.disable(

  client_name => 'sql tuning advisor',

  peration => NULL,

  window_name => NULL);

END;  

/

开启指定的client：

BEGIN

  dbms_auto_task_admin.enable(

  client_name => 'sql tuning advisor',

  peration => NULL,

  window_name => NULL);

END;  

/

更改运行时间：

BEGIN DBMS_SCHEDULER.SET_ATTRIBUTE(

　　'MONDAY_WINDOW',

　　'repeat_interval',

　　'freq=daily;byday=MON;byhour=1;byminute=0;bysecond=0');

end;

/