[20250203]21c library cache mutex的深入探究6(gets的变化).txt

[20250203]21c library cache mutex的深入探究6(gets的变化).txt

--//探究library cache mutex gets的变化,记录自己测试遇到的一个奇怪问题。

1.环境:
SYS@book> @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.测试:
SYS@book> oradebug setmypid
Statement processed.
SYS@book> oradebug dump library_cache 4
Statement processed.

$ grep "^Bucket:" /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_10770.trc | head -4
Bucket: #=0 Mutex=0x6cfa1400(1125281431552, 9, 0, 6)
Bucket: #=5 Mutex=0x6cfa14f0(1125281431552, 4, 0, 6)
Bucket: #=17 Mutex=0x6cfa1730(1125281431552, 8, 0, 6)
Bucket: #=39 Mutex=0x6cfa1b50(1125281431552, 4, 0, 6)
--//以上是前面测试的结果,直接找Bucket: #=0 Mutex=0x6cfa1400的语句测试。
--//该sql语句select /*+ 9 */ count(*) from dept where deptno = 93834;的bucket=0.
--//Bucket: #=0 Mutex=0x6cfa1400
--//0x6cfa1400-0x10 = 0x6cfa13f0

--//session 2:
SYS@book>  @opeek 0x6cfa13f0 48 0
[06CFA13F0, 06CFA1420) = 6CFA13F0 00000000 6CFA13F0 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
--//前面2个8字节等于0x6cfa13f0,说明没有对象使用该library cache mutex。

--//session 1:
SCOTT@book01p> alter session  set session_cached_cursors=0 ;
Session altered.
--//主要目的避免光标缓存。

$ cat ab.txt
select /*+ 9 */ count(*) from dept where deptno = 93834;
@ hash
--//hash.sql来自tpt的脚本。

SCOTT@book01p> @ ab.txt
  COUNT(*)
----------
         0

HASH_VALUE SQL_ID        CHILD_NUMBER KGL_BUCKET PLAN_HASH_VALUE HASH_HEX   SQL_EXEC_START      SQL_EXEC_ID
---------- ------------- ------------ ---------- --------------- ---------- ------------------- -----------
3403546624 as3g00v5dw000            0          0      2236899148  cade0000  2025-02-04 08:42:47    16777216
--//执行5次。
--//每执行1次使用@opeek 0x6cfa13f0 48 0查看1次。
--//session 2:
SYS@book> @opeek 0x6cfa13f0 48 0
[06CFA13F0, 06CFA1420) = 65589640 00000000 65589640 00000000 00000000 00000000 00000006 00000000 00000000 00000000 690D6690 00000000

SYS@book> @opeek 0x6cfa13f0 48 0
[06CFA13F0, 06CFA1420) = 65589640 00000000 65589640 00000000 00000000 00000000 00000009 00000000 00000000 00000000 690D6690 00000000

SYS@book> @opeek 0x6cfa13f0 48 0
[06CFA13F0, 06CFA1420) = 65589640 00000000 65589640 00000000 00000000 00000000 0000000C 00000000 00000000 00000000 690D6690 00000000

SYS@book> @opeek 0x6cfa13f0 48 0
[06CFA13F0, 06CFA1420) = 65589640 00000000 65589640 00000000 00000000 00000000 0000000F 00000000 00000000 00000000 690D6690 00000000

SYS@book> @opeek 0x6cfa13f0 48 0
[06CFA13F0, 06CFA1420) = 65589640 00000000 65589640 00000000 00000000 00000000 00000012 00000000 00000000 00000000 690D6690 00000000
--//gets的变化0x6,0x9,0xc,0xf,0x12,增量变化6,3,3,3,3.

--//session 1:
SCOTT@book01p> alter session  set session_cached_cursors=50 ;
Session altered.

SCOTT@book01p> @ ab.txt
  COUNT(*)
----------
         0


HASH_VALUE SQL_ID        CHILD_NUMBER KGL_BUCKET PLAN_HASH_VALUE HASH_HEX   SQL_EXEC_START      SQL_EXEC_ID
---------- ------------- ------------ ---------- --------------- ---------- ------------------- -----------
3403546624 as3g00v5dw000            0          0      2236899148  cade0000  2025-02-04 08:42:47    16777216

--//执行5次。
--//session 2:
SYS@book> @opeek 0x6cfa13f0 48 0
[06CFA13F0, 06CFA1420) = 65589640 00000000 65589640 00000000 00000000 00000000 00000012 00000000 00000000 00000000 690D6690 00000000

SYS@book> @opeek 0x6cfa13f0 48 0
[06CFA13F0, 06CFA1420) = 65589640 00000000 65589640 00000000 00000000 00000000 00000015 00000000 00000000 00000000 690D6690 00000000

SYS@book> @opeek 0x6cfa13f0 48 0
[06CFA13F0, 06CFA1420) = 65589640 00000000 65589640 00000000 00000000 00000000 00000017 00000000 00000000 00000000 690D6690 00000000

SYS@book> @opeek 0x6cfa13f0 48 0
[06CFA13F0, 06CFA1420) = 65589640 00000000 65589640 00000000 00000000 00000000 00000019 00000000 00000000 00000000 690D6690 00000000

SYS@book> @opeek 0x6cfa13f0 48 0
[06CFA13F0, 06CFA1420) = 65589640 00000000 65589640 00000000 00000000 00000000 0000001B 00000000 00000000 00000000 690D6690 00000000

SYS@book> @opeek 0x6cfa13f0 48 0
[06CFA13F0, 06CFA1420) = 65589640 00000000 65589640 00000000 00000000 00000000 0000001D 00000000 00000000 00000000 690D6690 00000000
--//gets的变化0x12(开始),0x15,0x17,0x19,0x1B,0x1D,增量变化3,2,2,2,2.
--//有点颠覆我的认知,我一直以为光标缓存后gets的数量不会变化,而且跟我前面的测试完全不同,我以前的测试在
--//session_cached_cursors=0的情况下仅仅增加1次。
--//实际的情况我以前的认知还是对的,看后面的测试。

SYS@book> @ sharepool/shp4z as3g00v5dw000 -1
HANDLE_TYPE            KGLHDADR         KGLHDPAR         C40                                        KGLHDLMD   KGLHDPMD   KGLHDIVC KGLOBHD0         KGLOBHD6           KGLOBHS0   KGLOBHS6   KGLOBT16   N0_6_16        N20   KGLNAHSH KGLOBT03        KGLOBT09
---------------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
parent handle address  0000000065589640 0000000065589640 select /*+ 9 */ count(*) from dept where          1          0          0 00000000636CDF28 00                     4064          0          0      4064       4064 3403546624 as3g00v5dw000      65535
--//父游标句柄地址0000000065589640已经写入mutex地址-0x10处。

3.继续测试:
--//退出session 1,重新登录:
SCOTT@book01p> show parameter session_cached_cursors
PARAMETER_NAME         TYPE    VALUE
---------------------- ------- -------
session_cached_cursors integer 50

--//连续执行3次:
SCOTT@book01p> select /*+ 9 */ count(*) from dept where deptno = 93834;
  COUNT(*)
----------
         0

/
/

--//session 2:
SYS@book> @opeek 0x6cfa13f0 48 0
[06CFA13F0, 06CFA1420) = 65589640 00000000 65589640 00000000 00000000 00000000 00000026 00000000 00000000 00000000 690D6690 00000000

SYS@book> @opeek 0x6cfa13f0 48 0
[06CFA13F0, 06CFA1420) = 65589640 00000000 65589640 00000000 00000000 00000000 00000027 00000000 00000000 00000000 690D6690 00000000

SYS@book> @opeek 0x6cfa13f0 48 0
[06CFA13F0, 06CFA1420) = 65589640 00000000 65589640 00000000 00000000 00000000 00000027 00000000 00000000 00000000 690D6690 00000000

SYS@book> @opeek 0x6cfa13f0 48 0
[06CFA13F0, 06CFA1420) = 65589640 00000000 65589640 00000000 00000000 00000000 00000027 00000000 00000000 00000000 690D6690 00000000
--//gets的变化0x26(开始),0x26,0x27,0x27,0x27,增量变化1,0,0,0.

4.如果换成ab.txt脚本执行:
--//ab.txt执行3次。

SYS@book> @opeek 0x6cfa13f0 48 0
[06CFA13F0, 06CFA1420) = 65589640 00000000 65589640 00000000 00000000 00000000 0000002B 00000000 00000000 00000000 690D6690 00000000

SYS@book> @opeek 0x6cfa13f0 48 0
[06CFA13F0, 06CFA1420) = 65589640 00000000 65589640 00000000 00000000 00000000 0000002D 00000000 00000000 00000000 690D6690 00000000

SYS@book> @opeek 0x6cfa13f0 48 0
[06CFA13F0, 06CFA1420) = 65589640 00000000 65589640 00000000 00000000 00000000 0000002F 00000000 00000000 00000000 690D6690 00000000

SYS@book> @opeek 0x6cfa13f0 48 0
[06CFA13F0, 06CFA1420) = 65589640 00000000 65589640 00000000 00000000 00000000 00000031 00000000 00000000 00000000 690D6690 00000000
--//gets的变化0x2B(开始),0x2B,0x2D,0x2F,0x31,增量变化2,2,2.

5.问题才刚刚开始:
--//我开始以为在session_cached_cursors=50 的情况下连续执行的情况下gets根本没有变化,也就是光标没有释放,如果光标释放gets
--//的数量还是出现变化,继续看下面的测试。

--//在session 1设置session_cached_cursors=50的情况下光标已经缓存。
--//session 2:
SYS@book> @opeek 0x6cfa13f0 48 0
[06CFA13F0, 06CFA1420) = 65589640 00000000 65589640 00000000 00000000 00000000 0000003C 00003D20 00000000 00000000 690D6690 00000000

--//session 1:
SCOTT@book01p> select /*+ 9 */ count(*) from dept where deptno = 93834;
  COUNT(*)
----------
         0

--//session 2:
SYS@book> @opeek 0x6cfa13f0 48 0
[06CFA13F0, 06CFA1420) = 65589640 00000000 65589640 00000000 00000000 00000000 0000003C 00003D20 00000000 00000000 690D6690 00000000
--//gets没有变化。

--//session 1:
SCOTT@book01p> select user from dual;
USER
------------------------------
SCOTT

--//session 2:
SYS@book> @opeek 0x6cfa13f0 48 0
[06CFA13F0, 06CFA1420) = 65589640 00000000 65589640 00000000 00000000 00000000 0000003C 00003D20 00000000 00000000 690D6690 00000000
--//gets没有变化。

--//session 1:
SCOTT@book01p> @ hash
HASH_VALUE SQL_ID        CHILD_NUMBER KGL_BUCKET PLAN_HASH_VALUE HASH_HEX   SQL_EXEC_START      SQL_EXEC_ID
---------- ------------- ------------ ---------- --------------- ---------- ------------------- -----------
2200891488 ahc9t761kxw30            0      61536      1388734953  832ef060  2025-02-04 09:46:16    16777216

--//session 2:
SYS@book> @opeek 0x6cfa13f0 48 0
[06CFA13F0, 06CFA1420) = 65589640 00000000 65589640 00000000 00000000 00000000 0000003C 00003D20 00000000 00000000 690D6690 00000000
--//gets没有变化。

--//到此问题明白了,当我执行ab.txt脚本时先执行select /*+ 9 */ count(*) from dept where deptno = 93834;然后执行hash.sql脚
--//本,是hash.sql导致前面执行的sql的library cache mutex address的gets数量发生变化。

--//session 1:
SCOTT@book01p> select /*+ 9 */ count(*) from dept where deptno = 93834;
  COUNT(*)
----------
         0

--//session 2:
SYS@book> @opeek 0x6cfa13f0 48 0
[06CFA13F0, 06CFA1420) = 65589640 00000000 65589640 00000000 00000000 00000000 0000003D 00003D20 00000000 00000000 690D6690 00000000
--//gets加1,不知道为什么?

--//session 1:
SCOTT@book01p> @ hash
HASH_VALUE SQL_ID        CHILD_NUMBER KGL_BUCKET PLAN_HASH_VALUE HASH_HEX   SQL_EXEC_START      SQL_EXEC_ID
---------- ------------- ------------ ---------- --------------- ---------- ------------------- -----------
3403546624 as3g00v5dw000            0          0      2236899148  cade0000  2025-02-04 09:50:27    16777244

--//session 2:
SYS@book> @opeek 0x6cfa13f0 48 0
[06CFA13F0, 06CFA1420) = 65589640 00000000 65589640 00000000 00000000 00000000 0000003F 00003D20 00000000 00000000 690D6690 00000000
--//gets加2.

--//我仔细看了hash.sql脚本发现有对v$sql视图的访问,我修改hash.sql脚本,建立新的hashz.sql,注解了对v$sql的查询。
$ cat  tpt/hashz.sql
-- Copyright 2018 Tanel Poder. All rights reserved. More info at http://tanelpoder.com
-- Licensed under the Apache License, Version 2.0. See LICENSE.txt for terms & conditions.

--------------------------------------------------------------------------------
--
-- File name:   hash.sql
-- Purpose:     Show the hash value, SQL_ID and child number of previously
--              executed SQL in session
--
-- Author:      Tanel Poder
-- Copyright:   (c) http://www.tanelpoder.com
--
-- Usage:       @hash
--
--
-- Other:       Doesn't work on 9i for 2 reasons. There appears to be a bug
--              with v$session.prev_hash_value in 9.2.x and also there's no
--              SQL_ID nor CHILD_NUMBER column in V$SESSION in 9i.
--
--------------------------------------------------------------------------------

col hash_hex for a10

--variable my_sid number
--exec  :my_sid :=  userenv('sid');

select
    ses.prev_hash_value                                hash_value
  , ses.prev_sql_id                                    sql_id
  , ses.prev_child_number                              child_number
  , MOD(ses.prev_hash_value, 131072)                   kgl_bucket
--  , (select sql.plan_hash_value
--     from v$sql sql
--     where
--         sql.sql_id = ses.prev_sql_id
--     and sql.child_number = ses.prev_child_number
--     and sql.address = ses.prev_sql_addr)        plan_hash_value
  , lower(to_char(ses.prev_hash_value, 'XXXXXXXX'))    hash_hex
  , ses.prev_exec_start                                sql_exec_start
  , ses.prev_exec_id                                   sql_exec_id
from
    v$session ses
where
    ses.sid = :my_sid
--    ses.sid = userenv('sid')
/
--//注:修改tpt的init.sql脚本加入了如下
variable my_sid number
exec  :my_sid :=  userenv('sid');
--//不然执行报错,注意不能打开上面的注解,因为这样hash看到的就是exec  :my_sid :=  userenv('sid')的sql_id.

--//修改ab.txt脚本。
$ cat ab.txt
select /*+ 9 */ count(*) from dept where deptno = 93834;
@ hashz

--//再次重复测试,问题就不存在了。
--//session 2:
SYS@book> @opeek 0x6cfa13f0 48 0
[06CFA13F0, 06CFA1420) = 65589640 00000000 65589640 00000000 00000000 00000000 00000042 00003D20 00000000 00000000 690D6690 00000000

--//session 1:
SCOTT@book01p> @ ab.txt
  COUNT(*)
----------
         0

HASH_VALUE SQL_ID        CHILD_NUMBER KGL_BUCKET HASH_HEX   SQL_EXEC_START      SQL_EXEC_ID
---------- ------------- ------------ ---------- ---------- ------------------- -----------
3403546624 as3g00v5dw000            0          0  cade0000  2025-02-04 10:05:09    16777248

--//session 2:
SYS@book> @opeek 0x6cfa13f0 48 0
[06CFA13F0, 06CFA1420) = 65589640 00000000 65589640 00000000 00000000 00000000 00000042 00003D20 00000000 00000000 690D6690 00000000
--//gets没有变化。

SCOTT@book01p> alter session  set session_cached_cursors=0 ;
Session altered.

--//执行ab.txt 3次。
SYS@book> @opeek 0x6cfa13f0 48 0
[06CFA13F0, 06CFA1420) = 65589640 00000000 65589640 00000000 00000000 00000000 00000047 00003D20 00000000 00000000 690D6690 00000000

SYS@book> @opeek 0x6cfa13f0 48 0
[06CFA13F0, 06CFA1420) = 65589640 00000000 65589640 00000000 00000000 00000000 00000048 00003D20 00000000 00000000 690D6690 00000000

SYS@book> @opeek 0x6cfa13f0 48 0
[06CFA13F0, 06CFA1420) = 65589640 00000000 65589640 00000000 00000000 00000000 00000049 00003D20 00000000 00000000 690D6690 00000000

SYS@book> @opeek 0x6cfa13f0 48 0
[06CFA13F0, 06CFA1420) = 65589640 00000000 65589640 00000000 00000000 00000000 0000004A 00003D20 00000000 00000000 690D6690 00000000
--//这样看到gets数量加1.

6.最后记录alter session  set session_cached_cursors=0 的情况gets的变化。
--//重启数据库。

select /*+ 9 */ count(*) from dept where deptno = 93834;
/
/
/

SYS@book> @opeek 0x6cfa13f0 48 0
[06CFA13F0, 06CFA1420) = 6CFA13F0 00000000 6CFA13F0 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

SYS@book> @opeek 0x6cfa13f0 48 0
[06CFA13F0, 06CFA1420) = 64932AA0 00000000 64932AA0 00000000 00000000 00000000 00000004 00000000 00000000 00000000 60C93CE0 00000000

SYS@book> @opeek 0x6cfa13f0 48 0
[06CFA13F0, 06CFA1420) = 64932AA0 00000000 64932AA0 00000000 00000000 00000000 00000005 00000000 00000000 00000000 60C93CE0 00000000

SYS@book> @opeek 0x6cfa13f0 48 0
[06CFA13F0, 06CFA1420) = 64932AA0 00000000 64932AA0 00000000 00000000 00000000 00000006 00000000 00000000 00000000 60C93CE0 00000000

SYS@book> @opeek 0x6cfa13f0 48 0
[06CFA13F0, 06CFA1420) = 64932AA0 00000000 64932AA0 00000000 00000000 00000000 00000007 00000000 00000000 00000000 60C93CE0 00000000

--//实际上的变化是4,5,6,7.增量4,1,1,1.
posted @   lfree  阅读(1)  评论(0编辑  收藏  举报
相关博文:
阅读排行:
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· 清华大学推出第四讲使用 DeepSeek + DeepResearch 让科研像聊天一样简单!
· 实操Deepseek接入个人知识库
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
· Plotly.NET 一个为 .NET 打造的强大开源交互式图表库
历史上的今天:
2024-02-19 [20240219]建立完善sql_idx.sh脚本.txt
2019-02-19 [20190219]那个更快(11g).txt
点击右上角即可分享
微信分享提示