I blogged about DFS lock handle contention in an earlier blog entry. SV resources in Global Resource Directory (GRD) is used to maintain the cached sequence values. I will further probe the internal mechanics involved in the cached sequences. I will also discuss minor changes in the resource names to support pluggable databases (version 12c).
Let’s create an ordered sequence in rs schema and then query values from the sequence few times.
create sequence rs.test_seq order cache 100; select rs.test_seq.nextval from dual; -- repeated a few times. ... / 21
Sequence values are permanently stored in the seq$ dictionary table. Cached sequence values are maintained in SV resources in GRD and SV resource names follows the naming convention to include object_id of the sequence. I will generate a string using a small helper script and we will use that resource name to search in the GRD.
SELECT DISTINCT '[0x' ||trim(TO_CHAR(object_id, 'xxxxxxxx')) ||'][0x' || trim(TO_CHAR(0,'xxxx')) || '],[SV]' res FROM dba_objects WHERE object_name=upper('&objname') AND owner=upper('&owner') AND object_type LIKE 'SEQUENCE%' / Enter value for objname: TEST_SEQ Enter value for owner: RS RES --------------------------- [0x165d7][0x0],[SV]