--//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
相关文章
- 01-13shared pool latch与library cache latch的简单探究
- 01-13[20210520]11g shared pool latch与library cache mutex的简单探究3.txt
- 01-13[20210520]11g shared pool latch与library cache mutex的简单探究.txt
- 01-13[转载】——故障排除：Shared Pool优化和Library Cache Latch冲突优化 (文档 ID 1523934.1)
- 01-13【每日一摩斯】-Shared Pool优化和Library Cache Latch冲突优化 (1523934.1)-系列3
- 01-13shared pool latch与library cache latch的简单探究