_resource_manager_always_on=FALSE引起排程異常的解決案例

一臺測試庫上所有的autotask當前均處於disable狀態，現在想立即啟用sql tuning advisor做一次SQL語句的自動調優，於是作了如下操作

---設定sql tuning advisor為enable狀態
SQL> select client_name,status from dba_autotask_client;

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

SQL> select window_name,WINDOW_NEXT_TIME,window_active,autotask_status,sql_tune_advisor from dba_Autotask_window_clients where window_name='THURSDAY_WINDOW';

WINDOW_NAME                    WINDOW_NEXT_TIME                                                            WINDO AUTOTASK SQL_TUNE
------------------------------ --------------------------------------------------------------------------- ----- -------- --------
THURSDAY_WINDOW                29-JAN-15 10.00.00.000000 PM +08:00                                         FALSE DISABLED DISABLED

SQL> select window_name,resource_plan from dba_scheduler_windows where window_name='THURSDAY_WINDOW';

WINDOW_NAME                    RESOURCE_PLAN
------------------------------ ------------------------------
THURSDAY_WINDOW               

exec dbms_auto_task_admin.enable;

exec dbms_auto_task_admin.enable('sql tuning advisor',NULL,NULL);

set linesize 150
SQL> select client_name,status from dba_autotask_client;

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

select window_name,WINDOW_NEXT_TIME,window_active,autotask_status,sql_tune_advisor from dba_Autotask_window_clients where window_name='THURSDAY_WINDOW';
WINDOW_NAME                    WINDOW_NEXT_TIME                                                            WINDO AUTOTASK SQL_TUNE
------------------------------ --------------------------------------------------------------------------- ----- -------- --------
THURSDAY_WINDOW                29-JAN-15 10.00.00.000000 PM +08:00                                         FALSE  ENABLED  ENABLED

exec dbms_Scheduler.close_window('THURSDAY_WINDOW');
BEGIN dbms_Scheduler.close_window('THURSDAY_WINDOW'); END;

*
ERROR at line 1:
ORA-27471: window "SYS.THURSDAY_WINDOW" is already closed
ORA-06512: at "SYS.DBMS_ISCHED", line 509
ORA-06512: at "SYS.DBMS_SCHEDULER", line 1236
ORA-06512: at line 1

---設定一個即將到來的時間，以儘快發起sql tuning advisor
exec dbms_scheduler.set_attribute('THURSDAY_WINDOW','repeat_interval','freq=daily;byday=THU;byhour=10;byminute=50; bysecond=0');

select window_name,WINDOW_NEXT_TIME,window_active,autotask_status,sql_tune_advisor from dba_Autotask_window_clients where window_name='THURSDAY_WINDOW';
WINDOW_NAME                    WINDOW_NEXT_TIME                                                            WINDO AUTOTASK SQL_TUNE
------------------------------ --------------------------------------------------------------------------- ----- -------- --------
THURSDAY_WINDOW                29-JAN-15 10.50.00.000000 AM +08:00                                         FALSE  ENABLED  ENABLED

---發現autotask雖然正常發起，但馬上報錯退出，檢查dba_scheduler_job_run_details報錯原因為ORA-29373
col additional_info format a20
col log_date format a30
col job_name format a20    
col status format a10
set linesize 140
select log_date,job_name,status,additional_info from dba_scheduler_job_run_details where job_name='ORA$AT_SQ_SQL_SW_817' order by log_date desc;
LOG_DATE                       JOB_NAME             STATUS     ADDITIONAL_INFO
------------------------------ -------------------- ---------- --------------------
29-JAN-15 10.50.01.303830 AM + ORA$AT_SQ_SQL_SW_817 FAILED     ORA-29373: resource
08:00                                                          manager is not on

折騰了好一會兒與另外一個庫進行了橫向比較發現測試庫上的隱含引數_resource_manager_always_on被設成了FALSE
SQL> show parameter _resource_manager_always_on

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
_resource_manager_always_on          boolean     FALSE

---設定_resource_manager_always_on=TRUE後再次測試，成功解決(_resource_manager_always_on修改後需要重啟資料庫)
alter system set "_resource_manager_always_on"=TRUE scope=spfile; 

startup force
show parameter _resource_manager_always_on
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
_resource_manager_always_on          boolean     TRUE

exec dbms_Scheduler.close_window('THURSDAY_WINDOW');

exec dbms_scheduler.set_attribute('THURSDAY_WINDOW','repeat_interval','freq=daily;byday=THU;byhour=11;byminute=25; bysecond=0');
                                                
---約1小時左右task完成
col additional_info format a20
col log_date format a30
col job_name format a20    
col status format a10
set linesize 140
select log_date,job_name,status,additional_info from dba_scheduler_job_run_details where job_name='ORA$AT_SQ_SQL_SW_475' order by log_date desc;

LOG_DATE                       JOB_NAME             STATUS     ADDITIONAL_INFO
------------------------------ -------------------- ---------- --------------------
29-JAN-15 12.20.07.975953 PM + ORA$AT_SQ_SQL_SW_818 SUCCEEDED
08:00

總結：_resource_manager_always_on引數控制資料庫啟動時是否啟用預設的resource_plan，在_resource_manager_always_on=TRUE的情況下，即使resource_manager_plan為空，仍然可以在v$rsrc_plan裡查詢到當前有一條名為internal_plan的資源計劃處於生效狀態
SQL> show parameter resource_manager

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
resource_manager_plan                string     

select * from v$rsrc_plan;
        ID NAME                             IS_TO CPU INS PARALLEL_SERVERS_ACTIVE PARALLEL_SERVERS_TOTAL PARALLEL_EXECUTION_MANAGED
---------- -------------------------------- ----- --- --- ----------------------- ---------------------- --------------------------------
     12546 INTERNAL_PLAN                    TRUE  OFF OFF                       0                     32 FIFO

在_resource_manager_always_on=FALSE的情況下v$rsrc_plan檢視為空。本例中是透過修改_resource_manager_always_on=TRUE，然後重啟instance的方法，如果不重啟instance，也可以像下面這樣透過為window顯式指定resource_plan的方式發起task
exec dbms_scheduler.set_attribute(name=>'THURSDAY_WINDOW',attribute=>'resource_plan',value=>'DEFAULT_MAINTENANCE_PLAN');
亦或者使用"alter system set resource_manager_plan='resource_plan_name' scope=memory;"的方式臨時指定一個resource_plan都能使autotask正常執行起來。
從以上測試我們可以看出_resource_manager_always_on=FALSE的作用並不是完全禁止resource_plan的使用，而在於當使用者不設定resource_plan的情況下(resource_manager_plan初始化引數為空或者window沒有關聯到某個resource_plan)，oracle也不會自動為使用者設定預設的resource plan。相反如果_resource_manager_always_on=TRUE(預設值)，在使用者不指定resource_manager_plan引數的情況下，oracle會為使用者指定預設的resource_plan，這個預設的resource plan可以從v$rsrc_plan看到。其實還有一個隱含引數_resource_manager_always_off(預設值為FALSE)，如果_resource_manager_always_off=TRUE的話那才是真正禁用了resource plan，這裡不再贅述，大家有興趣可以測一下