Oracle 11G统计信息自动收集及调整

--验证关闭情况，如下，optimizer_stats列为DISABLED

SELECT window_name,window_next_time,window_active,optimizer_stats
  FROM dba_autotask_window_clients
 WHERE window_name = ‘MONDAY_WINDOW‘
 ORDER BY window_next_time;

WINDOW_NAME               WINDOW_NEXT_TIME                                WINDO OPTIMIZE
------------------------------ --------------------------------------------------------------------------- ----- --------
MONDAY_WINDOW               27-MAY-19 10.00.00.000000 PM PRC                        FALSE ENABLED

关闭所有时间调度窗口，验证略

       BEGIN
           DBMS_AUTO_TASK_ADMIN.disable (
              client_name   => ‘auto optimizer stats collection‘,operation     => NULL,window_name   => NULL);
        END;
        /

开启单个调度时间窗口及所有时间调度窗口，只需要使用enable过程

  -- 注：单个应指定窗口名字，如window_name   => ‘MONDAY_WINDOW‘
        BEGIN
           DBMS_AUTO_TASK_ADMIN.enable (
              client_name   => ‘auto optimizer stats collection‘,window_name   => NULL);
        END;
        /

--修改时间窗口到特定的时间
--如下示例，将周五时间窗口时间到晚间23点30分
BEGIN
  DBMS_SCHEDULER.DISABLE(name => ‘"SYS"."FRIDAY_WINDOW"‘,force => TRUE);
END;

BEGIN
  DBMS_SCHEDULER.SET_ATTRIBUTE(name      => ‘"SYS"."FRIDAY_WINDOW"‘,attribute => ‘REPEAT_INTERVAL‘,VALUE     => ‘FREQ=WEEKLY;BYDAY=FRI;BYHOUR=23;BYMINUTE=30;BYSECOND=0‘);
END;

BEGIN
  DBMS_SCHEDULER.ENABLE(name => ‘"SYS"."FRIDAY_WINDOW"‘);
END;

--验证修改
SELECT w.window_name,dba_scheduler_windows w
 WHERE c.window_name = w.window_name
   AND c.optimizer_stats = ‘ENABLED‘
   AND c.window_name = ‘FRIDAY_WINDOW‘;

WINDOW_NAME                    REPEAT_INTERVAL                                                                  DURATION                                                                        ENABLED
------------------------------ -------------------------------------------------------------------------------- ------------------------------------------------------------------------------- -------
FRIDAY_WINDOW                  freq=daily;byday=FRI;byhour=22;byminute=0; bysecond=0                            +000 04:00:00                                                                   TRUE

--新增维护时间窗口
-- 假定我们要处理的是修改周一的时间窗口
-- 首先关闭周一的时间窗口
BEGIN
  DBMS_AUTO_TASK_ADMIN.disable(client_name => ‘auto optimizer stats collection‘,window_name => ‘MONDAY_WINDOW‘);
END;
/

--接下来创建一个窗口并设定时间调度间隔
--如下，每周一5点执行，持续时间为1小时
BEGIN
  DBMS_SCHEDULER.create_window(window_name     => ‘STATS_WINDOW‘,resource_plan   => ‘DEFAULT_MAINTENANCE_PLAN‘,repeat_interval => ‘freq=daily;byday=MON;byhour=5;byminute=0; bysecond=0‘,duration        => INTERVAL ‘1‘ HOUR,comments        => ‘Test window for stats task‘);
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE = -27477 THEN
      NULL;
    ELSE
      RAISE;
    END IF;
END;

BEGIN
  DBMS_SCHEDULER.set_attribute(‘STATS_WINDOW‘,‘SYSTEM‘,TRUE);
  DBMS_SCHEDULER.set_attribute(‘STATS_WINDOW‘,‘FOLLOW_DEFAULT_TIMEZONE‘,TRUE);
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE = -27477 THEN
      NULL;
    ELSE
      RAISE;
    END IF;
END;
/

SELECT window_name,repeat_interval,enabled
  FROM dba_scheduler_windows
 WHERE window_name = ‘STATS_WINDOW‘;

（编辑：青岛站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!