[20241125]关闭resmgr cpu quantum.txt

来源：这里教程网时间：2026-03-03 20:55:43 作者：

[20241125]关闭resmgr cpu quantum.txt --//由于在虚拟机器上测试，不小心就会触发resmgr:cpu quantum相关等待事件，不知道是否对测试有影响。 --//还是禁用它，看了相关文档结合21c的情况，做一个记录。 https://www.iteye.com/blog/dbzone-2359348 select window_name, resource_plan from dba_scheduler_windows; 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_on"=false scope=spfile sid='*'; --//alter system set "_resource_manager_always_off"=true scope=spfile sid='*'; SQL> alter system set resource_manager_plan='' scope=both; SQL> alter system set "_resource_manager_always_off"=true scope=spfile; SQL> alter system set "_resource_manager_always_on"=false scope=spfile; --//感觉执行后面2个就可以了。 1.环境： SYS@book> @ver2 ============================== PORT_STRING : x86_64/Linux 2.4.xx VERSION : 21.0.0.0.0 BANNER : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production BANNER_FULL : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production Version 21.3.0.0.0 BANNER_LEGACY : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production CON_ID : 0 PL/SQL procedure successfully completed. 2.操作步骤： SYS@book> @ hidez ^_resource_manager_always_ SYS@book> @ pr ============================== NUM : 488 N_HEX : 1E8 NAME : _resource_manager_always_off DESCRIPTION : disable the resource manager always DEFAULT_VALUE : TRUE SESSION_VALUE : FALSE SYSTEM_VALUE : FALSE ISSES_MODIFIABLE : FALSE ISSYS_MODIFIABLE : FALSE PL/SQL procedure successfully completed. --//21c 不存在_resource_manager_always_on隐含参数。 SYS@book> alter system set "_resource_manager_always_off"=true scope=spfile; System altered. --//重启数据库，观察： SYS@book> @ hidez ^_resource_manager_always_ SYS@book> @ pr ============================== NUM : 488 N_HEX : 1E8 NAME : _resource_manager_always_off DESCRIPTION : disable the resource manager always DEFAULT_VALUE : FALSE SESSION_VALUE : TRUE SYSTEM_VALUE : TRUE ISSES_MODIFIABLE : FALSE ISSYS_MODIFIABLE : FALSE PL/SQL procedure successfully completed. SYS@book01p> @hidez ^_resource_manager_always_ SYS@book01p> @ pr ============================== NUM : 488 N_HEX : 1E8 NAME : _resource_manager_always_off DESCRIPTION : disable the resource manager always DEFAULT_VALUE : FALSE SESSION_VALUE : TRUE SYSTEM_VALUE : TRUE ISSES_MODIFIABLE : FALSE ISSYS_MODIFIABLE : FALSE PL/SQL procedure successfully completed. --//cdb与pdb下观察 "_resource_manager_always_off"=true。 3.测试： $ cat m9.txt set verify off variable v_method varchar2(20) exec :v_method := '&&2'; --insert into job_times values ( sys_context ('userenv', 'sid') ,dbms_utility.get_time ,:v_method) ; --insert into job_times values ( sys_context ('userenv', 'sid') ,dbms_utility.get_time ,'&&2') ; --commit ; DECLARE l_count PLS_INTEGER; BEGIN FOR i IN 1..&&1 LOOP EXECUTE IMMEDIATE 'Select /*+ &2 */ count(*) from t where id = :j ' INTO l_count USING i; END LOOP; END; / --update job_times set time_ela = dbms_utility.get_time - time_ela where sid=sys_context ('userenv', 'sid') and method= :v_method; --commit; quit $ zzdate;seq 50 | xargs -P 50 -IQ sqlplus -s -l scott/book@book01p @m9.txt 4e5 AAAA > /dev/null;zzdate trunc(sysdate)+15/24+41/1440+05/86400 1732520465.940667774 trunc(sysdate)+15/24+42/1440+47/86400 1732520567.752239102 SYS@book> @ ashtop event 1=1 trunc(sysdate)+15/24+41/1440+05/86400 sysdate Total Distinct Distinct Distinct Seconds AAS %This EVENT FIRST_SEEN LAST_SEEN Execs Seen Tstamps Execs Seen1 --------- ------- ------- ------------------------------------------ ------------------- ------------------- ---------- -------- ----------- 3353 18.5 69% | 2024-11-25 15:41:06 2024-11-25 15:43:53 1744 113 1855 1477 8.2 31% | cursor: pin S 2024-11-25 15:41:09 2024-11-25 15:42:46 1 95 95 3 .0 0% | control file parallel write 2024-11-25 15:41:28 2024-11-25 15:41:41 1 3 3 1 .0 0% | wait for unread message on broadcast 2024-11-25 15:43:41 2024-11-25 15:43:41 1 1 1 channel --//观察一段时间看看是否不再出现。 3.顺便关闭自动任务： SYS@book> @ task CLIENT_NAME STATUS ATTRIBUTES WINDOW_GROUP ----------------------------------- ---------- -------------------------------------------------------- -------------------- sql tuning advisor DISABLED ONCE PER WINDOW, ON BY DEFAULT, VOLATILE, SAFE TO KILL ORA$AT_WGRP_SQ 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 STMT ------------------------------------------------------------------------------------------ exec dbms_auto_task_admin.enable('sql tuning advisor', null, null); exec dbms_auto_task_admin.enable('auto optimizer stats collection', null, null); exec dbms_auto_task_admin.enable('auto space advisor', null, null);