Oracle database internals by Riyaj

Discussions about Oracle performance tuning, RAC, Oracle internal & E-business suite.

RAC Internals: cached sequences and 12c

Posted by Riyaj Shamsudeen on September 9, 2013


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).

SV resources

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.

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.

    ||trim(TO_CHAR(object_id, 'xxxxxxxx'))
    || 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

With the resource name coined above, we can search GRD to identify the global enqueue resource, by searching gv$ges_resource.

col master_node head 'Mast|node' format 99
vol value_blk format a30
SELECT inst_id, resource_name, master_node, value_blk
     FROM gv$ges_resource WHERE resource_name LIKE '&resource_name%'
new   2:      FROM gv$ges_resource WHERE resource_name LIKE '[0x165d7][0x0],[SV]%'
---------- ------------------------------ ---- ----------------------------------------------------------------
	 1 [0x165d7][0x0],[SV][ext 0xc650    0 0x82c11700000000000000000000000000 .

Notice the value_blk column. Cached values of the sequences are maintained in the value_blk column of the underlying x$ table (x$kjirft). Value c117 is the hexadecimal representation of Oracle number data type for the value 22. Dumping sequence value in hex format (For the dump function, 22 is the value, 16 is the format identifier – hexadecimal) :

SQL> select dump(22, 16) from dual;

Typ=2 Len=2: c1,17

Pluggable Databases

So far so good, SV resource name is coined using the object_id of the sequence object. But, in a pluggable database two objects belonging to two different plugged-in database can have the same object_id. GRD – resources and enqueues – is stored in the container database (in fact, SGA belongs to the container database, not the pluggable database). If two sequences have the same object_id i.e. by cloning a pluggable database, then how do the resource name is maintained uniquely??

Enter extended resource naming convention. In earlier releases (I think, 11g), I have noticed an extension to the resource_name of the format [ext 0x0]. I researched that, but never understood it, until 12c. That resource name extension is used in the pluggable databases implementation. Notice that in 12c, resource_name – for example [0x165d7][0x0],[SV][ext 0xc650 – has extension filled with some hexadecimal value(0xc650).

Of course, I cloned pdb1 pluggable database as pdb2 and selected from the sequence after connecting to both pluggable databases, and then inspected the GRD resource names. I selected from the sequences to return exactly same value when I was inspecting the GRD.

Verifying the resource name first, we can see that coined resource name is matching in both plugged-in databases.

alter session set container=pdb1;
Session altered.

Enter value for objname: TEST_SEQ
Enter value for owner: RS

alter session set container=pdb2 ;
Session altered.

Enter value for objname: TEST_SEQ
Enter value for owner: RS

Querying gv$ges_resource, we see two unique resource names with different extensions. We can guess that extension is indicating the pluggable database.

col master_node head 'Mast|node' format 99
vol value_blk format a30
SELECT inst_id,resp, resource_name, master_node, value_blk
FROM gv$ges_resource WHERE resource_name LIKE '[0x165d7][0x0],[SV]%'
---------- ---------------- ------------------------------ ---- ----------------------------------------------------------------
	 1 000000007E38F180 [0x165d7][0x0],[SV][ext 0xc650    0 0x00000000000000000000000000000000 .
	 2 000000006938A358 [0x165d7][0x0],[SV][ext 0xe7b5    1 0x82c12b00000000000000000000000000 .+

That extension is nothing but the dbid of the pluggable database, no surprise there. I added the resp column (resource pointer) also in the SELECT statement above to dump the resource using oradebug command. Contents of the trace files show the full extension:

oradebug lkdebug -r 0x000000007E38F180
resname       : [0x165d7][0x0],[SV][ext 0xc6509949,0x0]
lmdid         : 0

So, the resource name has DBID of the pluggable database from 12c onwards, in this case, 0xc6509949. DBID of the PDBs can be queried from cub_pdbs and matches with the resource name extension.

col pdb_name format a30
select to_char(dbid, 'xxxxxxxxx'), pdb_name from cdb_pdbs;
---------- ------------------------------
  c6509949 PDB1
  f21ec531 PDB$SEED
  e7b59166 PDB2

In Summary, SV resources are used to cache the sequence values; SV resource names are extended by adding the dbid of the plugged-in database to the resource name in 12c.

I know, I know, I should create a weird stuff category for this blog entry 🙂

2 Responses to “RAC Internals: cached sequences and 12c”

  1. How did you know (or) decoded this format “[0x165d7][0x0],[SV]”.
    Prior to building up of query you may have done research,how did you found that it should an object sequence that is maintained by SV resource.

    • v$lock_type would tell you that SV is the enqueue for Sequence Ordering. After that, it is just search in gv$ges_resource and looking for objects matching with a sequence that I am playing with. And then, matching up numbers with value column, dumping the enqueue, to understand how the values are stored.

      Really, no magic. Just plain hard work, curiosity (and a few failed attempts before coming up with that SQL statement) 🙂


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: