结论
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 vers
ion
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) w
ait_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 [or
apid (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 '%¶meter%'
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
0