資料庫自動維護任務的管理

資料庫自動維護任務的管理（managing automated database maintenance tasks）

以下記錄如何用pl/sql包來管理oracle的自動維護任務。

一、自動維護任務

自動維護任務是以一定的時間間隔週期性地對資料庫進行維護操作的一個自動任務。
為查詢優化器自動收集統計資訊就是一個典型的自動維護任務。
自動維護任務是在維護視窗（maintenance windows）執行的，維護視窗需要提前設定，應該設定在一個系統的低負載時段。
你可以根據系統實際情況設定維護視窗，或者關閉特定的預設視窗。還可以建立自己的維護視窗。

oracle有三種預定義的自動維護任務：
1）自動優化資訊收集
為資料庫中哪些沒有統計資訊或統計資訊過舊的模式物件收集統計資訊。
統計資訊被查詢優化器用於改善sql的執行效能。

2）自動段建議器（automatic segment advisor）
定位到可用空間需要改造（reclamation，可翻譯為開墾）的段，並建議取出這些段的碎片。

3）自動sql調優建議器
檢查高負載的sql語句的效能，給出如何調整這些語句的建議。
可以將該建議設定為自動在sql profile中執行
（You can configure this advisor to automatically implement SQL profile recommendations.）

預設情況下這些自動維護任務在所有的維護視窗中都會執行

二、維護視窗
維護視窗就是自動維護任務執行的時間段。在oracle日程視窗中屬於“MAINTENANCE_WINDOW_GROUP”組。
維護視窗既可以是簡單的時間段，也可以是複雜的時間設定。

當維護視窗開啟時，oracle為此視窗中的每個維護任務建立一個日程任務。每個任務的任務名在執行時聲稱。
所有自動維護作業的任務都是以ORA$AT開頭的。比如自動段建議器的某個任務可能叫做ORA$AT_SA_SPC_SY_26。
自動維護任務完成後，會從oracle日程任務中刪除，但仍可以在任務歷史資訊中看到。

在維護視窗設定相對比較長的情況下，除自動sql調優建議器外，其他所有自動維護任務都是每小時自動重啟的。
該特性確保了維護任務的有規律執行，而不管維護視窗多大。

三、自動維護任務的設定
使用dbms_auto_task_admin包可以啟用或禁用維護視窗中的一些子任務。

1.為所有的維護視窗啟用和禁用維護任務

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

2.為特定的維護視窗啟用和禁用維護任務
預設情況下所有維護視窗執行所有的維護任務。
你可以在某個特定視窗禁用一個維護任務。如：
BEGIN
dbms_auto_task_admin.disable(
client_name => 'sql tuning advisor',
operation => NULL,
window_name => 'MONDAY_WINDOW');
END;
/

四、維護視窗的設定
1.修改維護視窗
可以使用dbms_scheduler包調整預定義的維護視窗時間。
需要先用dbms_scheduler.disable過程禁用視窗，然後執行以下命令進行修改：
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;
/
修改完成後再用dbms_scheduler.enable啟用視窗。
否則的話，若修改的視窗正在執行，就只能在下次執行時才生效了。
                                                                 
2.建立新的維護視窗
需要先建立日程視窗物件，並將其加入到MAINTANEANCE_WINDOW_GROUP組中。
使用dbms_scheduler.create_wiindow包建立視窗，add_group_member增加新的視窗到視窗組中。
如：
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;
/

3.刪除一個維護視窗
BEGIN
DBMS_SCHEDULER.REMOVE_GROUP_MEMBER(
group_name => 'MAINTENANCE_WINDOW_GROUP',
member => 'EARLY_MORNING_WINDOW');
END;
/

五、為自動維護任務分配資源
1.概述

預設情況下所有預定義的維護視窗使用default_maintenance_plan的資源計劃。
自動資源計劃在ora$autotask_sub_plan計劃下執行。
default_maintenance_plan定義了以下的資源分配計劃：
Consumer Group/subplan  Level 1  Level 2  Maximum Utilization Limit
ORA$AUTOTASK_SUB_PLAN   -     25%    90
ORA$DIAGNOSTICS      -     5%    90
OTHER_GROUPS        -     70%
SYS_GROUP         75%    -                           

也就是說，sys_group組的優先順序最高（該組中的會話都是sys或system賬號建立的會話）。
sys_group不用的資源分配給其他的組和子計劃。
在這些剩餘資源中，25%用於自動維護任務，5%用於後臺程式的診斷選項，70%用於使用者的會話。
ORA$AUTOTASK_SUB_PLAN和ORA$DIAGNOSTICS的最大使用限制為90。
這樣，即使cpu是空閒的，該組或計劃也不能分配90%以上的cpu資源。

可以通過調整default_maintenance_plan來改變自動維護任務的資源分配。

對於資源計劃而言，為某個消耗組或者子計劃分配的份額若沒有使用，就可以被其他的消耗組或子計劃使用。
資料庫的資源管理器不會根據資源計劃來限制資源分配，除非cpu使用率達到了100%。

2.為自動維護任務修改資源分配
通過修改ora$autotask_sub_plan子計劃佔用的資源，就可以修改自動維護任務的資源分配。

六、自動維護任務參考資訊
1.預定義的維護視窗

SQL> select WINDOW_NAME from DBA_AUTOTASK_WINDOW_CLIENTS;

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

7 rows selected

各個視窗的預設時間：
週一到週五分別是當日的22點到次日2點
週六、週日為6點至20點

 

==============================================================

[oracle@localhost ~]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Wed Jun 5 06:34:34 2013

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

SQL> conn /as sysdba
Connected.

--檢視哪些表儲存自動任務相關資訊：
SQL> select tname from tab where tname like 'DBA_AUTOTASK%';

TNAME
------------------------------
DBA_AUTOTASK_WINDOW_HISTORY
DBA_AUTOTASK_WINDOW_CLIENTS
DBA_AUTOTASK_TASK
DBA_AUTOTASK_SCHEDULE         --儲存了系統中有自動執行任務的執行歷史資訊
DBA_AUTOTASK_OPERATION
DBA_AUTOTASK_JOB_HISTORY
DBA_AUTOTASK_CLIENT_JOB       --當前執行的有自動維護任務建立的任務
DBA_AUTOTASK_CLIENT_HISTORY
DBA_AUTOTASK_CLIENT           --儲存了系統中有哪些自動執行的任務

9 rows selected.

--檢視自動執行的任務：
SQL> select a.client_name,a.consumer_group,a.client_tag,
  2  a.priority_override,a.attributes,a.window_group from dba_autotask_client a;

CLIENT_NAME                     CONSUMER_GROUP           CLIENT_TAG PRIORITY_OVERRIDE ATTRIBUTES                                             WINDOW_GROUP
------------------------------- ------------------------ ---------- ----------------- ------------------------------------------------------ --------------
auto optimizer stats collection ORA$AUTOTASK_STATS_GROUP OS         INVALID           ON BY DEFAULT, VOLATILE, SAFE TO KILL                  ORA$AT_WGRP_OS
auto space advisor              ORA$AUTOTASK_SPACE_GROUP SA         INVALID           ON BY DEFAULT, VOLATILE, SAFE TO KILL                  ORA$AT_WGRP_SA
sql tuning advisor              ORA$AUTOTASK_SQL_GROUP   SQ         INVALID           ONCE PER WINDOW, ON BY DEFAULT, VOLATILE, SAFE TO KILL ORA$AT_WGRP_SQ

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26451536/viewspace-766212/，如需轉載，請註明出處，否則將追究法律責任。