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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20200211]檢視v$db_object_cache的CHILD_LATCH欄位.txtObject
- 【ASK_ORACLE】Row Cache Enqueue鎖之概念篇OracleENQ
- [20211026]關於18c row cache mutex.txtMutex
- [20211031]18c row cache mutext等待事件探究.txtMutex事件
- latch:library cache lock等待事件事件
- 當刪除oracle資料庫user時發生row cache lock 等待事件Oracle資料庫事件
- ceph-immmutable-object-cacheObject
- [20210512]shared pool latch與library cache latch的簡單探究.txt
- [20190319]shared pool latch與library cache latch的簡單探究.txt
- 【BUFFER】Oracle buffer cache之 latch 學習記錄Oracle
- MySQL裡的found_row()與row_count()MySql
- A. Arrow a Row
- ORACLE ROW MOVEMENTOracle
- MySQL Binlogging Fails With Writing One Row To The Row-based Binary Log FailedMySqlAI
- How boltdb Write its Data?
- #1118 - Row size too large. The maximum row size for the used table type, not counting BLOBs
- pymysql.err.OperationalError: (1136, “Column count doesn‘t match value count at row 1“)報錯反省。MySqlError
- enq: TX - row lock contentionENQ
- msyql 5.7.21 [ 35%] Building CXX object sql/CMakeFiles/sql.dir/item_row.cc.oUIObjectSQL
- Flutter 之 Row、Column詳解Flutter
- flutter佈局-2-rowFlutter
- 623-Add One Row to Tree
- library cache pin和library cache lock(一)
- library cache pin和library cache lock (zt)
- library cache pin和library cache lock(二)
- Spring CacheSpring
- Guava CacheGuava
- Service Worker Cache 和 HTTP Cache 的區別HTTP
- MySQL：Table_open_cache_hits/Table_open_cache_misses/Table_open_cache_overflowsMySql
- [20210520]11g shared pool latch與library cache mutex的簡單探究.txtMutex
- MySQL8.0 binlog_row_metadataMySql
- Oracle vs PostgreSQL Develop（19） - PIPE ROWOracleSQLdev
- 等待事件enq: TX - row lock contention事件ENQ
- PostgreSQL DBA(102) - pgAdmin(Row Level Security)SQL
- InnoDB從內分析之Row（一）
- Flutter基礎元件Row&ColumnFlutter元件
- Flutter之Row/Column用法詳解Flutter
- Oracle中rownum和row_number()Oracle
- Library Cache最佳化篇（一）降低library cache lock和library cache pin的方法