[20190319]shared pool latch与library cache latch的简单探究.txt

来源：这里教程网时间：2026-03-03 13:03:53 作者：

[20190319]shared pool latch与library cache latch的简单探究.txt --//昨天看Oracle DBA手记3：数据库性能优化与内部原理解析.pdf 电子书,看了eygle的关于latch之类的测试. --//自己也重复测试看看. --//首先说明一下11g已经不存在这个shared pool latch,改为mutexes.所以测试在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登录会话. $ 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 --//600E7980=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 1: SCOTT@test> @ &r/spid SID SERIAL# PROCESS SERVER SPID PID P_SERIAL# C50 ---------- ---------- ------------ --------- ------ ------- ---------- -------------------------------------------------- 157 5 13448 DEDICATED 13449 17 3 alter system kill session '157,5' immediate; --//session 2: SYS@test> oradebug setmypid Statement processed. SYS@test> oradebug peek 0x00000000600E7840 4 [0600E7840, 0600E7844) = 00000000 SYS@test> oradebug poke 0x00000000600E7840 4 0x00000001 BEFORE: [0600E7840, 0600E7844) = 00000000 AFTER: [0600E7840, 0600E7844) = 00000001 --//给shared pool加上latch. --//session 1:(第1次执行) SCOTT@test> select * from dept where deptno=12; --//注意seelect小写与前面不同,主要是执行一次硬解析,同时避开一些递归语句. --//session 3: 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 157 5 114 latch: shared pool WAITING 0 60 00000000600E7840 00000000000000D6 00 1611561024 214 0 162 1 3266 latch: shared pool WAITING 0 66 00000000600E7840 00000000000000D6 00 1611561024 214 0 160 1 44 latch: shared pool WAITING 0 90 00000000600E7840 00000000000000D6 00 1611561024 214 0 159 1 625 latch: shared pool WAITING 0 69 --//session 2: SYS@test> oradebug dump systemstate 266 Statement processed. SYS@test> oradebug poke 0x00000000600E7840 4 0x00000000 BEFORE: [0600E7840, 0600E7844) = 000000FF AFTER: [0600E7840, 0600E7844) = 00000000 --//这样挂起会话中语句执行成功!! SYS@test> @ &r/wait10g no rows selected --//说明硬解析的sql语句需要获取shared pool. 4.检查转储文件内容: PROCESS 17: ---------------------------------------- SO: 0x7ce63c10, type: 2, owner: (nil), flag: INIT/-/-/0x00 (process) Oracle pid=17, calls cur/top: 0x7cfa89b0/0x7cfa89b0, flag: (0) - int error: 0, call error: 0, sess error: 0, txn error 0 (post info) last post received: 0 0 0 last post received-location: No post last process to post me: none last post sent: 0 0 33 last post sent-location: ksrpublish last process posted by me: 7ce604b8 1 22 (latch info) wait_event=0 bits=20 Location from where call was made: kghalo: waiting for 600e7840 Child shared pool level=7 child#=1 Location from where latch is held: kghupr1: Chunk Header Context saved from call: 1986860088 state=busy, wlstate=free waiters [orapid (seconds since: put on list, posted, alive check)]: 12 (197, 1552958147, 2) 13 (176, 1552958147, 2) 10 (173, 1552958147, 5) 17 (167, 1552958147, 167) waiter count=4 gotten 75402 times wait, failed first 851 sleeps 0 gotten 0 times nowait, failed: 0 on wait list for 600e7840 holding (efd=4) 7a7537f0 Child library cache level=5 child#=23 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Location from where latch is held: kglhdgn: child:: latch Context saved from call: 6 state=busy, wlstate=free Process Group: DEFAULT, pseudo proc: 0x7cea6ac8 O/S info: user: oracle, term: pts/3, ospid: 13449 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ OSD pid info: Unix process pid: 13449, image: oracle@xxxxdg (TNS V1-V3) Short stack dump: --//ospid: 13449,注意看下划线内容,指示它还持有library cache level=5 child#=23.对于child#=23.也就是第一次硬解析还需要持有 --//library cache latch,是否相同的sql语句每次解析都需要持有library cache latch,而且child#都是一样的呢? --//可以推测应该一样,假设2条相同的sql语句需要硬解析,如果持有library cache latch的child#不一样,这样有可能出现2个光标在共 --//享池?所以推测下次执行相同语句使用的library cache latch的child#应该与前一次相同. 5.继续测试: --//session 2: SYS@test> oradebug peek 0x000000007A7537F0 4 [07A7537F0, 07A7537F4) = 00000000 SYS@test> oradebug poke 0x000000007A7537F0 4 0x00000001 BEFORE: [07A7537F0, 07A7537F4) = 00000000 AFTER: [07A7537F0, 07A7537F4) = 00000001 --//给对应library cache 加latch,child#=23. --//session 1:(第2次执行) SCOTT@test> select * from dept where deptno=12; --//再次挂起! --//session 3: 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 157 5 146 latch: library cache WAITING 0 27 --//这就是软解析,需要持有library cache ltach.P1raw=000000007A7537F0也可以对上. --//注:另外补充说明一下第2,3次执行软解析也需要1次shared pool latch. --//session 2: SYS@test> oradebug poke 0x000000007A7537F0 4 0x00000000 BEFORE: [07A7537F0, 07A7537F4) = 000000FF AFTER: [07A7537F0, 07A7537F4) = 00000000 --//session 1:(第2次执行) SCOTT@test> select * from dept where deptno=12; no rows selected --//执行成功!!继续: --//session 2: SYS@test> oradebug poke 0x000000007A7537F0 4 0x00000001 BEFORE: [07A7537F0, 07A7537F4) = 00000000 AFTER: [07A7537F0, 07A7537F4) = 00000001 --//session 1:(第3次执行) SCOTT@test> select * from dept where deptno=12; --//再次挂起! --//session 2: SYS@test> oradebug poke 0x000000007A7537F0 4 0x00000000 BEFORE: [07A7537F0, 07A7537F4) = 000000FF AFTER: [07A7537F0, 07A7537F4) = 00000000 --//session 1: SCOTT@test> select * from dept where deptno=12; no rows selected 6.测试2: --//当前已经执行select * from dept where deptno=12;3次,如果session_cached_cursors非0的情况下,该光标已经被缓存,也就是相同 --//会话的情况下次执行,就是软软解析,不需要在持有library cache ltach. --//session 3: SYS@test> select * from v$open_cursor where SID=157; SADDR SID USER_NAME ADDRESS HASH_VALUE SQL_ID SQL_TEXT ---------------- ---------- --------- ---------------- ---------- ------------- ---------------------------------- 000000007CF80200 157 SCOTT 000000007683E920 30432287 2f476y80x0r0z select * from dept where deptno=12 000000007CF80200 157 SCOTT 000000007C9C1D68 3393152264 4gd6b1r53yt88 table_1_ff_14f_0_0_0 --//session 2: SYS@test> oradebug poke 0x000000007A7537F0 4 0x00000001 BEFORE: [07A7537F0, 07A7537F4) = 00000000 AFTER: [07A7537F0, 07A7537F4) = 00000001 SYS@test> oradebug poke 0x00000000600E7840 4 0x00000001 BEFORE: [0600E7840, 0600E7844) = 00000000 AFTER: [0600E7840, 0600E7844) = 00000001 --//session 1: SCOTT@test> select * from dept where deptno=12; no rows selected --//执行成功!!也就是当光标缓存后,sql语句执行不需要在持有对应的library cache latch,oracle通过这样的方式(软解析,软软解析)减少latch的使用. 7.测试3: --//如果打开新的会话窗口,再次执行select * from dept where deptno=12;,对于新会话并没有缓存该光标,第1次执行应该也是软解析. --//通过测试说明问题. --//session 2: SYS@test> oradebug poke 0x00000000600E7840 4 0x00000000 BEFORE: [0600E7840, 0600E7844) = 000000FF AFTER: [0600E7840, 0600E7844) = 00000000 --//首先取消shared pool latch.注意library cache latch还在.child#=23. --//注:另外补充说明一下第2,3次执行软解析也需要1次shared pool latch.估计这样不允许这时建立新的子光标. --//session 4: SCOTT@test> @ &r/spid SID SERIAL# PROCESS SERVER SPID PID P_SERIAL# C50 ---------- ---------- ------------ --------- ------ ------- ---------- -------------------------------------------------- 141 45 13648 DEDICATED 13649 25 3 alter system kill session '141,45' immediate; SCOTT@test> select * from dept where deptno=12; --//再次挂起!! --//session 3: 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 141 45 33 latch: library cache WAITING 0 18 --//sid=141,正是新打开的会话sid=141. --//session 2:取消library cache latch. SYS@test> oradebug poke 0x000000007A7537F0 4 0x00000000 BEFORE: [07A7537F0, 07A7537F4) = 000000FF AFTER: [07A7537F0, 07A7537F4) = 00000000 --//session 4: SCOTT@test> select * from dept where deptno=12; no rows selected --//正常执行!!也就是新打开会话即使其它会话已经缓存光标,第1次执行也是软解析. --//如果会话再次执行,应该该光标缓存,就不再需要对应的library cache latch. --//session 2: SYS@test> oradebug poke 0x000000007A7537F0 4 0x00000001 BEFORE: [07A7537F0, 07A7537F4) = 00000000 AFTER: [07A7537F0, 07A7537F4) = 00000001 --//session 4: SCOTT@test> select * from dept where deptno=12; no rows selected --//ok,执行成功!!也就是这次是软软解析. 8.测试3: --//如果会话改变参数,这样会产生新的子光标,看看这样的情况会持有那些latch. --//session 1: SCOTT@test> show parameter optimizer_index_caching NAME TYPE VALUE ----------------------- ------- ----- optimizer_index_caching integer 0 SCOTT@test> alter session set optimizer_index_caching=1; Session altered. --//修改参数optimizer_index_caching,这样如果执行select * from dept where deptno=12;会产生新的子光标. --//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 --//session 1:(修改参数后第1次执行) SCOTT@test> select * from dept where deptno=12; --//挂起!! --//session 3: 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 157 5 172 latch: library cache WAITING 0 21 00000000600E7840 00000000000000D6 00 1611561024 214 0 160 1 59 latch: shared pool WAITING 0 48 00000000600E7840 00000000000000D6 00 1611561024 214 0 159 1 799 latch: shared pool WAITING 0 45 --//注意仅仅看sid=157行,还需要library cache latch,因为父光标已经存在. --//session 2:取消对应library cache latch. SYS@test> oradebug poke 0x000000007A7537F0 4 0x00000000 BEFORE: [07A7537F0, 07A7537F4) = 000000FF AFTER: [07A7537F0, 07A7537F4) = 00000000 --//session 1:检查发现会话一样挂起!! SCOTT@test> select * from dept where deptno=12; --//session 3: 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 157 5 173 latch: shared pool WAITING 0 51 00000000600E7840 00000000000000D6 00 1611561024 214 0 160 1 59 latch: shared pool WAITING 0 192 00000000600E7840 00000000000000D6 00 1611561024 214 0 159 1 799 latch: shared pool WAITING 0 189 --//可以发现现在等待事件变成了latch: shared pool(sid=157).可以发现要因为参数的变化,要生产新的子光标,需要持有shared pool --//latch 从共享池内存建立子光标. --//session 2:取消shared pool latch. 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 --//执行成功!!继续测试: --//session 2: 再次设置latch. 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:(修改参数后第2次执行) SCOTT@test> select * from dept where deptno=12; --//挂起!! --//session 3: 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 157 5 176 latch: library cache WAITING 0 21 --//需要对应library cache latch. --//session 2: 取消library cache latch. SYS@test> oradebug poke 0x000000007A7537F0 4 0x00000000 BEFORE: [07A7537F0, 07A7537F4) = 000000FF AFTER: [07A7537F0, 07A7537F4) = 00000000 --//session 1: 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 ---------------- ---------------- ----- ---------- ---------- ---------- ---------- ---------- ---------- --------------------- ------- --------- --------------- 00000000600E7840 00000000000000D6 00 1611561024 214 0 157 5 177 latch: shared pool WAITING 0 12 --//奇怪还是需要持有shared pool latch.为什么?? --//session 2: 取消shared pool latch. SYS@test> oradebug poke 0x00000000600E7840 4 0x00000000 BEFORE: [0600E7840, 0600E7844) = 000000FF AFTER: [0600E7840, 0600E7844) = 00000000 --//session 1:(修改参数后第2次执行) SCOTT@test> select * from dept where deptno=12; no rows selected --//不理解为什么第2次执行(在修改参数optimizer_index_caching后)还需要持有shared pool latch... --//再来看看: --//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 --//session 1:(修改参数后第3次执行) SCOTT@test> select * from dept where deptno=12; --//挂起!! --//session 3: 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 157 5 180 latch: library cache WAITING 0 24 --//session 2:取消library cache latch. SYS@test> oradebug poke 0x000000007A7537F0 4 0x00000000 BEFORE: [07A7537F0, 07A7537F4) = 000000FF AFTER: [07A7537F0, 07A7537F4) = 00000000 --//session 1: SCOTT@test> select * from dept where deptno=12; --//挂起!! --//session 3: 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 157 5 181 latch: shared pool WAITING 0 64 --//session 2: 取消shared pool latch. 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 --//执行成功!! --//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 --//session 1: SCOTT@test> select * from dept where deptno=12; no rows selected --//可以发现同一会话如果产生新的子光标,第1,2,3次执行都需要library cache latch和shared pool latch.第4次执行光标已经cache,不再需要持有对应latch. 9.测试4: --//再回到session 4,参数没有变化optimizer_index_caching变化.看看现在的情况(该会话已经执行)select * from dept where deptno=12; 2次: --//session 2: SYS@test> oradebug poke 0x00000000600E7840 4 0x00000001 BEFORE: [0600E7840, 0600E7844) = 00000001 AFTER: [0600E7840, 0600E7844) = 00000001 SYS@test> oradebug poke 0x000000007A7537F0 4 0x00000001 BEFORE: [07A7537F0, 07A7537F4) = 000000FF AFTER: [07A7537F0, 07A7537F4) = 00000001 --//session 4: SCOTT@test> select * from dept where deptno=12; no rows selected --//软软解析依旧有效. SYS@test> select * from v$open_cursor where SID in (157,141) and sql_text like '%deptno%'; SADDR SID USER_NAME ADDRESS HASH_VALUE SQL_ID SQL_TEXT ---------------- ---------- --------- ---------------- ---------- ------------- ---------------------------------- 000000007CF80200 157 SCOTT 000000007683E920 30432287 2f476y80x0r0z select * from dept where deptno=12 000000007CF6AB80 141 SCOTT 000000007683E920 30432287 2f476y80x0r0z select * from dept where deptno=12 10.测试5: --//修改session 4参数: --//session 4: 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) = 000000FF AFTER: [07A7537F0, 07A7537F4) = 00000001 --//session 4: SCOTT@test> select * from dept where deptno=12; --//挂起!! --//session 2: SYS@test> oradebug poke 0x000000007A7537F0 4 0x00000000 BEFORE: [07A7537F0, 07A7537F4) = 00000001 AFTER: [07A7537F0, 07A7537F4) = 00000000 --//session 4: SCOTT@test> select * from dept where deptno=12; no rows selected --//session 2: SYS@test> oradebug poke 0x000000007A7537F0 4 0x00000001 BEFORE: [07A7537F0, 07A7537F4) = 00000000 AFTER: [07A7537F0, 07A7537F4) = 00000001 SYS@test> oradebug poke 0x00000000600E7840 4 0x00000001 BEFORE: [0600E7840, 0600E7844) = 00000000 AFTER: [0600E7840, 0600E7844) = 00000001 --//session 4: SCOTT@test> select * from dept where deptno=12; no rows selected --//软软解析!! 11.总结: --//不好写.11g已经不存在这个shared pool 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.

编辑推荐：

返回列表

[20190319]shared pool latch与library cache latch的简单探究.txt

编辑推荐：

相关推荐

excel表格怎样在数字前输入0

excel表格里怎么替换数据

excel表格里面如何插入圆形

excel表格里怎么添加表格数据透视表

excel表格中怎么添加线边框

excel表格里怎样设置条件自动排序

excel表格例怎样导入数据库

Excel表格如何设置数据排序

excel表格里怎么选择性粘贴快捷键

excel表格怎么插入分隔符号

excel表格怎么设置多行多列数据内容排序

Excel表格如何设置行和列相互转换

excel表格连接公式怎么使用

excel表格怎样链接到cad中

雷神推出 MIX PRO II 迷你主机：基于 Ultra 200H，玻璃上盖 + ARGB 灯效

制造商 Musnap 推出彩色墨水屏电纸书 Ocean C：支持手写笔、第三方安卓应用

最新软件资讯

热文推荐

天极热推