_resource_manager_always_on=FALSE引起调度异常的解决案例

最新推荐文章于 2024-01-23 21:01:40 发布

cuiqiong6888

最新推荐文章于 2024-01-23 21:01:40 发布

阅读量924

点赞数

文章标签：数据库

一台测试库上所有的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;

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');

---发现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，这里不再赘述，大家有兴趣可以测一下