How to Match a Row Cache Object Child Latch to its Row Cache
How to Match a Row Cache Object Child Latch to its Row Cache (Doc ID 468334.1)
Applies to:
Oracle Database - Enterprise Edition - Version 9.2.0.1 and laterInformation in this document applies to any platform.
Goal
- Starting with Oracle 9.2, the 'row cache objects' latch has multiple children.
- There are a number of row caches e.g. dc_objects, dc_users, dc_histograms etc.
- Each child latch covers a single row cache.
- In 9.2.0.8, each row cache is covered by 8 children latches.
In Database Performance investigations where 'latch free' waits
on this type of latch consume significant database time, it is useful to
be able to map each 'row cache objects' child latch to the row cache it
covers. In this way, we can map the number of SLEEPS for a child latch
(CHILD#) in V$LATCH_CHILDREN to a particular type of row cache.
Solution
The query shown below will accomplish the above requirement:
column cache# format 99999
column name format a33
column latch# format 999999
select distinct s.kqrstcln latch#,r.cache#,r.parameter name,r.type,r.subordinate#
from v$rowcache r,x$kqrst s
where r.cache#=s.kqrstcid
order by 1,4,5;
Here is example output from various releases.
9.2.0.1:
LATCH# CACHE# NAME TYPE SUBORDINATE#
------- ------ ------------------------- ----------- ------------
1 1 dc_free_extents PARENT
2 4 dc_used_extents PARENT
3 2 dc_segments PARENT
4 0 dc_tablespaces PARENT
5 5 dc_tablespace_quotas PARENT
6 6 dc_files PARENT
7 7 dc_users PARENT
7 7 dc_users SUBORDINATE 0
7 7 dc_user_grants SUBORDINATE 1
7 7 dc_app_role SUBORDINATE 2
8 3 dc_rollback_segments PARENT
9 8 dc_objects PARENT
10 17 dc_global_oids PARENT
11 12 dc_constraints PARENT
12 11 dc_object_ids PARENT
13 13 dc_sequences PARENT
14 10 dc_usernames PARENT
15 15 dc_database_links PARENT
16 16 dc_histogram_defs PARENT
16 16 dc_histogram_data SUBORDINATE 0
16 16 dc_histogram_data_values SUBORDINATE 1
17 21 dc_table_scns PARENT
17 21 dc_partition_scns SUBORDINATE 0
18 18 dc_outlines PARENT
19 14 dc_profiles PARENT
20 19 dc_encrypted_objects PARENT
21 20 dc_encryption_profiles PARENT
22 9 dc_qmc_cache_entries PARENT
28 rows selected.
9.2.0.8: in this release each row cache is covered by 8 child latches
LATCH# CACHE# NAME TYPE SUBORDINATE#
------- ------ ------------------------- ----------- ------------
1 1 dc_free_extents PARENT
2 1 dc_free_extents PARENT
3 1 dc_free_extents PARENT
4 1 dc_free_extents PARENT
5 1 dc_free_extents PARENT
6 1 dc_free_extents PARENT
7 1 dc_free_extents PARENT
8 1 dc_free_extents PARENT
9 4 dc_used_extents PARENT
10 4 dc_used_extents PARENT
11 4 dc_used_extents PARENT
12 4 dc_used_extents PARENT
13 4 dc_used_extents PARENT
14 4 dc_used_extents PARENT
15 4 dc_used_extents PARENT
16 4 dc_used_extents PARENT
17 2 dc_segments PARENT
18 2 dc_segments PARENT
19 2 dc_segments PARENT
20 2 dc_segments PARENT
21 2 dc_segments PARENT
22 2 dc_segments PARENT
23 2 dc_segments PARENT
24 2 dc_segments PARENT
25 0 dc_tablespaces PARENT
26 0 dc_tablespaces PARENT
...
174 9 dc_qmc_cache_entries PARENT
175 9 dc_qmc_cache_entries PARENT
176 9 dc_qmc_cache_entries PARENT
177 22 dc_qmc_ldap_cache_entries PARENT
178 22 dc_qmc_ldap_cache_entries PARENT
179 22 dc_qmc_ldap_cache_entries PARENT
180 22 dc_qmc_ldap_cache_entries PARENT
181 22 dc_qmc_ldap_cache_entries PARENT
182 22 dc_qmc_ldap_cache_entries PARENT
183 22 dc_qmc_ldap_cache_entries PARENT
184 22 dc_qmc_ldap_cache_entries PARENT
232 rows selected.
10.1.0.2 - 10.1.0.5:
LATCH# CACHE# NAME TYPE SUBORDINATE#
------- ------ ------------------------- ----------- ------------
1 1 dc_free_extents PARENT
2 4 dc_used_extents PARENT
3 2 dc_segments PARENT
4 0 dc_tablespaces PARENT
5 5 dc_tablespace_quotas PARENT
6 6 dc_files PARENT
7 7 dc_users PARENT
7 7 dc_users SUBORDINATE 0
...
24 23 rule_or_piece PARENT
24 23 rule_fast_operators SUBORDINATE 0
25 9 dc_qmc_cache_entries PARENT
26 25 dc_qmc_ldap_cache_entries PARENT
27 26 outstanding_alerts PARENT
28 24 dc_awr_control PARENT
29 27 dc_hintsets PARENT
36 rows selected.
10.2.0.1-10.2.0.2:
LATCH# CACHE# NAME TYPE SUBORDINATE#
------- ------ ------------------------- ----------- ------------
1 1 dc_free_extents PARENT
2 4 dc_used_extents PARENT
3 2 dc_segments PARENT
4 0 dc_tablespaces PARENT
5 5 dc_tablespace_quotas PARENT
6 6 dc_files PARENT
7 7 dc_users PARENT
7 7 dc_users SUBORDINATE 0
7 7 dc_users SUBORDINATE 1
...
29 30 qmtmrctp_cache_entries PARENT
30 31 qmtmrciq_cache_entries PARENT
31 32 qmtmrctq_cache_entries PARENT
32 24 outstanding_alerts PARENT
33 22 dc_awr_control PARENT
34 25 dc_hintsets PARENT
42 rows selected.
10.2.0.3: there are 8 new row caches:
LATCH# CACHE# NAME TYPE SUBORDINATE#
------- ------ ------------------------- ----------- ------------
1 1 dc_free_extents PARENT
2 4 dc_used_extents PARENT
3 2 dc_segments PARENT
...
21 34 realm cache PARENT
21 34 realm auth SUBORDINATE 0
22 35 Command rule cache PARENT
23 36 Realm Object cache PARENT
23 36 Realm Subordinate Cache SUBORDINATE 0
24 37 event map PARENT
25 38 format PARENT
26 39 audit collector PARENT
27 26 global database name PARENT
28 20 rule_info PARENT
29 21 rule_or_piece PARENT
29 21 rule_fast_operators SUBORDINATE 0
30 9 dc_qmc_cache_entries PARENT
...
39 22 dc_awr_control PARENT
40 25 dc_hintsets PARENT
50 rows selected.
11.1.0.6: a few more changes:
LATCH# CACHE# NAME TYPE SUBORDINATE#
------- ------ --------------------------------- ----------- ------------
1 3 dc_rollback_segments PARENT
2 1 dc_free_extents PARENT
...
21 49 Realm Subordinate Cache SUBORDINATE 0
22 34 extensible security user and rol PARENT
23 35 extensible security principal pa PARENT
24 37 extensible security UID to princ PARENT
25 36 extensible security principal na PARENT
26 39 extensible security principal ne PARENT
27 38 extensible security privilege PARENT
27 38 extensible security leaf privile SUBORDINATE 0
28 42 extensible security midtier cach PARENT
29 44 event map PARENT
...
42 9 qmrc_cache_entries PARENT
43 24 outstanding_alerts PARENT
44 22 dc_awr_control PARENT
45 25 SMO rowcache PARENT
46 40 sch_lj_objs PARENT
47 41 sch_lj_oids PARENT
58 rows selected.
Full list for 11.2.0.3
Note: There are slight differences in previous versions of 11.2. If you need a list from a previous patchset please run the query given above to obtain the list
LATCH# CACHE# NAME TYPE SUBORDINATE#
------- ------ --------------------------------- ----------- ------------
1 3 dc_rollback_segments PARENT
2 1 dc_free_extents PARENT
3 4 dc_used_extents PARENT
4 2 dc_segments PARENT
5 0 dc_tablespaces PARENT
6 5 dc_tablespace_quotas PARENT
7 6 dc_files PARENT
8 10 dc_users PARENT
8 7 dc_users SUBORDINATE 0
8 7 dc_users SUBORDINATE 1
8 7 dc_users SUBORDINATE 2
9 8 dc_objects PARENT
9 8 dc_object_grants SUBORDINATE 0
10 17 dc_global_oids PARENT
11 12 dc_constraints PARENT
12 13 dc_sequences PARENT
13 16 dc_histogram_defs PARENT
13 16 dc_histogram_data SUBORDINATE 0
13 16 dc_histogram_data SUBORDINATE 1
14 54 dc_sql_prs_errors PARENT
15 32 kqlsubheap_object PARENT
16 19 dc_table_scns PARENT
16 19 dc_partition_scns SUBORDINATE 0
17 18 dc_outlines PARENT
18 14 dc_profiles PARENT
19 47 realm cache PARENT
19 47 realm auth SUBORDINATE 0
20 48 Command rule cache PARENT
21 49 Realm Object cache PARENT
21 49 Realm Subordinate Cache SUBORDINATE 0
22 46 Rule Set Cache PARENT
23 55 DV Auth Cache PARENT
24 34 extensible security user and rol PARENT
25 35 extensible security principal pa PARENT
26 37 extensible security UID to princ PARENT
27 36 extensible security principal na PARENT
28 33 extensible security principal ne PARENT
29 38 XS security class privilege PARENT
30 39 extensible security midtier cach PARENT
31 43 AV row cache 1 PARENT
32 44 AV row cache 2 PARENT
33 45 AV row cache 3 PARENT
34 15 global database name PARENT
35 20 rule_info PARENT
36 21 rule_or_piece PARENT
36 21 rule_fast_operators SUBORDINATE 0
37 23 dc_qmc_ldap_cache_entries PARENT
38 52 qmc_app_cache_entries PARENT
39 53 qmc_app_cache_entries PARENT
40 27 qmtmrcin_cache_entries PARENT
41 28 qmtmrctn_cache_entries PARENT
42 29 qmtmrcip_cache_entries PARENT
43 30 qmtmrctp_cache_entries PARENT
44 31 qmtmrciq_cache_entries PARENT
45 26 qmtmrctq_cache_entries PARENT
46 9 qmrc_cache_entries PARENT
47 50 qmemod_cache_entries PARENT
48 24 outstanding_alerts PARENT
49 22 dc_awr_control PARENT
50 25 SMO rowcache PARENT
51 40 sch_lj_objs PARENT
52 41 sch_lj_oids PARENT
62 rows selected.
Full list for 12.1.0.1.0
Note: There are slight differences in 12.1.0.1.0.
LATCH# CACHE# NAME TYPE SUBORDINATE#
------- ------ --------------------------------- ----------- ------------
1 3 dc_rollback_segments PARENT
2 1 dc_free_extents PARENT
3 4 dc_used_extents PARENT
4 2 dc_segments PARENT
5 0 dc_tablespaces PARENT
6 5 dc_tablespace_quotas PARENT
7 6 dc_files PARENT
8 10 dc_users PARENT
8 7 dc_users SUBORDINATE 0
8 7 dc_users SUBORDINATE 1
8 7 dc_users SUBORDINATE 2
8 7 dc_users SUBORDINATE 3
8 7 dc_users SUBORDINATE 4
8 7 dc_users SUBORDINATE 5
9 8 dc_objects PARENT
9 8 dc_object_grants SUBORDINATE 0
10 17 dc_global_oids PARENT
11 12 dc_constraints PARENT
12 13 dc_sequences PARENT
13 16 dc_histogram_defs PARENT
13 16 dc_histogram_data SUBORDINATE 0
13 16 dc_histogram_data SUBORDINATE 1
14 54 dc_sql_prs_errors PARENT
15 32 kqlsubheap_object PARENT
16 19 dc_table_scns PARENT
16 19 dc_partition_scns SUBORDINATE 0
17 18 dc_outlines PARENT
18 15 dc_props PARENT
19 14 dc_profiles PARENT
20 47 realm cache PARENT
20 47 realm auth SUBORDINATE 0
20 47 realm auth SUBORDINATE 1
21 48 Command rule cache PARENT
22 49 Realm Object cache PARENT
22 49 Realm Subordinate Cache SUBORDINATE 0
23 46 Rule Set Cache PARENT
24 55 DV Auth Cache PARENT
25 34 extensible security principal in PARENT
26 33 Unused PARENT
27 36 Unused PARENT
28 37 Unused PARENT
29 38 XS security class privilege PARENT
30 39 extensible security midtier cach PARENT
31 43 AV row cache 1 PARENT
32 44 AV row cache 2 PARENT
33 45 AV row cache 3 PARENT
34 35 triton security name to ID PARENT
35 20 rule_info PARENT
36 21 rule_or_piece PARENT
36 21 rule_fast_operators SUBORDINATE 0
37 23 dc_qmc_ldap_cache_entries PARENT
38 52 qmc_app_cache_entries PARENT
39 53 qmc_app_cache_entries PARENT
40 27 qmtmrcin_cache_entries PARENT
41 28 qmtmrctn_cache_entries PARENT
42 29 qmtmrcip_cache_entries PARENT
43 30 qmtmrctp_cache_entries PARENT
44 31 qmtmrciq_cache_entries PARENT
45 26 qmtmrctq_cache_entries PARENT
46 9 qmrc_cache_entries PARENT
47 50 qmemod_cache_entries PARENT
48 24 outstanding_alerts PARENT|
49 22 dc_awr_control PARENT
50 25 SMO rowcache PARENT
51 40 sch_lj_objs PARENT
52 41 sch_lj_oids PARENT
53 56 dc_cdbfiles PARENT
54 57 dc_cdbservices PARENT
55 58 dc_pdbdba PARENT
69 rows selected.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/31393455/viewspace-2132412/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Metlink:How to Match a Row Cache Object Child Latch to its Row CacheObject
- row cache objects latch研究Object
- Latch: Row Cache Objects (One bug?)Object
- 基於row cache object latch研究對於sga抖動的影響Object
- ORACLE Active dataguard 一個latch: row cache objects BUGOracleObject
- 等待事件之Row Cache Lock事件
- WAITEVENT: "latch: row cache objects" Reference Note (Doc ID 1550722.1)AIObject
- latch: row cache objects 和cursor: pin S wait on X共同出現ObjectAI
- hanganalyze解決row cache lock(ZT)
- WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK!AIENQ
- oracle 'row cache objects' 等待事件解釋OracleObject事件
- [ORACLE 11G]ROW CACHE LOCK 等待Oracle
- latch 相關效能問題診斷： latch: row cache objects等待事件導致CPU負載高Object事件負載
- ORA-600(504)(row cache objects)錯誤Object
- 轉）用hanganalyze解決row cache lock
- （轉）用hanganalyze解決row cache lock
- 【ASK_ORACLE】Row Cache Enqueue鎖之概念篇OracleENQ
- 故障排除："WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK! "AIENQ
- WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK!的分析AIENQ
- 轉貼_用hanganalyze解決row cache lock
- 用hanganalyze解決row cache lock（轉貼）
- WAITEVENT: "row cache lock" Reference Note (文件 ID 34609.1)AI
- 一次WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCKAIENQ
- 由row cache lock等待事件引起的效能問題事件
- Resolving Issues Where 'Row Cache Lock' Waits are OccurringAI
- Metlink:Troubleshooting:WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK!AIENQ
- 一次Row Cache Lock問題處理過程
- Rac 環境中分割槽表建立index hang(row cache lock)Index
- WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK! 與 dc_tablespcesAIENQ
- 通過system state dump分析WAITED TOO LONG FOR A ROW CACHE ENQUEUEAIENQ
- WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK-[ID 278316.1]AIENQ
- cache buffers LRU chain latchAI
- Latch: cache buffer chains (%)AI
- latch: cache buffers chainsAI
- latch free(cache buffers chain)AI
- buffer cache實驗5-latch:cache buffers chainAI
- [20200211]檢視v$db_object_cache的CHILD_LATCH欄位.txtObject
- hang了,嚴重的row cache lock 等待事件--就因大sql文字事件SQL