运维首页 > 数据运维 > 轻松高效管理：全面掌握Oracle数据库自动维护任务

轻松高效管理：全面掌握Oracle数据库自动维护任务

2024年 1月 5日 57.9k 0

1 自动任务概念

自动维护任务

自动维护任务是指定期自动启动对数据库执行维护操作的任务，比如为查询优化器收集模式对象统计信息。

自动维护任务在维护窗口中运行，维护窗口是预定义的时间间隔，旨在在系统负载较低的时间段内发生。您可以根据数据库的资源使用模式自定义维护窗口，或者禁用某些默认窗口的运行。您还可以创建自己的维护窗口。

Oracle数据库有以下预定义的自动维护任务:

  • Automatic Optimizer Statistics Collection - 收集数据库中没有统计信息或只有过时统计信息的所有模式对象的优化器统计信息。SQL查询优化器使用该任务收集的统计信息来提高SQL执行的性能。
  • Optimizer Statistics advisor - 分析如何收集统计信息，并建议可以进行的更改，以微调统计信息收集。
  • Automatic Segment Advisor-识别有可用空间回收的段，并就如何整理这些段提出建议。 您还可以手动运行段顾问以获取更多最新的建议，或者获取关于自动段顾问没有检查可能的空间回收的段的建议。
  • Automatic SQL Tuning Advisor - 检查高负载SQL语句的性能，并就如何调优这些语句提出建议。您可以配置此advisor以自动实现SQL概要文件建议。
  • SQL Plan Management (SPM) Evolve Advisor - 演进最近添加到SQL计划基线的计划。advisor通过消除手动执行的需求来简化计划的演进。

维护窗口

维护窗口是运行自动维护任务的连续时间间隔。维护窗口是属于名为MAINTENANCE_WINDOW_GROUP的窗口组的Oracle Scheduler窗口。

Scheduler窗口可以是一个简单的重复间隔(例如“在每个星期六的午夜到早上6点之间”)，也可以是一个更复杂的间隔(例如“在每个月的最后一个工作日的午夜到早上6点之间，不包括公司假期”)。

当维护窗口打开时，Oracle数据库为计划在该窗口中运行的每个维护任务创建一个Oracle Scheduler作业。为每个作业分配一个作业名称，该名称是在运行时生成的。所有自动维护任务作业名称都以ORA$AT开头。例如，自动段顾问的作业可能被称为ORA$AT_SA_SPC_SY_26。当自动维护任务任务完成后，它将从Oracle Scheduler作业系统中删除。但是，仍然可以在Scheduler作业历史记录中找到该作业。

在维护窗口非常长的情况下，除了Automatic SQL Tuning Advisor之外的所有自动维护任务每四个小时重新启动一次。无论窗口大小如何，该特性都可以确保定期运行维护任务。

自动维护任务的框架依赖于在数据库中定义的维护窗口。每次安装Oracle数据库都会自动定义维护窗口，如下表所示。

窗口名称 描述
MONDAY_WINDOW 周一晚上10点开始，凌晨2点结束。
TUESDAY_WINDOW 从周二晚上10点开始，到凌晨2点结束。
WEDNESDAY_WINDOW 从周三晚上10点开始，到凌晨2点结束。
THURSDAY_WINDOW 从周四晚上10点开始，到凌晨2点结束。
FRIDAY_WINDOW 从周五晚上10点开始，到凌晨2点结束。
SATURDAY_WINDOW 从周六早上6点开始，时长20个小时。
SUNDAY_WINDOW 从周日早上6点开始，时长20个小时。

2 配置自动任务

要在维护窗口的任何子集中启用或禁用特定的维护任务，可以使用DBMS_AUTO_TASK_ADMIN PL/SQL包。

启用和禁用任务

默认情况下，所有维护任务都在所有预定义的维护窗口中运行。您可以禁用特定窗口的维护任务。

  • 查看默认的自动维护窗口任务及其状态

col client_name for a35
select client_name,status from dba_autotask_client;

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

  • 禁用某个任务 - 比如 sql tuning advisor

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

set line 200
select status,last_change from dba_autotask_client where client_name='sql tuning advisor';

STATUS LAST_CHANGE
-------- ----------------------------------------------
DISABLED 26-SEP-23 01.10.35.759730 PM +00:00

  • 启用某个任务 - 比如auto space advisor

exec dbms_auto_task_admin.enable(client_name=>'auto space advisor',operation=> NULL,window_name=>NULL);

col last_change for a40
select client_name,status,last_change from dba_autotask_client;

CLIENT_NAME STATUS LAST_CHANGE
----------------------------------- -------- ----------------------------------------
sql tuning advisor DISABLED 26-SEP-23 01.10.35.759730 PM +00:00
auto optimizer stats collection ENABLED
auto space advisor ENABLED 26-SEP-23 01.14.52.071532 PM +00:00

  • 禁用和启用任务的所有窗口

EXECUTE DBMS_AUTO_TASK_ADMIN.DISABLE;

col window_name for a20
select window_name,autotask_status from dba_autotask_window_clients;

WINDOW_NAME AUTOTASK
-------------------- --------
MONDAY_WINDOW DISABLED
TUESDAY_WINDOW DISABLED
WEDNESDAY_WINDOW DISABLED
THURSDAY_WINDOW DISABLED
FRIDAY_WINDOW DISABLED
SATURDAY_WINDOW DISABLED
SUNDAY_WINDOW DISABLED

7 rows selected.

--启动所有
EXECUTE DBMS_AUTO_TASK_ADMIN.ENABLE;

WINDOW_NAME AUTOTASK
-------------------- --------
MONDAY_WINDOW ENABLED
TUESDAY_WINDOW ENABLED
WEDNESDAY_WINDOW ENABLED
THURSDAY_WINDOW ENABLED
FRIDAY_WINDOW ENABLED
SATURDAY_WINDOW ENABLED
SUNDAY_WINDOW ENABLED

7 rows selected.

配置窗口

默认情况下，所有维护任务都在所有预定义的维护窗口中运行。您可以禁用特定窗口的维护任务。此外，可以使用DBMS_SCHEDULER PL/SQL包自定义维护窗口，比如希望将预定义的维护窗口调整为适合您的数据库环境的时间，或者创建一个新的维护窗口。您。

  • 查看默认的自动维护任务的窗口(MAINTENANCE_WINDOW_GROUP)情况

set line 300
col window_next_time for a38
select * from dba_autotask_window_clients;

WINDOW_NAME WINDOW_NEXT_TIME WINDOW_ACTIVE AUTOTASK_STATUS OPTIMIZER_STATS SEGMENT_ADVISOR SQL_TUNE_ADVISOR
-------------------- ------------------------------------- ------------- --------------- --------------- --------------- ----------------
MONDAY_WINDOW 02-OCT-23 10.00.00.000000 PM ETC/UTC FALSE ENABLED ENABLED ENABLED ENABLED
TUESDAY_WINDOW 26-SEP-23 10.00.00.000000 PM ETC/UTC FALSE ENABLED ENABLED ENABLED ENABLED
WEDNESDAY_WINDOW 27-SEP-23 10.00.00.000000 PM ETC/UTC FALSE ENABLED ENABLED ENABLED ENABLED
THURSDAY_WINDOW 28-SEP-23 10.00.00.000000 PM ETC/UTC FALSE ENABLED ENABLED ENABLED ENABLED
FRIDAY_WINDOW 29-SEP-23 10.00.00.000000 PM ETC/UTC FALSE ENABLED ENABLED ENABLED ENABLED
SATURDAY_WINDOW 30-SEP-23 06.00.00.000000 AM ETC/UTC FALSE ENABLED ENABLED ENABLED ENABLED
SUNDAY_WINDOW 01-OCT-23 06.00.00.000000 AM ETC/UTC FALSE ENABLED ENABLED ENABLED ENABLED

7 rows selected.

  • 禁用某个任务某个窗口的运行，比如任务sql tuning advisor在窗口MONDAY_WINDOW的

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

select autotask_status, optimizer_stats, segment_advisor, sql_tune_advisor
from dba_autotask_window_clients where window_name='MONDAY_WINDOW';

AUTOTASK OPTIMIZE SEGMENT_ SQL_TUNE
-------- -------- -------- --------
ENABLED ENABLED ENABLED DISABLED

  • 修改某个维护窗口的属性，通过DBMS_SCHEDULER PL/SQL包包含一个SET_ATTRIBUTE存储过程来实现，比如如下

--修改周六窗口的属性，改为持续时间为4小时
set line 300
col window_name for a20
col repeat_interval for a60
col duration for a16
col next_start_date for a42
select window_name,repeat_interval,duration,next_start_date,enabled,active
from dba_scheduler_windows where window_name='SATURDAY_WINDOW';

WINDOW_NAME REPEAT_INTERVAL DURATION NEXT_START_DATE ENABL ACTIVE
-------------------- ------------------------------------------------------------ ---------------- ------------------------------------------ ----- -----
SATURDAY_WINDOW freq=daily;byday=SAT;byhour=6;byminute=0; bysecond=0 +000 20:00:00 30-SEP-23 06.00.00.000000 AM ETC/UTC TRUE FALSE

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;
/

select window_name,repeat_interval,duration,enabled
from dba_scheduler_windows where window_name='SATURDAY_WINDOW';

WINDOW_NAME REPEAT_INTERVAL DURATION ENABL
-------------------- ------------------------------------------------------------ ---------------- -----
SATURDAY_WINDOW freq=daily;byday=SAT;byhour=6;byminute=0; bysecond=0 +000 04:00:00 TRUE

注意，必须使用DBMS_SCHEDULER.DISABLE子程序在对窗口进行更改之前禁用该窗口，然后使用DBMS_SCHEDULER.ENABLE重新启用该窗口。如果在当前打开的窗口中更改了该窗口，则该更改直到下次窗口打开时才生效。

  • 创建一个新的维护窗口：先创建一个Oracle Scheduler窗口对象，然后将其添加到窗口组MAINTENANCE_WINDOW_GROUP中。然后使用DBMS_SCHEDULER.CREATE_WINDOW存储过程创建窗口，以及DBMS_SCHEDULER.ADD_GROUP_MEMBER过程将新窗口添加到窗口组。

--创建一个名为EARLY_MORNING_WINDOW的维护窗口。每天在早上5点到6点之间运行一个小时
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_GROUP_MEMBER(
group_name => 'MAINTENANCE_WINDOW_GROUP',
member => 'EARLY_MORNING_WINDOW');
END;
/

  • 从MAINTENANCE_WINDOW_GROUP窗口中移除维护窗口：若窗口继续存在且不再运行自动维护任务，但是分配给此窗口的任何其他Oracle Scheduler作业将继续正常运行。

--移除窗口EARLY_MORNING_WINDOW
BEGIN
DBMS_SCHEDULER.REMOVE_GROUP_MEMBER(
group_name => 'MAINTENANCE_WINDOW_GROUP',
member => 'EARLY_MORNING_WINDOW');
END;
/

配置资源分配

默认情况下，所有预定义维护窗口都使用资源计划DEFAULT_MAINTENANCE_PLAN。自动维护任务在其子计划ORA$AUTOTASK下运行。该子计划将其在总资源分配中所占的份额平均分配给维护任务。

DEFAULT_MAINTENANCE_PLAN定义了以下资源分配:

消费组/子计划 级别1 最大使用限制
ORA$AUTOTASK 5% 90
OTHER_GROUPS 20% -
SYS_GROUP 75% -

在此计划中，SYS_GROUP消费者组中的任何会话都具有优先级。(该组中的会话由用户SYS和SYSTEM创建)。SYS_GROUP中会话未使用的任何资源分配，然后由属于该计划中的其他消费者组和子计划的会话共享。在这些分配中，5%用于维护任务，20%用于用户会话。ORA$AUTOTASK的最大使用限制是90。即使CPU空闲，该组/计划分配的CPU资源也不能超过90%。

为了减少或增加自动维护任务的资源分配，您可以对DEFAULT_MAINTENANCE_PLAN进行调整。

与任何资源计划一样，分配中未被消费者组或子计划使用的部分可用于其他消费者组或子计划。还要注意，直到100%的CPU被使用，数据库资源管理器才开始根据资源计划限制资源分配。

注意:虽然DEFAULT_MAINTENANCE_PLAN是默认值，但是您可以将任何资源计划分配给任何维护窗口。如果要更改维护窗口资源计划，请确保在新计划中包含子计划ORA$AUTOTASK。

3 自动维护任务运行情况

系统视图

可以查询一组数据字典视图，以获取自动化维护任务的信息。

  • DBA_AUTOTASK_CLIENT_JOB 包含有关为自动维护任务创建的当前运行的调度器作业的信息。它提供了关于这些作业所针对的一些对象的信息，以及来自同一任务的先前实例化的一些附加统计信息。其中一些附加数据来自通用Scheduler视图。

JOB_SCHEDULER_STATUS列包含状态有：
DISABLED, RETRY SCHEDULED, SCHEDULED, RUNNING, COMPLETED, BROKEN, FAILED, REMOTE, SUCCEEDED, CHAIN_STALLED

TASK_PRIORITY列包含的值有：
URGENT, HIGH, MEDIUM, LOW

  • DBA_AUTOTASK_CLIENT 提供每个自动化维护任务7天和30天的统计数据。
  • DBA_AUTOTASK_JOB_HISTORY 列出自动维护任务作业运行的历史记录。作业在完成执行后被添加到这个视图中。
  • DBA_AUTOTASK_WINDOW_CLIENTS 列出属于MAINTENANCE_WINDOW_GROUP的窗口，以及每个维护任务的窗口的启用或禁用状态。
  • DBA_AUTOTASK_CLIENT_HISTORY 提供每个自动维护任务的作业执行计数的每个窗口历史记录。
  • ALL_SCHEDULER_WINDOWS 显示数据库中所有Scheduler窗口的信息。

SQL> desc dba_scheduler_windows
Name Null? Type
------------------------- -------- ----------------------------
OWNER NOT NULL VARCHAR2(128)
WINDOW_NAME NOT NULL VARCHAR2(128)
RESOURCE_PLAN VARCHAR2(128)
SCHEDULE_OWNER VARCHAR2(4000)
SCHEDULE_NAME VARCHAR2(4000)
SCHEDULE_TYPE VARCHAR2(8)
START_DATE TIMESTAMP(6) WITH TIME ZONE
REPEAT_INTERVAL VARCHAR2(4000)
END_DATE TIMESTAMP(6) WITH TIME ZONE
DURATION INTERVAL DAY(3) TO SECOND(0)
WINDOW_PRIORITY VARCHAR2(4)
NEXT_START_DATE TIMESTAMP(6) WITH TIME ZONE
LAST_START_DATE TIMESTAMP(6) WITH TIME ZONE
ENABLED VARCHAR2(5)
ACTIVE VARCHAR2(5)
MANUAL_OPEN_TIME TIMESTAMP(6) WITH TIME ZONE
MANUAL_DURATION INTERVAL DAY(3) TO SECOND(0)
COMMENTS VARCHAR2(4000)

查看历史执行情况

  • 查看 每个自动维护任务的作业执行计数的每个窗口历史记录

--最近7天
set line 300 pages 999
col client_name for a32
col window_name for a18
col window_start_time for a40
col window_duration for a28
col window_end_time for a40
select * from dba_autotask_client_history where window_start_time > sysdate -7
order by window_start_time ;

-------------------------------- ------------------ ---------------------------------------- ---------------------------- ------------ ------------ -------------- ----------------------------------------
auto optimizer stats collection THURSDAY_WINDOW 21-SEP-23 10.00.00.129854 PM +08:00 +000000000 03:59:59.922867 1 1 1 22-SEP-23 02.00.00.052721 AM +08:00
auto space advisor THURSDAY_WINDOW 21-SEP-23 10.00.00.129854 PM +08:00 +000000000 03:59:59.922867 1 1 1 22-SEP-23 02.00.00.052721 AM +08:00
sql tuning advisor THURSDAY_WINDOW 21-SEP-23 10.00.00.129854 PM +08:00 +000000000 03:59:59.922867 1 1 1 22-SEP-23 02.00.00.052721 AM +08:00
auto optimizer stats collection FRIDAY_WINDOW 22-SEP-23 10.00.00.029022 PM +08:00 +000000000 04:00:00.088779 1 1 1 23-SEP-23 02.00.00.117801 AM +08:00
auto space advisor FRIDAY_WINDOW 22-SEP-23 10.00.00.029022 PM +08:00 +000000000 04:00:00.088779 1 1 1 23-SEP-23 02.00.00.117801 AM +08:00
sql tuning advisor FRIDAY_WINDOW 22-SEP-23 10.00.00.029022 PM +08:00 +000000000 04:00:00.088779 1 1 1 23-SEP-23 02.00.00.117801 AM +08:00
auto optimizer stats collection SATURDAY_WINDOW 23-SEP-23 06.00.00.159663 AM +08:00 +000000000 20:00:00.012970 5 5 5 24-SEP-23 02.00.00.172633 AM +08:00
auto space advisor SATURDAY_WINDOW 23-SEP-23 06.00.00.159663 AM +08:00 +000000000 20:00:00.012970 5 5 5 24-SEP-23 02.00.00.172633 AM +08:00
sql tuning advisor SATURDAY_WINDOW 23-SEP-23 06.00.00.159663 AM +08:00 +000000000 20:00:00.012970 1 1 1 24-SEP-23 02.00.00.172633 AM +08:00
auto optimizer stats collection SUNDAY_WINDOW 24-SEP-23 06.00.00.173812 AM +08:00 +000000000 19:59:59.949583 5 5 5 25-SEP-23 02.00.00.123395 AM +08:00
auto space advisor SUNDAY_WINDOW 24-SEP-23 06.00.00.173812 AM +08:00 +000000000 19:59:59.949583 5 5 5 25-SEP-23 02.00.00.123395 AM +08:00
sql tuning advisor SUNDAY_WINDOW 24-SEP-23 06.00.00.173812 AM +08:00 +000000000 19:59:59.949583 1 1 1 25-SEP-23 02.00.00.123395 AM +08:00
auto optimizer stats collection MONDAY_WINDOW 25-SEP-23 10.00.00.129921 PM +08:00 +000000000 04:00:00.010450 1 1 1 26-SEP-23 02.00.00.140371 AM +08:00
auto space advisor MONDAY_WINDOW 25-SEP-23 10.00.00.129921 PM +08:00 +000000000 04:00:00.010450 1 1 1 26-SEP-23 02.00.00.140371 AM +08:00
sql tuning advisor MONDAY_WINDOW 25-SEP-23 10.00.00.129921 PM +08:00 +000000000 04:00:00.010450 1 1 1 26-SEP-23 02.00.00.140371 AM +08:00
auto optimizer stats collection TUESDAY_WINDOW 26-SEP-23 10.00.00.069518 PM +08:00 +000000000 04:00:00.048609 1 1 1 27-SEP-23 02.00.00.118127 AM +08:00
auto space advisor TUESDAY_WINDOW 26-SEP-23 10.00.00.069518 PM +08:00 +000000000 04:00:00.048609 1 1 1 27-SEP-23 02.00.00.118127 AM +08:00
sql tuning advisor TUESDAY_WINDOW 26-SEP-23 10.00.00.069518 PM +08:00 +000000000 04:00:00.048609 1 1 1 27-SEP-23 02.00.00.118127 AM +08:00
auto optimizer stats collection WEDNESDAY_WINDOW 27-SEP-23 10.00.00.037326 PM +08:00 +000000000 03:59:59.978490 1 1 1 28-SEP-23 02.00.00.015816 AM +08:00
auto space advisor WEDNESDAY_WINDOW 27-SEP-23 10.00.00.037326 PM +08:00 +000000000 03:59:59.978490 1 1 1 28-SEP-23 02.00.00.015816 AM +08:00
sql tuning advisor WEDNESDAY_WINDOW 27-SEP-23 10.00.00.037326 PM +08:00 +000000000 03:59:59.978490 1 1 1 28-SEP-23 02.00.00.015816 AM +08:00

21 rows selected.

爱可生开源社区
数据运维
0 0

相关文章

Oracle如何使用授予和撤销权限的语法和示例
Awesome Project: 探索 MatrixOrigin 云原生分布式数据库
下载丨66页PDF，云和恩墨技术通讯（2024年7月刊）
社区版oceanbase安装
Oracle 导出CSV工具-sqluldr2
ETL数据集成丨快速将MySQL数据迁移至Doris数据库

发布评论

爱可生开源社区

这个人很懒，什么都没有留下～

文章2708 评论0