[20250208]library cache mutex X与library cache bucket mutex X的区别(21c).txt
[20250208]library cache mutex X与library cache bucket mutex X的区别(21c).txt
--//以前在学习参考文档里面:链接https://tanelpoder.com/files/Oracle_Latch_And_Mutex_Contention_Troubleshooting.pdf
--//P17页,如下内容:
PARAMETER1 - idn:
. cursor:* wait events
. idn = hash value of the library cache object under protection
. library cache: mutex* wait events
1) library cache hash bucket number (if idn <= 131072)
2) idn = hash value of the library cache object under protection (if idn > 131072)
--//似乎idn<131072,library cache hash bucket number.idn > 131072,hash value of the library cache object under
--//protection有点武断,有可能sql语句的hash_value有可能小于131072,这篇pdf文档建立日期非常早2009年,我记忆里当时看了几个
--//生产库,没有找到sql语句的hash_value小于131072的情况,可以猜测出现idn <= 131072,大部分原因是library cache hash bucket
--//number 的可能性还是很大.
--//hash_value最大0xffffffff = 4294967295, 131072/4294967295 = .00003051757813210542,相当于10万里面有3个,概率还是不大。
--//自已一段时间没事做这方面测试,首先要找到一个sql语句hash_value<131072,正好最近一段时间有空,尝试看看。
--//library cache mutex X与library cache bucket mutex X的区别,两者有点混淆,做1个测试说明细节。
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.
2.测试前准备:
--//首先要找到一个sql语句hash_value<131072。
$ cat m9.txt
DECLARE
l_count PLS_INTEGER;
BEGIN
FOR i IN 1..&&1
LOOP
EXECUTE IMMEDIATE 'select /*+ &&2 */ count(*) from dept where deptno = '||i INTO l_count ;
END LOOP;
END;
/
--//注:没有使用绑定变量.
$ seq 10 | xargs -IQ sqlplus -s -l scott/book@book01p @ m9.txt 1e5 Q > /dev/null
--//注意不能并发执行,不然即使出现也很快从共享池清理出去。
select * from (select sql_text,hash_value from v$sql where hash_value<131072 ) order by 2 ;
select sql_text,hash_value from v$sql order by 2;
--//而且要一边执行m9.txt脚本,一边执行以上sql语句。
--//找到2条:
select /*+ 2 */ count(*) from dept where deptno = 41554;
selecT /*+ 1 */ count(*) from dept where deptno = 49094;
SCOTT@book01p> select /*+ 2 */ count(*) from dept where deptno = 41554;
COUNT(*)
----------
0
SCOTT@book01p> @ hashz
HASH_VALUE SQL_ID CHILD_NUMBER KGL_BUCKET HASH_HEX SQL_EXEC_START SQL_EXEC_ID
---------- ------------- ------------ ---------- ---------- ------------------- -----------
37807 7rc8r200014xg 0 37807 93af 2025-02-08 16:03:53 16777216
--//HASH_VALUE=KGL_BUCKET=37807
--//建立测试脚本如下:
$ cat ma.txt
alter session set session_cached_cursors=0;
DECLARE
v_pad VARCHAR2 (200);
l_count PLS_INTEGER;
BEGIN
FOR i IN 1..&&1
LOOP
EXECUTE IMMEDIATE 'select /*+ 2 */ count(*) from dept where deptno = 41554' INTO l_count;
END LOOP;
END;
/
--//设置session_cached_cursors=0;,这样每次执行都是软解析。
3.测试:
--//测试前刷新共享池,alter system flush shared_pool;因为前面执行的语句与脚本执行语句的游标不共享。
$ zzdate;seq 10 | xargs -P 10 -IQ sqlplus -s -l scott/book@book01p @ma.txt 1e6 > /dev/null;zzdate
trunc(sysdate)+16/24+24/1440+33/86400 -1739003073.214669336
trunc(sysdate)+16/24+26/1440+07/86400 1739003167.199566399
--//Sum = 93.984897063
SYS@book> @ ashtop event,p1raw,p1,p3raw 1=1 trunc(sysdate)+16/24+24/1440+33/86400 trunc(sysdate)+16/24+26/1440+07/86400
Total Distinct Distinct Distinct
Seconds AAS %This EVENT P1RAW P1 P3RAW FIRST_SEEN LAST_SEEN Execs Seen Tstamps Execs Seen1
--------- ------- ------- ------------------------------------------ ----------------- ---------- ----------------- ------------------- ------------------- ---------- -------- -----------
463 4.9 50% | 37807 2025-02-08 16:24:34 2025-02-08 16:26:06 151 93 235
196 2.1 21% | library cache: mutex X 00000000000093AF 37807 000093AF00000055 2025-02-08 16:24:35 2025-02-08 16:26:04 1 77 77
83 .9 9% | library cache: mutex X 00000000000093AF 37807 000093AF0000006A 2025-02-08 16:24:34 2025-02-08 16:26:03 10 54 10
72 .8 8% | library cache: mutex X 00000000000093AF 37807 000093AF00000039 2025-02-08 16:24:34 2025-02-08 16:26:04 1 54 54
43 .5 5% | library cache: bucket mutex X 00000000000093AF 37807 000000000000003E 2025-02-08 16:24:38 2025-02-08 16:25:56 10 22 10
17 .2 2% | cursor: pin S 00000000000093AF 37807 0000000500000000 2025-02-08 16:24:37 2025-02-08 16:25:59 1 15 15
17 .2 2% | 0 2025-02-08 16:24:38 2025-02-08 16:26:04 9 12 14
13 .1 1% | cursor: pin S 00000000000093AF 37807 0000000900000000 2025-02-08 16:24:42 2025-02-08 16:25:57 1 11 11
8 .1 1% | cursor: pin S wait on X 000000009843C600 2554578432 0000000500000000 2025-02-08 16:24:33 2025-02-08 16:24:33 1 1 1
6 .1 1% | cursor: mutex S 0000000000000000 0 0000000200000000 2025-02-08 16:24:38 2025-02-08 16:25:40 1 5 5
4 .0 0% | cursor: mutex S 00000000000093AF 37807 0000000100000000 2025-02-08 16:24:40 2025-02-08 16:26:02 1 4 4
3 .0 0% | 1 2025-02-08 16:25:17 2025-02-08 16:25:19 1 3 3
2 .0 0% | resmgr:cpu quantum 0000000000000003 3 0000000000000000 2025-02-08 16:25:18 2025-02-08 16:25:22 1 2 1
2 .0 0% | 100 2025-02-08 16:24:48 2025-02-08 16:24:50 1 2 2
1 .0 0% | library cache lock 000000006585F0B0 1703276720 0000000000520002 2025-02-08 16:24:33 2025-02-08 16:24:33 1 1 1
1 .0 0% | 20 2025-02-08 16:25:45 2025-02-08 16:25:45 1 1 1
1 .0 0% | 48 2025-02-08 16:24:51 2025-02-08 16:24:51 1 1 1
1 .0 0% | 1700754432 2025-02-08 16:24:33 2025-02-08 16:24:33 1 1 1
18 rows selected.
--//P1对应就是idn,21c估计做了改进拆分出来了library cache: bucket mutex X等待事情,在11g重复做1次,好像11g没有library
--//cache: bucket mutex X。
--//如果无法区分看P3RAW,前面8位我估计对应的就是sql语句的hash值,后面值转换位10进制 0x55=85 , 0x6a=106 , 0x39 = 57 ,0x3e= 62
--//补充说明在11g下P3RAW前面8位全部是00000000
--//在这样的情况下library cache: mutex X,library cache: bucket mutex X成为主要等待事件。
SYS@book> @ mutexprofz idn,hash,val,loc,maddr "ts>=trunc(sysdate)+16/24+24/1440+33/86400 and ts<=trunc(sysdate)+16/24+26/1440+07/86400 and idn=37807"
-- 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 VAL GET_LOCATION mutex_addr OBJECT_NAME
---------- -------------- --------------- ---------- ---------- ---------------- --------------------------------- -------------------- --------------------------------------------------------------------------------
7 23177 Library Cache 37807 37807 0000009000000000 kglhdgn1 62 000000006C962850 select /*+ 2 */ count(*) from dept where deptno = 41554
4 378806 Library Cache 37807 37807 0000009200000000 kgllkc1 57 00000000647A0208 select /*+ 2 */ count(*) from dept where deptno = 41554
3 Library Cache 37807 37807 0000011400000000 kglhdgn1 62 000000006C962850 select /*+ 2 */ count(*) from dept where deptno = 41554
3 Library Cache 37807 37807 0000009400000000 kgllkdl1 85 00000000647A0208 select /*+ 2 */ count(*) from dept where deptno = 41554
3 Library Cache 37807 37807 0000001100000000 kgllkdl1 85 00000000647A0208 select /*+ 2 */ count(*) from dept where deptno = 41554
3 Library Cache 37807 37807 0000009400000000 kglhdgn1 62 000000006C962850 select /*+ 2 */ count(*) from dept where deptno = 41554
2 hash table 37807 37807 0000011300000001 kkshGetNextChild [KKSHBKLOC1] 000000006479AB20 select /*+ 2 */ count(*) from dept where deptno = 41554
2 Library Cache 37807 37807 0000018900000000 kglhdgn1 62 000000006C962850 select /*+ 2 */ count(*) from dept where deptno = 41554
2 39432 Library Cache 37807 37807 0000009200000000 kglhdgn1 62 000000006C962850 select /*+ 2 */ count(*) from dept where deptno = 41554
2 Library Cache 37807 37807 0000001500000000 kglhdgn2 106 00000000647A0208 select /*+ 2 */ count(*) from dept where deptno = 41554
1 hash table 37807 37807 0000018900000001 kkshGetNextChild [KKSHBKLOC1] 000000006479AB20 select /*+ 2 */ count(*) from dept where deptno = 41554
1 hash table 37807 37807 0000009000000001 kkshGetNextChild [KKSHBKLOC1] 000000006479AB20 select /*+ 2 */ count(*) from dept where deptno = 41554
1 Library Cache 37807 37807 0000011300000000 kgllkdl1 85 00000000647A0208 select /*+ 2 */ count(*) from dept where deptno = 41554
1 Cursor Pin 37807 37807 0000009200000003 kksLockDelete [KKSCHLPIN6] 000000006479AD10 select /*+ 2 */ count(*) from dept where deptno = 41554
1 Cursor Pin 37807 37807 0000001100000001 kkslce [KKSCHLPIN2] 000000006479AD10 select /*+ 2 */ count(*) from dept where deptno = 41554
1 Cursor Pin 37807 37807 0000001500000004 kkslce [KKSCHLPIN2] 000000006479AD10 select /*+ 2 */ count(*) from dept where deptno = 41554
1 Cursor Pin 37807 37807 00 kkslce [KKSCHLPIN2] 000000006479AD10 select /*+ 2 */ count(*) from dept where deptno = 41554
1 Cursor Pin 37807 37807 0000011300000002 kksLockDelete [KKSCHLPIN6] 000000006479AD10 select /*+ 2 */ count(*) from dept where deptno = 41554
1 Cursor Pin 37807 37807 0000000000000002 kksLockDelete [KKSCHLPIN6] 000000006479AD10 select /*+ 2 */ count(*) from dept where deptno = 41554
1 hash table 37807 37807 0000009200000001 kkshGetNextChild [KKSHBKLOC1] 000000006479AB20 select /*+ 2 */ count(*) from dept where deptno = 41554
20 rows selected.
--//注:这里条件非常特殊HASH_VALUE=KGL_BUCKET=37807,所以OBJECT_NAME都有值。
SYS@book> @ mutexprofz idn,hash,loc,maddr "ts>=trunc(sysdate)+16/24+24/1440+33/86400 and ts<=trunc(sysdate)+16/24+26/1440+07/86400 and idn=37807"
-- 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
---------- -------------- --------------- ---------- ---------- --------------------------------- -------------------- --------------------------------------------------------------------------------
19 245905 Library Cache 37807 37807 kglhdgn1 62 000000006C962850 select /*+ 2 */ count(*) from dept where deptno = 41554
10 761233 hash table 37807 37807 kkshGetNextChild [KKSHBKLOC1] 000000006479AB20 select /*+ 2 */ count(*) from dept where deptno = 41554
9 714961 Library Cache 37807 37807 kgllkdl1 85 00000000647A0208 select /*+ 2 */ count(*) from dept where deptno = 41554
6 446755 Cursor Pin 37807 37807 kkslce [KKSCHLPIN2] 000000006479AD10 select /*+ 2 */ count(*) from dept where deptno = 41554
5 528077 Library Cache 37807 37807 kgllkc1 57 00000000647A0208 select /*+ 2 */ count(*) from dept where deptno = 41554
3 235980 Cursor Pin 37807 37807 kksLockDelete [KKSCHLPIN6] 000000006479AD10 select /*+ 2 */ count(*) from dept where deptno = 41554
2 Library Cache 37807 37807 kglhdgn2 106 00000000647A0208 select /*+ 2 */ count(*) from dept where deptno = 41554
1 Library Cache 37807 37807 kgllkdl1 85 00000000647A0208 select /*+ 2 */ count(*) from dept where deptno = 41554
8 rows selected.
SYS@book> @ sharepool/shp4 7rc8r200014xg 0
HANDLE_TYPE KGLHDADR KGLHDPAR C40 KGLHDLMD KGLHDPMD KGLHDIVC KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLOBT09
---------------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
child handle address 0000000064A84CA0 00000000647A00B0 select /*+ 2 */ count(*) from dept where 0 0 0 0000000064A84AE0 000000006479ADB8 8128 12128 3320 23576 23576 37807 7rc8r200014xg 0
parent handle address 00000000647A00B0 00000000647A00B0 select /*+ 2 */ count(*) from dept where 0 0 0 00000000648F4C40 00 4064 0 0 4064 4064 37807 7rc8r200014xg 65535
SYS@book> @ fchaz 000000006C962850
GET_LOCATION KSMCHPTR KSMCHIDX KSMCHDUR KSMCHCOM KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR KSMCHPTR_BEGIN KSMCHPTR_END+1
--------------------------------- ---------------- ---------- ---------- ---------------- ---------- -------- ---------- ---------------- ---------------- -----------------
SGA 000000006C960760 1 1 KGLSG 12304 perm 0 000000006C804000 000000006C960760 000000006C963770
--//该chunk就是library cache bucket mutex所在的chunk。
--//0x000000006C962850-0x10 = 0x6c962840
SYS@book> @ opeek 000000006C962840 48 0
[06C962840, 06C962870) = 647A00B0 00000000 647A00B0 00000000 00000000 00000000 00989812 00001624 000093AF 00000000 6630ADE8 00000000
--//前面2个8字节记录的sql语句的父游标地址00000000647A00B0。
--//gets数量0x00989812 = 10000402,从执行次数上可以对上。sleeps=0x00001624 = 5668。上面的查询仅仅19次。21c版本不再累积,
--//记录每次sleep次数。
SYS@book> select sum(sleeps) from x$mutex_sleep_history where mutex_identifier=37807 and mutex_addr='000000006C962850';
SUM(SLEEPS)
-----------
19
SYS@book> @ mutexprofz sleeps,idn,hash,loc,val,maddr "ts>=trunc(sysdate)+16/24+24/1440+33/86400 and ts<=trunc(sysdate)+16/24+26/1440+07/86400 and idn=37807 and maddr='000000006C962850'"
-- 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 SLEEPS IDN HASH GET_LOCATION VAL mutex_addr OBJECT_NAME
---------- -------------- --------------- ---------- ---------- ---------- ------------ ---------------- -------------------- --------------------------------------------------------------------------------
4 Library Cache 4 37807 37807 kglhdgn1 62 0000009000000000 000000006C962850 select /*+ 2 */ count(*) from dept where deptno = 41554
3 Library Cache 3 37807 37807 kglhdgn1 62 0000011400000000 000000006C962850 select /*+ 2 */ count(*) from dept where deptno = 41554
3 Library Cache 3 37807 37807 kglhdgn1 62 0000009400000000 000000006C962850 select /*+ 2 */ count(*) from dept where deptno = 41554
2 39432 Library Cache 1 37807 37807 kglhdgn1 62 0000009200000000 000000006C962850 select /*+ 2 */ count(*) from dept where deptno = 41554
2 Library Cache 2 37807 37807 kglhdgn1 62 0000009000000000 000000006C962850 select /*+ 2 */ count(*) from dept where deptno = 41554
2 Library Cache 2 37807 37807 kglhdgn1 62 0000018900000000 000000006C962850 select /*+ 2 */ count(*) from dept where deptno = 41554
1 Library Cache 1 37807 37807 kglhdgn1 62 0000009000000000 000000006C962850 select /*+ 2 */ count(*) from dept where deptno = 41554
1 Library Cache 1 37807 37807 kglhdgn1 62 00 000000006C962850 select /*+ 2 */ count(*) from dept where deptno = 41554
1 Library Cache 1 37807 37807 kglhdgn1 62 0000001500000000 000000006C962850 select /*+ 2 */ count(*) from dept where deptno = 41554
9 rows selected.
SYS@book> @ fchaz 000000006479AB20
GET_LOCATION KSMCHPTR KSMCHIDX KSMCHDUR KSMCHCOM KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR KSMCHPTR_BEGIN KSMCHPTR_END+1
--------------------------------- ---------------- ---------- ---------- ---------------- ---------- -------- ---------- ---------------- ---------------- -----------------
SGA 000000006479A560 1 1 KGLH0^93af 4096 recr 4095 00000000648F4C40 000000006479A560 000000006479B560
--//KSMCHPAR=00000000648F4C40,也就是父游标堆0的地址.
SYS@book> @ fchaz 00000000647A0208
GET_LOCATION KSMCHPTR KSMCHIDX KSMCHDUR KSMCHCOM KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR KSMCHPTR_BEGIN KSMCHPTR_END+1
--------------------------------- ---------------- ---------- ---------- ---------------- ---------- -------- ---------- ---------------- ---------------- -----------------
SGA 00000000647A0080 1 1 KGLHD 816 recr 80 00 00000000647A0080 00000000647A03B0
--//0x00000000647A0080+0x30 = 0x647a00b0 父游标句柄。
SYS@book> @ fchaz 000000006479AD10
GET_LOCATION KSMCHPTR KSMCHIDX KSMCHDUR KSMCHCOM KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR KSMCHPTR_BEGIN KSMCHPTR_END+1
--------------------------------- ---------------- ---------- ---------- ---------------- ---------- -------- ---------- ---------------- ---------------- -----------------
SGA 000000006479A560 1 1 KGLH0^93af 4096 recr 4095 00000000648F4C40 000000006479A560 000000006479B560
--//KSMCHPAR=00000000648F4C40,也就是父游标堆0的地址.
4.补充测试:
--//注解alter session set session_cached_cursors=0;
$ cat ma.txt
--alter session set session_cached_cursors=0;
DECLARE
v_pad VARCHAR2 (200);
l_count PLS_INTEGER;
BEGIN
FOR i IN 1..&&1
LOOP
EXECUTE IMMEDIATE 'select /*+ 2 */ count(*) from dept where deptno = 41554' INTO l_count;
END LOOP;
END;
/
$ zzdate;seq 10 | xargs -P 10 -IQ sqlplus -s -l scott/book@book01p @ma.txt 1e6 > /dev/null;zzdate
trunc(sysdate)+16/24+55/1440+49/86400 -1739004949.928895134
trunc(sysdate)+16/24+56/1440+33/86400 1739004993.707077407
--//Sum = 43.778182273
SYS@book> @ ashtop event,p1raw,p1,p3raw 1=1 trunc(sysdate)+16/24+55/1440+49/86400 trunc(sysdate)+16/24+56/1440+33/86400
Total Distinct Distinct Distinct
Seconds AAS %This EVENT P1RAW P1 P3RAW FIRST_SEEN LAST_SEEN Execs Seen Tstamps Execs Seen1
--------- ------- ------- ------------------------------------------ ----------------- ---------- ----------------- ------------------- ------------------- ---------- -------- -----------
213 4.8 51% | 37807 2025-02-08 16:55:50 2025-02-08 16:56:32 119 43 153
57 1.3 14% | resmgr:cpu quantum 0000000000000002 2 0000000000000000 2025-02-08 16:55:56 2025-02-08 16:56:31 1 29 29
35 .8 8% | resmgr:cpu quantum 0000000000000003 3 0000000000000000 2025-02-08 16:55:59 2025-02-08 16:56:29 10 19 10
33 .8 8% | cursor: pin S 00000000000093AF 37807 0000000900000000 2025-02-08 16:55:51 2025-02-08 16:56:32 1 19 19
30 .7 7% | cursor: pin S 00000000000093AF 37807 0000000300000000 2025-02-08 16:55:51 2025-02-08 16:56:31 1 21 21
29 .7 7% | 2 2025-02-08 16:55:58 2025-02-08 16:56:26 20 19 25
21 .5 5% | 3 2025-02-08 16:55:51 2025-02-08 16:56:30 17 16 20
3 .1 1% | 1413697536 2025-02-08 16:55:50 2025-02-08 16:55:50 3 1 3
8 rows selected.
--//见鬼resmgr:cpu quantum等待事件又出现了,前面测试忘记关闭resmgr:cpu quantum。
SYS@book> @ mutexprofz idn,hash,loc,maddr "ts>=trunc(sysdate)+16/24+55/1440+49/86400 and ts<=trunc(sysdate)+16/24+56/1440+33/86400 and idn=37807"
-- 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 484736 Cursor Pin 37807 37807 kksLockDelete [KKSCHLPIN6] 000000006479AD10 select /*+ 2 */ count(*) from dept where deptno = 41554
SYS@book> @ sharepool/shp4 7rc8r200014xg 0
HANDLE_TYPE KGLHDADR KGLHDPAR C40 KGLHDLMD KGLHDPMD KGLHDIVC KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLOBT09
---------------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
child handle address 0000000064A84CA0 00000000647A00B0 select /*+ 2 */ count(*) from dept where 0 0 0 0000000064A84AE0 000000006479ADB8 8128 12128 3320 23576 23576 37807 7rc8r200014xg 0
parent handle address 00000000647A00B0 00000000647A00B0 select /*+ 2 */ count(*) from dept where 0 0 0 00000000648F4C40 00 4064 0 0 4064 4064 37807 7rc8r200014xg 65535
SYS@book> @ fchaz 000000006479AD10
GET_LOCATION KSMCHPTR KSMCHIDX KSMCHDUR KSMCHCOM KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR KSMCHPTR_BEGIN KSMCHPTR_END+1
--------------------------------- ---------------- ---------- ---------- ---------------- ---------- -------- ---------- ---------------- ---------------- -----------------
SGA 000000006479A560 1 1 KGLH0^93af 4096 recr 4095 00000000648F4C40 000000006479A560 000000006479B560
--//KSMCHPAR=00000000648F4C40,也就是父游标堆0的地址.
--//以前在学习参考文档里面:链接https://tanelpoder.com/files/Oracle_Latch_And_Mutex_Contention_Troubleshooting.pdf
--//P17页,如下内容:
PARAMETER1 - idn:
. cursor:* wait events
. idn = hash value of the library cache object under protection
. library cache: mutex* wait events
1) library cache hash bucket number (if idn <= 131072)
2) idn = hash value of the library cache object under protection (if idn > 131072)
--//似乎idn<131072,library cache hash bucket number.idn > 131072,hash value of the library cache object under
--//protection有点武断,有可能sql语句的hash_value有可能小于131072,这篇pdf文档建立日期非常早2009年,我记忆里当时看了几个
--//生产库,没有找到sql语句的hash_value小于131072的情况,可以猜测出现idn <= 131072,大部分原因是library cache hash bucket
--//number 的可能性还是很大.
--//hash_value最大0xffffffff = 4294967295, 131072/4294967295 = .00003051757813210542,相当于10万里面有3个,概率还是不大。
--//自已一段时间没事做这方面测试,首先要找到一个sql语句hash_value<131072,正好最近一段时间有空,尝试看看。
--//library cache mutex X与library cache bucket mutex X的区别,两者有点混淆,做1个测试说明细节。
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.
2.测试前准备:
--//首先要找到一个sql语句hash_value<131072。
$ cat m9.txt
DECLARE
l_count PLS_INTEGER;
BEGIN
FOR i IN 1..&&1
LOOP
EXECUTE IMMEDIATE 'select /*+ &&2 */ count(*) from dept where deptno = '||i INTO l_count ;
END LOOP;
END;
/
--//注:没有使用绑定变量.
$ seq 10 | xargs -IQ sqlplus -s -l scott/book@book01p @ m9.txt 1e5 Q > /dev/null
--//注意不能并发执行,不然即使出现也很快从共享池清理出去。
select * from (select sql_text,hash_value from v$sql where hash_value<131072 ) order by 2 ;
select sql_text,hash_value from v$sql order by 2;
--//而且要一边执行m9.txt脚本,一边执行以上sql语句。
--//找到2条:
select /*+ 2 */ count(*) from dept where deptno = 41554;
selecT /*+ 1 */ count(*) from dept where deptno = 49094;
SCOTT@book01p> select /*+ 2 */ count(*) from dept where deptno = 41554;
COUNT(*)
----------
0
SCOTT@book01p> @ hashz
HASH_VALUE SQL_ID CHILD_NUMBER KGL_BUCKET HASH_HEX SQL_EXEC_START SQL_EXEC_ID
---------- ------------- ------------ ---------- ---------- ------------------- -----------
37807 7rc8r200014xg 0 37807 93af 2025-02-08 16:03:53 16777216
--//HASH_VALUE=KGL_BUCKET=37807
--//建立测试脚本如下:
$ cat ma.txt
alter session set session_cached_cursors=0;
DECLARE
v_pad VARCHAR2 (200);
l_count PLS_INTEGER;
BEGIN
FOR i IN 1..&&1
LOOP
EXECUTE IMMEDIATE 'select /*+ 2 */ count(*) from dept where deptno = 41554' INTO l_count;
END LOOP;
END;
/
--//设置session_cached_cursors=0;,这样每次执行都是软解析。
3.测试:
--//测试前刷新共享池,alter system flush shared_pool;因为前面执行的语句与脚本执行语句的游标不共享。
$ zzdate;seq 10 | xargs -P 10 -IQ sqlplus -s -l scott/book@book01p @ma.txt 1e6 > /dev/null;zzdate
trunc(sysdate)+16/24+24/1440+33/86400 -1739003073.214669336
trunc(sysdate)+16/24+26/1440+07/86400 1739003167.199566399
--//Sum = 93.984897063
SYS@book> @ ashtop event,p1raw,p1,p3raw 1=1 trunc(sysdate)+16/24+24/1440+33/86400 trunc(sysdate)+16/24+26/1440+07/86400
Total Distinct Distinct Distinct
Seconds AAS %This EVENT P1RAW P1 P3RAW FIRST_SEEN LAST_SEEN Execs Seen Tstamps Execs Seen1
--------- ------- ------- ------------------------------------------ ----------------- ---------- ----------------- ------------------- ------------------- ---------- -------- -----------
463 4.9 50% | 37807 2025-02-08 16:24:34 2025-02-08 16:26:06 151 93 235
196 2.1 21% | library cache: mutex X 00000000000093AF 37807 000093AF00000055 2025-02-08 16:24:35 2025-02-08 16:26:04 1 77 77
83 .9 9% | library cache: mutex X 00000000000093AF 37807 000093AF0000006A 2025-02-08 16:24:34 2025-02-08 16:26:03 10 54 10
72 .8 8% | library cache: mutex X 00000000000093AF 37807 000093AF00000039 2025-02-08 16:24:34 2025-02-08 16:26:04 1 54 54
43 .5 5% | library cache: bucket mutex X 00000000000093AF 37807 000000000000003E 2025-02-08 16:24:38 2025-02-08 16:25:56 10 22 10
17 .2 2% | cursor: pin S 00000000000093AF 37807 0000000500000000 2025-02-08 16:24:37 2025-02-08 16:25:59 1 15 15
17 .2 2% | 0 2025-02-08 16:24:38 2025-02-08 16:26:04 9 12 14
13 .1 1% | cursor: pin S 00000000000093AF 37807 0000000900000000 2025-02-08 16:24:42 2025-02-08 16:25:57 1 11 11
8 .1 1% | cursor: pin S wait on X 000000009843C600 2554578432 0000000500000000 2025-02-08 16:24:33 2025-02-08 16:24:33 1 1 1
6 .1 1% | cursor: mutex S 0000000000000000 0 0000000200000000 2025-02-08 16:24:38 2025-02-08 16:25:40 1 5 5
4 .0 0% | cursor: mutex S 00000000000093AF 37807 0000000100000000 2025-02-08 16:24:40 2025-02-08 16:26:02 1 4 4
3 .0 0% | 1 2025-02-08 16:25:17 2025-02-08 16:25:19 1 3 3
2 .0 0% | resmgr:cpu quantum 0000000000000003 3 0000000000000000 2025-02-08 16:25:18 2025-02-08 16:25:22 1 2 1
2 .0 0% | 100 2025-02-08 16:24:48 2025-02-08 16:24:50 1 2 2
1 .0 0% | library cache lock 000000006585F0B0 1703276720 0000000000520002 2025-02-08 16:24:33 2025-02-08 16:24:33 1 1 1
1 .0 0% | 20 2025-02-08 16:25:45 2025-02-08 16:25:45 1 1 1
1 .0 0% | 48 2025-02-08 16:24:51 2025-02-08 16:24:51 1 1 1
1 .0 0% | 1700754432 2025-02-08 16:24:33 2025-02-08 16:24:33 1 1 1
18 rows selected.
--//P1对应就是idn,21c估计做了改进拆分出来了library cache: bucket mutex X等待事情,在11g重复做1次,好像11g没有library
--//cache: bucket mutex X。
--//如果无法区分看P3RAW,前面8位我估计对应的就是sql语句的hash值,后面值转换位10进制 0x55=85 , 0x6a=106 , 0x39 = 57 ,0x3e= 62
--//补充说明在11g下P3RAW前面8位全部是00000000
--//在这样的情况下library cache: mutex X,library cache: bucket mutex X成为主要等待事件。
SYS@book> @ mutexprofz idn,hash,val,loc,maddr "ts>=trunc(sysdate)+16/24+24/1440+33/86400 and ts<=trunc(sysdate)+16/24+26/1440+07/86400 and idn=37807"
-- 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 VAL GET_LOCATION mutex_addr OBJECT_NAME
---------- -------------- --------------- ---------- ---------- ---------------- --------------------------------- -------------------- --------------------------------------------------------------------------------
7 23177 Library Cache 37807 37807 0000009000000000 kglhdgn1 62 000000006C962850 select /*+ 2 */ count(*) from dept where deptno = 41554
4 378806 Library Cache 37807 37807 0000009200000000 kgllkc1 57 00000000647A0208 select /*+ 2 */ count(*) from dept where deptno = 41554
3 Library Cache 37807 37807 0000011400000000 kglhdgn1 62 000000006C962850 select /*+ 2 */ count(*) from dept where deptno = 41554
3 Library Cache 37807 37807 0000009400000000 kgllkdl1 85 00000000647A0208 select /*+ 2 */ count(*) from dept where deptno = 41554
3 Library Cache 37807 37807 0000001100000000 kgllkdl1 85 00000000647A0208 select /*+ 2 */ count(*) from dept where deptno = 41554
3 Library Cache 37807 37807 0000009400000000 kglhdgn1 62 000000006C962850 select /*+ 2 */ count(*) from dept where deptno = 41554
2 hash table 37807 37807 0000011300000001 kkshGetNextChild [KKSHBKLOC1] 000000006479AB20 select /*+ 2 */ count(*) from dept where deptno = 41554
2 Library Cache 37807 37807 0000018900000000 kglhdgn1 62 000000006C962850 select /*+ 2 */ count(*) from dept where deptno = 41554
2 39432 Library Cache 37807 37807 0000009200000000 kglhdgn1 62 000000006C962850 select /*+ 2 */ count(*) from dept where deptno = 41554
2 Library Cache 37807 37807 0000001500000000 kglhdgn2 106 00000000647A0208 select /*+ 2 */ count(*) from dept where deptno = 41554
1 hash table 37807 37807 0000018900000001 kkshGetNextChild [KKSHBKLOC1] 000000006479AB20 select /*+ 2 */ count(*) from dept where deptno = 41554
1 hash table 37807 37807 0000009000000001 kkshGetNextChild [KKSHBKLOC1] 000000006479AB20 select /*+ 2 */ count(*) from dept where deptno = 41554
1 Library Cache 37807 37807 0000011300000000 kgllkdl1 85 00000000647A0208 select /*+ 2 */ count(*) from dept where deptno = 41554
1 Cursor Pin 37807 37807 0000009200000003 kksLockDelete [KKSCHLPIN6] 000000006479AD10 select /*+ 2 */ count(*) from dept where deptno = 41554
1 Cursor Pin 37807 37807 0000001100000001 kkslce [KKSCHLPIN2] 000000006479AD10 select /*+ 2 */ count(*) from dept where deptno = 41554
1 Cursor Pin 37807 37807 0000001500000004 kkslce [KKSCHLPIN2] 000000006479AD10 select /*+ 2 */ count(*) from dept where deptno = 41554
1 Cursor Pin 37807 37807 00 kkslce [KKSCHLPIN2] 000000006479AD10 select /*+ 2 */ count(*) from dept where deptno = 41554
1 Cursor Pin 37807 37807 0000011300000002 kksLockDelete [KKSCHLPIN6] 000000006479AD10 select /*+ 2 */ count(*) from dept where deptno = 41554
1 Cursor Pin 37807 37807 0000000000000002 kksLockDelete [KKSCHLPIN6] 000000006479AD10 select /*+ 2 */ count(*) from dept where deptno = 41554
1 hash table 37807 37807 0000009200000001 kkshGetNextChild [KKSHBKLOC1] 000000006479AB20 select /*+ 2 */ count(*) from dept where deptno = 41554
20 rows selected.
--//注:这里条件非常特殊HASH_VALUE=KGL_BUCKET=37807,所以OBJECT_NAME都有值。
SYS@book> @ mutexprofz idn,hash,loc,maddr "ts>=trunc(sysdate)+16/24+24/1440+33/86400 and ts<=trunc(sysdate)+16/24+26/1440+07/86400 and idn=37807"
-- 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
---------- -------------- --------------- ---------- ---------- --------------------------------- -------------------- --------------------------------------------------------------------------------
19 245905 Library Cache 37807 37807 kglhdgn1 62 000000006C962850 select /*+ 2 */ count(*) from dept where deptno = 41554
10 761233 hash table 37807 37807 kkshGetNextChild [KKSHBKLOC1] 000000006479AB20 select /*+ 2 */ count(*) from dept where deptno = 41554
9 714961 Library Cache 37807 37807 kgllkdl1 85 00000000647A0208 select /*+ 2 */ count(*) from dept where deptno = 41554
6 446755 Cursor Pin 37807 37807 kkslce [KKSCHLPIN2] 000000006479AD10 select /*+ 2 */ count(*) from dept where deptno = 41554
5 528077 Library Cache 37807 37807 kgllkc1 57 00000000647A0208 select /*+ 2 */ count(*) from dept where deptno = 41554
3 235980 Cursor Pin 37807 37807 kksLockDelete [KKSCHLPIN6] 000000006479AD10 select /*+ 2 */ count(*) from dept where deptno = 41554
2 Library Cache 37807 37807 kglhdgn2 106 00000000647A0208 select /*+ 2 */ count(*) from dept where deptno = 41554
1 Library Cache 37807 37807 kgllkdl1 85 00000000647A0208 select /*+ 2 */ count(*) from dept where deptno = 41554
8 rows selected.
SYS@book> @ sharepool/shp4 7rc8r200014xg 0
HANDLE_TYPE KGLHDADR KGLHDPAR C40 KGLHDLMD KGLHDPMD KGLHDIVC KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLOBT09
---------------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
child handle address 0000000064A84CA0 00000000647A00B0 select /*+ 2 */ count(*) from dept where 0 0 0 0000000064A84AE0 000000006479ADB8 8128 12128 3320 23576 23576 37807 7rc8r200014xg 0
parent handle address 00000000647A00B0 00000000647A00B0 select /*+ 2 */ count(*) from dept where 0 0 0 00000000648F4C40 00 4064 0 0 4064 4064 37807 7rc8r200014xg 65535
SYS@book> @ fchaz 000000006C962850
GET_LOCATION KSMCHPTR KSMCHIDX KSMCHDUR KSMCHCOM KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR KSMCHPTR_BEGIN KSMCHPTR_END+1
--------------------------------- ---------------- ---------- ---------- ---------------- ---------- -------- ---------- ---------------- ---------------- -----------------
SGA 000000006C960760 1 1 KGLSG 12304 perm 0 000000006C804000 000000006C960760 000000006C963770
--//该chunk就是library cache bucket mutex所在的chunk。
--//0x000000006C962850-0x10 = 0x6c962840
SYS@book> @ opeek 000000006C962840 48 0
[06C962840, 06C962870) = 647A00B0 00000000 647A00B0 00000000 00000000 00000000 00989812 00001624 000093AF 00000000 6630ADE8 00000000
--//前面2个8字节记录的sql语句的父游标地址00000000647A00B0。
--//gets数量0x00989812 = 10000402,从执行次数上可以对上。sleeps=0x00001624 = 5668。上面的查询仅仅19次。21c版本不再累积,
--//记录每次sleep次数。
SYS@book> select sum(sleeps) from x$mutex_sleep_history where mutex_identifier=37807 and mutex_addr='000000006C962850';
SUM(SLEEPS)
-----------
19
SYS@book> @ mutexprofz sleeps,idn,hash,loc,val,maddr "ts>=trunc(sysdate)+16/24+24/1440+33/86400 and ts<=trunc(sysdate)+16/24+26/1440+07/86400 and idn=37807 and maddr='000000006C962850'"
-- 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 SLEEPS IDN HASH GET_LOCATION VAL mutex_addr OBJECT_NAME
---------- -------------- --------------- ---------- ---------- ---------- ------------ ---------------- -------------------- --------------------------------------------------------------------------------
4 Library Cache 4 37807 37807 kglhdgn1 62 0000009000000000 000000006C962850 select /*+ 2 */ count(*) from dept where deptno = 41554
3 Library Cache 3 37807 37807 kglhdgn1 62 0000011400000000 000000006C962850 select /*+ 2 */ count(*) from dept where deptno = 41554
3 Library Cache 3 37807 37807 kglhdgn1 62 0000009400000000 000000006C962850 select /*+ 2 */ count(*) from dept where deptno = 41554
2 39432 Library Cache 1 37807 37807 kglhdgn1 62 0000009200000000 000000006C962850 select /*+ 2 */ count(*) from dept where deptno = 41554
2 Library Cache 2 37807 37807 kglhdgn1 62 0000009000000000 000000006C962850 select /*+ 2 */ count(*) from dept where deptno = 41554
2 Library Cache 2 37807 37807 kglhdgn1 62 0000018900000000 000000006C962850 select /*+ 2 */ count(*) from dept where deptno = 41554
1 Library Cache 1 37807 37807 kglhdgn1 62 0000009000000000 000000006C962850 select /*+ 2 */ count(*) from dept where deptno = 41554
1 Library Cache 1 37807 37807 kglhdgn1 62 00 000000006C962850 select /*+ 2 */ count(*) from dept where deptno = 41554
1 Library Cache 1 37807 37807 kglhdgn1 62 0000001500000000 000000006C962850 select /*+ 2 */ count(*) from dept where deptno = 41554
9 rows selected.
SYS@book> @ fchaz 000000006479AB20
GET_LOCATION KSMCHPTR KSMCHIDX KSMCHDUR KSMCHCOM KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR KSMCHPTR_BEGIN KSMCHPTR_END+1
--------------------------------- ---------------- ---------- ---------- ---------------- ---------- -------- ---------- ---------------- ---------------- -----------------
SGA 000000006479A560 1 1 KGLH0^93af 4096 recr 4095 00000000648F4C40 000000006479A560 000000006479B560
--//KSMCHPAR=00000000648F4C40,也就是父游标堆0的地址.
SYS@book> @ fchaz 00000000647A0208
GET_LOCATION KSMCHPTR KSMCHIDX KSMCHDUR KSMCHCOM KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR KSMCHPTR_BEGIN KSMCHPTR_END+1
--------------------------------- ---------------- ---------- ---------- ---------------- ---------- -------- ---------- ---------------- ---------------- -----------------
SGA 00000000647A0080 1 1 KGLHD 816 recr 80 00 00000000647A0080 00000000647A03B0
--//0x00000000647A0080+0x30 = 0x647a00b0 父游标句柄。
SYS@book> @ fchaz 000000006479AD10
GET_LOCATION KSMCHPTR KSMCHIDX KSMCHDUR KSMCHCOM KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR KSMCHPTR_BEGIN KSMCHPTR_END+1
--------------------------------- ---------------- ---------- ---------- ---------------- ---------- -------- ---------- ---------------- ---------------- -----------------
SGA 000000006479A560 1 1 KGLH0^93af 4096 recr 4095 00000000648F4C40 000000006479A560 000000006479B560
--//KSMCHPAR=00000000648F4C40,也就是父游标堆0的地址.
4.补充测试:
--//注解alter session set session_cached_cursors=0;
$ cat ma.txt
--alter session set session_cached_cursors=0;
DECLARE
v_pad VARCHAR2 (200);
l_count PLS_INTEGER;
BEGIN
FOR i IN 1..&&1
LOOP
EXECUTE IMMEDIATE 'select /*+ 2 */ count(*) from dept where deptno = 41554' INTO l_count;
END LOOP;
END;
/
$ zzdate;seq 10 | xargs -P 10 -IQ sqlplus -s -l scott/book@book01p @ma.txt 1e6 > /dev/null;zzdate
trunc(sysdate)+16/24+55/1440+49/86400 -1739004949.928895134
trunc(sysdate)+16/24+56/1440+33/86400 1739004993.707077407
--//Sum = 43.778182273
SYS@book> @ ashtop event,p1raw,p1,p3raw 1=1 trunc(sysdate)+16/24+55/1440+49/86400 trunc(sysdate)+16/24+56/1440+33/86400
Total Distinct Distinct Distinct
Seconds AAS %This EVENT P1RAW P1 P3RAW FIRST_SEEN LAST_SEEN Execs Seen Tstamps Execs Seen1
--------- ------- ------- ------------------------------------------ ----------------- ---------- ----------------- ------------------- ------------------- ---------- -------- -----------
213 4.8 51% | 37807 2025-02-08 16:55:50 2025-02-08 16:56:32 119 43 153
57 1.3 14% | resmgr:cpu quantum 0000000000000002 2 0000000000000000 2025-02-08 16:55:56 2025-02-08 16:56:31 1 29 29
35 .8 8% | resmgr:cpu quantum 0000000000000003 3 0000000000000000 2025-02-08 16:55:59 2025-02-08 16:56:29 10 19 10
33 .8 8% | cursor: pin S 00000000000093AF 37807 0000000900000000 2025-02-08 16:55:51 2025-02-08 16:56:32 1 19 19
30 .7 7% | cursor: pin S 00000000000093AF 37807 0000000300000000 2025-02-08 16:55:51 2025-02-08 16:56:31 1 21 21
29 .7 7% | 2 2025-02-08 16:55:58 2025-02-08 16:56:26 20 19 25
21 .5 5% | 3 2025-02-08 16:55:51 2025-02-08 16:56:30 17 16 20
3 .1 1% | 1413697536 2025-02-08 16:55:50 2025-02-08 16:55:50 3 1 3
8 rows selected.
--//见鬼resmgr:cpu quantum等待事件又出现了,前面测试忘记关闭resmgr:cpu quantum。
SYS@book> @ mutexprofz idn,hash,loc,maddr "ts>=trunc(sysdate)+16/24+55/1440+49/86400 and ts<=trunc(sysdate)+16/24+56/1440+33/86400 and idn=37807"
-- 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 484736 Cursor Pin 37807 37807 kksLockDelete [KKSCHLPIN6] 000000006479AD10 select /*+ 2 */ count(*) from dept where deptno = 41554
SYS@book> @ sharepool/shp4 7rc8r200014xg 0
HANDLE_TYPE KGLHDADR KGLHDPAR C40 KGLHDLMD KGLHDPMD KGLHDIVC KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLOBT09
---------------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
child handle address 0000000064A84CA0 00000000647A00B0 select /*+ 2 */ count(*) from dept where 0 0 0 0000000064A84AE0 000000006479ADB8 8128 12128 3320 23576 23576 37807 7rc8r200014xg 0
parent handle address 00000000647A00B0 00000000647A00B0 select /*+ 2 */ count(*) from dept where 0 0 0 00000000648F4C40 00 4064 0 0 4064 4064 37807 7rc8r200014xg 65535
SYS@book> @ fchaz 000000006479AD10
GET_LOCATION KSMCHPTR KSMCHIDX KSMCHDUR KSMCHCOM KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR KSMCHPTR_BEGIN KSMCHPTR_END+1
--------------------------------- ---------------- ---------- ---------- ---------------- ---------- -------- ---------- ---------------- ---------------- -----------------
SGA 000000006479A560 1 1 KGLH0^93af 4096 recr 4095 00000000648F4C40 000000006479A560 000000006479B560
--//KSMCHPAR=00000000648F4C40,也就是父游标堆0的地址.
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· 清华大学推出第四讲使用 DeepSeek + DeepResearch 让科研像聊天一样简单!
· 实操Deepseek接入个人知识库
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
· Plotly.NET 一个为 .NET 打造的强大开源交互式图表库
2021-02-18 [20210218]shared latch spin count 5.txt