最近数据库升级后rman备份出现了很多等待事件SQL> select * from v$version;
BANNER——————————————————————————Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit ProductionPL/SQL Release 11.2.0.3.0 – ProductionCORE 11.2.0.3.0 ProductionTNS for Linux: Version 11.2.0.3.0 – ProductionNLSRTL Version 11.2.0.3.0 – Production
SQL> SQL> SELECT sid,event FROM v$session WHERE wait_class#<>6;
SID EVENT———- —————————————————————- 1165 Backup Restore Throttle sleep 1208 Backup Restore Throttle sleep 1292 Backup Restore Throttle sleep 1379 Backup Restore Throttle sleep 1552 Backup Restore Throttle sleep 2239 resmgr:cpu quantum 2542 SQL*Net message to client
查询alert日志信息Tue Oct 23 22:00:00 2012Setting Resource Manager plan SCHEDULER[0x256B668]:DEFAULT_MAINTENANCE_PLAN via scheduler windowSetting Resource Manager plan DEFAULT_MAINTENANCE_PLAN via parameterTue Oct 23 22:00:00 2012Starting background process VKRMTue Oct 23 22:00:00 2012VKRM started with pid=120, OS id=16419 Tue Oct 23 22:00:02 2012Begin automatic SQL Tuning Advisor run for special tuning task “SYS_AUTO_SQL_TUNING_TASK”Tue Oct 23 22:00:34 2012Thread 1 advanced to log sequence 18849 (LGWR switch) Current log# 1 seq# 18849 mem# 0: /oradata/finance/datafile/redo01.log Current log# 1 seq# 18849 mem# 1: /oradata/finance/datafile/redo01_1.logTue Oct 23 22:00:38 2012Archived Log entry 19993 added for thread 1 sequence 18848 ID 0x99d4983c dest 1:Tue Oct 23 23:00:05 2012End automatic SQL Tuning Advisor run for special tuning task “SYS_AUTO_SQL_TUNING_TASK”Tue Oct 23 23:30:10 2012Thread 1 advanced to log sequence 18850 (LGWR switch) Current log# 4 seq# 18850 mem# 0: /oradata/finance/datafile/redo04.log Current log# 4 seq# 18850 mem# 1: /oradata/finance/datafile/redo04_4.logTue Oct 23 23:30:14 2012Archived Log entry 19994 added for thread 1 sequence 18849 ID 0x99d4983c dest 1:Wed Oct 24 01:17:07 2012Thread 1 advanced to log sequence 18851 (LGWR switch) Current log# 5 seq# 18851 mem# 0: /oradata/finance/datafile/redo05.log Current log# 5 seq# 18851 mem# 1: /oradata/finance/datafile/redo05_5.logWed Oct 24 01:17:17 2012Archived Log entry 19995 added for thread 1 sequence 18850 ID 0x99d4983c dest 1:Wed Oct 24 02:00:00 2012Closing scheduler windowClosing Resource Manager plan via scheduler windowClearing Resource Manager plan via parameterWed Oct 24 07:23:20 2012
从这里可以看出来，因为SCHEDULER定时启动和关闭资源管理的DEFAULT_MAINTENANCE_PLAN从而导致在晚上10点到2点Resource Manager plan处于启用状态,所有rman运行任务，Backup Restore Throttle sleep等待是因为Resource Manager plan启用导致(使用SCHEDULER控制其启用和关闭),很多情况下数据库跑的应用比较单一，不是十分的需要启动资源管理.
在11g中关闭方法如下：1. Set the current resource manager plan to null (or another plan that is not restrictive): alter system set resource_manager_plan=” scope=both; 2. Change the active windows to use the null resource manager plan (or other nonrestrictive plan) using: execute dbms_scheduler.set_attribute(‘WEEKNIGHT_WINDOW’,’RESOURCE_PLAN’,”);execute dbms_scheduler.set_attribute(‘WEEKEND_WINDOW’,’RESOURCE_PLAN’,”); 3. Then, for each window_name (WINDOW_NAME from DBA_SCHEDULER_WINDOWS), run: execute dbms_scheduler.set_attribute(‘<window name>’,’RESOURCE_PLAN’,”);
SQL> select WINDOW_NAME from DBA_SCHEDULER_WINDOWS;
WINDOW_NAME——————————WEEKNIGHT_WINDOWWEEKEND_WINDOWMONDAY_WINDOWTUESDAY_WINDOWWEDNESDAY_WINDOWTHURSDAY_WINDOWFRIDAY_WINDOWSATURDAY_WINDOWSUNDAY_WINDOW
9 rows selected.
execute dbms_scheduler.set_attribute(‘WEEKNIGHT_WINDOW’,’RESOURCE_PLAN’,”);execute dbms_scheduler.set_attribute(‘WEEKEND_WINDOW’,’RESOURCE_PLAN’,”);execute dbms_scheduler.set_attribute(‘MONDAY_WINDOW’,’RESOURCE_PLAN’,”);execute dbms_scheduler.set_attribute(‘TUESDAY_WINDOW’,’RESOURCE_PLAN’,”);execute dbms_scheduler.set_attribute(‘WEDNESDAY_WINDOW’,’RESOURCE_PLAN’,”);execute dbms_scheduler.set_attribute(‘THURSDAY_WINDOW’,’RESOURCE_PLAN’,”);execute dbms_scheduler.set_attribute(‘FRIDAY_WINDOW’,’RESOURCE_PLAN’,”);execute dbms_scheduler.set_attribute(‘SATURDAY_WINDOW’,’RESOURCE_PLAN’,”);execute dbms_scheduler.set_attribute(‘SUNDAY_WINDOW’,’RESOURCE_PLAN’,”);
