[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进程,我给在测试环境测试看看.
[20191203]大量resmgrcpu quantum等待事件.txt
来源：这里教程网
时间：2026-03-03 14:39:29
作者：
编辑推荐：
- [20191203]大量resmgrcpu quantum等待事件.txt03-03
- 如何分析及处理 Flink 反压？03-03
- 案例ORA-00600: internal error code, arguments: [qkaffsindex3], [], [], [], []03-03
- [20191203]enq: ZA - add std audit table partition.txt03-03
- SQL 判断字段是否以数字开头或者包含数字03-03
- 基于 Flink 的实时数仓生产实践03-03
- 中报背后的阿里影业：互联网影视如何沉淀平台方法论03-03
- oracle 错误03-03
下一篇：
相关推荐
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
雷神推出 MIX PRO II 迷你主机：基于 Ultra 200H，玻璃上盖 + ARGB 灯效
2 月 9 日消息，雷神 (THUNDEROBOT) 现已宣布推出基于英
-
制造商 Musnap 推出彩色墨水屏电纸书 Ocean C：支持手写笔、第三方安卓应用
2 月 10 日消息，制造商 Musnap 现已在海外推出一款 Oce
热文推荐
- 如何分析及处理 Flink 反压？
如何分析及处理 Flink 反压？
26-03-03 - 基于 Flink 的实时数仓生产实践
基于 Flink 的实时数仓生产实践
26-03-03 - 中报背后的阿里影业：互联网影视如何沉淀平台方法论
中报背后的阿里影业：互联网影视如何沉淀平台方法论
26-03-03 - oracle 报大小写错误
oracle 报大小写错误
26-03-03 - oracle 函数
oracle 函数
26-03-03 - oracle
oracle
26-03-03 - 【感恩节福利】你说谢谢我送礼，小米音箱、手机话费、京东购物卡等好礼等你来拿!
- latch free等待问题之Result Cache:RC Latch优化案例
- 阿里云基于OSS的云上统一数据保护方案2.0技术解析
阿里云基于OSS的云上统一数据保护方案2.0技术解析
26-03-03 - 阿里云祝顺民（江鹤）：云原生SDWAN加速企业上云 引领未来智能网络
阿里云祝顺民（江鹤）：云原生SDWAN加速企业上云 引领未来智能网络
26-03-03
