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

来源：这里教程网时间：2026-03-03 20:56:56 作者：

[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次。