[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的環境在測試看看。

[20211025]12c sequence nocache測試補充.txt

相關文章