1. ROWCACHE Views
Oralce pvovided 3 Views on Row Cache Objects.
V$ROWCACHE (X$KQRST):
a summary view of DC statistics. The suffix "ST" in underlying X$ Table probably means STatistics.
One special Column: FASTGETS is marked as Reserved for internal use,
which could indicate that some on-going work of new development.
V$ROWCACHE is a stats view having 70 fixed rows.
An appropriate name would be V$ROWCACHE_Stats.
V$ROWCACHE_PARENT (X$KQRFS):
an instantaneous view of DC activity. Its 4 real-time Columns:
LOCK_MODE, LOCK_REQUEST, TXN, SADDR
indicate that this view is reflecting each lock owner and each row waiter instantaneously.
It is a run-time view having dynamic number of rows (eg. 61057).
An appropriate name would be V$ROWCACHE_objects.
V$ROWCACHE_SUBORDINATE (X$KQRFP):
list 4 existed Subcache(Subordinate):
select cache#, subcache_name, count(*) cnt from V$ROWCACHE_SUBORDINATE
group by cache#, subcache_name order by subcache_name desc;
CACHE# SUBCACHE_NAME CNT
------ ------------------------- ----
7 dc_user_grants 32
8 dc_object_grants 492
16 dc_histogram_data_values 142
16 dc_histogram_data 26
It has a dynamic number of rows (eg. 2225). It looks like a residue of Row Cache development,
and it is not clear about its practical usage.
2. ROWCACHE Contents
Learned from Case study on Oracle rowcache internals, cached non-existent objects and a describe bug, we can compose queries to list all Dictionary Caches and their contents. Each query looks like how Oracle makes the recursive queries to acces Row Caches.
(Note that Solaris x86 is little-endian)
We can compose queries to list all Dictionary Caches and their contents. Each query looks like how Oracle makes the recursive queries to acces Row Caches.
(Note that Test DB on Solaris x86 is little-endian)
At first, create a conversion function.
----helper function----
create or replace function dump_hex2str (dump_hex varchar2) return varchar2 is
l_str varchar2(100);
begin
with sq_pos as (select level pos from dual connect by level <= 1000)
,sq_chr as (select pos, chr(to_number(substr(dump_hex, (pos-1)*2+1, 2), 'XX')) ch
from sq_pos where pos <= length(dump_hex)/2)
select listagg(ch, '') within group (order by pos) word
into l_str
from sq_chr;
return l_str;
end;
/
2.1. dc_props
select dump_hex2str(rtrim(key, '0')) dc_prop_name, v.*
from v$rowcache_parent v
where cache_name in ('dc_props')
order by key;
DC_PROP_NAME INDX HASH ADDRESS CACHE# CACHE_NAME EXISTENT
----------------------------- ----- ----- ----------------- ------- ----------- --------
BACK_END_DB 37420 48 000000017AC05ED8 15 dc_props N
DBTIMEZONE 37377 7 000000017C1D2538 15 dc_props Y
DEFAULT_EDITION 37416 46 000000017C3D9668 15 dc_props Y
DEFAULT_PERMANENT_TABLESPACE 37378 7 00000001783FDF00 15 dc_props Y
DEFAULT_TBS_TYPE 37384 12 000000017C1DAB98 15 dc_props Y
DEFAULT_TEMP_TABLESPACE 37371 0 000000017C1B0A78 15 dc_props Y
...
NLS_CALENDAR 37374 3 000000017C18CF18 15 dc_props Y
NLS_CHARACTERSET 37402 29 000000017C18F038 15 dc_props Y
...
60 rows selected.
2.2. dc_global_oids
with ro as (select /*+ materialize */ substr(key, 9, 32) key_oid$, ro.*
from v$rowcache_parent ro where cache_name in ('dc_global_oids')
and rownum <= 100 -- select 100 for test
)
,so as (select /*+ materialize */ obj#, ro.* from sys.oid$, ro where oid$ = ro.key_oid$)
select owner||'.'||object_name object, so.*
from dba_objects do, so
where object_id = so.obj#;
OBJECT OBJ# KEY_OID$ INDX HASH ADDRESS CACHE# CACHE_NAME EXISTENT
-------------- -------- --------------------------------- ----- ----- ----------------- ------- --------------- --------
SYS.PRVT_ILM 1581930 2CE6B7D8955B3AEBE054D48564CDEC20 1799 508 00000001714A1DD0 17 dc_global_oids Y
SYS.PRVT_ILM 1581930 2CE6B7D895B03AEBE054D48564CDEC20 1788 158 00000001714A3BF0 17 dc_global_oids Y
K.T_OBJ_RET 2360168 795ECCD5A45D48DDE054005056984D97 1793 410 00000001714DE8C0 17 dc_global_oids Y
K.T_OBJ_IN 2360169 7807E9A682B5082FE054005056984D97 1786 126 00000001714F2078 17 dc_global_oids Y
K.T_OBJ_OUT 2360170 7807E9A682BA082FE054005056984D97 1794 464 00000001714FB2B0 17 dc_global_oids Y
K.T_OBJ_INOUT 2360171 77DDFDFCEB484AF6E054005056984D97 1785 105 00000001714FB4E0 17 dc_global_oids Y
2.3. dc_objects
select to_number(ltrim((substr(key, 7, 2)||substr(key, 5, 2)||substr(key, 3, 2)||substr(key, 1, 2)), '0'), 'XXXX') Schema_User_ID,
(select username from dba_users where user_id =
to_number(ltrim((substr(key, 7, 2)||substr(key, 5, 2)||substr(key, 3, 2)||substr(key, 1, 2)), '0'), 'XXXX')) username,
dump_hex2str(rtrim(substr(key, 13), '0')) dc_object_name,
to_number(trim(both '0' from substr(key, 11, 2)||substr(key, 9, 2)), 'XXXX') key_str_len,
v.*
from v$rowcache_parent v
where cache_name in ('dc_objects')
and rownum<= 100 -- select 100 for test
order by key;
SCHEMA_USER_ID USERNAME DC_OBJECT_NAME KEY_STR_LEN INDX HASH ADDRESS CACHE# CACHE_NAME EXISTENT
--------------- ------------------ ---------------------------- ------------ ----- ------ ----------------- ------- ----------- --------
FILE$ 5 546 11880 000000017BF87310 8 dc_objects Y
UNDO$ 5 505 4027 000000017BFAA5E0 8 dc_objects Y
VIEW$ 5 534 10244 0000000164B8D4B8 8 dc_objects Y
PROPS$ 6 585 17055 000000017B72E3C8 8 dc_objects Y
1 DUAL 4 607 19722 0000000177B824B8 8 dc_objects Y
1 V$ROWCACHE_PARENT 17 610 20351 00000001662CEAB0 8 dc_objects Y
1 SYS_TEMP_0FD9D664D_E88F5145 27 512 5516 0000000164D16BF0 8 dc_objects N
49 K FOO 3 532 10104 0000000177BD3AB8 8 dc_objects Y
49 K T_OBJ_RET 9 676 30902 00000001662AD9B8 8 dc_objects Y
49 K DBA_OBJECTS 11 519 6726 000000016630D270 8 dc_objects N
49 K DBMS_OUTPUT 11 594 18151 00000001662796D0 8 dc_objects N
49 K DUMP_HEX2STR 12 517 6336 0000000179577980 8 dc_objects Y
49 K V$ROWCACHE_PARENT 17 639 24261 00000001662D0EA0 8 dc_objects N
1779 GSMADMIN_INTERNAL CLOUD 5 646 26011 0000000164E42DF0 8 dc_objects Y
2.4. dc_tablespaces
select to_number(substr(key, 5, 2)||substr(key, 3, 2)||substr(key, 1, 2), 'XXXXXX') ts#,
(select name from v$tablespace
where ts# = to_number(substr(key, 5, 2)||substr(key, 3, 2)||substr(key, 1, 2), 'XXXXXX')) ts_name,
v.*
from v$rowcache_parent v
where cache_name in ('dc_tablespaces')
and rownum <= 100 -- select 100 for test
order by 1;
TS# TS_NAME INDX HASH ADDRESS CACHE# CACHE_NAME EXISTENT
----- --------- ----- ----- ----------------- ------- --------------- --------
0 SYSTEM 56 4 0000000164DB14D0 0 dc_tablespaces Y
3 TEMP 23 2 0000000170F58628 0 dc_tablespaces Y
1999 USER1 37 2 0000000182F74750 0 dc_tablespaces Y
2000 USER2 68 5 0000000166167F68 0 dc_tablespaces Y
2.5. dc_users
The output shows that SYS is in CACHE#=7, whereas SYSTEM in CACHE#=10.
select to_number(substr(key, 3, 2)||substr(key, 1, 2), 'XXXX') key_len,
dump_hex2str(rtrim(substr(key, 5, 2*to_number(substr(key, 3, 2)||substr(key, 1, 2), 'XXXX')), '0')) user_or_role_name,
v.*
from v$rowcache_parent v
where cache_name in ('dc_users')
and rownum <= 100 -- select 100 for test
order by key;
KEY_LEN USER_OR_ROLE_NAME INDX HASH ADDRESS CACHE# CACHE_NAME EXISTENT
-------- ------------------ ----- ------ ----------------- ------- ----------- --------
3 SYS 1406 10788 0000000165B77CA0 7 dc_users Y
3 SYS 2342 51836 0000000165B77CA0 7 dc_users Y
5 OUTLN 1419 41803 0000000165E54C78 10 dc_users Y
5 OUTLN 2331 21366 0000000165E54C78 10 dc_users Y
6 DBSNM 1408 14676 00000001672EDCD0 10 dc_users Y
6 DBSNM 2338 45053 00000001672EDCD0 10 dc_users Y
6 SYSTEM 1422 50615 000000017C2F6468 10 dc_users Y
6 SYSTEM 2330 17882 000000017C2F6468 10 dc_users Y
2.6. dc_sequences
create sequence test_seq start with 1 cache 1000;
with sq as (
select to_number(substr(key, 5, 2)||substr(key, 3, 2)||substr(key, 1, 2), 'XXXXXX') seq_object_id
,v.*
from v$rowcache_parent v
where cache_name in ('dc_sequences'))
select o.object_name, seq_object_id, cache#, cache_name
--,sq.*
from dba_objects o, sq
where o.object_id = sq.seq_object_id
and o.object_name = 'TEST_SEQ';
OBJECT_NAM SEQ_OBJECT_ID CACHE# CACHE_NAME
---------- ------------- ------ ------------
TEST_SEQ 4153811 13 dc_sequences
2.7 dc_rollback_segments
with sq as (
select to_number(ltrim((substr(key, 7, 2)||substr(key, 5, 2)||substr(key, 3, 2)||substr(key, 1, 2)), '0'), 'XXXX')
rollback_segment_id
,indx, hash, address, cache#, cache_name -- v.*
from v$rowcache_parent v
where cache_name in ('dc_rollback_segments'))
select s.sid, s.serial#, t.xidusn, t.xidslot, t.xidsqn, t.ubafil, t.ubablk
,(select segment_name from dba_rollback_segs where segment_id = sq.rollback_segment_id) segment_name
,sq.*
from sq, v$session s, v$transaction t
where sq.rollback_segment_id = t.xidusn and s.taddr=t.addr;
SID SERIAL# XIDUSN XIDSLOT XIDSQN UBAFIL UBABLK SEGMENT_NAME ROLLBACK_SEGMENT_ID INDX HASH ADDRESS CACHE# CACHE_NAME
--- ------- ------ ------- ------ ------ ------ ---------------------- ------------------- ---- ---- ---------------- ------ --------------------
558 53837 264 23 152 3 15926 _SYSSMU264_2251784465$ 264 2731 3975 00000000A22DBD00 3 dc_rollback_segments
722 27322 280 16 142 3 12193 _SYSSMU280_2691079894$ 280 2819 7188 00000000A22D8A00 3 dc_rollback_segments
-- Only those rollback_segments have 'ACTIVE' undo_extents
select * from dba_undo_extents where status = 'ACTIVE';
OWN SEGMENT_NAME TABLESPACE_NAME EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO COMMIT_JTIME COMMIT_WTIME STATUS
--- ---------------------- --------------- --------- ------- -------- ------- ------ ------------ ------------ ------------ ------
SYS _SYSSMU264_2251784465$ UNDO 2 3 15872 1048576 128 3 ACTIVE
SYS _SYSSMU280_2691079894$ UNDO 2 3 12160 1048576 128 3 ACTIVE
-- Lock held to perform DDL on the undo segment
select * from v$enqueue_statistics where eq_type = 'US';
-- 'US' Lock
select * from v$lock where type = 'US';
3. ROWCACHE Hash Bucket (Update 2020-Aug-03)
With following query, we can see that 60 rowcache objects in dc_props are distributed into 37 hash buckets:
select (hash+1) Hash_Bucket, count(*) Hash_Bucket_Size from v$rowcache_parent where cache_name = 'dc_props'
group by hash order by 1;
HASH_BUCKET HASH_BUCKET_SIZE
----------- ----------------
1 3
4 1
6 2
8 2
9 1
11 2
... ...
62 3
63 1
64 1
37 rows selected.
select Hash_Bucket_Size, count(*) Number_of_Buckets, (Hash_Bucket_Size * count(*)) Number_of_RowCache_Objects from
(select (hash+1) Hash_Bucket, count(*) Hash_Bucket_Size from v$rowcache_parent where cache_name = 'dc_props' group by hash)
group by Hash_Bucket_Size order by 2 desc;
HASH_BUCKET_SIZE NUMBER_OF_BUCKETS NUMBER_OF_ROWCACHE_OBJECTS
---------------- ----------------- --------------------------
1 20 20
2 11 22
3 6 18
