[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。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2773083/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20210520]11g shared pool latch與library cache mutex的簡單探究.txtMutex
- [20210521]11g shared pool latch與library cache mutex的簡單探究4.txtMutex
- 優化Shared Pool Latch與Library Cache Latch競爭優化
- 共享池之六:shared pool latch/ library cache latch /lock pin 簡介
- shared pool library cache latch 競爭優化辦法優化
- Shared Pool優化和Library Cache Latch衝突優化優化
- [20201203]探究library cache mutex X 3.txtMutex
- 故障排除：Shared Pool優化和Library Cache Latch衝突優化優化
- Shared pool的library cache lock/pin及硬解析
- 等待模擬-library cache shared pool 硬解析
- 深入理解shared pool共享池之library cache的library cache lock系列四
- 深入理解shared pool共享池之library cache的library cache pin系列三
- shared pool之三：library cache結構/library cache object的結構-dump LibraryHandleObject
- 【每日一摩斯】-Shared Pool優化和Library Cache Latch衝突優化 (1523934.1)-系列6優化
- 【每日一摩斯】-Shared Pool優化和Library Cache Latch衝突優化 (1523934.1)-系列5優化
- 【每日一摩斯】-Shared Pool優化和Library Cache Latch衝突優化 (1523934.1)-系列4優化
- 【每日一摩斯】-Shared Pool優化和Library Cache Latch衝突優化 (1523934.1)-系列3優化
- 【每日一摩斯】-Shared Pool優化和Library Cache Latch衝突優化 (1523934.1)-系列2優化
- 【每日一摩斯】-Shared Pool優化和Library Cache Latch衝突優化 (1523934.1)-系列1優化
- latch:shared pool的一點理解
- Flush an Object Out The Library Cache [SGA] Using The DBMS_SHARED_POOLObject
- 深入理解shared pool共享池之library cache系列一
- 深入理解shared pool共享池之library cache系列二
- 簡單分析shared pool(一)
- 簡單分析shared pool(二)
- 簡單分析shared pool(三)
- [20220303]oracle如何定位使用library cache mutex 3.txtOracleMutex
- library cache: mutex X引發的故障Mutex
- 'cursor:mutex ..'/ 'cursor:pin ..'/ 'library cache:mutex ..'型別的等待事件Mutex型別事件
- latch:library cache lock等待事件事件
- Oracle Library cache mutex x tipsOracleMutex
- 轉_診斷latch:shared pool等待事件事件
- 用markhot緩解library cache:mutex xMutex
- oracle library cache相關的等待事件及latchOracle事件
- [20170727]library cache: mutex X.txtMutex
- oradebug poke模擬shared pool latch與硬解析原理小析
- 一個關於latch: library cache事件的處理事件
- oracle10g_oracle11g_library cache_shared pool管理方面的小區別Oracle