[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.
--//探究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.
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· 清华大学推出第四讲使用 DeepSeek + DeepResearch 让科研像聊天一样简单!
· 实操Deepseek接入个人知识库
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
· Plotly.NET 一个为 .NET 打造的强大开源交互式图表库
2024-02-19 [20240219]建立完善sql_idx.sh脚本.txt
2019-02-19 [20190219]那个更快(11g).txt