基于row cache object latch研究对于sga抖动的影响
结论
1,row cache object latch共计50个child latch2,这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
正文到此结束
热门推荐
相关文章
Loading...
![[HBLOG]公众号](http://www.liuhaihua.cn/img/qrcode_gzh.jpg)
