[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);