[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上.
posted @   lfree  阅读(0)  评论(0编辑  收藏  举报
相关博文:
阅读排行:
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· 清华大学推出第四讲使用 DeepSeek + DeepResearch 让科研像聊天一样简单!
· 实操Deepseek接入个人知识库
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
· Plotly.NET 一个为 .NET 打造的强大开源交互式图表库
历史上的今天:
2021-02-18 [20210218]shared latch spin count 5.txt
点击右上角即可分享
微信分享提示