在oracle 10.2.0.5分析硬解析及软解析及软软解析获取shared pool latch机制系列五

结论

1,测试环境是oracle 10.2.0.5
2,_kghdsidx_count=1，即共享池子池个数是1个
3,硬解析需要获取shared pool latch
4,软解析需要获取shared pool latch
5,软软解析不需要获取shared pool latch
6,上次各类解析皆要获取library cache latch
7,上述各类解析要获取的shared pool latch是child#=1,即第1个子latch
  却不会去获取其它6个可用的子latch
8,增大共享池子池到5个
即参数 _kghdsidx_count=5
 8.1,仅硬解析需要获取shared pool latch
 8.2,软解析及软软解析不再需要获取shared pool latch
 8.3,如果共享池子池配置为1个，软解析也要获取shared pool latch
     而增加共享池子池为5个，软解析不需要再获取shared pool latch了，这就是增加子池的优点

测试

---oracle version
SQL> select * from v$version where rownum=1;


BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi






---仅1个子池
_kghdsidx_count                                    1                                                  max kghdsidx count




SQL> select addr,latch#,level#,name from v$latch where name='shared pool';


ADDR                 LATCH#     LEVEL# NAME
---------------- ---------- ---------- --------------------------------------------------
0000000060022CD0        216          7 shared pool


SQL> select addr,latch#,level#,name,child# from v$latch_children where name='shared pool' order by 5;


ADDR                 LATCH#     LEVEL# NAME                                                   CHILD#
---------------- ---------- ---------- -------------------------------------------------- ----------
00000000600E7AF0        216          7 shared pool                                                 1
00000000600E7B90        216          7 shared pool                                                 2
00000000600E7C30        216          7 shared pool                                                 3
00000000600E7CD0        216          7 shared pool                                                 4
00000000600E7D70        216          7 shared pool                                                 5
00000000600E7E10        216          7 shared pool                                                 6
00000000600E7EB0        216          7 shared pool                                                 7


7 rows selected.


---hang child#=1的shared pool latch,此时还有6个shared pool latch
SQL> oradebug setmypid
Statement processed.
SQL> oradebug poke 0x00000000600E7AF0 4 1
BEFORE: [0600E7AF0, 0600E7AF4) = 00000000
AFTER:  [0600E7AF0, 0600E7AF4) = 00000001


新生会话HANG住且SQL查询HANG住
[ora10g@seconary ~]$ sqlplus '/as sysdba'


SQL> select sid,serial#,program,event from v$session where type='USER';


分析SYSTEMSTATE DUMP，看上述HANG会话在等待什么
SQL> oradebug dump systemstate 10
Statement processed.
SQL> oradebug tracefile_name
/home/ora10g/admin/ora10g/udump/ora10g_ora_3580.trc




PROCESS 33:
  ----------------------------------------
  SO: 0xa42dc1b0, type: 2, owner: (nil), flag: INIT/-/-/0x00
  (process) Oracle pid=33, calls cur/top: 0xa43e82d0/0xa43e82d0, 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 0
              last post sent-location: No post
              last process posted by me: none
    (latch info) wait_event=0 bits=20
        Location from where call was made: kghupr1: Chunk Header
        Context saved from call: 2678808096
      waiting for 600e7af0 Child shared pool level=7 child#=1 ---等待shared pool latch
        Location from where latch is held: kghupr1: Chunk Header
        Context saved from call: 2658951960
        state=busy, wlstate=free
          waiters [orapid (seconds since: put on list, posted, alive check)]:
           10 (328, 1447926130, 328)
           11 (325, 1447926130, 325)
           34 (258, 1447926130, 258)
           8 (177, 1447926130, 177)
           33 (153, 1447926130, 147)
           9 (144, 1447926130, 144)
           waiter count=6
          gotten 70198 times wait, failed first 0 sleeps 0
          gotten 0 times nowait, failed: 0
      on wait list for 600e7af0
      holding    (efd=3) a14ee0b0 Child library cache level=5 child#=1 --同时持有library cache latch
        Location from where latch is held: kgllkdl: child: no lock handle: latch
        Context saved from call: 0
        state=busy, wlstate=free
    Process Group: DEFAULT, pseudo proc: 0xa42fe938
    O/S info: user: ora10g, term: pts/5, ospid: 4154 
    OSD pid info: Unix process pid: 4154, image: oracle@seconary (TNS V1-V3)
    (FOB) flags=2 fib=0xa27d54b0 incno=0 pending i/o cnt=0
     fname=/home/ora10g/ora10g/system01.dbf
     fno=1 lblksz=8192 fsiz=75520


而且同时发现smon,mmon后台进程也在等待shared pool latch,不再贴出相关TRC文件内容


释放child#=1 shared pool latch 
SQL> oradebug poke 0x00000000600E7AF0 4 0
BEFORE: [0600E7AF0, 0600E7AF4) = 000000FF
AFTER:  [0600E7AF0, 0600E7AF4) = 00000000




发现一个奇怪的问题，有7个shared pool latch,为何都等待同一个已HANG的shared pool latch,为何不去获取其它6个的shared pool latch呢
所以现在尝试hang child=2的shared pool latch,看会如何


SQL> oradebug poke 0x00000000600E7B90 4 1
BEFORE: [0600E7B90, 0600E7B94) = 00000000
AFTER:  [0600E7B90, 0600E7B94) = 00000001


可以正常生成登陆会话
[ora10g@seconary ~]$ sqlplus '/as sysdba'


新的SQL也可以运行
SQL> select count(*) from dba_objects;


  COUNT(*)
----------
     50228




SQL> select count(*) from dict;


  COUNT(*)
----------
      1882     




由此可见oracle并不是采用轮询的机制获取shared pool latch的,哪么到底采用什么机制呢，先暂在这么，先研究其它的问题


释放child#=2 shared pool latch
SQL> oradebug poke 0x00000000600E7B90 4 0
BEFORE: [0600E7B90, 0600E7B94) = 00000001
AFTER:  [0600E7B90, 0600E7B94) = 00000000


再研究下软解析，即SQL执行计划及文本已在共享池中，看会不会持有shared pool latch
SQL> select sid from v$mystat where rownum=1;


       SID
----------
       122


SQL> select * from t_2_latch;


         A
----------
         1


持有child#=1 shared pool latch
SQL> oradebug poke 0x00000000600E7AF0 4 1
BEFORE: [0600E7AF0, 0600E7AF4) = 00000000
AFTER:  [0600E7AF0, 0600E7AF4) = 00000001


重复运行上述查询
SQL> select * from t_2_latch;
--hang住


查看TRC文件，可知软解析也会等待shared pool latch,且持有library cache latch
PROCESS 33:
  ----------------------------------------
  SO: 0xa42dc1b0, type: 2, owner: (nil), flag: INIT/-/-/0x00
  (process) Oracle pid=33, calls cur/top: 0xa43e82d0/0xa43e82d0, flag: (0) -
            int error: 0, call error: 0, sess error: 0, txn error 0
  (post info) last post received: 110 0 4
              last post received-location: kslpsr
              last process to post me: a42cea88 1 6
              last post sent: 0 0 24
              last post sent-location: ksasnd
              last process posted by me: a42cea88 1 6
    (latch info) wait_event=0 bits=20
        Location from where call was made: kghupr1: Chunk Header
        Context saved from call: 2675911648
      waiting for 600e7af0 Child shared pool level=7 child#=1 
        Location from where latch is held: kghupr1: Chunk Header
        Context saved from call: 2658951960
        state=busy, wlstate=free
          waiters [orapid (seconds since: put on list, posted, alive check)]:
           10 (114, 1447927688, 114)
           11 (99, 1447927688, 99)
           33 (33, 1447927688, 33)
           waiter count=3
          gotten 111441 times wait, failed first 6 sleeps 7
          gotten 0 times nowait, failed: 0
      on wait list for 600e7af0
      holding    (efd=3) a14ee0b0 Child library cache level=5 child#=1 
        Location from where latch is held: kgllkdl: child: no lock handle: latch
        Context saved from call: 0
        state=busy, wlstate=free
    Process Group: DEFAULT, pseudo proc: 0xa42fe938
    O/S info: user: ora10g, term: pts/5, ospid: 4860 
    OSD pid info: Unix process pid: 4860, image: oracle@seconary (TNS V1-V3)


释放child#=1的shared pool latch
SQL> oradebug poke 0x00000000600E7AF0 4 0
BEFORE: [0600E7AF0, 0600E7AF4) = 000000FF
AFTER:  [0600E7AF0, 0600E7AF4) = 00000000    




最后看看软软解析，会不会持有shared pool latch呢
SQL> show user
USER is "SCOTT"
SQL> select sid from v$mystat where rownum=1;


       SID
----------
       122


SQL> select * from t_2_latch;


         A
----------
         1


SQL> select * from t_2_latch;


         A
----------
         1


SQL> select * from t_2_latch;


         A
----------
         1


SQL> select * from t_2_latch;


         A
----------
         1


SQL> 
持有child#=1 shared pool latch
SQL> oradebug setmypid
Statement processed.
SQL> oradebug poke 0x00000000600E7AF0 4 1
BEFORE: [0600E7AF0, 0600E7AF4) = 00000000
AFTER:  [0600E7AF0, 0600E7AF4) = 00000001


可见软软解析，不会再持有shared pool latch
SQL> select * from t_2_latch;


         A
----------
         1




调整共享池子池为5个
SQL> alter system set "_kghdsidx_count"=5 scope=spfile;


System altered.


SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORACLE instance started.


Total System Global Area 1157627904 bytes
Fixed Size                  2095800 bytes
Variable Size             301991240 bytes
Database Buffers          822083584 bytes
Redo Buffers               31457280 bytes
Database mounted.
Database opened.




SQL> col name_1 for a50
SQL> col value_1 for a50
SQL> col desc1 for a50
SQL> set linesize 300
SQL> /
Enter value for parameter: _kghdsidx_count
old   6: where (x.indx = y.indx) and lower(x.ksppinm) like '%&parameter%'
new   6: where (x.indx = y.indx) and lower(x.ksppinm) like '%_kghdsidx_count%'


NAME_1                                             VALUE_1                                            DESC1
-------------------------------------------------- -------------------------------------------------- --------------------------------------------------
_kghdsidx_count                                    5                                                  max kghdsidx count


SQL> select addr,latch#,level#,name from v$latch where name='shared pool';


ADDR                 LATCH#     LEVEL# NAME
---------------- ---------- ---------- --------------------------------------------------
0000000060022CD0        216          7 shared pool


SQL> select addr,latch#,level#,name,child# from v$latch_children where name='shared pool' order by 5;


ADDR                 LATCH#     LEVEL# NAME                                                   CHILD#
---------------- ---------- ---------- -------------------------------------------------- ----------
00000000600E7AF0        216          7 shared pool                                                 1
00000000600E7B90        216          7 shared pool                                                 2
00000000600E7C30        216          7 shared pool                                                 3
00000000600E7CD0        216          7 shared pool                                                 4
00000000600E7D70        216          7 shared pool                                                 5
00000000600E7E10        216          7 shared pool                                                 6
00000000600E7EB0        216          7 shared pool                                                 7


7 rows selected.


--先看硬解析


----hang child#=1 shared pool latch
SQL> oradebug setmypid
Statement processed.
SQL> oradebug poke 0x00000000600E7AF0 4 1
BEFORE: [0600E7AF0, 0600E7AF4) = 00000000
AFTER:  [0600E7AF0, 0600E7AF4) = 00000001


SQL> select sid from v$mystat where rownum=1;


       SID
----------
       123


--可见硬解析hang住，并且可见因为增加了共享池而去请求获取其它可用的shared pool latch的子latch
SQL> select count(1),count(1) from t_row;


---释放 child#=1 shared pool latch
SQL> oradebug poke 0x00000000600E7AF0 4 0
BEFORE: [0600E7AF0, 0600E7AF4) = 000000FF
AFTER:  [0600E7AF0, 0600E7AF4) = 00000000


再看下软解析
SQL> select count(1),count(1) from emp;


  COUNT(1)   COUNT(1)
---------- ----------
        14         14


----hang child#=1 shared pool latch
SQL> oradebug poke 0x00000000600E7AF0 4 1
BEFORE: [0600E7AF0, 0600E7AF4) = 00000000
AFTER:  [0600E7AF0, 0600E7AF4) = 00000001


可见软解析不会再获取shared pool latch了
SQL> select count(1),count(1) from emp;


  COUNT(1)   COUNT(1)
---------- ----------
        14         14       




再看下软软解析
SQL> select count(1) from dept;


  COUNT(1)
----------
         4


SQL> select count(1) from dept;


  COUNT(1)
----------
         4


SQL> select count(1) from dept;


  COUNT(1)
----------
         4




SQL> oradebug poke 0x00000000600E7AF0 4 1
BEFORE: [0600E7AF0, 0600E7AF4) = 00000000
AFTER:  [0600E7AF0, 0600E7AF4) = 00000001


可见软软解析也不会再获取shared pool latch
SQL> select count(1) from dept;


  COUNT(1)
----------
         4