在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 '%¶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
