[20241129]21c下测试软软解析改cursor pin S的mutext gets的变化.txt

--//21c下测试软软解析改cursor pin S的mutext gets的变化

1.环境：
SCOTT@book01p> @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.

SYS@book> alter system flush shared_pool;
System altered.

2.建立测试环境：
SCOTT@book01p> create table deptxxx as select * from dept;
Table created.

$ uniq -c m2.sh
     1 #! /bin/bash
     1 sleep $(echo $1/500 | bc -l )
     1 sqlplus -s -l scott/book@book01p <<EOF  >/dev/null
     1 set feedback off
     1 select count(1) from deptxxx;
 19999 /
     1 quit
     1 EOF

3.测试：
 $ /usr/local/bin/zzdate ; seq 10 | xargs -IQ -P 10  ./m2.sh Q ; /usr/local/bin/zzdate
trunc(sysdate)+16/24+47/1440+58/86400 1732870078.723943549
trunc(sysdate)+16/24+48/1440+30/86400 1732870110.913587749

SYS@book> @ mutexprofz idn,hash,loc,maddr "ts>=trunc(sysdate)+16/24+47/1440+58/86400 and ts<=trunc(sysdate)+16/24+48/1440+30/86400 and idn=2945576271"
-- MutexProf by Tanel Poder (http://www.tanelpoder.com)
-- Showing profile of top 20 sleeps...
-- column info : id idn hash hash_value=>hash_value ts=>sleep_timestamp
--               req=>requesting_session blk=>blocking_session val=>mutex_value maddr=>mutex_addr

SUM_SLEEPS      GETS_DIFF MUTEX_TYPE             IDN       HASH GET_LOCATION                      mutex_addr           OBJECT_NAME
---------- -------------- --------------- ---------- ---------- --------------------------------- -------------------- ----------------------------
         4         221257 Cursor Pin      2945576271 2945576271 kksLockDelete [KKSCHLPIN6]        0000000061310570     select count(1) from deptxxx
         2          50202 Cursor Pin      2945576271 2945576271 kksfbc [KKSCHLPIN1]               0000000061310570     select count(1) from deptxxx

SYS@book> @ opeek 0000000061310570 24 0
[061310570, 061310588) = 00000000 00000000 00061AA6 0000000B AF91ED4F 00000000

SYS@book> @ xto10 00061AA6 16
BASE16                         BASE10                         ERRMSG
------------------------------ ------------------------------ ------
00061aa6                       400038                         good
--//该语句一共执行10*20000 = 200000，实际上每次基本gets 2次。多了38次怎么回事，也许硬解析，软解析测试不同。

4.继续：
--// 重新登录，第1次执行软解析。
SCOTT@book01p> select count(1) from deptxxx;
  COUNT(1)
----------
         4

SYS@book> @ opeek 0000000061310570 24 0
[061310570, 061310588) = 00000000 00000000 00061AA8 0000000B AF91ED4F 00000000

--//第2次执行软软解析.
SCOTT@book01p> select count(1) from deptxxx;

  COUNT(1)
----------
         4

SYS@book> @ opeek 0000000061310570 24 0
[061310570, 061310588) = 00000000 00000000 00061AAA 0000000B AF91ED4F 00000000
--//可以看出每个软解析以及软软解析都是gets 2次。

5.看看硬解析需要几次：

SYS@book> @ sharepool/shp4  2utgtqyrt3vag 0
HANDLE_TYPE            KGLHDADR         KGLHDPAR         C40                                        KGLHDLMD   KGLHDPMD   KGLHDIVC KGLOBHD0         KGLOBHD6           KGLOBHS0   KGLOBHS6   KGLOBT16   N0_6_16        N20   KGLNAHSH KGLOBT03        KGLOBT09
---------------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
child handle address   000000006961A100 0000000061310DF0 select count(1) from deptxxx                      1          0          0 000000006130F900 0000000061310618       8128      12128       3293     23549      23549 2945576271 2utgtqyrt3vag          0
parent handle address  0000000061310DF0 0000000061310DF0 select count(1) from deptxxx                      1          0          0 000000006CF10B60 00                     4064          0          0      4064       4064 2945576271 2utgtqyrt3vag      65535

SYS@book> @ ksmsp 000000006CF10B60 '' ''

GET_LOCATION                      KSMCHPTR           KSMCHIDX   KSMCHDUR KSMCHCOM           KSMCHSIZ KSMCHCLS   KSMCHTYP KSMCHPAR         KSMCHPTR_BEGIN   KSMCHPTR_END+1    HEAP_DESC
--------------------------------- ---------------- ---------- ---------- ---------------- ---------- -------- ---------- ---------------- ---------------- ----------------- -------------------------
SGA                               000000006CF10AF0          1          1 KGLDA                   544 freeabl           0 00               000000006CF10AF0 000000006CF10D10
SGA                               000000006130FDC0          1          1 KGLH0^af91ed4f         4096 recr           4095 000000006CF10B60 000000006130FDC0 0000000061310DC0  KSMCHPAR=000000006CF10B60

SYS@book> @ fchaz 0000000061310570
GET_LOCATION                      KSMCHPTR           KSMCHIDX   KSMCHDUR KSMCHCOM           KSMCHSIZ KSMCHCLS   KSMCHTYP KSMCHPAR         KSMCHPTR_BEGIN   KSMCHPTR_END+1
--------------------------------- ---------------- ---------- ---------- ---------------- ---------- -------- ---------- ---------------- ---------------- -----------------
SGA                               000000006130FDC0          1          1 KGLH0^af91ed4f         4096 recr           4095 000000006CF10B60 000000006130FDC0 0000000061310DC0

--//0000000061310570-000000006130FDC0 = 0x7b0 = 1968.
--//muext在父游标堆0开始偏移0x7b0 ， 1968的位置。

$ disp_addr.sh 000000006130FDC0 7b0
0x61310570:     0x00000000      0x00000000      0x00061ab2      0x0000000b
0x61310580:     0xaf91ed4f      0x00000000      0x00000000      0x00000000

SYS@book> @ opeek 0000000061310570 24 0
[061310570, 061310588) = 00000000 00000000 00061AB2 0000000B AF91ED4F 00000000

--//正常相同语句该mutex的偏移地址不会变动。

SYS@book> alter system flush shared_pool;
System altered.

SYS@book> alter system flush shared_pool;
System altered.

SCOTT@book01p> select count(1) from deptxxx;

  COUNT(1)
----------
         4

@ sharepool/shp4  2utgtqyrt3vag 0
HANDLE_TYPE            KGLHDADR         KGLHDPAR         C40                                        KGLHDLMD   KGLHDPMD   KGLHDIVC KGLOBHD0         KGLOBHD6           KGLOBHS0   KGLOBHS6   KGLOBT16   N0_6_16        N20   KGLNAHSH KGLOBT03        KGLOBT09
---------------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
child handle address   000000006961A100 0000000061310DF0 select count(1) from deptxxx                      1          0          0 000000006130F900 0000000061310618       8128      12128       3293     23549      23549 2945576271 2utgtqyrt3vag          0
parent handle address  0000000061310DF0 0000000061310DF0 select count(1) from deptxxx                      1          0          0 000000006CF10B60 00                     4064          0          0      4064       4064 2945576271 2utgtqyrt3vag      65535

SYS@book> @ ksmsp 000000006CDD0FC8 '' ''
GET_LOCATION                      KSMCHPTR           KSMCHIDX   KSMCHDUR KSMCHCOM           KSMCHSIZ KSMCHCLS   KSMCHTYP KSMCHPAR         KSMCHPTR_BEGIN   KSMCHPTR_END+1    HEAP_DESC
--------------------------------- ---------------- ---------- ---------- ---------------- ---------- -------- ---------- ---------------- ---------------- ----------------- -------------------------
SGA                               000000006FE3A180          1          1 KGLH0^af91ed4f         4096 recr           4095 000000006CDD0FC8 000000006FE3A180 000000006FE3B180  KSMCHPAR=000000006CDD0FC8
SGA                               000000006CDD0F58          1          1 KGLDA                   512 freeabl           0 00               000000006CDD0F58 000000006CDD1158

$ disp_addr.sh 000000006FE3A180  7b0 8
0x6fe3a930:     0x00000000      0x00000000      0x00000004      0x00000000
0x6fe3a940:     0xaf91ed4f      0x00000000      0x00000000      0x00000000
--//第1次硬解析是4次gets。
--//af91ed4f = 2945576271,与KGLNAHSH一致。

$ disp_addr.sh 000000006FE3A180  7b0 8
0x6fe3a930:     0x00000000      0x00000000      0x00000006      0x00000000
0x6fe3a940:     0xaf91ed4f      0x00000000      0x00000000      0x00000000
--//第2次软解析是2次gets。

 $ disp_addr.sh 000000006FE3A180  7b0 8
0x6fe3a930:     0x00000000      0x00000000      0x00000008      0x00000000
0x6fe3a940:     0xaf91ed4f      0x00000000      0x00000000      0x00000000
--//第3次软解析是2次gets。

$ disp_addr.sh 000000006FE3A180  7b0 8
0x6fe3a930:     0x00000000      0x00000000      0x0000000a      0x00000000
0x6fe3a940:     0xaf91ed4f      0x00000000      0x00000000      0x00000000
--//第3次软软解析是2次gets。
--//仅仅多了2次,也许前面几次执行存在一些竞争。

--//修改m2.sh,sleep 间隔延长。
$ uniq -c m2.sh
      1 #! /bin/bash
      1 sleep $(echo $1/2 | bc -l )
      1 sqlplus -s -l scott/book@book01p <<EOF  >/dev/null
      1 set feedback off
      1 select count(1) from deptxxx;
  19999 /
      1 quit
      1 EOF

$ /usr/local/bin/zzdate ; seq 2 | xargs -IQ -P 2  ./m2.sh Q ; /usr/local/bin/zzdate
trunc(sysdate)+17/24+16/1440+26/86400 1732871786.465136942
trunc(sysdate)+17/24+16/1440+39/86400 1732871799.895447755

$ disp_addr.sh 000000006BA11338  7b0 8
0x6ba11ae8:     0x00000000      0x00000000      0x00013882      0x00000000
0x6ba11af8:     0xaf91ed4f      0x00000000      0x00000000      0x00000000

SYS@book> @ xto10 00013882 16
BASE16                         BASE10                         ERRMSG
------------------------------ ------------------------------ ------
00013882                       80002                          good
--//20000*2*2+2  = 80002.
--//细节不再探究，基本gets每次2次。