Shulou(Shulou.com)06/02 Report--

Recently, there have been many wait events SQL > select * from v$version for rman backup after database upgrade.

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

Query alert log information 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

It can be seen here that because SCHEDULER regularly starts and shuts down the DEFAULT_MAINTENANCE_PLAN of resource management, which causes Resource Manager plan to be enabled from 10:00 to 2 p.m., all rman runs tasks, and Backup Restore Throttle sleep waits because Resource Manager plan is enabled (using SCHEDULER to control it on and off). In many cases, the application of database running is relatively single, so it is not very necessary to start resource management.

The shutdown method in 11g is as follows: 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 ('', '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','')

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.