[20250210]library cache mutex X与library cache bucket mutex X的区别(11g).txt

来源：这里教程网时间：2026-03-03 21:33:40 作者：

[20250210]library cache mutex X与library cache bucket mutex X的区别(11g).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个测试说明细节。 --//我估计11g应该没有library cache bucket mutex X相关等待实际。 1.环境: SCOTT@book> @ver2 ============================== PORT_STRING : x86_64/Linux 2.4.xx VERSION : 11.2.0.4.0 BANNER : Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production 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@book> select /*+ 2 */ count(*) from dept where deptno = 41554; COUNT(*) -------- 0 SCOTT@book> @ hash HASH_VALUE SQL_ID CHILD_NUMBER KGL_BUCKET PLAN_HASH_VALUE HASH_HEX SQL_EXEC_START SQL_EXEC_ID ---------- ------------- ------------ ---------- --------------- ---------- ------------------- ----------- 37807 7rc8r200014xg 1 37807 2236899148 93af 2025-02-10 15:54:06 26763481 --//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 @ma.txt 1e6 > /dev/null;zzdate trunc(sysdate)+15/24+55/1440+19/86400 1739174119.361999460 trunc(sysdate)+15/24+56/1440+43/86400 1739174203.690841412 SYS@book> @ ashtop event,p1raw,p1,p3raw 1=1 trunc(sysdate)+15/24+55/1440+19/86400 trunc(sysdate)+15/24+56/1440+43/86400 Total Distinct Distinct Distinct Seconds AAS %This EVENT P1RAW P1 P3RAW FIRST_SEEN LAST_SEEN Execs Seen Tstamps Execs Seen1 --------- ------- ------- ------------------------------------------ ----------------- ---------- ----------------- ------------------- ------------------- ---------- -------- ----------- 448 5.3 54% | 37807 2025-02-10 15:55:20 2025-02-10 15:56:42 146 83 215 148 1.8 18% | library cache: mutex X 00000000000093AF 37807 0000000000000055 2025-02-10 15:55:20 2025-02-10 15:56:42 10 72 10 94 1.1 11% | library cache: mutex X 00000000000093AF 37807 000000000000006A 2025-02-10 15:55:20 2025-02-10 15:56:41 10 61 10 47 .6 6% | library cache: mutex X 00000000000093AF 37807 0000000000000039 2025-02-10 15:55:20 2025-02-10 15:56:40 1 39 39 34 .4 4% | library cache: mutex X 00000000000093AF 37807 000000000000003E 2025-02-10 15:55:21 2025-02-10 15:56:40 10 24 10 21 .3 3% | cursor: pin S 00000000000093AF 37807 0000000500000000 2025-02-10 15:55:20 2025-02-10 15:56:33 1 12 12 10 .1 1% | 0 2025-02-10 15:55:33 2025-02-10 15:56:25 8 8 10 8 .1 1% | cursor: mutex S 0000000000000000 0 0000000200000000 2025-02-10 15:55:49 2025-02-10 15:56:34 1 6 6 8 .1 1% | cursor: mutex S 00000000000093AF 37807 0000000100000000 2025-02-10 15:55:29 2025-02-10 15:56:33 1 7 7 8 .1 1% | cursor: pin S 00000000000093AF 37807 0000000900000000 2025-02-10 15:55:29 2025-02-10 15:56:22 1 8 8 1 .0 0% | control file parallel write 0000000000000002 2 0000000000000002 2025-02-10 15:55:26 2025-02-10 15:55:26 1 1 1 1 .0 0% | 5 2025-02-10 15:55:19 2025-02-10 15:55:19 1 1 1 1 .0 0% | 500 2025-02-10 15:55:19 2025-02-10 15:55:19 1 1 1 13 rows selected. --//P1对应就是idn，注意看11g没有library cache: bucket mutex X。通过看P3RAW，也无法区分，后面4位值转换位10进制 0x55=85 , 0x6a=106 , 0x39 = 57 ,0x3e= 62 --//在这样的情况下看到library cache: mutex X等待事情，是无法区分那个是library cache bucket mutex引起的，因为HASH_VALUE=KGL_BUCKET=37807。 --//也可以发现21c做了一些改进。 SYS@book> @ mutexprofx idn,hash,val,loc,maddr "ts>=trunc(sysdate)+15/24+55/1440+19/86400 and ts<=trunc(sysdate)+15/24+56/1440+43/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 MAX_SLEEPS SLEEPS_DIFF MUTEX_TYPE IDN HASH VAL GET_LOCATION mutex_addr OBJECT_NAME ---------- ----------- --------------- ---------- ---------- ---------------- --------------------------------- -------------------- -------------------------------------------------------------------------------- 4873 78 Library Cache 37807 37807 0000008700000000 kglhdgn2 106 0000000087B12798 select /*+ 2 */ count(*) from dept where deptno = 41554 4854 Library Cache 37807 37807 00 kglhdgn2 106 0000000087B12798 select /*+ 2 */ count(*) from dept where deptno = 41554 4846 Library Cache 37807 37807 000000C200000000 kgllkdl1 85 0000000087B12798 select /*+ 2 */ count(*) from dept where deptno = 41554 4762 113 Library Cache 37807 37807 00 kgllkdl1 85 0000000087B12798 select /*+ 2 */ count(*) from dept where deptno = 41554 4751 Library Cache 37807 37807 00 kgllkdl1 85 0000000087B12798 select /*+ 2 */ count(*) from dept where deptno = 41554 4718 Library Cache 37807 37807 000000C200000000 kgllkdl1 85 0000000087B12798 select /*+ 2 */ count(*) from dept where deptno = 41554 4619 Library Cache 37807 37807 0000008700000000 kgllkdl1 85 0000000087B12798 select /*+ 2 */ count(*) from dept where deptno = 41554 4438 Library Cache 37807 37807 0000008500000000 kgllkdl1 85 0000000087B12798 select /*+ 2 */ count(*) from dept where deptno = 41554 560 27 Cursor Pin 37807 37807 0000008500000001 kkslce [KKSCHLPIN2] 00000000877926C8 select /*+ 2 */ count(*) from dept where deptno = 41554 559 Cursor Pin 37807 37807 0000004600000003 kkslce [KKSCHLPIN2] 00000000877926C8 select /*+ 2 */ count(*) from dept where deptno = 41554 556 Library Cache 37807 37807 0000008500000000 kglhdgn1 62 000000008BAE20F0 select /*+ 2 */ count(*) from dept where deptno = 41554 549 Cursor Pin 37807 37807 000000C700000002 kkslce [KKSCHLPIN2] 00000000877926C8 select /*+ 2 */ count(*) from dept where deptno = 41554 547 Cursor Pin 37807 37807 0000008500000004 kksLockDelete [KKSCHLPIN6] 00000000877926C8 select /*+ 2 */ count(*) from dept where deptno = 41554 547 Cursor Pin 37807 37807 000000C200000000 kkslce [KKSCHLPIN2] 00000000877926C8 select /*+ 2 */ count(*) from dept where deptno = 41554 546 Cursor Pin 37807 37807 0000000000000002 kksLockDelete [KKSCHLPIN6] 00000000877926C8 select /*+ 2 */ count(*) from dept where deptno = 41554 537 80 Library Cache 37807 37807 00 kglhdgn1 62 000000008BAE20F0 select /*+ 2 */ count(*) from dept where deptno = 41554 521 18 Library Cache 37807 37807 0000004800000000 kglhdgn1 62 000000008BAE20F0 select /*+ 2 */ count(*) from dept where deptno = 41554 519 Library Cache 37807 37807 0000008700000000 kglhdgn1 62 000000008BAE20F0 select /*+ 2 */ count(*) from dept where deptno = 41554 517 Cursor Pin 37807 37807 0000004600000000 kkslce [KKSCHLPIN2] 00000000877926C8 select /*+ 2 */ count(*) from dept where deptno = 41554 486 Library Cache 37807 37807 000000C200000000 kglhdgn1 62 000000008BAE20F0 select /*+ 2 */ count(*) from dept where deptno = 41554 20 rows selected. ---//这里情况特殊HASH_VALUE=KGL_BUCKET=37807，导致即使是library cache hash bucket后面的OBJECT_NAME都有值。 SYS@book> @ mutexprofx idn,hash,loc,maddr "ts>=trunc(sysdate)+15/24+55/1440+19/86400 and ts<=trunc(sysdate)+15/24+56/1440+43/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 MAX_SLEEPS SLEEPS_DIFF MUTEX_TYPE IDN HASH GET_LOCATION mutex_addr OBJECT_NAME ---------- ----------- --------------- ---------- ---------- --------------------------------- -------------------- -------------------------------------------------------------------------------- 4873 78 Library Cache 37807 37807 kglhdgn2 106 0000000087B12798 select /*+ 2 */ count(*) from dept where deptno = 41554 4846 197 Library Cache 37807 37807 kgllkdl1 85 0000000087B12798 select /*+ 2 */ count(*) from dept where deptno = 41554 4751 313 Library Cache 37807 37807 kgllkdl1 85 0000000087B12798 select /*+ 2 */ count(*) from dept where deptno = 41554 560 43 Cursor Pin 37807 37807 kkslce [KKSCHLPIN2] 00000000877926C8 select /*+ 2 */ count(*) from dept where deptno = 41554 556 99 Library Cache 37807 37807 kglhdgn1 62 000000008BAE20F0 select /*+ 2 */ count(*) from dept where deptno = 41554 547 1 Cursor Pin 37807 37807 kksLockDelete [KKSCHLPIN6] 00000000877926C8 select /*+ 2 */ count(*) from dept where deptno = 41554 392 233 hash table 37807 37807 kkshGetNextChild [KKSHBKLOC1] 0000000087792308 select /*+ 2 */ count(*) from dept where deptno = 41554 7 rows selected. SYS@book> @ mutexprofz idn,hash,loc,maddr "ts>=trunc(sysdate)+15/24+55/1440+19/86400 and ts<=trunc(sysdate)+15/24+56/1440+43/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 ---------- -------------- --------------- ---------- ---------- --------------------------------- -------------------- -------------------------------------------------------------------------------- 18526 614118 Library Cache 37807 37807 kgllkdl1 85 0000000087B12798 select /*+ 2 */ count(*) from dept where deptno = 41554 14522 156497 Library Cache 37807 37807 kglhdgn2 106 0000000087B12798 select /*+ 2 */ count(*) from dept where deptno = 41554 14257 592291 Library Cache 37807 37807 kgllkdl1 85 0000000087B12798 select /*+ 2 */ count(*) from dept where deptno = 41554 5104 173149 Library Cache 37807 37807 kglhdgn1 62 000000008BAE20F0 select /*+ 2 */ count(*) from dept where deptno = 41554 ~~~~~~~~~~~ 3815 145116 Cursor Pin 37807 37807 kkslce [KKSCHLPIN2] 00000000877926C8 select /*+ 2 */ count(*) from dept where deptno = 41554 2171 1377094 hash table 37807 37807 kkshGetNextChild [KKSHBKLOC1] 0000000087792308 select /*+ 2 */ count(*) from dept where deptno = 41554 1093 296915 Cursor Pin 37807 37807 kksLockDelete [KKSCHLPIN6] 00000000877926C8 select /*+ 2 */ count(*) from dept where deptno = 41554 7 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 0000000087B121D8 0000000087B12658 select /*+ 2 */ count(*) from dept where 0 0 0 0000000087B12120 0000000087792770 4528 8088 3088 15704 15704 37807 7rc8r200014xg 0 parent handle address 0000000087B12658 0000000087B12658 select /*+ 2 */ count(*) from dept where 0 0 0 0000000087B125A0 00 4736 0 0 4736 4736 37807 7rc8r200014xg 65535 SYS@book> @ fchaz 0000000087B12798 GET_LOCATION KSMCHPTR KSMCHIDX KSMCHDUR KSMCHCOM KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR KSMCHPTR_BEGIN KSMCHPTR_END+1 --------------------------------- ---------------- ---------- ---------- ---------------- ---------- -------- ---------- ---------------- ---------------- ----------------- SGA 0000000087B12628 1 2 KGLHD 576 recr 80 00 0000000087B12628 0000000087B12868 --//父游标句柄 0x0000000087B12628+0x30 = 0x87b12658 SYS@book> @ opeek 0000000087B12798 24 0 [087B12798, 087B127B0) = 00000000 00000000 026261D6 0000B8B0 000093AF 00000000 --//sleeps次数 0x0000B8B0 = 47280,18526+14522+14257 = 47305 存在少量差距。 SYS@book> @ fchaz 000000008BAE20F0 GET_LOCATION KSMCHPTR KSMCHIDX KSMCHDUR KSMCHCOM KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR KSMCHPTR_BEGIN KSMCHPTR_END+1 --------------------------------- ---------------- ---------- ---------- ---------------- ---------- -------- ---------- ---------------- ---------------- ----------------- SGA 000000008B834000 1 1 permanent memor 3980680 perm 0 00 000000008B834000 000000008BBFFD88 --//该chunk就是library cache bucket mutex所在的chunk。 --//0x000000008BAE20F0-0x10 = 0x8bae20e0 SYS@book> @ opeek 0x8bae20e0 40 0 [08BAE20E0, 08BAE2108) = 87B12658 00000000 87B12658 00000000 00000000 00000000 0098972B 000013F0 000093AF 00000000 --//前面2个8字节记录的sql语句的父游标地址。 --//gets数量0x0098972B = 10000171，从执行次数上看可以对上。0x000093AF = 37807，library cache bucket mutex的桶号。 --//sleeps=0x000013F0 = 5104,上面的查询mutexprofz脚本一致。 SYS@book> select * from x$MUTEX_SLEEP_HISTORY where mutex_identifier=37807 and mutex_addr='000000008BAE20F0' order by 6; ADDR INDX INST_ID MUTEX_ADDR MUTEX_IDENTIFIER SLEEP_TIMESTAMP MUTEX_TYPE MUTEX_TYPE_ID GETS SLEEPS REQUESTING_SESSION BLOCKING_SESSION LOCATION_ID LOCATION MUTEX_VALUE ---------------- ---- ------- ---------------- ---------------- -------------------------- ------------- ------------- ------- ------ ------------------ ---------------- ----------- ------------ ---------------- 00007F3C31035400 26 1 000000008BAE20F0 37807 2025-02-10 15:56:41.182556 Library Cache 4 9785624 469 9 7 62 kglhdgn1 62 00 00007F3C31035520 27 1 000000008BAE20F0 37807 2025-02-10 15:56:41.507509 Library Cache 4 9823815 537 7 70 62 kglhdgn1 62 00 00007F3C30F45960 55 1 000000008BAE20F0 37807 2025-02-10 15:56:41.850522 Library Cache 4 9863953 519 199 135 62 kglhdgn1 62 0000008700000000 00007F3C30F45A80 56 1 000000008BAE20F0 37807 2025-02-10 15:56:42.326089 Library Cache 4 9919449 521 197 72 62 kglhdgn1 62 0000004800000000 00007F3C310351C0 46 1 000000008BAE20F0 37807 2025-02-10 15:56:42.358988 Library Cache 4 9923047 503 135 72 62 kglhdgn1 62 0000004800000000 00007F3C310351C0 24 1 000000008BAE20F0 37807 2025-02-10 15:56:42.464145 Library Cache 4 9935144 525 11 194 62 kglhdgn1 62 00 00007F3C30F45DE0 34 1 000000008BAE20F0 37807 2025-02-10 15:56:42.502980 Library Cache 4 9939027 531 70 133 62 kglhdgn1 62 00 00007F3C30F45BA0 57 1 000000008BAE20F0 37807 2025-02-10 15:56:42.615110 Library Cache 4 9951030 457 194 70 62 kglhdgn1 62 00 00007F3C310352E0 47 1 000000008BAE20F0 37807 2025-02-10 15:56:42.678895 Library Cache 4 9958565 486 133 194 62 kglhdgn1 62 000000C200000000 00007F3C30F45CC0 33 1 000000008BAE20F0 37807 2025-02-10 15:56:42.682067 Library Cache 4 9958773 556 72 133 62 kglhdgn1 62 0000008500000000 10 rows selected. --//看来以前学习有问题，gets是累积的，sleeps是不累积的,(我以前一直以为两者都是累积的，也许记忆混乱)。MUTEX_VALUE有时候并没有抓到值。 --//SLEEP_TIMESTAMP时间出现在2025-02-10 15:56:41，2025-02-10 15:56:42,几乎是测试的结束时间，也许这时一些会话已经退出，导致MUTEX_VALUE出现00的记录。 --//或许测试的执行太密集了. --//明天做一下尝试，在退出会话前加入3秒看看。 SYS@book> @ fchaz 00000000877926C8 GET_LOCATION KSMCHPTR KSMCHIDX KSMCHDUR KSMCHCOM KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR KSMCHPTR_BEGIN KSMCHPTR_END+1 --------------------------------- ---------------- ---------- ---------- ---------------- ---------- -------- ---------- ---------------- ---------------- ----------------- SGA 0000000087792000 1 3 KGLH0^93af 4096 recr 4095 0000000087B125A0 0000000087792000 0000000087793000 --//KSMCHPAR=0000000087B125A0,也就是父游标堆0的地址. SYS@book> @ opeek 00000000877926C8 24 0 [0877926C8, 0877926E0) = 00000000 00000000 01313126 00001527 000093AF 00000000 --//0x00001527 = 5415, 3815+1093 = 4908 ?? SYS@book> @ fchaz 0000000087792308 GET_LOCATION KSMCHPTR KSMCHIDX KSMCHDUR KSMCHCOM KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR KSMCHPTR_BEGIN KSMCHPTR_END+1 --------------------------------- ---------------- ---------- ---------- ---------------- ---------- -------- ---------- ---------------- ---------------- ----------------- SGA 0000000087792000 1 3 KGLH0^93af 4096 recr 4095 0000000087B125A0 0000000087792000 0000000087793000 --//KSMCHPAR=0000000087B125A0,也就是父游标堆0的地址. SYS@book> @ opeek 0000000087792308 24 0 [087792308, 087792320) = 00000000 00000000 01312D05 000007B4 000093AF 00000000 --//0x000007B4 = 1972，2171?? 4.小结： --//通过前面测试基本看出11g下library cache: mutex X等待事件特殊情况很难区分是否发生bucket上.

编辑推荐：

返回列表

[20250210]library cache mutex X与library cache bucket mutex X的区别(11g).txt

编辑推荐：

相关推荐

excel表格怎样在数字前输入0

excel表格里怎么替换数据

excel表格里面如何插入圆形

excel表格里怎么添加表格数据透视表

excel表格中怎么添加线边框

excel表格里怎样设置条件自动排序

excel表格例怎样导入数据库

Excel表格如何设置数据排序

excel表格里怎么选择性粘贴快捷键

excel表格怎么插入分隔符号

excel表格怎么设置多行多列数据内容排序

Excel表格如何设置行和列相互转换

excel表格连接公式怎么使用

excel表格怎样链接到cad中

雷神推出 MIX PRO II 迷你主机：基于 Ultra 200H，玻璃上盖 + ARGB 灯效

制造商 Musnap 推出彩色墨水屏电纸书 Ocean C：支持手写笔、第三方安卓应用

最新软件资讯

热文推荐

天极热推