Locks and Deadlocks - Expert Oracle RAC 12c - page 323

■ parallel servers named pZxx are employed to retrieve data from other instances if you query gv$ views. session
querying gv$ views acts as a query coordinator, parallel servers named pZxx (xx is number between 99 and 01) retrieve data
from other instances, and the query coordinator process receives data and returns it to the user. From version 11g onward,
even if you disable parallelism completely, pZ process can be spawned in other instances. also, pZ parallel server process
starts with 99 and decrements for each additional pZ process. For example, the first query accessing gv$ views will use pZ99
server processes in all nodes, and the second concurrent query accessing gv$ views will use pZ98 parallel server process.

Note

To review global locks, we need to access GES layer-level views. All currently held locks can be reviewed
by querying gv$ges_enqueue. Currently blocked locks can be reviewed by querying v$ges_blocking_enqueue.
Interpretations and methods will be discussed while discussing individual resource types.

Pluggable Databases (12c)
Oracle Database version 12c introduces the pluggable databases feature, which allows many pluggable databases to
be resident in a container database.
Pluggable databases bring an interesting problem for resource names. For example, for the table resources
(TM resource type), object_id is used to coin the resource name, as object_id is unique prior to 12c. But in 12c,
object_id is unique only within a pluggable database. As GES layer is a common service to all pluggable databases,
it is not sufficient to just use object_id alone to coin the resource_name.
So, from version 12c onward, resource_name is coded with container_id of the PDB also. For example, I created two
PDBs (hrdb1 and hrdb2) in a container database and managed to create a table GSTEST with the same object_id:89897.
As you can see in the following, the ext attribute of the resource_name is coded with container_id of the PDB to make the
resource_name unique. In the following, the first resource is for GSTEST table created in hrdb1 PDB with container_id
equal to 3 and second resource is for the GSTEST table created in hrdb2 PDB with container_id equal to 4.

select resource_name from gv$ges_resource where resource_name like '[0x15f29][0x0],[TM]%'
/
RESOURCE_NAME
------------------------------
[0x15f29][0x0],[TM][ext 0x3,0x  hrdb1 PDB - GSTEST table -object_id=89897
[0x15f29][0x0],[TM][ext 0x4,0x  hrdb2 PDB - GSTEST table -object_id=89897

select resource_name1, con_id, state, grant_level
from gv$ges_enqueue where resource_name1 like '[0x15f29][0x0],[TM]%';

RESOURCE_NAME1 CON_ID STATE GRANT_LEV
------------------------------ ---------- -------------------- ---------
[0x15f29][0x0],[TM][ext 0x4,0x 0 GRANTED KJUSEREX
[0x15f29][0x0],[TM][ext 0x3,0x 0 GRANTED KJUSEREX

So, from version 12c onward, you should pay close attention to ext attribute of resource_name to identify PDB
of the object.

Troubleshooting Locking Contention
To understand locking contention, we need to understand holders and waiters of global locks. A handful of global GES
views are useful to understand the locking contention. I will use a small example to illustrate the views and methods
to interpret the data from global views.