結論

1,row cache object latch共計50個child latch
2,這50個child latch個數基本等同於v$rowcache的不同cache#個數，當然二者值相差為1，大致是可以匹配，當然，具體為何差1，還要繼續思考與測試
3,發現重定義共享池大小操作，要獲取具體哪些row cache object latch,可見是1,5,8,32,46,47 child latch
4,引申下3，可見重定義共享池元件大小，要先後獲取上述6個不同的child latch,可見其複雜性
5,如果獲取不同對應的child latch,其等待事件為SGA: allocation forcing component growth ，並且P1,P2,P3全為0
此時只能從v$latch_children進行轉換思路分析，到底是何因引發這個操作等待了
6,本文學到一個新思路，先記錄v$latch及v$latch_children的值，然後hang child latch,接著恢復child latch,最後再記錄 v$latch及v$latch_children
基於gets,misses相關列的變化，檢視哪些child latch變化，就可以發現哪些latch及child latch與本次測試相關了

7,完全可以把第6引申出來的思路用於研究v$rowcache,就知道每次重定義共享池元件大小的操作，涉及哪些row cache了

測試

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

BANNER
----------------------------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

SQL> show parameter db_cache_size

NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
db_cache_size big integer 172M
SQL> show parameter shared_pool

NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
shared_pool_reserved_size big integer 24326963
shared_pool_size big integer 300M
SQL> select component,oper_type,oper_mode,parameter,initial_size,target_size/1024/1024 as target_size,final_size/1024/1024 as final_size,status,start_time,end_time from v$sga_resize_ops;

COMPONENT OPER_TYPE PARAMETER INITIAL_SIZE TARGET_SIZE FINAL_SIZE STATUS START_TIME END_TIME
------------------------------ --------------- -------------------- ------------ ----------- ---------- ------------------ ------------------- -------------------
DEFAULT 4K buffer cache STATIC db_4k_cache_size 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
RECYCLE buffer cache STATIC db_recycle_cache_siz 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
e

streams pool STATIC streams_pool_size 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
DEFAULT buffer cache STATIC db_cache_size 0 48 48 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
KEEP buffer cache STATIC db_keep_cache_size 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
DEFAULT 2K buffer cache STATIC db_2k_cache_size 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
DEFAULT 8K buffer cache STATIC db_8k_cache_size 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
ASM Buffer Cache STATIC db_cache_size 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
DEFAULT 16K buffer cache STATIC db_16k_cache_size 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29

COMPONENT OPER_TYPE PARAMETER INITIAL_SIZE TARGET_SIZE FINAL_SIZE STATUS START_TIME END_TIME
------------------------------ --------------- -------------------- ------------ ----------- ---------- ------------------ ------------------- -------------------
DEFAULT 32K buffer cache STATIC db_32k_cache_size 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
large pool STATIC large_pool_size 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
java pool STATIC java_pool_size 0 24 24 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
shared pool STATIC shared_pool_size 0 464 464 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
shared pool SHRINK shared_pool_size 486539264 12 268 ERROR 2015-12-15 06:57:38 2015-12-15 06:57:40
DEFAULT buffer cache GROW db_cache_size 50331648 1024 244 ERROR 2015-12-15 07:05:05 2015-12-15 07:05:05
DEFAULT buffer cache SHRINK db_cache_size 255852544 100 100 COMPLETE 2015-12-15 07:12:29 2015-12-15 07:12:29
shared pool GROW shared_pool_size 281018368 412 412 COMPLETE 2015-12-15 07:13:03 2015-12-15 07:13:03
shared pool SHRINK shared_pool_size 432013312 300 300 COMPLETE 2015-12-15 07:16:28 2015-12-15 07:16:28
DEFAULT buffer cache GROW db_cache_size 104857600 244 212 ERROR 2015-12-15 07:16:42 2015-12-15 07:16:42
DEFAULT buffer cache SHRINK db_cache_size 222298112 172 172 COMPLETE 2015-12-15 07:39:31 2015-12-15 07:39:31

20 rows selected.

SQL>

---共計50個row cache objects
SQL> select latch#,name,gets,misses,sleeps,immediate_gets,immediate_misses from v$latch where lower(name) like '%row%';

LATCH# NAME GETS MISSES SLEEPS IMMEDIATE_GETS IMMEDIATE_MISSES
---------- -------------------------------------------------- ---------- ---------- ---------- -------------- ----------------
270 row cache objects 274027 7 0 0 0
449 fixed table rows for x$hs_session 0 0 0 0 0

SQL> select latch#,child#,addr from v$latch_children where latch#=270 order by 2;

LATCH# CHILD# ADDR
---------- ---------- ----------------
270 1 000000007A1F2D88
270 2 000000007A1F3740
270 3 000000007A1F48F8
270 4 000000007A1F5AB0
270 5 000000007A2F5C68
270 6 000000007A2F6020
270 7 000000007A2F71D8
270 8 000000007A2F7798
270 9 000000007A3F7950
270 10 0000000079D34138
270 11 0000000079D362F0

LATCH# CHILD# ADDR
---------- ---------- ----------------
270 12 0000000079D374A8
270 13 0000000079D37E60
270 14 0000000079E38018
270 15 0000000079E383D0
270 16 0000000079E3A588
270 17 0000000079E3C740
270 18 0000000079E3C9F8
270 19 0000000079E3DBB0
270 20 0000000079E3ED68
270 21 0000000079F3EF20
270 22 0000000079F400D8

LATCH# CHILD# ADDR
---------- ---------- ----------------
270 23 0000000079F42290
270 24 0000000079F44448
270 25 0000000079F46600
270 26 0000000079F487B8
270 27 0000000079F49970
270 28 0000000079F4BB28
270 29 0000000079F4CCE0
270 30 0000000079F4DE98
270 31 0000000079F4F050
270 32 0000000079F50208
270 33 0000000079F503D0

LATCH# CHILD# ADDR
---------- ---------- ----------------
270 34 0000000079934138
270 35 0000000079A342F0
270 36 0000000079A364A8
270 37 0000000079A38660
270 38 0000000079A3A818
270 39 0000000079A3C9D0
270 40 0000000079A3EB88
270 41 0000000079A40D40
270 42 0000000079A42EF8
270 43 0000000079A450B0
270 44 0000000079A47268

LATCH# CHILD# ADDR
---------- ---------- ----------------
270 45 0000000079A47C20
270 46 0000000079A49DD8
270 47 0000000079A4AF90
270 48 0000000079A4B158
270 49 0000000079A4D310
270 50 0000000079A4E4C8

50 rows selected.

SQL>

SQL> select cache#,type,subordinate#,count,usage,fixed,gets,getmisses from v$rowcache order by 1;

CACHE# TYPE SUBORDINATE# COUNT USAGE FIXED GETS GETMISSES
---------- ---------------------- ------------ ---------- ---------- ---------- ---------- ----------
0 PARENT 9 9 0 3425 9
1 PARENT 0 0 0 0 0
2 PARENT 525 525 0 2493 525
3 PARENT 354 354 1 59701 353
4 PARENT 0 0 0 0 0
5 PARENT 0 0 0 0 0
6 PARENT 9 9 0 9 9
7 SUBORDINATE 0 2 2 0 4 2
7 SUBORDINATE 2 0 0 0 0 0
7 SUBORDINATE 1 24 24 0 95 24
8 SUBORDINATE 0 52 52 0 136 52

CACHE# TYPE SUBORDINATE# COUNT USAGE FIXED GETS GETMISSES
---------- ---------------------- ------------ ---------- ---------- ---------- ---------- ----------
8 PARENT 1046 1046 116 9029 930
9 PARENT 0 0 0 0 0
10 PARENT 35 35 0 2791 35
12 PARENT 0 0 0 0 0
13 PARENT 7 7 0 7 7
14 PARENT 1 1 0 2 1
15 PARENT 1 1 0 9335 1
16 PARENT 2895 2895 0 5312 2895
16 SUBORDINATE 1 124 124 0 941 124
16 SUBORDINATE 0 788 788 0 2319 788
17 PARENT 26 26 0 145 26

CACHE# TYPE SUBORDINATE# COUNT USAGE FIXED GETS GETMISSES
---------- ---------------------- ------------ ---------- ---------- ---------- ---------- ----------
18 PARENT 0 0 0 0 0
19 SUBORDINATE 0 0 0 0 0 0
19 PARENT 0 0 0 0 0
20 PARENT 0 0 0 0 0
21 SUBORDINATE 0 0 0 0 0 0
21 PARENT 0 0 0 0 0
22 PARENT 1 1 0 271 1
23 PARENT 0 0 0 0 0
24 PARENT 7 7 0 148 8
25 PARENT 0 0 0 0 0
26 PARENT 0 0 0 0 0

CACHE# TYPE SUBORDINATE# COUNT USAGE FIXED GETS GETMISSES
---------- ---------------------- ------------ ---------- ---------- ---------- ---------- ----------
27 PARENT 0 0 0 0 0
28 PARENT 0 0 0 0 0
29 PARENT 0 0 0 0 0
30 PARENT 0 0 0 0 0
31 PARENT 0 0 0 0 0
32 PARENT 0 0 0 0 0
33 PARENT 0 0 0 0 0
34 PARENT 0 0 0 0 0
35 PARENT 0 0 0 0 0
36 PARENT 0 0 0 0 0
37 PARENT 0 0 0 0 0

CACHE# TYPE SUBORDINATE# COUNT USAGE FIXED GETS GETMISSES
---------- ---------------------- ------------ ---------- ---------- ---------- ---------- ----------
38 PARENT 0 0 0 0 0
39 PARENT 0 0 0 0 0
40 PARENT 0 0 0 0 0
41 PARENT 0 0 0 0 0
43 PARENT 0 0 0 0 0
44 PARENT 0 0 0 0 0
45 PARENT 0 0 0 0 0
46 PARENT 0 0 0 0 0
47 SUBORDINATE 0 0 0 0 0 0
47 PARENT 0 0 0 0 0
48 PARENT 0 0 0 0 0

CACHE# TYPE SUBORDINATE# COUNT USAGE FIXED GETS GETMISSES
---------- ---------------------- ------------ ---------- ---------- ---------- ---------- ----------
49 SUBORDINATE 0 0 0 0 0 0
49 PARENT 0 0 0 0 0
50 PARENT 0 0 0 0 0
52 PARENT 0 0 0 0 0
53 PARENT 0 0 0 0 0

60 rows selected.

SQL>

---大致來看，基於row cache的latch共計50個，而v$row cache的快取型別共計51個，相差為1，大致是可以匹配，當然，具體為何差1，還要繼續思考與測試
SQL> select distinct cache# from v$rowcache order by 1;

CACHE#
----------
0
1
2
3
4
5
6
7
8
9
10

CACHE#
----------
12
13
14
15
16
17
18
19
20
21
22

CACHE#
----------
23
24
25
26
27
28
29
30
31
32
33

CACHE#
----------
34
35
36
37
38
39
40
41
43
44
45

CACHE#
----------
46
47
48
49
50
52
53

51 rows selected.

--hang child#=1 的row cache object latch
SQL> oradebug setmypid
Statement processed.
SQL> oradebug poke 0x000000007A1F2D88 4 1
BEFORE: [07A1F2D88, 07A1F2D8C) = 00000000
AFTER: [07A1F2D88, 07A1F2D8C) = 00000001

---調整SGA元件DB CACHE
SQL> select pid,spid from v$process where addr=(select paddr from v$session where sid=(select sid from v$mystat where rownum=1));

PID SPID
---------- ------------------------------------------------
23 9548

SQL> show parameter db_cache_size

NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
db_cache_size big integer 172M

--反向可以證明row cache object latch不用於buffer cache,而用於shared pool
SQL> alter system set db_cache_size=120m;

System altered.

SQL> show parameter shared_pool_size

NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
shared_pool_size big integer 300M

--hang住
SQL> alter system set shared_pool_size=200m;

---可見調整共享池大小的會話在等待sga:allocation forcing compent growth
SQL> select sid,event,p1,p1text,p2,p2text,p3,p3text from v$session where sid=1343;

SID EVENT P1 P1TEXT P2 P2TEXT P3 P3TEXT
---------- -------------------------------------------------- ---------- -------------------- ---------- -------------------- ---------- ---------------
1343 SGA: allocation forcing component growth 0 0 0

---row cache object latch的gets有所增高，但misses沒有變化
SQL> select latch#,name,gets,misses,sleeps,immediate_gets,immediate_misses from v$latch where lower(name) like '%row%';

LATCH# NAME GETS MISSES SLEEPS IMMEDIATE_GETS IMMEDIATE_MISSES
---------- -------------------------------------------------- ---------- ---------- ---------- -------------- ----------------
270 row cache objects 283465 7 0 0 0
449 fixed table rows for x$hs_session 0 0 0 0 0

--child#為1獲取的次數是最高的，然後是32,9,13,5,等等,換句話說，如果gets不為0，表明重定義共享池這個動作要獲取對應的child 的row cache object
---當然還要同時進行此SQL進行測試前後對比，方才有價值（即一切在變化對比方有價值）
SQL> select latch#,child#,addr,gets,misses,sleeps,immediate_gets,immediate_misses from v$latch_children where latch#=270 order by 4 desc;

LATCH# CHILD# ADDR GETS MISSES SLEEPS IMMEDIATE_GETS IMMEDIATE_MISSES
---------- ---------- ---------------- ---------- ---------- ---------- -------------- ----------------
270 1 000000007A1F2D88 173735 0 0 0 0
270 32 0000000079F50208 28981 3 0 0 0
270 9 000000007A3F7950 28130 2 0 0 0
270 13 0000000079D37E60 23223 1 0 0 0
270 5 000000007A2F5C68 10710 0 0 0 0
270 8 000000007A2F7798 8683 1 0 0 0
270 4 000000007A1F5AB0 8136 0 0 0 0
270 47 0000000079A4AF90 876 0 0 0 0
270 10 0000000079D34138 482 0 0 0 0
270 46 0000000079A49DD8 457 0 0 0 0
270 12 0000000079D374A8 56 0 0 0 0

LATCH# CHILD# ADDR GETS MISSES SLEEPS IMMEDIATE_GETS IMMEDIATE_MISSES
---------- ---------- ---------------- ---------- ---------- ---------- -------------- ----------------
270 7 000000007A2F71D8 36 0 0 0 0
270 17 0000000079E3C740 7 0 0 0 0 --一直截至到這兒
270 34 0000000079934138 0 0 0 0 0
270 33 0000000079F503D0 0 0 0 0 0
270 31 0000000079F4F050 0 0 0 0 0
270 30 0000000079F4DE98 0 0 0 0 0
270 29 0000000079F4CCE0 0 0 0 0 0
270 28 0000000079F4BB28 0 0 0 0 0
270 27 0000000079F49970 0 0 0 0 0
270 26 0000000079F487B8 0 0 0 0 0
270 25 0000000079F46600 0 0 0 0 0

LATCH# CHILD# ADDR GETS MISSES SLEEPS IMMEDIATE_GETS IMMEDIATE_MISSES
---------- ---------- ---------------- ---------- ---------- ---------- -------------- ----------------
270 24 0000000079F44448 0 0 0 0 0
270 23 0000000079F42290 0 0 0 0 0
270 22 0000000079F400D8 0 0 0 0 0
270 21 0000000079F3EF20 0 0 0 0 0
270 20 0000000079E3ED68 0 0 0 0 0
270 19 0000000079E3DBB0 0 0 0 0 0
270 18 0000000079E3C9F8 0 0 0 0 0
270 16 0000000079E3A588 0 0 0 0 0
270 15 0000000079E383D0 0 0 0 0 0
270 14 0000000079E38018 0 0 0 0 0
270 11 0000000079D362F0 0 0 0 0 0

LATCH# CHILD# ADDR GETS MISSES SLEEPS IMMEDIATE_GETS IMMEDIATE_MISSES
---------- ---------- ---------------- ---------- ---------- ---------- -------------- ----------------
270 6 000000007A2F6020 0 0 0 0 0
270 3 000000007A1F48F8 0 0 0 0 0
270 2 000000007A1F3740 0 0 0 0 0
270 35 0000000079A342F0 0 0 0 0 0
270 36 0000000079A364A8 0 0 0 0 0
270 37 0000000079A38660 0 0 0 0 0
270 38 0000000079A3A818 0 0 0 0 0
270 39 0000000079A3C9D0 0 0 0 0 0
270 40 0000000079A3EB88 0 0 0 0 0
270 41 0000000079A40D40 0 0 0 0 0
270 42 0000000079A42EF8 0 0 0 0 0

LATCH# CHILD# ADDR GETS MISSES SLEEPS IMMEDIATE_GETS IMMEDIATE_MISSES
---------- ---------- ---------------- ---------- ---------- ---------- -------------- ----------------
270 43 0000000079A450B0 0 0 0 0 0
270 44 0000000079A47268 0 0 0 0 0
270 45 0000000079A47C20 0 0 0 0 0
270 48 0000000079A4B158 0 0 0 0 0
270 50 0000000079A4E4C8 0 0 0 0 0
270 49 0000000079A4D310 0 0 0 0 0

50 rows selected.

SQL>

----所以我們基於gets>0的latch進行分析
LATCH# CHILD# ADDR GETS MISSES SLEEPS IMMEDIATE_GETS IMMEDIATE_MISSES
---------- ---------- ---------------- ---------- ---------- ---------- -------------- ----------------
270 1 000000007A1F2D88 173735 0 0 0 0
270 32 0000000079F50208 28981 3 0 0 0
270 9 000000007A3F7950 28130 2 0 0 0
270 13 0000000079D37E60 23223 1 0 0 0
270 5 000000007A2F5C68 10710 0 0 0 0
270 8 000000007A2F7798 8683 1 0 0 0
270 4 000000007A1F5AB0 8136 0 0 0 0
270 47 0000000079A4AF90 876 0 0 0 0
270 10 0000000079D34138 482 0 0 0 0
270 46 0000000079A49DD8 457 0 0 0 0
270 12 0000000079D374A8 56 0 0 0 0

LATCH# CHILD# ADDR GETS MISSES SLEEPS IMMEDIATE_GETS IMMEDIATE_MISSES
---------- ---------- ---------------- ---------- ---------- ---------- -------------- ----------------
270 7 000000007A2F71D8 36 0 0 0 0
270 17 0000000079E3C740 7 0 0 0 0 --一直截至到這兒

依據如下文章分析上述LATCH獲取的次序
基於oradebug poke分析不同redo相關latch獲取的先後次序
http://blog.itpub.net/9240380/viewspace-1869295/

---hang child#=32
SQL> oradebug poke 0x0000000079F50208 4 1
BEFORE: [079F50208, 079F5020C) = 00000000
AFTER: [079F50208, 079F5020C) = 00000001

---釋放child#=1,發現重定義shared_pool_size仍然hang
SQL> oradebug poke 0x000000007A1F2D88 4 0
BEFORE: [07A1F2D88, 07A1F2D8C) = 00000001
AFTER: [07A1F2D88, 07A1F2D8C) = 00000000

---僅child#=1的gets及misses和sleeps發生了變化
SQL> select latch#,child#,addr,gets,misses,sleeps,immediate_gets,immediate_misses from v$latch_children where latch#=270 order by 4 desc;

LATCH# CHILD# ADDR GETS MISSES SLEEPS IMMEDIATE_GETS IMMEDIATE_MISSES
---------- ---------- ---------------- ---------- ---------- ---------- -------------- ----------------
270 1 000000007A1F2D88 178170 2 2 0 0
270 32 0000000079F50208 28981 3 0 0 0
270 9 000000007A3F7950 28133 2 0 0 0
270 13 0000000079D37E60 23223 1 0 0 0
270 5 000000007A2F5C68 10713 0 0 0 0
270 8 000000007A2F7798 8686 1 0 0 0
270 4 000000007A1F5AB0 8136 0 0 0 0
270 47 0000000079A4AF90 882 0 0 0 0
270 10 0000000079D34138 482 0 0 0 0
270 46 0000000079A49DD8 457 0 0 0 0
270 12 0000000079D374A8 56 0 0 0 0

LATCH# CHILD# ADDR GETS MISSES SLEEPS IMMEDIATE_GETS IMMEDIATE_MISSES
---------- ---------- ---------------- ---------- ---------- ---------- -------------- ----------------
270 7 000000007A2F71D8 36 0 0 0 0
270 17 0000000079E3C740 7 0 0 0 0

---釋放child#=32
SQL> oradebug poke 0x0000000079F50208 4 0
BEFORE: [079F50208, 079F5020C) = 00000001
AFTER: [079F50208, 079F5020C) = 00000000

----我們不再研究上述多個LATCH的先後獲取次序，總之，重定義SGA共享池元件時，會先後要獲取多個不同的row cache object latch，可見ORACLE操作的複雜性
---透過釋放，也可以分析重定義共享池大小操作，要獲取具體哪些row cache object latch,可見是1,5,8,32,46,47
SQL> select latch#,child#,addr,gets,misses,sleeps,immediate_gets,immediate_misses from v$latch_children where latch#=270 order by 4 desc;

LATCH# CHILD# ADDR GETS MISSES SLEEPS IMMEDIATE_GETS IMMEDIATE_MISSES
---------- ---------- ---------------- ---------- ---------- ---------- -------------- ----------------
270 1 000000007A1F2D88 181546 2 2 0 0
270 32 0000000079F50208 29014 5 2 0 0
270 9 000000007A3F7950 28136 2 0 0 0 ---經與上述歷史對應gets對應，也要獲取latch#=9
270 13 0000000079D37E60 23223 1 0 0 0
270 5 000000007A2F5C68 10824 0 0 0 0 ---latch#=5同理
270 8 000000007A2F7798 8701 1 0 0 0 ---latch#=8
270 4 000000007A1F5AB0 8136 0 0 0 0
270 47 0000000079A4AF90 885 0 0 0 0 ---latch#=47
270 46 0000000079A49DD8 493 0 0 0 0 --latch#=46
270 10 0000000079D34138 482 0 0 0 0
270 12 0000000079D374A8 56 0 0 0 0

LATCH# CHILD# ADDR GETS MISSES SLEEPS IMMEDIATE_GETS IMMEDIATE_MISSES
---------- ---------- ---------------- ---------- ---------- ---------- -------------- ----------------
270 7 000000007A2F71D8 36 0 0 0 0
270 17 0000000079E3C740 7 0 0 0 0

基於row cache object latch研究對於sga抖動的影響

結論

測試

相關文章