Latch Row Cache Objects基本信息的确认

« 想要离开不容易 - 滞留世博上海 | Blog首页 | Oracle统计信息的收集、管理与清除 »

和Rowcache及其相关Latch的视图主要有：v$latch_children,v$rowcache.

通过Metalink Note 468334.1提供的脚本，可以获得关于Latch更为详细的信息。
以下是来自Windows上的Oracle 10.2.0.4数据库：

SQL> set pages 1000
SQL> column cache# format 99999
SQL> column name format a33
SQL> column latch# format 999999
SQL> select distinct s.kqrstcln latch#,
2 r.cache#,
3 r.parameter name,
4 r.type,
5 r.subordinate#,
6 r.gets
7 from v$rowcache r, x$kqrst s
8 where r.cache# = s.kqrstcid
9 order by 1, 4, 5;

LATCH# CACHE# NAME TYPE SUBORDINATE# GETS
------- ------ --------------------------------- ----------- ------------ ----------
1 1 dc_free_extents PARENT 0
2 4 dc_used_extents PARENT 0
3 2 dc_segments PARENT 1730
4 0 dc_tablespaces PARENT 5226
5 5 dc_tablespace_quotas PARENT 0
6 6 dc_files PARENT 6
7 7 dc_users PARENT 5601
7 7 dc_users SUBORDINATE 0 0
7 7 dc_users SUBORDINATE 1 16
7 7 dc_users SUBORDINATE 2 0
8 3 dc_rollback_segments PARENT 373
9 8 dc_objects PARENT 3770
9 8 dc_object_grants SUBORDINATE 0 10
10 17 dc_global_oids PARENT 271
11 12 dc_constraints PARENT 0
12 11 dc_object_ids PARENT 6428
13 13 dc_sequences PARENT 6
14 10 dc_usernames PARENT 650
15 15 dc_database_links PARENT 8
16 16 dc_histogram_defs PARENT 5921
16 16 dc_histogram_data SUBORDINATE 0 1745
16 16 dc_histogram_data SUBORDINATE 1 932
17 33 kqlsubheap_object PARENT 0
18 19 dc_table_scns PARENT 0
18 19 dc_partition_scns SUBORDINATE 0 0
19 18 dc_outlines PARENT 0
20 14 dc_profiles PARENT 73
21 34 realm cache PARENT 0
21 34 realm auth SUBORDINATE 0 0
22 35 Command rule cache PARENT 0
23 36 Realm Object cache PARENT 0
23 36 Realm Subordinate Cache SUBORDINATE 0 0
24 40 Rule Set Cache PARENT 0
25 37 event map PARENT 0
26 38 format PARENT 0
27 39 audit collector PARENT 0
28 26 global database name PARENT 0
29 20 rule_info PARENT 0
30 21 rule_or_piece PARENT 0
30 21 rule_fast_operators SUBORDINATE 0 0
31 9 dc_qmc_cache_entries PARENT 0
32 23 dc_qmc_ldap_cache_entries PARENT 0
33 27 qmtmrcin_cache_entries PARENT 0
34 28 qmtmrctn_cache_entries PARENT 0
35 29 qmtmrcip_cache_entries PARENT 0
36 30 qmtmrctp_cache_entries PARENT 0
37 31 qmtmrciq_cache_entries PARENT 0
38 32 qmtmrctq_cache_entries PARENT 0
39 24 outstanding_alerts PARENT 61
40 22 dc_awr_control PARENT 89
41 25 dc_hintsets PARENT 0

51 rows selected.

通过v$latch_children可以知道每个row cache被哪一个Latch所守护：

SQL> select addr,latch#,child#,level#,name,gets from v$latch_children
2 where name='row cache objects' and gets<>0 order by gets;

ADDR LATCH# CHILD# LEVEL# NAME GETS
-------- ------- ---------- ---------- --------------------------------- ----------
32508BBC 200 6 4 row cache objects 24
32508F64 200 15 4 row cache objects 25
32508E94 200 13 4 row cache objects 48
32509924 200 39 4 row cache objects 196
3250916C 200 20 4 row cache objects 223
3250998C 200 40 4 row cache objects 291
32508D5C 200 10 4 row cache objects 839
32508C8C 200 8 4 row cache objects 1213
32508EFC 200 14 4 row cache objects 1983
32508A84 200 3 4 row cache objects 6253
32508CF4 200 9 4 row cache objects 11266
32508AEC 200 4 4 row cache objects 15693
32508C24 200 7 4 row cache objects 16729
32508E2C 200 12 4 row cache objects 20162
32508FCC 200 16 4 row cache objects 24755

这里12号，16号子Latch使用的最为频繁，这两项分别是dc_histogram_* 和 dc_object_ids。
Latch : row cache objects 的竞争多数在这两个子Latch上发生。

不过也许你会注意到，也应该注意到，在Oracle Database 11g中，dc_object_ids已经不存在了：

LATCH# CACHE# NAME TYPE SUBORDINATE# GETS
------- ------ --------------------------------- ----------- ------------ ----------
1 3 dc_rollback_segments PARENT 3745261
2 1 dc_free_extents PARENT 0
3 4 dc_used_extents PARENT 0
4 2 dc_segments PARENT 2423433
5 0 dc_tablespaces PARENT 12054178
6 5 dc_tablespace_quotas PARENT 1303
7 6 dc_files PARENT 413573
8 10 dc_users PARENT 41512467
8 7 dc_users SUBORDINATE 0 3395509
8 7 dc_users SUBORDINATE 1 5143586
8 7 dc_users SUBORDINATE 2 0
9 8 dc_objects PARENT 12300504
9 8 dc_object_grants SUBORDINATE 0 33780
10 17 dc_global_oids PARENT 80680
11 12 dc_constraints PARENT 2630
12 13 dc_sequences PARENT 4941
13 15 dc_database_links PARENT 1581494
14 16 dc_histogram_defs PARENT 3227201
14 16 dc_histogram_data SUBORDINATE 0 242971
14 16 dc_histogram_data SUBORDINATE 1 92891
15 33 kqlsubheap_object PARENT 0
16 19 dc_table_scns PARENT 110
16 19 dc_partition_scns SUBORDINATE 0 0
17 18 dc_outlines PARENT 0
18 14 dc_profiles PARENT 1311867
19 47 realm cache PARENT 0
19 47 realm auth SUBORDINATE 0 0
20 48 Command rule cache PARENT 0
21 49 Realm Object cache PARENT 0
21 49 Realm Subordinate Cache SUBORDINATE 0 0
22 34 extensible security user and rol PARENT 0
23 35 extensible security principal pa PARENT 0
24 37 extensible security UID to princ PARENT 0
25 36 extensible security principal na PARENT 4
26 39 extensible security principal ne PARENT 0
27 38 extensible security privilege PARENT 0
27 38 extensible security leaf privile SUBORDINATE 0 0
28 42 extensible security midtier cach PARENT 0
29 44 event map PARENT 0
30 45 format PARENT 0
31 46 audit collector PARENT 0
32 26 global database name PARENT 22309304
33 20 rule_info PARENT 0
34 21 rule_or_piece PARENT 0
34 21 rule_fast_operators SUBORDINATE 0 0
35 23 dc_qmc_ldap_cache_entries PARENT 0
36 27 qmtmrcin_cache_entries PARENT 0
37 28 qmtmrctn_cache_entries PARENT 0
38 29 qmtmrcip_cache_entries PARENT 0
39 30 qmtmrctp_cache_entries PARENT 0
40 31 qmtmrciq_cache_entries PARENT 0
41 32 qmtmrctq_cache_entries PARENT 0
42 9 qmrc_cache_entries PARENT 0
43 24 outstanding_alerts PARENT 899584
44 22 dc_awr_control PARENT 552548
45 25 SMO rowcache PARENT 0
46 40 sch_lj_objs PARENT 353
47 41 sch_lj_oids PARENT 0