[20211025]12c sequence nocache测试补充.txt

[20211025]12c sequence nocache测试补充.txt --//上午做了12c sequence nocache测试，出现以前从来没有遇到的row cache mutex等待事件，下午在仔细探究看看。 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.测试： $ alias zzdate alias zzdate='date +'\''trunc(sysdate)+%H/24+%M/1440+%S/86400 == %Y/%m/%d %T'\''' $ zzdate;seq 20 | xargs -I{} -P 20 sqlplus -s -l scott/book@test01p @m14.txt 1e4 P=20 {} 1 >/dev/null;zzdate trunc(sysdate)+16/24+36/1440+41/86400 == 2021/10/25 16:36:41 trunc(sysdate)+16/24+37/1440+01/86400 == 2021/10/25 16:37:01 SYS@test01p> @tpt/ashtop event,sql_id "module='SQL*Plus' and username='SCOTT'" trunc(sysdate)+16/24+36/1440+41/86400  trunc(sysdate)+16/24+37/1440+01/86400     Total   Seconds     AAS %This   EVENT                                    SQL_ID        FIRST_SEEN          LAST_SEEN --------- ------- ------- ---------------------------------------- ------------- ------------------- -------------------       334    16.7   89% | row cache lock                           9cp836a3k67w2 2021-10-25 16:36:43 2021-10-25 16:37:00        38     1.9   10% | row cache mutex                          9cp836a3k67w2 2021-10-25 16:36:42 2021-10-25 16:37:00         3      .2    1% | log file sync                                          2021-10-25 16:36:41 2021-10-25 16:36:41         1      .1    0% |                                          9cp836a3k67w2 2021-10-25 16:36:52 2021-10-25 16:36:52 --//有点奇怪的是我前面写的注解丢失了，不知道为什么。SELECT /* &&3 */ seq1.NEXTVAL into v_id FROM DUAL;. SYS@test01p> @tpt/ashtop event,sql_id,p1,p2,p3 "module='SQL*Plus' and username='SCOTT' and event like 'row cache%'"     trunc(sysdate)+16/24+36/1440+41/86400  trunc(sysdate)+16/24+37/1440+01/86400     Total   Seconds     AAS %This   EVENT            SQL_ID         P1  P2  P3 FIRST_SEEN          LAST_SEEN --------- ------- ------- ---------------- ------------- --- --- --- ------------------- -------------------       334    16.7   90% | row cache lock   9cp836a3k67w2  13   0   5 2021-10-25 16:36:43 2021-10-25 16:37:00        21     1.1    6% | row cache mutex  9cp836a3k67w2  13  19   0 2021-10-25 16:36:42 2021-10-25 16:36:59         7      .4    2% | row cache mutex  9cp836a3k67w2  13  31   0 2021-10-25 16:36:45 2021-10-25 16:37:00         6      .3    2% | row cache mutex  9cp836a3k67w2  13  13   0 2021-10-25 16:36:43 2021-10-25 16:36:55         4      .2    1% | row cache mutex  9cp836a3k67w2  13  11   0 2021-10-25 16:36:47 2021-10-25 16:36:57 SYS@test01p> @ ev_name 'row cache lock' SYS@test01p> @ prxx ============================== EVENT#                        : 328 EVENT_ID                      : 1714089451 NAME                          : row cache lock PARAMETER1                    : cache id PARAMETER2                    : mode PARAMETER3                    : request WAIT_CLASS_ID                 : 3875070507 WAIT_CLASS#                   : 4 WAIT_CLASS                    : Concurrency DISPLAY_NAME                  : row cache lock CON_ID                        : 0 PL/SQL procedure successfully completed. --//先不探究row cache lock等待事件。 SYS@test01p> @ ev_name 'row cache mutex' SYS@test01p> @ prxx ============================== EVENT#                        : 327 EVENT_ID                      : 306610566 NAME                          : row cache mutex PARAMETER1                    : cache id PARAMETER2                    : where requested PARAMETER3                    : WAIT_CLASS_ID                 : 3875070507 WAIT_CLASS#                   : 4 WAIT_CLASS                    : Concurrency DISPLAY_NAME                  : row cache mutex CON_ID                        : 0 PL/SQL procedure successfully completed. --//PARAMETER1=cache id,PARAMETER2=where requested. SYS@test01p> select * from v$rowcache where cache#=13   2  @ prxx ============================== CACHE#                        : 13 TYPE                          : PARENT SUBORDINATE#                  : PARAMETER                     : dc_sequences COUNT                         : 9 USAGE                         : 9 FIXED                         : 0 GETS                          : 200008 FASTGETS                      : 0 GETMISSES                     : 9 SCANS                         : 0 SCANMISSES                    : 0 SCANCOMPLETES                 : 0 MODIFICATIONS                 : 200008 FLUSHES                       : 200008 DLM_REQUESTS                  : 0 DLM_CONFLICTS                 : 0 DLM_RELEASES                  : 0 CON_ID                        : 0 PL/SQL procedure successfully completed. --//说明发生在seq。 SYS@test01p> @ sqlid 9cp836a3k67w2 SQL_ID        HASH_VALUE SQLTEXT ------------- ---------- --------------------------------- 9cp836a3k67w2 2267225986 SELECT SEQ1.NEXTVAL FROM DUAL --//不理解为什么把我的注解给丢失了。 SYS@test01p> column LOCATION format a40 SYS@test01p> select * from x$mutex_sleep where mutex_type='Row Cache' order by sleeps desc; ADDR                   INDX    INST_ID     CON_ID MUTEX_TYPE MUTEX_TYPE_ID LOCATION_ID LOCATION                         SLEEPS  WAIT_TIME ---------------- ---------- ---------- ---------- ---------- ------------- ----------- -------------------------------- ------ ---------- 0000000031CD4298          3          1          0 Row Cache              4          19 [19] kqrpre                         548    1892880 0000000031CD4178          1          1          0 Row Cache              4          31 [31] kqrcmt                          54     165635 0000000031CD43B8          5          1          0 Row Cache              4          13 [13] kqreqd                          37     103333 0000000031CD4448          6          1          0 Row Cache              4          11 [11] kqrget                          17      29873 0000000031CD4328          4          1          0 Row Cache              4          17 [17] kqrCreateUsingSecondaryKey       6      20993 0000000031CD4208          2          1          0 Row Cache              4          25 [25] kqrpup                           1          6 0000000031CD40E8          0          1          0 Row Cache              4          32 [32] kqrsfd                           1        228 7 rows selected. --//猜测里面的LOCATION_ID=19,31,13,11 与前面P2一致。 SELECT *   FROM (  SELECT mutex_addr, COUNT (*)             FROM x$mutex_sleep_history            WHERE mutex_type = 'Row Cache'         GROUP BY mutex_addr         ORDER BY 2 DESC)  WHERE ROWNUM <= 5; MUTEX_ADDR         COUNT(*) ---------------- ---------- 000007FF17AAB5F8         16 000007FF0A968A10         12 000007FF17DC7FF8          4 000007FF178581D0          3 000007FF13930D20          2 --//能力有限，先探究到这里,而且windows的版本工具有限，还是找一台linux的环境在测试看看。