[20210520]11g shared pool latch与library cache mutex的简单探究.txt
[20210520]11g shared pool latch与library cache mutex的简单探究.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
--//session 1:
SCOTT@book> @ spid
SID SERIAL# PROCESS SERVER SPID PID P_SERIAL# C50
---------- ---------- ------------------------ --------- ------ ------- ---------- --------------------------------------------------
1 6585 6763 DEDICATED 6764 24 155 alter system kill session '1,6585' immediate;
--//记下SID=1.
--//session 2:
SYS@book> oradebug setmypid
Statement processed.
SYS@book> oradebug peek 0x000000006010D860 4
[06010D860, 06010D864) = 00000000
--//shared pool的地址
SYS@book> oradebug peek 0x0000000080528f40 4
[080528F40, 080528F44) = 00000000
--//对应bucket= 102650的mutext地址。
SYS@book> oradebug poke 0x000000006010D860 4 0x00000001
BEFORE: [06010D860, 06010D864) = 00000000
AFTER: [06010D860, 06010D864) = 00000001
SYS@book> oradebug poke 0x0000000080528f40 4 0x00000001
BEFORE: [080528F40, 080528F44) = 00000000
AFTER: [080528F40, 080528F44) = 00000001
--//session 1:
--//该语句的第1次执行：
SCOTT@book> select * from dept where deptno=20;
--//注意第1个字符小写,该语句第1次执行,需要硬解析.挂起!!
SYS@book> @ wait
P1RAW P2RAW P3RAW P1 P2 P3 SID SERIAL# SEQ# EVENT STATUS STATE WAIT_TIME_MICRO SECONDS_IN_WAIT WAIT_CLASS CLIENT_INFO
---------------- ---------------- ---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------------------------------- -------- ------------------- --------------- --------------- -------------------- --------------------
00000000000190FA 0000000000000001 000000000000003E 102650 1 62 1 6585 13633 library cache: mutex X ACTIVE WAITING 1033 0 Concurrency
000000006010D860 0000000000000150 00 1611716704 336 0 15 3 56187 latch: shared pool ACTIVE WAITING 10829818 11 Concurrency
--//持有library cache: mutex X,注意P1,P2.P3参数。这里P1标识library cache bucket，也就是hash的后21位(2进制)相当于hash%131072。
SYS@book> @ ev_name 'library cache: mutex X'
EVENT# EVENT_ID NAME PARAMETER1 PARAMETER2 PARAMETER3 WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS
------ ---------- ---------------------- ---------- ---------- ---------- ------------- ----------- -----------
289 1646780882 library cache: mutex X idn value where 3875070507 4 Concurrency
SYS@book> oradebug peek 0x80528f40 40
[080528F40, 080528F68) = 00000001 00000000 0000092B 00042180 000190FA 00000006 80528F58 00000000 80528F58 00000000
SYS@book> oradebug poke 0x0000000080528f40 4 0x00000000
BEFORE: [080528F40, 080528F44) = 00000001
AFTER: [080528F40, 080528F44) = 00000000
SYS@book> @ wait
P1RAW P2RAW P3RAW P1 P2 P3 SID SERIAL# SEQ# EVENT STATUS STATE WAIT_TIME_MICRO SECONDS_IN_WAIT WAIT_CLASS CLIENT_INFO
---------------- ---------------- ---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------------------------------- -------- ------------------- --------------- --------------- -------------------- --------------------
000000006010D860 0000000000000150 00 1611716704 336 0 1 6585 30899 latch: shared pool ACTIVE WAITING 11647911 12 Concurrency
000000006010D860 0000000000000150 00 1611716704 336 0 15 3 56187 latch: shared pool ACTIVE WAITING 81069438 81 Concurrency
000000006010D860 0000000000000150 00 1611716704 336 0 239 1 13363 latch: shared pool ACTIVE WAITING 64137792 64 Concurrency
--//持有latch: shared pool.
SYS@book> oradebug peek 0x80528f40 40
[080528F40, 080528F68) = 00000000 00000000 0000092C 00042180 000190FA 00000000 80528F58 00000000 80528F58 00000000
--//session 1:
SCOTT@book> select * from dept where deptno=20;
DEPTNO DNAME LOC
------ -------- ------
20 RESEARCH DALLAS
--//session 2:
SYS@book> select * from v$mutex_sleep_history where mutex_identifier=102650 order by sleep_timestamp desc
2 @ prxx
==============================
MUTEX_IDENTIFIER : 102650
--//这里mutex_identifier=102650明显标识bucket。
SLEEP_TIMESTAMP : 2021-05-20 09:44:51.172610
MUTEX_TYPE : Library Cache
GETS : 2346
--// 2346 = 0x92a,也就是gets的数量。
SLEEPS : 29193716
--// 29193716 = 0x1bd75f4
REQUESTING_SESSION : 1
BLOCKING_SESSION : 0
LOCATION : kglhdgn1 62
--//mutex的位置，kglhdgn1标识什么不懂。与前面P3参数一致。
MUTEX_VALUE : 0000000000000001
P1 : 0
P1RAW : 00
P2 : 0
P3 : 0
P4 : 0
P5 :
3.继续：
--//session 2:
SYS@book> oradebug poke 0x000000006010D860 4 0x00000001
BEFORE: [06010D860, 06010D864) = 00000000
AFTER: [06010D860, 06010D864) = 00000001
SYS@book> oradebug poke 0x0000000080528f40 4 0x00000001
BEFORE: [080528F40, 080528F44) = 00000000
AFTER: [080528F40, 080528F44) = 00000001
--//session 1:
--//该语句的第2次执行：
SCOTT@book> select * from dept where deptno=20;
--//再次挂起。
--//session 2:
SYS@book> @ wait
P1RAW P2RAW P3RAW P1 P2 P3 SID SERIAL# SEQ# EVENT STATUS STATE WAIT_TIME_MICRO SECONDS_IN_WAIT WAIT_CLASS CLIENT_INFO
---------------- ---------------- ---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------------------------------- -------- ------------------- --------------- --------------- -------------------- --------------------
000000006010D860 0000000000000150 00 1611716704 336 0 1 6585 30902 latch: shared pool ACTIVE WAITING 2224269 2 Concurrency
000000006010D860 0000000000000150 00 1611716704 336 0 15 3 56297 latch: shared pool ACTIVE WAITING 15761568 16 Concurrency
--//持有latch: shared pool
SYS@book> oradebug poke 0x000000006010D860 4 0x00000000
BEFORE: [06010D860, 06010D864) = 00000001
AFTER: [06010D860, 06010D864) = 00000000
SYS@book> @ wait
P1RAW P2RAW P3RAW P1 P2 P3 SID SERIAL# SEQ# EVENT STATUS STATE WAIT_TIME_MICRO SECONDS_IN_WAIT WAIT_CLASS CLIENT_INFO
---------------- ---------------- ---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------------------------------- -------- ------------------- --------------- --------------- -------------------- --------------------
00000000000190FA 0000000000000001 000000000000003E 102650 1 62 1 6585 48167 library cache: mutex X ACTIVE WAITING 879 0 Concurrency
--//持有library cache: mutex X.
SYS@book> oradebug poke 0x0000000080528f40 4 0x00000000
BEFORE: [080528F40, 080528F44) = 00000001
AFTER: [080528F40, 080528F44) = 00000000
--//session 1:
SCOTT@book> select * from dept where deptno=20;
DEPTNO DNAME LOC
------ -------- ------
20 RESEARCH DALLAS
--//session 2:
SYS@book> select * from v$mutex_sleep_history where mutex_identifier=102650 order by sleep_timestamp desc
2 @ prxx
==============================
MUTEX_IDENTIFIER : 102650
SLEEP_TIMESTAMP : 2021-05-20 09:53:25.089384
MUTEX_TYPE : Library Cache
GETS : 2348
SLEEPS : 31094348
REQUESTING_SESSION : 1
BLOCKING_SESSION : 0
LOCATION : kglhdgn1 62
MUTEX_VALUE : 0000000000000001
P1 : 0
P1RAW : 00
P2 : 0
P3 : 0
P4 : 0
P5 :
==============================
SYS@book> oradebug peek 0x80528f40 40
[080528F40, 080528F68) = 00000000 00000000 0000092D 000421D8 000190FA 00000000 80528F58 00000000 80528F58 00000000
--// 92D = 2349,我估计保存gets的数量.
--//重复测试：
--//session 2:
SYS@book> oradebug poke 0x000000006010D860 4 0x00000001
BEFORE: [06010D860, 06010D864) = 00000000
AFTER: [06010D860, 06010D864) = 00000001
SYS@book> oradebug poke 0x0000000080528f40 4 0x00000001
BEFORE: [080528F40, 080528F44) = 00000000
AFTER: [080528F40, 080528F44) = 00000001
--//session 1,该语句的第3次执行：
SCOTT@book> select * from dept where deptno=20;
--//再次挂起。
--//session 2:
SYS@book> @ wait
P1RAW P2RAW P3RAW P1 P2 P3 SID SERIAL# SEQ# EVENT STATUS STATE WAIT_TIME_MICRO SECONDS_IN_WAIT WAIT_CLASS CLIENT_INFO
---------------- ---------------- ---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------------------------------- -------- ------------------- --------------- --------------- -------------------- --------------------
000000006010D860 0000000000000150 00 1611716704 336 0 1 6585 31022 latch: shared pool ACTIVE WAITING 4849339 5 Concurrency
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
000000006010D860 0000000000000150 00 1611716704 336 0 15 3 56359 latch: shared pool ACTIVE WAITING 15238187 15 Concurrency
000000006010D860 0000000000000150 00 1611716704 336 0 211 1 5215 latch: shared pool ACTIVE WAITING 10285743 10 Concurrency
--//持有latch: shared pool
SYS@book> oradebug poke 0x000000006010D860 4 0x00000000
BEFORE: [06010D860, 06010D864) = 00000001
AFTER: [06010D860, 06010D864) = 00000000
SYS@book> @ wait
P1RAW P2RAW P3RAW P1 P2 P3 SID SERIAL# SEQ# EVENT STATUS STATE WAIT_TIME_MICRO SECONDS_IN_WAIT WAIT_CLASS CLIENT_INFO
---------------- ---------------- ---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------------------------------- -------- ------------------- --------------- --------------- -------------------- --------------------
00000000000190FA 0000000000000001 000000000000003E 102650 1 62 1 6585 12835 library cache: mutex X ACTIVE WAITED SHORT TIME 3 0 Concurrency
--//持有library cache: mutex,注意一些细节,这里是library cache: mutex X.
SYS@book> oradebug poke 0x0000000080528f40 4 0x00000000
BEFORE: [080528F40, 080528F44) = 00000001
AFTER: [080528F40, 080528F44) = 00000000
SYS@book> @ wait
no rows selected
--//session 1:
SCOTT@book> select * from dept where deptno=20;
DEPTNO DNAME LOC
------ -------- ------
20 RESEARCH DALLAS
--//重复测试：
--//session 3:
SYS@book> oradebug poke 0x000000006010D860 4 0x00000001
BEFORE: [06010D860, 06010D864) = 00000000
AFTER: [06010D860, 06010D864) = 00000001
SYS@book> oradebug poke 0x0000000080528f40 4 0x00000001
BEFORE: [080528F40, 080528F44) = 00000000
AFTER: [080528F40, 080528F44) = 00000001
--//session 1,该语句的第4次执行：
SCOTT@book> select * from dept where deptno=20;
DEPTNO DNAME LOC
------ -------- ------
20 RESEARCH DALLAS
--//ok，这次不需要任何latch: shared pool以及library cache: mutex x或者library cache: mutex.
--//以为第3次后光标已经缓存了。绕过了这两个等待事件。
总结：
--//不小心又写的很长，我总喜欢把一些细节记录下来，这样我自己以后还能看懂。
--//很明显前面3次出现都需要遇到这两个等待事件，
--//第1次library cache: mutex X，再持有latch: shared pool
--//第2，3次先持有latch: shared pool，然后才是library cache: mutex X.
--//另外从11g开始oracle大量使用mutex代替latch，muext无处不在，而v$mutex_sleep_history ，v$mutex_sleep中locate的缩写有时候真看不懂
--//一旦遇到muext问题，诊断会变得很困难。也许查询底层视图x$mutex_sleep_history，知道MUTEX_ADDR很关键。
--//另外这样测试有点繁琐，不小心导致挂起，看来给使用gdb来调式。
SYS@book> select * from x$mutex_sleep_history where mutex_identifier=102650 order by sleeps desc
2 @ prxx
==============================
ADDR : 00007F0A6CA6D490
INDX : 244
INST_ID : 1
MUTEX_ADDR : 0000000080528F40
MUTEX_IDENTIFIER : 102650
SLEEP_TIMESTAMP : 2021-05-20 09:59:17.085644
MUTEX_TYPE : Library Cache
MUTEX_TYPE_ID : 4
GETS : 2349
SLEEPS : 33710600
REQUESTING_SESSION : 1
BLOCKING_SESSION : 0
LOCATION_ID : 62
LOCATION : kglhdgn1 62
MUTEX_VALUE : 0000000000000001
P1 : 0
P1RAW : 00
P2 : 0
P3 : 0
P4 : 0
P5 :
PL/SQL procedure successfully completed.
