[20210512]shared pool latch與library cache latch的簡單探究.txt
[20210512]shared pool latch與library cache latch的簡單探究.txt
--//2019年的測試連結：http://blog.itpub.net/267265/viewspace-2638770/=>[20190319]shared pool latch與library cache latch的簡單探究.txt
--//當時的總結：
--//11g已經不存在這個library cache latch,改為mutexes.所以測試在10g下進行!!
--//硬解析先要library cache latch,估計查詢該游標是否在共享池存在,如果不存在,持有shared pool latch,使用共享池記憶體建立父子游標.
--//再持有library cache latch.關於這點從前面的轉儲可以發現.但是等待事件先出現的是shared pool latch,然後才是library cache latch.
~~~~~~~~~~~~--//錯誤！！
--//軟解析的情況要正常需要持有library cache latch以及shared pool latch.
--//軟軟解析不需要持有對應的library cache latch和shared pool latch.
--//即使游標快取的情況下,新建立的會話第1次執行也需要持有library cache latch.shared pool latch.
--//如果某種原因產生子游標的情況下,第1,2,3次執行都需要library cache latch和shared pool latch.第4次執行游標已經cache,不再需要持有對應
--//library cache latch和shared pool latch.
--//注意千萬不要在生產系統做這樣的測試!!
--//今天發現我以前測試說明存在一些問題，硬解析等待事件先出現的是library cache latch,然後才是shared pool latch.
--//而我2019年的測試是改變引數optimizer_index_caching的情況，而當時父遊標已經存在，可能以前測試也是錯誤的。
--//先出現的是library cache latch,然後才是shared pool latch.
--//雖然10g估計現在已經很少人使用,還是做一些補充測試說明。
1.環境:
SCOTT@test> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- ----------------------------------------------------------------
x86_64/Linux 2.4.xx 10.2.0.4.0 Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
--//把一些常用命令先執行多次(3次以上避免執行時分析時掛起),比如 @ &r/wait10g,也可以事先多開幾個sys登入會話.
--//desc dept.
--//select * from dept ;
--// @ &r/wait10g
--//Select * from dept where deptno=12;
$ cat wait10g.sql
select p1raw,p2raw,p3raw,p1,p2,p3,sid,serial#,seq#,event,state,wait_time,seconds_in_wait from v$session where wait_class<>'Idle'
and sid not in (select sid from v$mystat where rownum=1)
order by event ;
SELECT addr
,latch#
,child#
,level#
,name
,gets
,sleeps
,immediate_gets
,immediate_misses
,spin_gets
FROM V$LATCH_CHILDREN
WHERE name LIKE 'library cache';
ADDR LATCH# CHILD# LEVEL# NAME GETS SLEEPS IMMEDIATE_GETS IMMEDIATE_MISSES SPIN_GETS
---------------- ------ ------ ------ ------------- ---- ------ -------------- ---------------- ---------
000000007A753430 215 29 5 library cache 1256 0 0 0 1
000000007A7534D0 215 28 5 library cache 741 0 0 0 0
000000007A753570 215 27 5 library cache 1022 0 0 0 0
000000007A753610 215 26 5 library cache 958 0 0 0 0
000000007A7536B0 215 25 5 library cache 1079 0 0 0 0
000000007A753750 215 24 5 library cache 1022 0 0 0 0
000000007A7537F0 215 23 5 library cache 1076 0 0 0 3
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
000000007A753890 215 22 5 library cache 950 0 0 0 0
000000007A753930 215 21 5 library cache 1028 0 0 0 0
000000007A7539D0 215 20 5 library cache 1223 0 0 0 0
000000007A753A70 215 19 5 library cache 941 0 0 0 0
000000007A753B10 215 18 5 library cache 781 0 0 0 0
000000007A753BB0 215 17 5 library cache 1050 0 0 0 3
000000007A753C50 215 16 5 library cache 1098 0 0 0 0
000000007A753CF0 215 15 5 library cache 1295 0 0 0 1
000000007A753D90 215 14 5 library cache 1431 0 0 0 1
000000007A753E30 215 13 5 library cache 1186 0 1 0 0
000000007A753ED0 215 12 5 library cache 1265 0 0 0 2
000000007A753F70 215 11 5 library cache 942 0 0 0 0
000000007A754010 215 10 5 library cache 1401 0 0 0 6
000000007A7540B0 215 9 5 library cache 1181 0 0 0 0
000000007A754150 215 8 5 library cache 1131 0 0 0 0
000000007A7541F0 215 7 5 library cache 1191 0 0 0 1
000000007A754290 215 6 5 library cache 611 0 0 0 0
000000007A754330 215 5 5 library cache 1189 0 0 0 0
000000007A7543D0 215 4 5 library cache 1165 0 0 0 0
000000007A754470 215 3 5 library cache 861 0 0 0 0
000000007A754510 215 2 5 library cache 1328 0 0 0 1
000000007A7545B0 215 1 5 library cache 1199 1 0 0 0
29 rows selected.
--//29個latch.
--//library cache latch數量與cpu數量有關.選取大於cpu數量的最接近的質數.我當前cpu數量24(實際上2個cpu, 每個Core Count:
--//6,Core Enabled: 6,Thread Count: 12),這樣顯示的24個cpu,我自己還第一次注意這個細節.
--//我簡單驗證修改cpu_count=8,library cache latch的數量是11.修改cpu_count=12,library cache latch的數量是13.大家可以自行驗證.
--//注意必須重啟才生效.
SCOTT@test> show parameter cpu_count
NAME TYPE VALUE
--------- -------- -----
cpu_count integer 24
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
---------------- ------ ------ ------ ----------- ----- ------ -------------- ---------------- ---------
00000000600E7840 214 1 7 shared pool 43351 0 0 0 849
00000000600E78E0 214 2 7 shared pool 8 0 0 0 0
00000000600E7980 214 3 7 shared pool 8 0 0 0 0
00000000600E7A20 214 4 7 shared pool 8 0 0 0 0
00000000600E7AC0 214 5 7 shared pool 8 0 0 0 0
00000000600E7B60 214 6 7 shared pool 8 0 0 0 0
00000000600E7C00 214 7 7 shared pool 8 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.
--//另外latch實際上共享池記憶體的一片區域.你可以注意.相鄰地址相減大小一樣,相當於陣列.比如:
--//0x600E7840=1611561024
--//0x600E78E0=1611561184
--//0x600E7980=1611561344
--//1611561184-1611561024 = 160
--//1611561344-1611561184 = 160
2.測試前準備:
--//開啟3個會話視窗,將一些需要執行的sql語句執行多次,避免測試時硬解析時掛起.
--//desc dept.
--//select * from dept ;
--// @ &r/wait10g
--//Select * from dept where deptno=12;
3.測試1:
--//session 2:
SYS@test> @ tix
New tracefile_identifier = /u01/app/oracle/admin/test/udump/test_ora_11969_0001.trc
SYS@test> oradebug setmypid
Statement processed.
SYS@test> oradebug peek 0x00000000600E7840 4
[0600E7840, 0600E7844) = 00000000
SYS@test> oradebug peek 0x000000007A7537F0 4
[07A7537F0, 07A7537F4) = 00000000
--//說明一下這些地址前面的測試已經確定，不再說明。參考連結http://blog.itpub.net/267265/viewspace-2638770/
SYS@test> oradebug poke 0x00000000600E7840 4 0x00000001
BEFORE: [0600E7840, 0600E7844) = 00000000
AFTER: [0600E7840, 0600E7844) = 00000001
SYS@test> oradebug poke 0x000000007A7537F0 4 0x00000001
BEFORE: [07A7537F0, 07A7537F4) = 00000000
AFTER: [07A7537F0, 07A7537F4) = 00000001
--//session 1:
SCOTT@test> select * from dept where deptno=12;
--//注意select小寫。
--//session 2:
SYS@test> @ wait10g
P1RAW P2RAW P3RAW P1 P2 P3 SID SERIAL# SEQ# EVENT STATE WAIT_TIME SECONDS_IN_WAIT
---------------- ---------------- ---------------- ---------- ---------- ---------- ---------- ---------- ---------- -------------------- ------- ---------- ---------------
000000007A7537F0 00000000000000D7 00 2054502384 215 0 142 5 152 latch: library cache WAITING 0 30
00000000600E7840 00000000000000D6 00 1611561024 214 0 160 1 37 latch: shared pool WAITING 0 57
00000000600E7840 00000000000000D6 00 1611561024 214 0 159 1 572 latch: shared pool WAITING 0 51
--//先持有latch: library cache.
SYS@test> oradebug poke 0x000000007A7537F0 4 0x00000000
BEFORE: [07A7537F0, 07A7537F4) = 000000FF
AFTER: [07A7537F0, 07A7537F4) = 00000000
SYS@test> @ wait10g
P1RAW P2RAW P3RAW P1 P2 P3 SID SERIAL# SEQ# EVENT STATE WAIT_TIME SECONDS_IN_WAIT
---------------- ---------------- ---------------- ---------- ---------- ---------- ---------- ---------- ---------- ------------------ ------- ---------- ---------------
00000000600E7840 00000000000000D6 00 1611561024 214 0 142 5 153 latch: shared pool WAITING 0 9
00000000600E7840 00000000000000D6 00 1611561024 214 0 160 1 37 latch: shared pool WAITING 0 120
00000000600E7840 00000000000000D6 00 1611561024 214 0 159 1 572 latch: shared pool WAITING 0 114
--//再持有latch: shared pool.
SYS@test> oradebug poke 0x00000000600E7840 4 0x00000000
BEFORE: [0600E7840, 0600E7844) = 000000FF
AFTER: [0600E7840, 0600E7844) = 00000000
--//session 1:
SCOTT@test> select * from dept where deptno=12;
no rows selected
--//執行成功!!
--//可以看出我以前的分析有一點點錯誤，就是對於沒有父遊標的硬分析，先出現latch: library cache後出現latch: shared pool.
--//我前面的測試是改變引數optimizer_index_caching的情況，而當時父遊標已經存在。做一個補充說明。
SYS@test> oradebug poke 0x00000000600E7840 4 0x00000001
BEFORE: [0600E7840, 0600E7844) = 00000000
AFTER: [0600E7840, 0600E7844) = 00000001
SYS@test> oradebug poke 0x000000007A7537F0 4 0x00000001
BEFORE: [07A7537F0, 07A7537F4) = 00000000
AFTER: [07A7537F0, 07A7537F4) = 00000001
--//sessin 1:
SCOTT@test> Select * from dept where deptno=12;
no rows selected
--//你可以發現大寫開頭的語句可以正常執行,因為該語句已經快取.
--//還原:
SYS@test> oradebug poke 0x00000000600E7840 4 0x00000000
BEFORE: [0600E7840, 0600E7844) = 000000FF
AFTER: [0600E7840, 0600E7844) = 00000000
SYS@test> oradebug poke 0x000000007A7537F0 4 0x00000000
BEFORE: [07A7537F0, 07A7537F4) = 000000FF
AFTER: [07A7537F0, 07A7537F4) = 00000000
4.還有我想知道的為什麼該語句使用library cache的child#=23.
SCOTT@192.168.100.33:1521/test> select * from dept where deptno=12;
no rows selected
SCOTT@192.168.100.33:1521/test> @ hash
HASH_VALUE SQL_ID CHILD_NUMBER HASH_HEX
---------- ------------- ------------ ---------
30432287 2f476y80x0r0z 0 1d05c1f
--//30432287 %29 = 6
--//30432287 %131072 = 23583
--//23583 % 29 = 6
--//根本對不上。
SYS@192.168.100.33:1521/test> @ tix
New tracefile_identifier = /u01/app/oracle/admin/test/udump/test_ora_15341_0001.trc
SYS@192.168.100.33:1521/test> oradebug dump library_cache 10;
Statement processed.
SYS@192.168.100.33:1521/test> @ tix
New tracefile_identifier = /u01/app/oracle/admin/test/udump/test_ora_15341_0002.trc
SYS@192.168.100.33:1521/test> oradebug dump library_cache 24;
Statement processed.
SYS@192.168.100.33:1521/test> @ sharepool/shp4 2f476y80x0r0z 0
TEXT KGLHDADR KGLHDPAR C40 KGLHDLMD KGLHDPMD KGLHDIVC KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLOBT09
--------------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
child handle address 000000007C9BE058 000000007C971958 select * from dept where deptno=12 0 0 0 000000007C855E70 00000000772C4A98 3664 8088 1803 13555 13555 30432287 2f476y80x0r0z 0
parent handle address 000000007C971958 000000007C971958 select * from dept where deptno=12 0 0 0 000000007C9505F0 00 2812 0 0 2812 2812 30432287 2f476y80x0r0z 65535
--//10g的library_cache與11g的library_cache格式存在許多不同的地方,檢視轉儲:
LIBRARY CACHE HASH TABLE: size=131072 count=316
Buckets with more than 20 objects:
NONE
Hash Chain Size Number of Buckets
--------------- -----------------
0 130757
1 314
2 1
3 0
4 0
5 0
6 0
7 0
8 0
9 0
10 0
11 0
12 0
13 0
14 0
15 0
16 0
17 0
18 0
19 0
20 0
>20 0
....
BUCKET 23583:
--//BUCKET 23583 與前面測試的30432287 %131072 = 23583對應.
LIBRARY OBJECT HANDLE: handle=7c971958 mtx=0x7c971a88(1) cdp=1
name=select * from dept where deptno=12
hash=fa4ab910ef98d2aa2710e6f201d05c1f timestamp=05-12-2021 10:07:12
namespace=CRSR flags=RON/KGHP/TIM/PN0/SML/KST/DBN/MTX/[120100d0]
kkkk-dddd-llll=0000-0001-0001 lock=0 pin=0 latch#=23 hpc=0000 hlc=0000
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
--//這裡的latch#=23 是如何計算的呢?
lwt=0x7c971a00[0x7c971a00,0x7c971a00] ltm=0x7c971a10[0x7c971a10,0x7c971a10]
pwt=0x7c9719c8[0x7c9719c8,0x7c9719c8] ptm=0x7c9719d8[0x7c9719d8,0x7c9719d8]
ref=0x7c971a30[0x7c971a30,0x7c971a30] lnd=0x7c971a48[0x7c971a48,0x7c971a48]
LIBRARY OBJECT: object=772c4c78
type=CRSR flags=EXS[0001] pflags=[0000] status=VALD load=0
CHILDREN: size=16
child# table reference handle
------ -------- --------- --------
0 772c4740 772c43b0 7c9be058
DATA BLOCKS:
data# heap pointer status pins change whr
----- -------- -------- --------- ---- ------ ---
0 7c9505f0 772c4d90 I/P/A/-/- 0 NONE 00
BUCKET 23583 total object count=1
...
LIBRARY OBJECT HANDLE: handle=7c9be058 mtx=0x7c9be188(0) cdp=0
namespace=CRSR flags=RON/KGHP/PN0/EXP/[10010100]
kkkk-dddd-llll=0000-0001-0001 lock=0 pin=0 latch#=23 hpc=fffe hlc=fffe
lwt=0x7c9be100[0x7c9be100,0x7c9be100] ltm=0x7c9be110[0x7c9be110,0x7c9be110]
pwt=0x7c9be0c8[0x7c9be0c8,0x7c9be0c8] ptm=0x7c9be0d8[0x7c9be0d8,0x7c9be0d8]
ref=0x7c9be130[0x772c43b0,0x772c43b0] lnd=0x7c9be148[0x7c9be148,0x7c9be148]
CHILD REFERENCES:
reference latch flags
--------- ----- -------------------
772c43b0 6 CHL[02]
LIBRARY OBJECT: object=7713b7c0
type=CRSR flags=EXS[0001] pflags=[0000] status=VALD load=0
DEPENDENCIES: count=1 size=16
dependency# table reference handle position flags
----------- -------- --------- -------- -------- -------------------
0 7713b288 7713afc8 767e3268 14 DEP[01]
AUTHORIZATIONS: count=1 size=16 minimum entrysize=16
00000000 39000000 00020000 00000000
ACCESSES: count=1 size=16
dependency# types
----------- -----
0 0009
TRANSLATIONS: count=1 size=16
original final
-------- --------
767e3268 767e3268
DATA BLOCKS:
data# heap pointer status pins change whr
----- -------- -------- --------- ---- ------ ---
0 7c855e70 7713b8d8 I/-/A/-/- 0 NONE 00
6 772c4a98 76a5d3d8 I/-/A/-/E 0 NONE 00
--//就在我準備選擇放棄的時候我發現29-6 不正好等於 23嗎,不會是某種巧合吧.
--// 29 - (23583 % 29) = 23
--//查詢V$LATCH_CHILDREN 的CHILD# 範圍是1-29.如果簡單的使用bucket取模運算範圍是0-28,也許正是這個原因,需要使用
--//29-(bucket%29)獲取latch#的值.
--//可以驗證我的判斷是否正確.
$ grep -E "^BUCKET|^ kkkk-dddd-llll" test_ora_15341_0001.trc | paste -d" " - - | grep "^BUCKET" > aa.txt
--//注我的轉儲僅僅有1個bucket有2個物件.如果按照上面的執行,僅僅取出一部分,檢查發現BUCKET 95158有2個物件.
BUCKET 95158:
LIBRARY OBJECT HANDLE: handle=7ca00368 mtx=0x7ca00498(2) cdp=2
name=select col#,intcol#,toid,version#,packed,intcols,intcol#s,flags, synobj#, nvl(typidcol#, 0) from coltype$ where obj#=:1 order by intcol# desc
hash=c4e38e35ac58a4bb7a6b84ef2f4d73b6 timestamp=05-12-2021 10:04:16
namespace=CRSR flags=RON/KGHP/TIM/PN0/MED/KST/DBN/MTX/[500100d0]
kkkk-dddd-llll=0000-0001-0001 lock=N pin=0 latch#=20 hpc=001a hlc=001a
...
LIBRARY OBJECT HANDLE: handle=7ca48958 mtx=0x7ca48a88(4) cdp=3
name=
select i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.type#,i.flags,i.property,i.pctfree$,i.initrans,i.maxtrans,i.blevel,i.leafcnt,i.distkey,i.lblkkey,i.dblkkey,i.clufac,i.cols,i.analyzetime,i.samplesize,i.dataobj#,nvl(i.degree,1),nvl(i.instances,1),i.rowcnt,mod(i.pctthres$,256),i.indmethod#,i.trunccnt,nvl(c.unicols,0),nvl(c.deferrable#+c.valid#,0),nvl(i.spare1,i.intcols),i.spare4,i.spare2,i.spare6,decode(i.pctthres$,null,null,mod(trunc(i.pctthres$/256),256)),ist.cachedblk,ist.cachehit,ist.logicalread from ind$
hash=1d4a51a2c89ab6b07a3c64bebc5573b6 timestamp=05-12-2021 10:04:16
namespace=CRSR flags=RON/KGHP/TIM/PN0/KST/DBN/MTX/[100100d0]
kkkk-dddd-llll=0000-0001-0001 lock=N pin=0 latch#=20 hpc=003c hlc=003c
--//修改其中一個kkkk-dddd-llll 為 kkkk-dddd-xxxx.再次執行
$ awk "{print $2,$6}" aa.txt | sed "s/: latch#=/ /"
$ awk "{print $2,$6}" aa.txt | sed "s/: latch#=/ /" | awk "{print 29-($1 % 29),$2}" | head
5 5
24 24
8 8
26 26
25 25
12 12
11 11
21 21
20 20
15 15
$ awk "{print $2,$6}" aa.txt | sed "s/: latch#=/ /" | awk "{print 29-($1 % 29),$2}" | tail
27 27
18 18
25 25
27 27
18 18
9 9
5 5
12 12
27 27
14 14
--//完全能對上,驗證我的判斷正確.
$ awk "{print $2,$6}" aa.txt | sed "s/: latch#=/ /" | awk "{if (29-($1 % 29)!=$2){print $0}}"
--//沒有任何輸出.
5.總結:
--//估計沒幾個人認真看我的文章,不然這個錯誤很容易發現,總結已經寫在開頭不再重複.
--//僅僅補充 latch# = library_cache_latch總數 - (bucket % library_cache_latch總數)
--//bucket 使用 hash_value % 131072
--//另外11g已經不存在這個library cache latch改為mutexes.每個BUCKET都有一個mutex,這樣出現碰撞的機率大大降低.
--//當然最好對於OLTP系統,還是避免在sql語句中使用文字變數,而應該合理的使用繫結變數.
6.補充改變引數產生子游標的情況：
--//session 2:
SYS@test> oradebug setmypid
Statement processed.
SYS@test> oradebug peek 0x00000000600E7840 4
[0600E7840, 0600E7844) = 00000000
SYS@test> oradebug peek 0x000000007A7537F0 4
[07A7537F0, 07A7537F4) = 00000000
--//session 1:
SCOTT@test> @ &r/spid
SID SERIAL# PROCESS SERVER SPID PID P_SERIAL# C50
---------- ---------- ------------ --------- ------ ------- ---------- --------------------------------------------------
145 4 29162 DEDICATED 29163 23 1 alter system kill session '145,4' immediate;
SCOTT@test> alter session set optimizer_index_caching=1;
Session altered.
--//session 2:
SYS@test> oradebug poke 0x00000000600E7840 4 0x00000001
BEFORE: [0600E7840, 0600E7844) = 00000000
AFTER: [0600E7840, 0600E7844) = 00000001
SYS@test> oradebug poke 0x000000007A7537F0 4 0x00000001
BEFORE: [07A7537F0, 07A7537F4) = 00000000
AFTER: [07A7537F0, 07A7537F4) = 00000001
SCOTT@test> select * from dept where deptno=12;
--//掛起!!
SYS@test> @ &r/wait10g
P1RAW P2RAW P3RAW P1 P2 P3 SID SERIAL# SEQ# EVENT STATE WAIT_TIME SECONDS_IN_WAIT
---------------- ---------------- ---------------- ---------- ---------- ---------- ---------- ---------- ---------- -------------------- ------- ---------- ---------------
000000007A7537F0 00000000000000D7 00 2054502384 215 0 145 4 156 latch: library cache WAITING 0 6
00000000600E7840 00000000000000D6 00 1611561024 214 0 160 1 36 latch: shared pool WAITING 0 15
00000000600E7840 00000000000000D6 00 1611561024 214 0 159 1 572 latch: shared pool WAITING 0 12
--//持有latch: library cache,sid=145
SYS@test> oradebug poke 0x000000007A7537F0 4 0x00000000
BEFORE: [07A7537F0, 07A7537F4) = 000000FF
AFTER: [07A7537F0, 07A7537F4) = 00000000
SYS@test> @ &r/wait10g
P1RAW P2RAW P3RAW P1 P2 P3 SID SERIAL# SEQ# EVENT STATE WAIT_TIME SECONDS_IN_WAIT
---------------- ---------------- ---------------- ---------- ---------- ---------- ---------- ---------- ---------- -------------------- ------- ---------- ---------------
00000000600E7840 00000000000000D6 00 1611561024 214 0 145 4 157 latch: shared pool WAITING 0 0
00000000600E7840 00000000000000D6 00 1611561024 214 0 160 1 36 latch: shared pool WAITING 0 39
00000000600E7840 00000000000000D6 00 1611561024 214 0 159 1 572 latch: shared pool WAITING 0 36
--//持有latch: shared pool,sid=145
SYS@test> oradebug poke 0x00000000600E7840 4 0x00000000
BEFORE: [0600E7840, 0600E7844) = 000000FF
AFTER: [0600E7840, 0600E7844) = 00000000
SYS@test> @ &r/wait10g
no rows selected
--//sesion 1:
SCOTT@test> select * from dept where deptno=12;
no rows selected
SYS@test> @ &r/sharepool/shp4 2f476y80x0r0z 0
TEXT KGLHDADR KGLHDPAR C40 KGLHDLMD KGLHDPMD KGLHDIVC KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLOBT09
--------------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
child handle address 00000000767180F8 0000000076718320 select * from dept where deptno=12 0 0 0 0000000076718038 00000000770A4250 3664 8088 1803 13555 13555 30432287 2f476y80x0r0z 0
child handle address 00000000766F5498 0000000076718320 select * from dept where deptno=12 1 0 0 00000000766F53D8 00000000770A3C88 3664 8088 1803 13555 13555 30432287 2f476y80x0r0z 1
parent handle address 0000000076718320 0000000076718320 select * from dept where deptno=12 1 0 0 0000000076718260 00 2812 0 0 2812 2812 30432287 2f476y80x0r0z 65535
--//看來我以前這裡也分析錯了。都是先latch: library cache，後latch: shared pool
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2772034/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20190319]shared pool latch與library cache latch的簡單探究.txt
- [20210520]11g shared pool latch與library cache mutex的簡單探究.txtMutex
- [20210521]11g shared pool latch與library cache mutex的簡單探究4.txtMutex
- [20210520]11g shared pool latch與library cache mutex的簡單探究3.txtMutex
- [20210708]使用那個shared pool latch.txt
- [20210803]使用那個shared pool latch(補充).txt
- [20220412]shared pool latch與使用sga heap的疑問2.txt
- [20220413]shared pool latch與使用sga heap的疑問3.txt
- latch:library cache lock等待事件事件
- [20220406]使用那個shared pool latch的疑問1.txt
- [20190419]shared latch spin count.txt
- [20190419]shared latch spin count 2.txt
- [20210218]shared latch spin count 6.txt
- [20210218]shared latch spin count 5.txt
- [20190416]檢視shared latch gets的變化.txt
- [20190415]關於shared latch(共享栓鎖).txt
- [20210208][20200426]檢視shared latch gets的變化.txt
- [20190423]簡單測試latch nowilling等待模式.txt模式
- [20190416]完善shared latch測試指令碼2.txt指令碼
- 透過案例學調優之--和 SHARED POOL 相關的主要 Latch
- [20201203]探究library cache mutex X 3.txtMutex
- [20200211]檢視v$db_object_cache的CHILD_LATCH欄位.txtObject
- [20190416]process allocation latch.txt
- [20190102]DBMS_SHARED_POOL.MARKHOT與表.txt
- [20190418]exclusive latch spin count.txt
- [20200223]關於latch and mutext的優化.txtMutex優化
- [20190416]exclusive latch測試指令碼.txt指令碼
- 【BUFFER】Oracle buffer cache之 latch 學習記錄Oracle
- [20200217]使用snapper探究DBMS_SHARED_POOL.MARKHOT標識熱物件的等待事件.txtAPP物件事件
- [20210418]CBC latch再討論3.txt
- [20210419]CBC latch再討論4.txt
- [20210413]CBC latch再討論2.txt
- [20190409]latch get 引數where and why.txt
- [20190416]11g下那些latch是Exclusive的.txt
- [20190415]10g下那些latch是共享的.txt
- [20190415]11g下那些latch是共享的.txt
- [20210507]dump library_cache.txt
- [20190402]Library Cache mutex.txtMutex