oracle 11.1.0.6 版本中的resmgr:cpu quantum 等待事件

前几天,帮客户看套生产系统,系统是部署在windows 64bit+oracle 11.1.0.6下的,反映非常慢 1.看awr报告
Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 100.00 In-memory Sort %: 100.00
Library Hit %: 92.40 Soft Parse %: 95.18
Execute to Parse %: 20.30 Latch Hit %: 99.47
Parse CPU to Parse Elapsd %: 0.01 % Non-Parse CPU: 98.31
Shared Pool Statistics Begin End
------ ------
Memory Usage %: 10.60 49.48
% SQL with executions>1: 47.69 89.24
% Memory for SQL w/exec>1: 48.49 89.16
Top 5 Timed Foreground Events
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Avg
wait % DB
Event Waits Time(s) (ms) time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
resmgr:cpu quantum 4,960,271 241,511 49 70.4 Scheduler
DB CPU 42,635 12.4
enq: TX - row lock contention 82 41,544 5.1E+05 12.1 Applica
SQL*Net more data to client 3,738,976 16,080 4 4.7 Network
SQL*Net break/reset to client 418,805 9,469 23 2.8 Applicatio

发现resmgr:cpu quantum 等待事件非常高,而且占用db时间达到70%以上
通过查metalink,这是启用了11g 功能resouce plan资源计划管理功能

通过取消这项功能,resmgr:cpu quantum 等待事件消失,以下为具体命令

alter system set resource_manager_plan='';
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','');

Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 100.00 In-memory Sort %: 100.00
Library Hit %: 92.40 Soft Parse %: 95.18
Execute to Parse %: 20.30 Latch Hit %: 99.47
Parse CPU to Parse Elapsd %: 0.01 % Non-Parse CPU: 98.31
Shared Pool Statistics Begin End
------ ------
Memory Usage %: 10.60 49.48
% SQL with executions>1: 47.69 89.24
% Memory for SQL w/exec>1: 48.49 89.16
Top 5 Timed Foreground Events
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Avg
wait % DB
Event Waits Time(s) (ms) time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
resmgr:cpu quantum 4,960,271 241,511 49 70.4 Scheduler
DB CPU 42,635 12.4
enq: TX - row lock contention 82 41,544 5.1E+05 12.1 Applica
SQL*Net more data to client 3,738,976 16,080 4 4.7 Network
SQL*Net break/reset to client 418,805 9,469 23 2.8 Applicatio