【数据库管理】数据库自动维护任务介绍

概述

本文介绍改变数据库自动维护任务（Automated Maintenance Tasks）相关的基础知识和总体介绍。

Oracle数据库通过预定义的维护窗口中，定期自动地运行一些维护任务，用于实现如统计信息收集、SQL调优顾问等工作，以减少DBA的工作量，使数据库最优。

自动维护任务的演变

Oracle在10g的版本上最初推出自动维护作业，并在11g版本开始改为自动维护任务，
并在以后的版本上不断功能加强和追加新的功能。
以下是自动维护任务在各个版本中主要变化：

10g

预定义的维护窗口（Maintenance Windows）

在10g版本上，Oracle主要有2个预定义的维护窗口（Maintenance Windows）：

WEEKNIGHT_WINDOW : 周一到周五 每天22:00开始 ～ 次日06:00结束。
WEEKEND_WINDOW   :周六和周日的全天

通过这两个维护窗口，组成MAINTENANCE_WINDOW_GROUP 用于管理维护作业（maintenance jobs）。

预定义的自动系统任务（Predefined Automatic System Tasks）

在10g版本上，主要有以下2个自动系统任务在维护窗口（Maintenance Windows）中运行。

自动统计信息收集作业（Automatic Statistics Collection Job）：

    收集数据库中所有无统计信息或统计信息过时的对象的统计信息，以便提高 SQL 执行的性能。

自动段指导作业（Automatic Segment Advisor Job）       ：

    识别是否有可用回收空间的段，并提出如何消除这些段中的碎片的建议。

关于10g的维护作业的其他详细内容可以参考以下的在线文档。

Database Administrator’s Guide

>23 Managing Automatic System Tasks Using the Maintenance Window

11g

预定义的维护窗口（Maintenance Windows）

在11g版本上，Oracle预定义了9个维护窗口（Maintenance Windows）。

新追加的，每天的维护窗口：
（周一到周五每天22:00 ～次日02:00；周六和周日 06:00 ～次日02:00）

MONDAY_WINDOW           : 周一 22:00 ～ 周二 02:00
TUESDAY_WINDOW          : 周二 22:00 ～ 周三 02:00
WEDNESDAY_WINDOW        : 周三 22:00 ～ 周四 02:00
THURSDAY_WINDOW         : 周四 22:00 ～ 周五 02:00
FRIDAY_WINDOW           : 周五 22:00 ～ 周六 02:00
SATURDAY_WINDOW         : 周六 06:00 ～ 周日 02:00
SUNDAY_WINDOW           : 周日 06:00 ～ 周一02:00

为了兼容以前的版本而留下的维护窗口：（默认不使用）

WEEKNIGHT_WINDOW : 周一到周五 每天22:00开始 ～ 次日06:00结束。
WEEKEND_WINDOW   : 周六和周日的全天

通过这九个维护窗口，组成MAINTENANCE_WINDOW_GROUP 用于管理维护任务（maintenance tasks）。

预定义自动维护任务（Automated Maintenance Tasks）

在11g版本上，主要有以下3个自动管理维护任务在每天的维护窗口（Maintenance Windows）中运行。

自动优化器统计信息收集（Automatic Optimizer Statistics Collection）：

    收集数据库中所有无统计信息或统计信息过时的对象的统计信息，以便提高 SQL 执行的性能。

自动段指导（Automatic Segment Advisor）：

    识别是否有可用回收空间的段，并提出如何消除这些段中的碎片的建议。

自动 SQL 优化指导（Automatic SQL Tuning Advisor）：

    检查高负载 SQL 语句的性能，并提出如何优化这些语句的建议。

关于11g的维护任务的其他详细内容可以参考以下的在线文档。

Database Administrator’s Guide

>26 Managing Automated Database Maintenance Tasks

12c

预定义的维护窗口（Maintenance Windows）

在12c版本上，和11g一样，Oracle预定义了9个维护窗口（Maintenance Windows）。

包括，每天的维护窗口：
（周一到周五每天22:00 ～次日02:00；周六和周日 06:00 ～次日02:00）

MONDAY_WINDOW           : 周一 22:00 ～ 周二 02:00
TUESDAY_WINDOW          : 周二 22:00 ～ 周三 02:00
WEDNESDAY_WINDOW        : 周三 22:00 ～ 周四 02:00
THURSDAY_WINDOW         : 周四 22:00 ～ 周五 02:00
FRIDAY_WINDOW           : 周五 22:00 ～ 周六 02:00
SATURDAY_WINDOW         : 周六 06:00 ～ 周日 02:00
SUNDAY_WINDOW           : 周日 06:00 ～ 周一02:00

为了兼容以前的版本而留下的维护窗口：（默认不使用）

WEEKNIGHT_WINDOW : 周一到周五 每天22:00开始 ～ 次日06:00结束。
WEEKEND_WINDOW   : 周六和周日的全天

通过这九个维护窗口，组成MAINTENANCE_WINDOW_GROUP 用于管理维护任务（maintenance tasks）。

预定义自动维护任务（Automated Maintenance Tasks）

在12c版本上，新追加了一个SQL计划管理（SPM）进化指导（SPM Evolve Advisor）任务，用于SQL计划管理计划（SPM）的进化；所以，主要有以下4个自动管理维护任务在每天的维护窗口（Maintenance Windows）中运行。

自动优化器统计信息收集（Automatic Optimizer Statistics Collection）：

    收集数据库中所有无统计信息或统计信息过时的对象的统计信息，以便提高 SQL 执行的性能。

自动段指导（Automatic Segment Advisor）：

    识别是否有可用回收空间的段，并提出如何消除这些段中的碎片的建议。

自动 SQL 优化指导（Automatic SQL Tuning Advisor）：

    检查高负载 SQL 语句的性能，并提出如何优化这些语句的建议。

SQL计划管理（SPM）进化指导（SPM Evolve Advisor）：

    针对新追加的SQL计划管理计划进行进化。

关于12c的维护任务的其他详细内容可以参考以下的在线文档。

Database Administrator’s Guide

>26 Managing Automated Database Maintenance Tasks

各版本的主要变化

自动维护任务各版本的主要变化可以参考下表：

自动维护任务的控制（启用、禁用、更改）

可以通过如下的方法对自动维护任务进行控制。

10g

1.针对特定维护作业的操作

启用方法：

EXECUTE DBMS_SCHEDULER.ENABLE('GATHER_STATS_JOB');
EXECUTE DBMS_SCHEDULER.ENABLE('AUTO_SPACE_ADVISOR_JOB');

禁用方法：

EXECUTE DBMS_SCHEDULER.DISABLE('GATHER_STATS_JOB');
EXECUTE DBMS_SCHEDULER.DISABLE('AUTO_SPACE_ADVISOR_JOB');

2.确认各个维护作业的状态：

select job_name,enabled 
from dba_scheduler_jobs 
where job_name in ( 'GATHER_STATS_JOB', 'AUTO_SPACE_ADVISOR_JOB');

维护窗口设定更改：

可以通过DBMS_SCHEDULER.SET_ATTRIBUTE程序包对维护窗口进行如运行时间、频度等设定的修改。如：

--修改周一到周五的维护窗口（WEEKNIGHT_WINDOW）为每天0点开始。        
EXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE(
   'WEEKNIGHT_WINDOW', 
   'repeat_interval',
   'freq=daily;byday=MON, TUE, WED, THU, FRI;byhour=0;byminute=0;bysecond=0');

参考：
Database PL/SQL Packages and Types Reference
>93 DBMS_SCHEDULER

11g& 12c

1.针对特定维护任务的操作

禁用方法：

--禁用sql tuning advisor
BEGIN
  dbms_auto_task_admin.disable(
    client_name => 'sql tuning advisor',
    operation   => NULL,
    window_name => NULL);
END;
/

启用方法：

--启用sql tuning advisor
BEGIN
  dbms_auto_task_admin.enable(
    client_name => 'sql tuning advisor',
    operation   => NULL,
    window_name => NULL);
END;
/

其中，client_name可以为DBA_AUTOTASK_CLIENT表中的任意一个任务名（client_name）：

SQL> select client_name from DBA_AUTOTASK_CLIENT;

CLIENT_NAME
----------------------------------------------------------------
auto optimizer stats collection
auto space advisor
sql tuning advisor

另外，需要注意的是在12c的环境中，CLIENT_NAME：sql tuning advisor同时控制着自动 SQL 优化指导（Automatic SQL Tuning Advisor）和SQL计划管理（SPM）进化指导（SPM Evolve Advisor）。

2.启用和禁用所有的维护任务：

--禁用
EXECUTE DBMS_AUTO_TASK_ADMIN.DISABLE;
--启用
EXECUTE DBMS_AUTO_TASK_ADMIN.ENABLE;

3.确认各个维护任务的状态

--例：自动 SQL 优化指导（Automatic SQL Tuning Advisor）
COL CLIENT_NAME FORMAT a20
SELECT CLIENT_NAME, STATUS
FROM   DBA_AUTOTASK_CLIENT
WHERE  CLIENT_NAME = 'sql tuning advisor';

4.维护窗口设定更改：

可以通过DBMS_SCHEDULER.SET_ATTRIBUTE程序包对维护窗口进行如运行时间、频度等设定的修改。如：

--修改SATURDAY_WINDOW窗口的持续时间
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;
/

监视视图

对于维护任务（作业）调查，相关的一些监视视图。

10g

DBA_SCHEDULER_JOBS：数据库内所有作业的相关详细内容。

DBA_SCHEDULER_JOB_LOG：作业的执行日志
DBA_SCHEDULER_JOB_RUN_DETAILS：作业的执行详细

DBA_SCHEDULER_WINGROUP_MEMBERS：维护窗口的详细
DBA_SCHEDULER_WINDOWS：数据库内所有窗口的相关内容。

DBA_SCHEDULER_JOB_CLASSES：Resource Plan相关信息。

11g& 12c

DBA_AUTOTASK_CLIENT：查看各个维护任务客户端的相关内容。
DBA_AUTOTASK_TASK：查看各个维护任务的相关内容。

DBA_AUTOTASK_JOB_HISTORY：维护任务作业的执行历史
DBA_AUTOTASK_SCHEDULE：今后32天内的各个Client的执行计划
DBA_AUTOTASK_OPERATION：各个Client的执行操作
DBA_AUTOTASK_WINDOW_HISTORY：各个维护窗口的执行历史

DBA_AUTOTASK_WINDOW_CLIENTS：属于各个维护窗口客户端的维护任务的状态。
DBA_SCHEDULER_WINDOWS：数据库内所有窗口的相关内容。
DBA_SCHEDULER_WINDOW_GROUPS：数据库内所有窗口组的相关内容。

DBA_RSRC_PLANS：数据库中所有的资源计划（Resource Plan）信息
DBA_RSRC_PLAN_DIRECTIVES：资源计划指令信息

重建数据库自动维护任务

在某些情况下，如发现数据库自动维护任务有问题或者想恢复成默认的设定时，你可以通过以下的方法重建数据库维护窗口和自动维护任务。

conn /as sysdba

-- 这个脚本会删除维护窗口，但是可能会报一些可以忽略的错误。
@?/rdbms/admin/catnomwn.sql 

-- 也可以通过手动删除维护窗口代替catnomwn.sql。
execute dbms_scheduler.drop_window('MONDAY_WINDOW');
execute dbms_scheduler.drop_window('TUESDAY_WINDOW');
execute dbms_scheduler.drop_window('WEDNESDAY_WINDOW');
execute dbms_scheduler.drop_window('THURSDAY_WINDOW');
execute dbms_scheduler.drop_window('FRIDAY_WINDOW');
execute dbms_scheduler.drop_window('SATURDAY_WINDOW');
execute dbms_scheduler.drop_window('SUNDAY_WINDOW');

-- 重建维护窗口和维护作业（任务）
@?/rdbms/admin/catmwin.sql

欢迎关注微信订阅号：TeacherWhat