[20250210]library cache mutex X与library cache bucket mutex X的区别(11g).txt
[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上.
--//以前在学习参考文档里面:链接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上.
【推荐】编程新体验,更懂你的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