[20211025]12g sequemce nocahe测试.txt

[20211025]12g sequemce nocahe测试.txt --//上个星期我发现19c生产系统有几个sequence开发给设置为nocache属性.导致出现row cache lock. --//当我单独查询该语句的等待事件时,出现一个我以前从来没见过的row cache mutex的等待事件. --//难道19c做了某些改进,开始使用mutex。 --//我在12c重复测试看看。 1.环境: SCOTT@test01p> @ ver1 PORT_STRING                    VERSION        BANNER                                                                               CON_ID ------------------------------ -------------- -------------------------------------------------------------------------------- ---------- IBMPC/WIN_NT64-9.1.0           12.2.0.1.0     Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production              0 create table job_times (sid number, time_ela number,method varchar2(20)); create sequence seq1 nocache; SCOTT@test01p> @ ddl scott.seq1 C300 ------------------------------------------------------------------------------------------------------------------------------------------------------------------ CREATE SEQUENCE  "SCOTT"."SEQ1"  MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 NOCACHE  NOORDER  NOCYCLE  NOKEEP  NOSCALE  GLOBAL ; $ cat m14.txt set verify off --//host sleep $(echo &&3/50 | bc -l ) variable vmethod varchar2(20); exec :vmethod := '&&2'; insert into job_times values ( sys_context ('userenv', 'sid') ,dbms_utility.get_time ,:vmethod) ; commit ; declare v_id number; v_d date; v_val VARCHAR2(1000); l_count PLS_INTEGER; begin     for i in 1 .. &&1 loop         --//select value into v_val from v$nls_parameters where parameter = 'NLS_CHARACTERSET';         --//select value into v_val from nls_database_parameters where parameter = 'NLS_CHARACTERSET';         --//select sql_text into v_val from v$sql where rownum=1;         SELECT /* &&3 */ seq1.NEXTVAL into v_id FROM DUAL;     end loop; end ; / update job_times set time_ela = dbms_utility.get_time - time_ela where sid=sys_context ('userenv', 'sid') and method=:vmethod; commit; quit 2.测试: --//我没有想到这么慢，在没有打开cache的情况下,只能减少并发数量以及循环次数1e4: $ zzdate;seq 20 | xargs -I{} -P 20 sqlplus -s -l scott/book@test01p @m14.txt 1e4 P=20 {} 1 >/dev/null;zzdate trunc(sysdate)+10/24+05/1440+21/86400 == 2021/10/25 10:05:21 trunc(sysdate)+10/24+05/1440+42/86400 == 2021/10/25 10:05:42 --// 21秒。 SYS@test01p> @tpt/ashtop event,sql_id "module='SQL*Plus' and username='SCOTT'" trunc(sysdate)+10/24+05/1440+21/86400 trunc(sysdate)+10/24+05/1440+42/86400     Total   Seconds     AAS %This   EVENT                                    SQL_ID        FIRST_SEEN          LAST_SEEN --------- ------- ------- ---------------------------------------- ------------- ------------------- -------------------       338    16.1   92% | row cache lock                           9cp836a3k67w2 2021-10-25 10:05:22 2021-10-25 10:05:41        26     1.2    7% | row cache mutex                          9cp836a3k67w2 2021-10-25 10:05:22 2021-10-25 10:05:41         1      .0    0% |                                          9cp836a3k67w2 2021-10-25 10:05:29 2021-10-25 10:05:29         1      .0    0% |                                                        2021-10-25 10:05:33 2021-10-25 10:05:33 --//出现row cache lock，row cache mutex 两个等待事件。而且row cache mutex等待的出现事件在2021-10-25 10:05:22 到 --//2021-10-25 10:05:41之间,也就是从测试开始到结束。 --//你可以发现对比11g多了一个row cache mutex的等待事件。 --//11g 下类似测试： SYS@book>  @ashtop event,sql_id "module='SQL*Plus' and username='SCOTT'" trunc(sysdate)+09/24+12/1440+14/86400 trunc(sysdate)+09/24+14/1440+24/86400     Total   Seconds     AAS %This   EVENT                                    SQL_ID        FIRST_SEEN          LAST_SEEN --------- ------- ------- ---------------------------------------- ------------- ------------------- -------------------      6288    48.4   97% | row cache lock                           9cp836a3k67w2 2021-10-25 09:12:14 2021-10-25 09:14:23       152     1.2    2% |                                          9cp836a3k67w2 2021-10-25 09:12:14 2021-10-25 09:14:23         4      .0    0% |                                                        2021-10-25 09:12:51 2021-10-25 09:14:21         2      .0    0% |                                          a01fjfup7ruah 2021-10-25 09:12:52 2021-10-25 09:13:12         2      .0    0% |                                          gxgnku5buj8cm 2021-10-25 09:13:51 2021-10-25 09:14:09         1      .0    0% | log file switch (checkpoint incomplete)  9cp836a3k67w2 2021-10-25 09:14:07 2021-10-25 09:14:07         1      .0    0% |                                          1mm9uymckm8z4 2021-10-25 09:12:28 2021-10-25 09:12:28         1      .0    0% |                                          24c64p3xa1hr5 2021-10-25 09:13:28 2021-10-25 09:13:28         1      .0    0% |                                          7b9znjngh150d 2021-10-25 09:13:17 2021-10-25 09:13:17         1      .0    0% |                                          fsc8vbts8tu24 2021-10-25 09:12:26 2021-10-25 09:12:26         1      .0    0% |                                          g2q7km1t9cdnj 2021-10-25 09:13:29 2021-10-25 09:13:29         1      .0    0% |                                          gpa5h7jh3dg4d 2021-10-25 09:14:10 2021-10-25 09:14:10         1      .0    0% |                                          gty6quqfxrgna 2021-10-25 09:13:39 2021-10-25 09:13:39 13 rows selected. --//仅仅出现row cache lock 等待事件。 SYS@test01p> Select method,count(*),round(avg(TIME_ELA),0),sum(TIME_ELA) from scott.job_times group by method order by 3 ; METHOD                 COUNT(*) ROUND(AVG(TIME_ELA),0) SUM(TIME_ELA) -------------------- ---------- ---------------------- ------------- P=20                         20                   1941         38826