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