Metlink:How to Match a Row Cache Object Child Latch to its Row Cache
Applies to:
Oracle Server - Enterprise Edition -
Version: 9.2.0.1 to 11.2.0.2 - Release: 9.2 to 11.2
Information in this document applies to any platform.
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:
set pages 1000
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.2
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 34 extensible security user and rol PARENT
24 35 extensible security principal pa PARENT
25 37 extensible security UID to princ PARENT
26 36 extensible security principal na PARENT
27 33 extensible security principal ne PARENT
28 38 XS security class privilege PARENT
29 39 extensible security midtier cach PARENT
30 43 AV row cache 1 PARENT
31 44 AV row cache 2 PARENT
32 45 AV row cache 3 PARENT
33 15 global database name PARENT
34 20 rule_info PARENT
35 21 rule_or_piece PARENT
35 21 rule_fast_operators SUBORDINATE 0
36 23 dc_qmc_ldap_cache_entries PARENT
37 52 qmc_app_cache_entries PARENT
38
53 qmc_app_cache_entries
PARENT
39 27 qmtmrcin_cache_entries PARENT
40 28 qmtmrctn_cache_entries PARENT
41 29 qmtmrcip_cache_entries PARENT
42 30 qmtmrctp_cache_entries PARENT
43 31 qmtmrciq_cache_entries PARENT
44 26 qmtmrctq_cache_entries PARENT
45 9 qmrc_cache_entries PARENT
46 50 qmemod_cache_entries PARENT
47
24 outstanding_alerts PARENT
48 22 dc_awr_control PARENT
49 25 SMO rowcache PARENT
50 40 sch_lj_objs PARENT
51 41 sch_lj_oids PARENT
61 rows selected.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/13750068/viewspace-740864/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 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
- Metlink:Troubleshooting:WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK!AIENQ
- 基於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
- 一次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
- [20200211]檢視v$db_object_cache的CHILD_LATCH欄位.txtObject
- buffer cache實驗5-latch:cache buffers chainAI
- hang了,嚴重的row cache lock 等待事件--就因大sql文字事件SQL