欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2022-06-11 08:29:26
[20190319]shared pool latch与library cache latch的简单探究.txt--//昨天看Oracle DBA手记3:数据库性能优化与内部原理解析.pdf 电子书,看了eygle的关于latch之类的测试.--//自己也重复测试看看.--//首先说明一下11g已经 ......

[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.