[20210520]11g shared pool latch与library cache mutex的简单探究3.txt
[20210520]11g shared pool latch与library cache mutex的简单探究3.txt
--//前面测试library cache 的转储,发现11g不再使用latch library cache,而是使用library cache mutex代替.
--//测试11g下这种使用情况.
1.环境:
SCOTT@book> @ ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
SELECT addr
,latch#
,child#
,level#
,name
,gets
,sleeps
,immediate_gets
,immediate_misses
,spin_gets
FROM V$LATCH_CHILDREN
WHERE name LIKE 'shared pool'
ORDER BY addr;
ADDR LATCH# CHILD# LEVEL# NAME GETS SLEEPS IMMEDIATE_GETS IMMEDIATE_MISSES SPIN_GETS
---------------- ---------- ---------- ---------- ----------- ---------- ---------- -------------- ---------------- ----------
000000006010D860 336 1 7 shared pool 6780053 1054 0 0 12871
000000006010D900 336 2 7 shared pool 11 0 0 0 0
000000006010D9A0 336 3 7 shared pool 11 0 0 0 0
000000006010DA40 336 4 7 shared pool 11 0 0 0 0
000000006010DAE0 336 5 7 shared pool 11 0 0 0 0
000000006010DB80 336 6 7 shared pool 11 0 0 0 0
000000006010DC20 336 7 7 shared pool 11 0 0 0 0
7 rows selected.
--//注意仅仅一个shared pool latch的gets很大,其它实际上启动后不会使用.
--//shared pool latch的数量与共享池内存大小,cpu数量有关.实际上取 共享池内存大小/512M(对于11G是这样,早期版本有一些除256M,128M)
--//以及cpu数量/4的最小值.我设置sga才484M.仅仅1个shared pool latch.
2.测试:
--//session 1:
SCOTT@book> select * from dept where deptno=20;
DEPTNO DNAME LOC
---------- -------------- -------------
20 RESEARCH DALLAS
--//执行多次。
SCOTT@book> @ hash
HASH_VALUE SQL_ID CHILD_NUMBER HASH_HEX
---------- ------------- ------------ ---------
95129850 80baj2c2ur47u 0 5ab90fa
SYS@book> @ tix
New tracefile_identifier = /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_13734_0001.trc
SYS@book> oradebug setmypid
Statement processed.
SYS@book> oradebug dump library_cache 26;
Statement processed.
--//这样可以获得堆大小信息。 2+8+16 = 26
--//alter session set events 'immediate trace name library_cache level N';
. Level=1,转储库缓存统计信息。
. Level=2,转储散列表概要。
. Level=4,转储库缓存对象,只包含基本信息。
. Level=8,转储库缓存对象,包含详细信息(包括 child references、pin waiters等)。
. Level=16,增加堆大小信息。
. Level=32,增加堆信息。
SYS@book> @ sharepool/shp4 80baj2c2ur47u 0
TEXT KGLHDADR KGLHDPAR C40 KGLHDLMD KGLHDPMD KGLHDIVC KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLOBT09
--------------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
child handle address 000000007D949148 000000007C351C40 select * from dept where deptno=20 1 0 0 000000007C8CF828 000000007DCF55E0 4528 12144 3067 19739 19739 95129850 80baj2c2ur47u 0
parent handle address 000000007C351C40 000000007C351C40 select * from dept where deptno=20 1 0 0 000000007BE1E1F8 00 4720 0 0 4720 4720 95129850 80baj2c2ur47u 65535
Bucket: #=102650 Mutex=0x80528f40(0, 22, 0, 6)
LibraryHandle: Address=0x7c351c40 Hash=5ab90fa LockMode=N PinMode=0 LoadLockMode=0 Status=VALD
ObjectName: Name=select * from dept where deptno=20
FullHashValue=e8ec445edab00042802d511305ab90fa Namespace=SQL AREA(00) Type=CURSOR(00) Identifier=95129850 OwnerIdn=83
Statistics: InvalidationCount=0 ExecutionCount=6 LoadCount=2 ActiveLocks=1 TotalLockCount=3 TotalPinCount=1
Counters: BrokenCount=1 RevocablePointer=1 KeepDependency=1 Version=0 BucketInUse=2 HandleInUse=2 HandleReferenceCount=0
Concurrency: DependencyMutex=0x7c351cf0(0, 1, 0, 0) Mutex=0x7c351d80(1, 30, 0, 6)
Flags=RON/PIN/TIM/PN0/DBN/[10012841]
WaitersLists:
Lock=0x7c351cd0[0x7c351cd0,0x7c351cd0]
Pin=0x7c351cb0[0x7c351cb0,0x7c351cb0]
LoadLock=0x7c351d28[0x7c351d28,0x7c351d28]
Timestamp: Current=05-13-2021 10:04:12
HandleReference: Address=0x7c351e10 Handle=(nil) Flags=[00]
ReferenceList:
Reference: Address=0x7d9f70c0 Handle=0x7cf101a0 Flags=ROD[21]
LibraryObject: Address=0x7dcf4f20 HeapMask=0000-0001-0001-0000 Flags=EXS[0000] Flags2=[0000] PublicFlags=[0000]
DataBlocks:
Block: #='0' name=KGLH0^5ab90fa pins=0 Change=NONE
Heap=0x7be1e1f8 Pointer=0x7dcf4fc0 Extent=0x7dcf4ea0 Flags=I/-/P/A/-/-
FreedLocation=0 Alloc=2.437500 Size=3.976562 LoadTime=12215214510
ChildTable: size='16'
Child: id='0' Table=0x7dcf5dd0 Reference=0x7dcf5810 Handle=0x7d949148
Children:
Child: childNum='0'
LibraryHandle: Address=0x7d949148 Hash=0 LockMode=N PinMode=0 LoadLockMode=0 Status=VALD
Name: Namespace=SQL AREA(00) Type=CURSOR(00)
Statistics: InvalidationCount=0 ExecutionCount=6 LoadCount=1 ActiveLocks=1 TotalLockCount=3 TotalPinCount=7
Counters: BrokenCount=1 RevocablePointer=1 KeepDependency=0 Version=0 BucketInUse=0 HandleInUse=0 HandleReferenceCount=0
Concurrency: DependencyMutex=0x7d9491f8(0, 0, 0, 0) Mutex=0x7c351d80(1, 30, 0, 6)
Flags=RON/PIN/PN0/EXP/CHD/[10012111]
WaitersLists:
Lock=0x7d9491d8[0x7d9491d8,0x7d9491d8]
Pin=0x7d9491b8[0x7d9491b8,0x7d9491b8]
LoadLock=0x7d949230[0x7d949230,0x7d949230]
ReferenceList:
Reference: Address=0x7dcf5810 Handle=0x7c351c40 Flags=CHL[02]
LibraryObject: Address=0x7c9b3210 HeapMask=0000-0001-0001-0000 Flags=EXS[0000] Flags2=[0000] PublicFlags=[0000]
Dependencies: count='2' size='16' table='0x7c9b4048'
Dependency: num='0'
Reference=0x7c9b37c8 Position=0 Flags=DEP[0001]
Handle=0x7c606dc0 Type=NONE(255) Parent=SCOTT
Dependency: num='1'
Reference=0x7c9b3868 Position=14 Flags=DEP[0001]
Handle=0x7d46f3a0 Type=TABLE(02) Parent=SCOTT.DEPT
ReadOnlyDependencies: count='1' size='16'
ReadDependency: num='0' Table=0x7c9b40e0 Reference=0x7c9b36c8 Handle=0x7cf101a0 Flags=DEP/ROD/KPP[61]
Authorizations: count='1' size='16' entryeize='16'
Accesses: count='1' size='16'
Dependency: num='1' Type=0009
Translations: count='1' size='16'
Translation: num='0' Original=0x7d46f3a0 Final=0x7d46f3a0
DataBlocks:
Block: #='0' name=KGLH0^5ab90fa pins=0 Change=NONE
Heap=0x7c8cf828 Pointer=0x7c9b32b0 Extent=0x7c9b3190 Flags=I/-/P/A/-/-
FreedLocation=0 Alloc=2.593750 Size=3.976562 LoadTime=12215214510
Block: #='6' name=SQLA^5ab90fa pins=0 Change=NONE
Heap=0x7dcf55e0 Pointer=0x7dbe4500 Extent=0x7dbe38c0 Flags=I/-/-/A/-/E
FreedLocation=0 Alloc=8.445312 Size=11.859375 LoadTime=0
NamespaceDump:
Child Cursor: Heap0=0x7c9b32b0 Heap6=0x7dbe4500 Heap0 Load Time=05-13-2021 10:04:12 Heap6 Load Time=05-13-2021 10:04:12
NamespaceDump:
Parent Cursor: sql_id=80baj2c2ur47u parent=0x7dcf4fc0 maxchild=1 plk=y ppn=n
--//hash=5ab90fa = 95129850
--//95129850 % 131072 = 102650
--//知道这条语句一定使用Bucket: #=102650 Mutex=0x80528f40.
--//BUCKET肯定不会变,我关闭重启数据库发现Mutex对应的地址也没有变,估计这个启动时就设定好,我前面测试这类mutex结构体占用
--//40字节。
SYS@book> oradebug peek 0x80528f40 40
[080528F40, 080528F68) = 00000000 00000000 00000018 00000000 000190FA 00000000 80528F58 00000000 80528F58 00000000
--//看不出什么信息, 0x000190FA = 102650,对应Bucket.
--//退出回话,刷新共享池:
alter system flush shared_pool;
alter system flush shared_pool;
alter system flush shared_pool;
SYS@book> oradebug peek 0x80528f40 40
[080528F40, 080528F68) = 00000000 00000000 0000001B 00000000 000190FA 00000000 80528F58 00000000 80528F58 00000000
3.测试:
--//再次登录,测试前执行命令多次.主要是避免递归sql语句以及测试时遇到硬解析时挂起.
--//desc dept;
--//Select * from dept where deptno=20;
--//@ wait
--//前面的测试使用不小心很容易挂起,我决定换一种方式使用gdb跟踪方式看看。
--//session 1:
SCOTT@book> @ s
SCOTT@book(15,7)> @ spid
SID SERIAL# PROCESS SERVER SPID PID P_SERIAL# C50
---------- ---------- ------------------------ --------- ------ ------- ---------- --------------------------------------------------
15 7 7304 DEDICATED 7305 25 4 alter system kill session '15,7' immediate;
--//session 2:
--//建立gdb脚本。
$ cat latch_mutex.gdb
break kslgetl if $rdi==0X6010D860
commands
silent
printf "kslgetl %x, %d, %d, %d\n", $rdi, $rsi, $rdx, $rcx
c
end
break kglGetMutex if $rsi==0X80528f40
commands
silent
printf "kglGetMutex %x, %x, %x, %d\n", $rdi, $rsi, $rdx, $rcx
c
end
$ gdb -p 7305 -x latch_mutex.gdb
...
Loaded symbols for /u01/app/oracle/product/11.2.0.4/dbhome_1/lib/libnque11.so
0x000000379a00da70 in __read_nocancel () from /lib64/libpthread.so.0
Breakpoint 1 at 0x93f97a8
Breakpoint 2 at 0x98451a0
--//session 1,第1次执行:
SCOTT@book(15,7)> select * from dept where deptno=20;
DEPTNO DNAME LOC
---------- -------------- -------------
20 RESEARCH DALLAS
--//session 2:
(gdb) c
Continuing.
kglGetMutex c0cc9e0, 80528f40, 7c779468, 1
kslgetl 6010d860, 1, 0, 3980
kslgetl 6010d860, 1, 0, 3980
kslgetl 6010d860, 1, 0, 3980
kslgetl 6010d860, 1, 0, 4039
kslgetl 6010d860, 1, 0, 4039
kslgetl 6010d860, 1, 0, 4039
kslgetl 6010d860, 1, 0, 4039
kslgetl 6010d860, 1, 0, 4039
kslgetl 6010d860, 1, 0, 4039
kslgetl 6010d860, 1, 0, 4041
kslgetl 6010d860, 1, 0, 3983
kslgetl 6010d860, 1, 0, 3980
kslgetl 6010d860, 1, 0, 3980
kslgetl 6010d860, 1, 0, 3980
kslgetl 6010d860, 1, 0, 4039
kslgetl 6010d860, 1, 0, 4039
kslgetl 6010d860, 1, 0, 3980
kslgetl 6010d860, 1, 0, 4039
kslgetl 6010d860, 1, 0, 3980
kslgetl 6010d860, 1, 0, 3980
kslgetl 6010d860, 1, 0, 3980
kslgetl 6010d860, 1, 0, 3980
kslgetl 6010d860, 1, 0, 3980
kslgetl 6010d860, 1, 0, 3980
kslgetl 6010d860, 1, 0, 4039
kslgetl 6010d860, 1, 0, 4039
kslgetl 6010d860, 1, 0, 3980
kslgetl 6010d860, 1, 0, 4039
kslgetl 6010d860, 1, 0, 3980
kslgetl 6010d860, 1, 0, 4039
kslgetl 6010d860, 1, 0, 4039
kslgetl 6010d860, 1, 0, 3980
kslgetl 6010d860, 1, 0, 4039
kslgetl 6010d860, 1, 2109087424, 3991
kslgetl 6010d860, 1, 0, 4039
kslgetl 6010d860, 1, 0, 3980
kslgetl 6010d860, 1, 0, 4039
kslgetl 6010d860, 1, 0, 4039
kslgetl 6010d860, 1, 2112437952, 3991
kslgetl 6010d860, 1, 2108142296, 3991
kslgetl 6010d860, 1, 2108141736, 3991
kslgetl 6010d860, 1, 0, 3980
kslgetl 6010d860, 1, 0, 3980
kslgetl 6010d860, 1, 0, 4041
kslgetl 6010d860, 1, 2108970984, 3991
kslgetl 6010d860, 1, 2088454424, 3991
kslgetl 6010d860, 1, 2112262184, 3991
kslgetl 6010d860, 1, 2112437408, 3991
kslgetl 6010d860, 1, 2086935112, 3991
--//先出现mutex。
--//session 1,第2次执行:
SCOTT@book(15,7)> select * from dept where deptno=20;
DEPTNO DNAME LOC
---------- -------------- -------------
20 RESEARCH DALLAS
--//session 2:
kslgetl 6010d860, 1, 2086943304, 3991
kglGetMutex c0cc9e0, 80528f40, 7c779468, 1
kslgetl 6010d860, 1, 0, 4039
kslgetl 6010d860, 1, 0, 3980
kslgetl 6010d860, 1, 0, 4039
kslgetl 6010d860, 1, 2086935112, 3991
--//session 1,第3次执行:
SCOTT@book(15,7)> select * from dept where deptno=20;
DEPTNO DNAME LOC
---------- -------------- -------------
20 RESEARCH DALLAS
SCOTT@book(15,7)> select * from dept where deptno=20;
--//session 2:
kslgetl 6010d860, 1, 2086943304, 3991
kglGetMutex c0cc9e0, 80528f40, 7c779468, 1
--//session 1,第4次执行:
SCOTT@book(15,7)> select * from dept where deptno=20;
DEPTNO DNAME LOC
---------- -------------- -------------
20 RESEARCH DALLAS
--//session 2:
--//没有任何输出。
总结:
--//很明显前面3次出现都需要遇到这两个等待事件,
--//第1次library cache: mutex X,再持有latch: shared pool
--//第2,3次先持有latch: shared pool,然后才是library cache: mutex X.
--//第4次没有需要latch: shared pool,library cache: mutex X。
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· [.NET]调用本地 Deepseek 模型
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· .NET Core 托管堆内存泄露/CPU异常的常见思路
· PostgreSQL 和 SQL Server 在统计信息维护中的关键差异
· C++代码改造为UTF-8编码问题的总结
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· 清华大学推出第四讲使用 DeepSeek + DeepResearch 让科研像聊天一样简单!
· 实操Deepseek接入个人知识库
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
· Plotly.NET 一个为 .NET 打造的强大开源交互式图表库
2019-05-20 [20190520]exp imp on th fly.txt