ITPub博客

首页 > 数据库 > Oracle > [20191203]大量resmgrcpu quantum等待事件.txt

[20191203]大量resmgrcpu quantum等待事件.txt

原创 Oracle 作者:lfree 时间:2019-12-03 11:55:24 0 删除 编辑

[20191203]大量resmgrcpu quantum等待事件.txt

--//生产系统遇到大量resmgr:cpu quantum等待,出现严重问题是12.1号(正好星期天).机器已经重启.事后分析.

1.环境:
SYS@192.168.xx.zzz:1521/orcl> @ ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
IBMPC/WIN_NT64-9.1.0           11.2.0.1.0     Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
--//windows的版本,11.2.0.1 这个版本问题多多...

2.分析:
SYS@192.168.xx.zzz:1521/orcl> select trunc(sysdate-2)+8/24 , trunc(sysdate-2)+9/24 from dual ;
TRUNC(SYSDATE-2)+8/ TRUNC(SYSDATE-2)+9/
------------------- -------------------
2019-12-01 08:00:00 2019-12-01 09:00:00

SYS@192.168.xx.zzz:1521/orcl> @ tpt/ash/dash_wait_chains event2 1=1 trunc(sysdate-2)+8/24  trunc(sysdate-2)+9/24

-- Display ASH Wait Chain Signatures script v0.2 BETA by Tanel Poder ( http://blog.tanelpoder.com )
%This     SECONDS        AAS WAIT_CHAIN
------ ---------- ---------- -------------------------------
  64%       25240          7 -> ON CPU
  36%       14360          4 -> resmgr:cpu quantum
   0%          30          0 -> control file parallel write
   0%          10          0 -> db file scattered read

SYS@192.168.xx.zzz:1521/orcl> show parameter cpu_count
NAME       TYPE     VALUE
---------- -------- -----
cpu_count  integer  6

--//25240/3600 = 7.01.而仅仅6个CPU,没打开多线程吗?无法登录服务器不了解相关设置.明显cpu资源已经耗尽,无法相应前台的登录请求,大量
--//ora-12xxx错误.

SYS@192.168.xx.zzz:1521/orcl> @ tpt/ash/dash_wait_chains event2 session_type='FOREGROUND' trunc(sysdate-2)+8/24  trunc(sysdate-2)+9/24
-- Display ASH Wait Chain Signatures script v0.2 BETA by Tanel Poder ( http://blog.tanelpoder.com )
%This     SECONDS        AAS WAIT_CHAIN
------ ---------- ---------- --------------------------------
  98%       14230          4 -> resmgr:cpu quantum
   2%         340         .1 -> ON CPU
--//前台主要等待事件是resmgr:cpu quantum

SYS@192.168.xx.zzz:1521/orcl> @ tpt/ash/dash_wait_chains program2||event2 1=1 trunc(sysdate-2)+8/24  trunc(sysdate-2)+9/24
-- Display ASH Wait Chain Signatures script v0.2 BETA by Tanel Poder ( http://blog.tanelpoder.com )
%This     SECONDS        AAS WAIT_CHAIN
------ ---------- ---------- ----------------------------------------
  63%       24850        6.9 -> rman.exe ON CPU
  33%       12920        3.6 -> (wnwp.exe) resmgr:cpu quantum
   1%         420         .1 -> (TJWorklist.exe) resmgr:cpu quantum
   1%         340         .1 -> (wnwp.exe) ON CPU
   1%         300         .1 -> (rman.exe) resmgr:cpu quantum
   1%         290         .1 -> (zxtopacs.exe) resmgr:cpu quantum
   0%         130          0 -> (Mnnn) resmgr:cpu quantum
   0%         120          0 -> (oracle) resmgr:cpu quantum
   0%          90          0 -> (Jnnn) resmgr:cpu quantum
   0%          90          0 -> (plsqldev.exe) resmgr:cpu quantum
   0%          30          0 -> (CKPT) control file parallel write
   0%          20          0 -> (CTWR) ON CPU
   0%          20          0 -> (PMON) ON CPU
   0%          10          0 -> (Mnnn) db file scattered read
   0%          10          0 -> (DBRM) ON CPU
15 rows selected.
--//还在备份什么回事....备份没完成吗?

SYS@192.168.xx.zzz:1521/orcl> @ ev_name "resmgr:cpu quantum"
    EVENT#   EVENT_ID NAME               PARAMETER1           PARAMETER2           PARAMETER3           WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS
---------- ---------- ------------------ -------------------- -------------------- -------------------- ------------- ----------- ----------
       286 1452455426 resmgr:cpu quantum location                                                          2396326234          10 Scheduler

--//查询v$rman_backup_job_details视图发现备份时间与后台调度的分析时间重合.都是22点上下.结果不贴出了.

SELECT *
  FROM DBA_AUTOTASK_JOB_HISTORY
 WHERE client_name       =  'auto optimizer stats collection'
   AND window_start_time >= '2019/11/29 22:00:00.378000 +08:00'
 order by 3;

SYS@192.168.xx.zzz:1521/orcl> select * from DBA_AUTOTASK_JOB_HISTORY where client_name='auto optimizer stats collection' and window_start_time>= '2019/11/29 22:00:00.378000 +08:00' order by 3;
CLIENT_NAME                     WINDOW_NAME          WINDOW_START_TIME                        WINDOW_DURATION                JOB_NAME                JOB_STATUS JOB_START_TIME                     JOB_DURATION    JOB_ERROR JOB_INFO
------------------------------- -------------------- ---------------------------------------- ------------------------------ ----------------------- ---------- ---------------------------------- -------------- ---------- --------------------
auto optimizer stats collection FRIDAY_WINDOW        2019-11-29 22:00:00.378000 +08:00        +000000000 03:59:59.668000     ORA$AT_OS_OPT_SY_16545  SUCCEEDED  2019-11-29 22:00:00.955000 +08:00  +000 00:01:11           0
auto optimizer stats collection SATURDAY_WINDOW      2019-11-30 06:00:00.135000 +08:00        +000000000 19:59:59.914000     ORA$AT_OS_OPT_SY_16550  SUCCEEDED  2019-11-30 22:02:52.153000 +08:00  +000 00:00:11           0
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
auto optimizer stats collection SATURDAY_WINDOW      2019-11-30 06:00:00.135000 +08:00        +000000000 19:59:59.914000     ORA$AT_OS_OPT_SY_16546  SUCCEEDED  2019-11-30 06:00:01.554000 +08:00  +000 00:00:14           0
auto optimizer stats collection SATURDAY_WINDOW      2019-11-30 06:00:00.135000 +08:00        +000000000 19:59:59.914000     ORA$AT_OS_OPT_SY_16547  SUCCEEDED  2019-11-30 10:00:15.683000 +08:00  +000 00:00:42           0
auto optimizer stats collection SATURDAY_WINDOW      2019-11-30 06:00:00.135000 +08:00        +000000000 19:59:59.914000     ORA$AT_OS_OPT_SY_16548  SUCCEEDED  2019-11-30 14:01:08.141000 +08:00  +000 00:00:10           0
auto optimizer stats collection SATURDAY_WINDOW      2019-11-30 06:00:00.135000 +08:00        +000000000 19:59:59.914000     ORA$AT_OS_OPT_SY_16549  SUCCEEDED  2019-11-30 18:01:59.976000 +08:00  +000 00:00:06           0
auto optimizer stats collection SUNDAY_WINDOW        2019-12-01 06:00:00.200000 +08:00        +000000000 19:59:59.855000     ORA$AT_OS_OPT_SY_16564  SUCCEEDED  2019-12-01 10:05:29.013000 +08:00  +000 00:00:56           0
auto optimizer stats collection SUNDAY_WINDOW        2019-12-01 06:00:00.200000 +08:00        +000000000 19:59:59.855000     ORA$AT_OS_OPT_SY_16551  SUCCEEDED  2019-12-01 06:00:02.259000 +08:00  +000 00:04:17           0
8 rows selected.

--//注意看下划线.WINDOW_START_TIME='2019-11-30 06:00:00.135000 +08:00',而实际上JOB_START_TIME在2019-11-30 22:02:52.153000 +08:00
--//才开始执行.执行时间仅仅11秒.说明11.30号已经出现问题.
--//注:12.2下午我已经停止auto optimizer stats collection的分析.
--//exec DBMS_AUTO_TASK_ADMIN.DISABLE(client_name => 'auto optimizer stats collection',operation => NULL,window_name => NULL);
--//我不知道是备份还是auto optimizer stats collection分析导致出现性能问题.两者重合了,激活了resmgr:cpu quantum这个bug.
--//如果上网查询: 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('<window name>','RESOURCE_PLAN','');

execute dbms_scheduler.set_attribute('SATURDAY_WINDOW','RESOURCE_PLAN','');
execute dbms_scheduler.set_attribute('SUNDAY_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','');

--//问题在于我们根本没有启动资源管理.
SYS@192.168.xx.zzz:1521/orcl> show parameter resource_manager_plan
NAME                  TYPE    VALUE
--------------------- ------- --------
resource_manager_plan string

SYS@192.168.xx.zzz:1521/orcl> @ hide _resource_manager_always_o
NAME                           DESCRIPTION                         DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE ISSES ISSYS_MOD
------------------------------ ----------------------------------- ------------- ------------- ------------ ----- ---------
_io_resource_manager_always_on io resource manager always on       TRUE          FALSE         FALSE        FALSE FALSE
_resource_manager_always_off   disable the resource manager always TRUE          FALSE         FALSE        FALSE FALSE
_resource_manager_always_on    enable the resource manager always  TRUE          TRUE          TRUE         FALSE FALSE

--//SQL> alter system set "_resource_manager_always_off"=true scope=spfile;
--//SQL> alter system set "_resource_manager_always_on"=false scope=spfile;

SYS@192.168.xx.zzz:1521/orcl> SELECT window_name ,resource_plan ,repeat_interval ,duration FROM DBA_SCHEDULER_WINDOWS;
WINDOW_NAME      RESOURCE_PLAN            REPEAT_INTERVAL                                                           DURATION
---------------- ------------------------ ------------------------------------------------------------------------- -------------
MONDAY_WINDOW    DEFAULT_MAINTENANCE_PLAN freq=daily;byday=MON;byhour=22;byminute=0; bysecond=0                     +000 04:00:00
TUESDAY_WINDOW   DEFAULT_MAINTENANCE_PLAN freq=daily;byday=TUE;byhour=22;byminute=0; bysecond=0                     +000 04:00:00
WEDNESDAY_WINDOW DEFAULT_MAINTENANCE_PLAN freq=daily;byday=WED;byhour=22;byminute=0; bysecond=0                     +000 04:00:00
THURSDAY_WINDOW  DEFAULT_MAINTENANCE_PLAN freq=daily;byday=THU;byhour=22;byminute=0; bysecond=0                     +000 04:00:00
FRIDAY_WINDOW    DEFAULT_MAINTENANCE_PLAN freq=daily;byday=FRI;byhour=22;byminute=0; bysecond=0                     +000 04:00:00
SATURDAY_WINDOW  DEFAULT_MAINTENANCE_PLAN freq=daily;byday=SAT;byhour=6;byminute=0; bysecond=0                      +000 20:00:00
SUNDAY_WINDOW    DEFAULT_MAINTENANCE_PLAN freq=daily;byday=SUN;byhour=6;byminute=0; bysecond=0                      +000 20:00:00
WEEKNIGHT_WINDOW                          freq=daily;byday=MON,TUE,WED,THU,FRI;byhour=22;byminute=0; bysecond=0     +000 08:00:00
WEEKEND_WINDOW                            freq=daily;byday=SAT;byhour=0;byminute=0;bysecond=0                       +002 00:00:00
9 rows selected.
--//注意看DURATION时间.
--//难道WINDOW_NAME受DEFAULT_MAINTENANCE_PLAN的控制.

--//解决如下:
execute dbms_scheduler.set_attribute('SATURDAY_WINDOW','RESOURCE_PLAN','');
execute dbms_scheduler.set_attribute('SUNDAY_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','');

SYS@192.168.xx.zzz:1521/orcl> SELECT window_name ,resource_plan ,repeat_interval ,duration FROM DBA_SCHEDULER_WINDOWS;
WINDOW_NAME      RESOURCE_PLAN             REPEAT_INTERVAL                                                       DURATION
---------------- ------------------------- --------------------------------------------------------------------- -------------
MONDAY_WINDOW                              freq=daily;byday=MON;byhour=22;byminute=0; bysecond=0                 +000 04:00:00
TUESDAY_WINDOW                             freq=daily;byday=TUE;byhour=22;byminute=0; bysecond=0                 +000 04:00:00
WEDNESDAY_WINDOW                           freq=daily;byday=WED;byhour=22;byminute=0; bysecond=0                 +000 04:00:00
THURSDAY_WINDOW                            freq=daily;byday=THU;byhour=22;byminute=0; bysecond=0                 +000 04:00:00
FRIDAY_WINDOW                              freq=daily;byday=FRI;byhour=22;byminute=0; bysecond=0                 +000 04:00:00
SATURDAY_WINDOW                            freq=daily;byday=SAT;byhour=6;byminute=0; bysecond=0                  +000 20:00:00
SUNDAY_WINDOW                              freq=daily;byday=SUN;byhour=6;byminute=0; bysecond=0                  +000 20:00:00
WEEKNIGHT_WINDOW                           freq=daily;byday=MON,TUE,WED,THU,FRI;byhour=22;byminute=0; bysecond=0 +000 08:00:00
WEEKEND_WINDOW                             freq=daily;byday=SAT;byhour=0;byminute=0;bysecond=0                   +002 00:00:00
9 rows selected.

alter system set "_resource_manager_always_off"=true scope=spfile;
alter system set "_resource_manager_always_on"=false scope=spfile;

--//注:修改这2个参数要重启.我目前无法重启.先暂时停止auto optimizer stats collection的分析.
SYS@192.168.xx.zzz:1521/orcl> exec DBMS_AUTO_TASK_ADMIN.DISABLE(client_name => 'auto optimizer stats collection',operation => NULL,window_name => NULL);
PL/SQL procedure successfully completed.

--//分析主要参考链接:https://www.iteye.com/blog/dbzone-2359348

总结:
--//解决如下:
execute dbms_scheduler.set_attribute('SATURDAY_WINDOW','RESOURCE_PLAN','');
execute dbms_scheduler.set_attribute('SUNDAY_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','');

alter system set "_resource_manager_always_off"=true scope=spfile;
alter system set "_resource_manager_always_on"=false scope=spfile;

--//临时解决,关闭相关schedule任务.
--//exec DBMS_AUTO_TASK_ADMIN.DISABLE(client_name => 'auto optimizer stats collection',operation => NULL,window_name => NULL);

SYS@192.168.xx.zzz:1521/orcl> column ATTRIBUTES format a55
SYS@192.168.xx.zzz:1521/orcl> select client_name, status,attributes,window_group from dba_autotask_client;
CLIENT_NAME                     STATUS           ATTRIBUTES                                              WINDOW_GROUP
------------------------------- ---------------- ------------------------------------------------------- ---------------
auto optimizer stats collection DISABLED         ON BY DEFAULT, VOLATILE, SAFE TO KILL                   ORA$AT_WGRP_OS
auto space advisor              DISABLED         ON BY DEFAULT, VOLATILE, SAFE TO KILL                   ORA$AT_WGRP_SA
sql tuning advisor              DISABLED         ONCE PER WINDOW, ON BY DEFAULT, VOLATILE, SAFE TO KILL  ORA$AT_WGRP_SQ

SYS@192.168.xx.zzz:1521/orcl> @ bg dbrm
old   1: select  addr,pid,spid,program from v$process where background=1 and PROGRAM like upper('%&1%')
new   1: select  addr,pid,spid,program from v$process where background=1 and PROGRAM like upper('%dbrm%')
ADDR                 PID SPID   PROGRAM
---------------- ------- ------ --------------------------
0000000390614218       6 6672   ORACLE.EXE (DBRM)

--//正常设置"_resource_manager_always_off"=true,"_resource_manager_always_on"=false,应该看不到DBRM进程,我给在测试环境测试看看.

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-2666643/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2548
  • 访问量
    6335616