Cache Fusion Processing

In a single instance, a database block can reside only in the local buffer cache. Therefore, if the block is not in the local buffer cache, it is read from the disk. In RAC, the block can be resident in any buffer cache. So, a special type of processing takes place that deviates sharply from single-instance processing.

The following section provides a rough outline of the cache fusion-processing scheme. This discussion should be considered as an outline of cache fusion processing, not necessarily a documentation of the complete algorithm.

Figure 10-1 shows the mechanics of a cache fusion block transfer.

Each of the numbered steps is explained below. Items with dotted lines in the picture indicate the state after the block transfer.

• 1.  A foreground (FG) process (also known as a server process) tries to access a database block and the data block is not in the local buffer cache. A hashing algorithm¹ is applied on the resource_name protecting the block to calculate the resource master instance. In Figure 10-1, the resource master instance for this resource is 2.

• a.  The FG process retrieves the network address of a remote LMS background process running on the resource master instance.
• b.  The FG process constructs a message for the block access and sends that message to the remote LMS process.
• c.  The FG process must wait until a response is received from the remote LMS process. FG process cannot predict the LMS response since the block could be in a remote buffer cache or the block may not be resident in any buffer cache. So, the FG process waits on a placeholder wait event.

• 2.  LMS process in the resource master instance receives the message, identifies the block, and accesses GRD to identify the current state of the resource.

• a.  For CURRENT mode request, if the block is not in any buffer cache, the LMS process allocates a new resource in GRD; then allocates a lock on the resource;² and then sends a message back to the FG process with a grant to read the block from the disk.
• b.  For CR mode requests, GRD representation of resource is not required. The block may be simply reconstructed without requiring additional lock conversion.

• 3.  In Figure 10-1, the block is resident in the buffer cache of instance 1. So, an LMS process running on the resource master instance sends a request to the LMS process running on the owning instance (instance 1).
• 4.  LMS process running in owning instance constructs a block and sends the block to the FG process running in instance 3.³
• 5.  The FG process receives the block, copies the block to the instance 3 buffer cache, and continues processing further.

After the transfer of a block, LMS process running on the resource master instance also modifies the resource and adds a lock indicating that the block is now resident in both instances 1 and 3. Obviously, the LMS background process plays a critical role in the cache fusion process and it is the workhorse process behind cache fusion processing.

Even though an individual session requests locks, cache fusion locks are owned by instances, not individual sessions. Let’s say that another process in instance 3 accesses the same block: there is no additional cache fusion processing needed, as the lock is already held in a compatible mode. As long as the lock held is in compatible mode, any process can access the block locally without additional cache fusion processing. Of course, if the block is not in a compatible mode, then the block lock mode must be upgraded.

GRD

A combination of file_id and block_id uniquely identifies a block; BL resource names are coined using the combination of file_id and block_id of the block. A lock on that BL resource is acquired before altering the block or reading from the disk.

Let me explain the BL locking mechanism with a small table. The following code creates a table t_one and populates the table with 500 rows, adds an index, and then collects statistics on the table. With this setup, I will query a row to show how BL resources are employed to maintain cache fusion consistency.

DROP TABLE t_one;
CREATE TABLE t_one (n1 NUMBER , v1 VARCHAR2(100));
INSERT INTO t_one
SELECT n1, lpad (n1, 100,'DEADBEEF')
FROM
  ( SELECT level n1 FROM dual CONNECT BY level <=500
  );
COMMIT;
CREATE INDEX t_one_n1 ON t_one (n1);
EXEC dbms_stats.gather_table_stats ( USER, 't_one', CASCADE =>true);

BL Resources and Locks

The query printed below has an index range scan access path, so index block is read using the predicate n1=100, and then the table block is read using the rowid retrieved from the index entry. For this discussion, I will exclusively focus on BL resources and locks protecting the table data block, although a similar locking scheme is applied to index block also. As BL resource_name is derived using the combination of file_id and block_id, I will query the file_id and block_id of a block using dbms_rowid package.

-- script tc_one_row.sql --
SELECT n1,
  dbms_rowid.rowid_to_absolute_fno (rowid, user,'T_ONE') fno,
  dbms_rowid.rowid_block_number(rowid) block,
  dbms_rowid.rowid_object(rowid) obj,
  LENGTH(v1) v1
FROM t_one
WHERE n1=100;
 
N1        FNO         BLOCK      OBJ        V1
---------- ---------- ---------- ---------- ----------
       100          4        180      75742        250

Resource_name starts with a format [0xblock_id][0xfile_id],BL, where both block_id and file_id are converted to hexadecimal format.⁴  The query printed below constructs a resource_name protecting the block using dbms_rowid package. This resource_name will be used to query GRD to view resources and locks.

SELECT DISTINCT '[0x'||trim(TO_CHAR(dbms_rowid.rowid_block_number(rowid), 'xxxxxxxx'))
    ||'][0x'|| trim(TO_CHAR(dbms_rowid.rowid_to_absolute_fno (rowid,user,'T_ONE'),'xxxx'))
    ||'],[BL]' res
FROM t_one
WHERE n1=100;
RES
---------------------------
[0xb4][0x4],[BL]

With the derived resource_name, I will query gv$ges_resource to print the GRD resource protecting the block. The output of the query below shows that this resource is visible in inst_id=2 (I am connected to instance 2). Master node of the resource is 1 (Master_node starts with 0, so master_node=1 is inst_id=2). This resource protects the block with (file_id=4 and block_id=180). Column on_convert_q shows the count of processes waiting in the converting queue to acquire locks, indicating the count of lock waiters. Column on_grant_q shows the count of processes holding locks, essentially, a count of lock holders.

RS@ORCL2:2>SELECT resource_name, ON_CONVERT_Q, ON_GRANT_Q, MASTER_NODE
FROM gv$ges_resource
WHERE resource_name LIKE '[0xb4][0x4],[BL]%'
/
RESOURCE_NAME                  ON_CONVERT_Q ON_GRANT_Q MASTER_NODE
------------------------------ ------------ ---------- -----------
[0xb4][0x4],[BL][ext 0x0,0x0]             0          1           1

gv$ges_enqueue externalizes the locks held in GRD. The following query accesses gv$ges_enqueue to print lock details. We can see that owner_node column is set to 1 (column owner_node starts with 0, and so owner_node=1 is inst_id=2). Essentially, in this example, the resource is mastered by inst_id=2 as shown in the output of gv$ges_resuorce and lock on that resource is also owned by the same instance. (Note that SQL statements in the section are available in downloadable scripts Listing_10-1.sql).

Listing 10-1.  Gv$ges_enqueue Output

col state format a15
RS@ORCL2:2>SELECT resource_name1, grant_level, state, owner_node
FROM v$ges_enqueue
WHERE resource_name1 LIKE '[0xb4][0x4],[BL]%'
/
RESOURCE_NAME1                 GRANT_LEV STATE      OWNER_NODE
------------------------------ --------- ---------- ----------
[0xb4][0x4],[BL][ext 0x0,0x0]  KJUSERPR  GRANTED             1

Connecting to instance ORCL1 (with inst_id=1), I will query the same row to read data blocks into instance 1 buffer cache.

RS@ORCL1:1> SELECT n1,
  dbms_rowid.rowid_to_absolute_fno (rowid, 'RS','T_ONE') fno,
  dbms_rowid.rowid_block_number(rowid) block,
  dbms_rowid.rowid_object(rowid) obj,
  LENGTH(v1) v1
FROM rs.t_one
WHERE n1=100;
 
N1         FNO        BLOCK      OBJ        V1
---------- ---------- ---------- ---------- ----------
       100          4        180      75742        250

Accessing gv$ges_resource, as printed in the following query output, we see that block is still mastered by instance 1.

RS@ORCL2:2> SELECT resource_name, ON_CONVERT_Q, ON_GRANT_Q, MASTER_NODE
FROM gv$ges_resource
WHERE resource_name LIKE '[0xb4][0x4],[BL]%';
 
RESOURCE_NAME                  ON_CONVERT_Q ON_GRANT_Q MASTER_NODE
------------------------------ ------------ ---------- -----------
[0xb4][0x4],[BL][ext 0x0,0x0]             0          1           1

Accessing gv$ges_enqueue, there are two locks on that resource, which are owned by instances 1 and 2 in KJUSERPR (PR) mode. Since sessions are accessing the block in read-only mode, locks are acquired in PR (KJUSERPR) mode. Both locks can be held in the KJUSERPR mode, as the lock mode KJUSERPR is compatible with another KJUSERPR mode.

KJUSERPR mode protects concurrent changes to the block. So, if another session connected to instance 3 tries to change the block, then both instances 1 and 2 must downgrade the block before instance 3 can acquire the lock on the resource in exclusive mode.

RS@ORCL2:2>SELECT resource_name1, grant_level, state, owner_node
FROM v$ges_enqueue
WHERE resource_name1 LIKE '[0xb4][0x4],[BL]%'
/
RESOURCE_NAME1                 GRANT_LEV STATE      OWNER_NODE
------------------------------ --------- ---------- ----------
[0xb4][0x4],[BL][ext 0x0,0x0]  KJUSERPR  GRANTED             1
[0xb4][0x4],[BL][ext 0x0,0x0]  KJUSERPR  GRANTED             0

Figure 10-2 represents the resource and locks discussed in this section. The resource master instance has a BL resource allocated to protect the block in discussion. Two GCS (Global Cache Services) locks also have been acquired. However, there is a minor distinction to be made between these two GCS locks, namely, GCS client and GCS shadow: if the buffer represented by the resource is resident in the local buffer cache, then the corresponding GCS lock structure is known as the GCS client; if the buffer is resident in the remote buffer cache, then the lock structure is known as GCS shadow. A GCS client points to lock element, which in turn points to a buffer header structure. The buffer header structure holds a pointer to the buffer in the buffer cache.

Fixed table x$kjbr externalizes resource structures, x$kjbl externalizes BL locking structures, x$le externalizes the lock elements, and x$bh externalizes the buffer header structures.

The query in Listing 10-2 can be used to probe the internal structures further. (I should point out that this SQL statement has no practical value, it is provided purely to help you understand the internal structures.) In 12c, this statement must be executed in a container database (CDB) if the database is a CDB. You may be able to research the internal structures further using the script in Listing 10-2.

Listing 10-2.  Lock_to_buffer

set lines 120
col kjblname format a30
col kjblname2 format a20
col kjblgrant format a10
col kjblrole format 9999
col kjblrequest format A10
set verify off
select /*+ leading (c a b ) */ b.kjblname, b.kjblname2 ,
b.kjblgrant, b.kjblrole, b.kjblrequest , b.kjblmaster, b.kjblowner
from x$le a , x$kjbl b, x$bh c
where a.le_kjbl = b.kjbllockp
and a.le_addr =c.le_addr
and dbablk=&&block and obj=&&obj
;
KJBLNAME                   KJBLNAME2            KJBLGRANT  KJBLROLE KJBLREQUES KJBLMASTER
-------------------------- -------------------- ---------- -------- ---------- ----------
[0xb4][0x4],[BL][ext 0x0,0 180,4,BL             KJUSEREX          0 KJUSERNL            0

In summary, a BL resource protects a block, the action of reading a block into buffer cache creates a resource in the resource master instance, and a lock is added to that resource in a specific mode. In this example, the block is read with no intent to change and hence KJUSERPR locking mode (PR) is sufficient. We will see that locking mode will be different depending upon whether the block is altered or not. Locking modes are also discussed in Chapter 11.

Analysis of the Receiving Side

This section focuses on the receiving side of global cache performance metrics. RAC introduces a few wait events for time model instrumentation. You would use AWR or another time-based model to understand the wait events causing performance issues. Then, tune the top event consuming time.

Generally, wait events can be grouped into the following categories:

1. Current mode wait events: This wait event is incurred if the version of transferred or requested block is the latest. Current mode wait events can be encountered for both read and write requests. You should note that the block version of the disk could be the latest version, and hence the sheer action of reading a block from the disk can acquire locks in the current mode.
2. CR mode wait events: If the current version of the block is ahead of Query environment System Change Number (SCN), then the LMS process must reconstruct a block version for read-only purposes and then send the block to the FG process. This type of block transfer is known as CR mode transfer.
3. Busy wait events: Time is accounted to these events if the block transfer suffered from additional processing delays encountered by LMS process. For example, LMS process may have applied undo records to create CR mode blocks.
4. Congestion-related events: These events imply that delays were incurred since the LMS process was too busy servicing other requests or the LMS process was suffering from a resource starvation.

Wait events can be further grouped depending on the number of instances participating in a block transfer. Up to three instances can participate in a block transfer. Requesting instance is the instance requesting the block, master instance tracks the resource status, and the owner instance currently holds a lock on the resource. If three instances participate in a block transfer, then that block transfer is tagged with 3-way wait events. In some cases, the resource master instance might be the owner instance too. In that case, only two instances participate in a block transfer; hence, those wait events are tagged with 2-way wait events.

col owner format a30
col object_name format a30
set lines 160
WITH ash_gc AS
  (SELECT inst_id, event, current_obj#, COUNT(*) cnt
  FROM gv$active_session_history
  WHERE event=lower('&event')
  GROUP BY inst_id, event, current_obj#
  HAVING COUNT (*) > &threshold )
SELECT * FROM
  (SELECT inst_id, nvl( owner,'Non-existent') owner ,
           nvl ( object_name,'Non-existent') object_name,
           nvl ( object_type, 'Non-existent') object_type,
           cnt
  FROM ash_gc a, dba_objects o
  WHERE (a.current_obj#=o.object_id (+))
  AND a.current_obj#  >=1
  UNION
  SELECT inst_id, '', '', 'Undo Header/Undo block', cnt
  FROM ash_gc a WHERE a.current_obj#=0
  UNION
  SELECT inst_id, '', '', 'Undo Block', cnt
  FROM ash_gc a
  WHERE a.current_obj#=-1
  )
ORDER BY cnt DESC
/
Enter value for event: gc current block 2-way
Enter value for threshold: 30
 
INST_ID OWNER                OBJECT_NAME                      OBJECT_TYPE          CNT
------- -------------------- -------------------------------- ------------- ----------
      3 PO                   RCV_SHIPMENT_LINES               TABLE               2228
      2 PO                   RCV_SHIPMENT_LINES               TABLE               2199
      1 PO                   RCV_SHIPMENT_LINES               TABLE               2197
      3 PO                   PO_REQUISITION_LINES_ALL         TABLE               2061
      2 PO                   PO_REQUISITION_LINES_ALL         TABLE               1985
      3                                                       Undo Block           120
...

col owner format a30
col object_name format a30
set lines 160
WITH ash_gc AS
  (SELECT inst_id, event, current_obj#,con_id, COUNT(*) cnt
  FROM gv$active_session_history
  WHERE event=lower('&event')
  GROUP BY inst_id, event, current_obj#, con_id
  HAVING COUNT (*) > &threshold )
SELECT * FROM
  (SELECT inst_id, a.con_id, nvl( owner,'Non-existent') owner ,
           nvl ( object_name,'Non-existent') object_name,
           nvl ( object_type, 'Non-existent') object_type,
           cnt
  FROM ash_gc a, cdb_objects o
  WHERE (a.current_obj#=o.object_id (+))
  AND a.current_obj#  >=1
  AND a.con_id =o.con_id (+)
  UNION
  SELECT inst_id,0, '', '', 'Undo Header/Undo block', cnt
  FROM ash_gc a WHERE a.current_obj#=0
  UNION
  SELECT inst_id,0, '', '', 'Undo Block', cnt
  FROM ash_gc a
  WHERE a.current_obj#=-1
  )
ORDER BY cnt DESC
/
Inst Cont OWNER OBJECT_NAME                    OBJECT_TYPE            CNT
---- ---- ----  ------------------------------ ---------------        ---
   2    3 RS    HUGETABLE                      TABLE                   21
   2    3 RS    HUGETABLE_HASH                 TABLE PARTITION          9
...

WITH ash_gc AS
  (SELECT    /*+ materialize */ inst_id, event, sql_id, COUNT(*) cnt
  FROM gv$active_session_history
  WHERE event=lower('&event')
  GROUP BY inst_id, event, sql_id
  HAVING COUNT (*) > &threshold )
SELECT inst_id, sql_id, cnt FROM ash_gc
ORDER BY cnt DESC
/
Enter value for event: gc current block 2-way
Enter value for threshold: 100
 
   INST_ID SQL_ID               CNT
---------- ------------- ----------
         3 26shktr5f1bqk       2717
         3 4rfpqz63y34rk       2332
         2 4rfpqz63y34rk       2294
 ...

WITH ash_gc AS
  (SELECT    /*+ materialize */ inst_id, con_id,event, sql_id, COUNT(*) cnt
  FROM gv$active_session_history
  WHERE event=lower('&event')
  GROUP BY inst_id, event, sql_id, con_id
  HAVING COUNT (*) > &threshold )
SELECT inst_id,con_id, sql_id, cnt FROM ash_gc
ORDER BY cnt DESC
/

SELECT inst_id, event, wait_time_milli, wait_count,
      TRUNC(100*(wait_count/tot),2) per
FROM
  (SELECT inst_id, event, wait_time_milli, wait_count,
    SUM (wait_count) over(partition BY inst_id, event
                  order by inst_id
                  rows BETWEEN unbounded preceding AND unbounded following
                  ) tot
  FROM
    (SELECT * FROM gv$event_histogram
     WHERE event LIKE '%&event_name%'
     ORDER BY inst_id, event#, WAIT_TIME_MILLI
    )
  )
ORDER BY inst_id, event, WAIT_TIME_MILLI ;
Enter value for event_name: gc cr block 2-way
 
   INST_ID EVENT                          WAIT_TIME_MILLI WAIT_COUNT        PER
---------- ------------------------------ --------------- ---------- ----------
         1 gc cr block 2-way                            1     105310       2.03
         1 gc cr block 2-way                            2    3461802      66.87
         1 gc cr block 2-way                            4    1593929      30.79
         1 gc cr block 2-way                            8      15163        .29
         1 gc cr block 2-way                           16        340          0
         1 gc cr block 2-way                           32         26          0
...

set lines 160
col begin_interval_time format a30
col event_name format a30
col instance_number format 99 head "Inst"
break on begin_interval_time
SELECT snaps.begin_interval_time,
  snaps.instance_number,
  hist.event_name,
  hist.wait_time_milli,
  hist.wait_count
FROM dba_hist_event_histogram hist, DBA_HIST_SNAPSHOT snaps
WHERE snaps.snap_id           = hist.snap_id
AND snaps.instance_number     = hist.instance_number
AND snaps.begin_interval_time > sysdate-1 -- modify to specify constrained time window
AND hist.event_name           = lower('&event_name')
ORDER BY snaps.snap_id ,
  instance_number,
  wait_time_milli
/
 
BEGIN_INTERVAL_TIME            Inst EVENT_NAME           WAIT_TIME_MILLI WAIT_COUNT
------------------------------ ---- -------------------- --------------- ----------
01-NOV-12 05.00.15.638 PM         1 gc cr block 2-way                  1     110970
                                  1 gc cr block 2-way                  2    3620790
                                  1 gc cr block 2-way                  4    1708432
                                  1 gc cr block 2-way                  8      15685
                                  1 gc cr block 2-way                 16        340
                                  1 gc cr block 2-way                 32         26
                                  1 gc cr block 2-way                 64          8
                                  1 gc cr block 2-way                128          1
                                  1 gc cr block 2-way                256          1
                                  1 gc cr block 2-way               2048          1

SELECT instance ||'->' || inst_id transfer,
  class,
  cr_block cr_blk,
  TRUNC(cr_block_time     /cr_block/1000,2) avg_Cr,
  current_block cur_blk,
  TRUNC(current_block_time/current_block/1000,2) avg_cur
FROM gv$instance_cache_transfer
WHERE cr_block >0 AND current_block>0
ORDER BY instance, inst_id, class
/
TRANS CLASS                  CR_BLK     AVG_CR    CUR_BLK    AVG_CUR
----- ------------------ ---------- ---------- ---------- ----------
...
1->2  data block           51035711       1.57  930591874        1.6
...
1->2  undo block           21548056       1.05         70       1.82
1->2  undo header          17073000       1.01     272653        1.2
...
1->3  data block           35554782       1.67  804269141       1.78
...
1->3  undo block           16125459       1.06          1       2.03
1->3  undo header          25643014        .99     270995       1.15
...
2->1  data block          178936080       1.44 1382236663       1.55

TRANS CLASS                  CR_BLK     AVG_CR    CUR_BLK    AVG_CUR
----- ------------------ ---------- ---------- ---------- ----------
1->2  data block           87934887       1.23    9834152       1.8
2->1  data block           28392332       1.30    1764932       2.1
...
3->1  data block           12519985      11.57    2231921      21.6
...
3->2  undo block            4676398       8.85        320      27.82

RAC Wait Events

I will discuss important RAC wait events in this section. This is not a complete list of all wait events, but just a list of most frequently encountered wait events.

GC Current Block 2-Way/3-Way

Current block wait events indicate that the transferred block version is the latest version of the block. This wait event can be encountered for both read and write activities. If the block is accessed for read activity, then a lock on a resource is acquired in KJUSERPR (PR) mode. The example I discussed earlier in the “Resources and Locks” section shows the locks in KJUSERPR mode.

In the following example, I queried a row from table t_one, resulting in a disk read connecting to node 2. Reviewing the SQL trace file, there are no global cache wait events. The reasoning is that the block is mastered locally, and so the FG process can acquire locks on the resources directly without incurring any global cache wait. This type of locking is also called the affinity-locking scheme. The Dynamic Resource Mastering (DRM) section will discuss affinity locking in detail.

RS@ORCL2:2> @tc_one_row.sql
 
        N1        FNO      BLOCK        OBJ         V1
---------- ---------- ---------- ---------- ----------
       100          4        180      75742        250
 
Trace file:
 
nam='db file sequential read' ela= 563 file#=4 block#=180 blocks=1 obj#=75742

I queried the same row connecting to instance 1.⁵  Since that block is cached in instance 2 already, the block is transferred from instance 2 to 1. Trace line shows that a wait event gc current block 2-way was encountered for the block with file_id=4, block_id=180. This block transfer is a 2-way block transfer, as the owning instance is same as the resource master instance (instance 2).

SYS@ORCL1:1> @tc_one_row.sql
 
        N1        FNO      BLOCK        OBJ         V1
---------- ---------- ---------- ---------- ----------
       100          4        180      75742        250
 
Trace file:
 
nam='gc current block 2-way' ela= 629 p1=4 p2=180 p3=1 obj#=75742 tim=1350440823837572

Next, I will create conditions for a 3-way wait event, but first let me flush buffer cache in all three instances to start with a clean buffer cache. In the following example,

1. I will query the row connecting to instance 1 (which would load the block into the buffer cache of instance 1).
2. I will connect to an instance and query the same row from instance 3. The FG process for my session will request LMS process running in instance 2 for the block (as instance 2 is the resource master).
3. Instance 2 LMS process forwards the request to LMS process in instance 1.
4. An LMS process in instance 1 will send the block to the FG process in instance 3.

Essentially, three instances participate in a block transfer; hence, this is a 3-way block transfer.

-- alter system flush buffer_cache; from all instances
RS@ORCL1:1> @tc_one_row.sql
 
        N1        FNO      BLOCK        OBJ         V1
---------- ---------- ---------- ---------- ----------
       100          4        180      75742        250
 
RS@ORCL3:3> @tc_one_row.sql
        N1        FNO      BLOCK        OBJ         V1
---------- ---------- ---------- ---------- ----------
       100          4        180      75742        250

Trace file:

nam='gc current block 3-way' ela= 798 p1=4 p2=180 p3=1 obj#=75742

Reviewing locks on the resource connecting to instance 2, we see that there are two locks on the resources held for instance 1 and instance 3 (owner_node equals to 0 and 2).

RS@ORCL2:2> SELECT resource_name1, grant_level, state, owner_node
     FROM v$ges_enqueue
     WHERE resource_name1 LIKE '[0xb4][0x4],[BL]%';
 
RESOURCE_NAME1                 GRANT_LEV STATE         OWNER_NODE
------------------------------ --------- ------------- ----------
[0xb4][0x4],[BL][ext 0x0,0x0]  KJUSERPR  GRANTED                2
[0xb4][0x4],[BL][ext 0x0,0x0]  KJUSERPR  GRANTED                0

Excessive waits for gc current block 2-way or gc current block 3-way wait event are generally either due to (a) an inefficient execution plan, leading to numerous block visits, or (b) application affinity not being in play. Consider implementing application affinity if the object access is localized. Also, use the techniques discussed earlier in the section “A generic analysis for all wait events.”

GC CR Block 2-Way/3-Way

CR mode block transfer is requested for read-only access. Consider that a block is resident in current mode in instance 2; instance 2 holds the exclusive mode BL lock on the resource. Another session connected to instance 1 requests that block. As the “readers do not see uncommitted changes” in Oracle Database, SELECT statements request a specific version of the block as of the query start time. SCN is employed for block versioning; essentially, SELECT statement requests a version of a block consistent with a SCN. LMS process servicing the request in instance 2 will clone current mode buffer, verify that the SCN version is consistent with the request, and then send the CR copy of the block to the FG process.

The primary difference between these CR mode transfers and current mode transfers is that, in the case of CR mode transfers, no resource or locks are maintained in GRD for the CR buffers. Essentially, CR mode blocks do not require global cache resources or locks. Received CR copy is usable only by the requesting session and only for that specific SQL execution. That’s why Oracle Database does not acquire any lock on that BL resource for CR transfers.

Since there are no global cache locks protecting the buffer, the next execution of SQL statement accessing that block connected to instance 1 would suffer from wait for gc cr block 2-way or gc cr block 3-way event too. So, every access to the block from instance 1 would trigger a new CR buffer fabrication. Even if nothing has changed in the buffer in instance 2, still, the FG process in instance 1 would suffer from CR wait events. CR buffers residing in instance 1 are not reusable either, since the requested query SCN will be different for every SQL execution.

The following trace line shows that a block was transferred from a resource master instance to the requesting instance with a latency of 0.6 ms. Further, file_id, block_id, and object_id information in the trace file can be used to identify the objects suffering from these two wait events. Of course, ASH data can be queried to identify the object also.

nam='gc cr block 2-way' ela= 627 p1=7 p2=6852 p3=1 obj#=76483 tim=37221074057

After executing tc_one_row.sql five times and then querying buffer cache headers, you can see that there are five CR buffers for that block in both instances 1 and 2. Notice that CR_SCN_BAS and CR_SCN_WRP columns⁶ have different values for each CR buffer copy. Querying gv$ges_resource and gv$ges_enqueue, you can see that there are no GC locks protecting these buffers though.

Listing 10-10.  Buffer Status

SELECT
  DECODE(state,0,'free',1,'xcur',2,'scur',3,'cr', 4,'read',5,'mrec',
               6,'irec',7,'write',8,'pi', 9,'memory',10,'mwrite',
              11,'donated', 12,'protected', 13,'securefile', 14,'siop',
              15,'recckpt', 16, 'flashfree', 17, 'flashcur', 18, 'flashna') state,
  mode_held, le_addr, dbarfil, dbablk, cr_scn_bas, cr_scn_wrp , class
FROM sys.x$bh
WHERE obj= &&obj
AND dbablk= &&block
AND state!=0 ;
Enter value for obj: 75742
Enter value for block: 180

STATE       MODE_HELD LE        DBARFIL     DBABLK CR_SCN_BAS CR_SCN_WRP      CLASS
---------- ---------- -- -------------- ---------- ---------- ---------- ----------
cr                  0 00              1      75742  649314930       3015          1
cr                  0 00              1      75742  648947873       3015          1
cr                  0 00              1      75742  648926281       3015          1
cr                  0 00              1      75742  648810300       3015          1
cr                  0 00              1      75742 1177328436       3013          1

CR buffer generation is a special case, and no global cache locks are acquired to protect CR buffers. It is possible to create a CR storm if there are long-pending transactions on highly accessed objects. Hence, it is prudent to schedule batch processes updating heavily accessed tables to a less busy timeframe.

GC CR Grant 2-Way/Gc Current Grant 2-Way

gc cr grant 2-way and gc current grant 2-way wait events are encountered if the requested block is not resident in any buffer cache. The FG process requests LMS process for a block, but the block is not resident in any buffer cache. So, LMS process replies with a grant message to the FG process to read the block from disk. The FG process reads the block from disk and continues the processing.

The following line shows that for the block access with file_id=4 and lock_id=180, a grant response was received by the FG process from LMS process. The subsequent line shows that a physical read was performed to read the block from disk.

nam='gc cr grant 2-way' ela= 402 p1=4 p2=180 p3=1 obj#=75742
nam='db file sequential read' ela= 553 file#=4 block#=180 blocks=1 obj#=75742

Excessive waits for these events imply either that the buffer cache is undersized or that the SQL statements are aggressively flushing the buffer cache. Identify SQL statements and objects suffering from waits for the events, and tune those SQL statements.

The DRM feature is designed to reduce the occurrences of waits for the grants.

GC CR Block Busy/GC Current Block Busy

Busy events indicate that LMS performed additional work due to concurrency-related issues. For example, to build a CR block, LMS process may have to apply undo records to reconstruct a block consistent with query SCN. While transferring the block back to the FG process, LMS will mark the block transfer to have suffered from gc cr block busy event or gc current block busy event depending upon the type of block transfer.

GC CR Block Congested/GC Current Block Congested

If the LMS process did not process a request within 1 ms after the receipt of a request, then LMS process marks the response that block suffered from congestion-related wait events. There are many reasons for congestion-related wait events, such as that the LMS process is overwhelmed by numerous global cache requests, LMS process is suffering from CPU Scheduling delays, LMS process has suffered from another resource starvation (such as memory), etc.

Typically, the LMS process runs in a real-time CPU scheduling priority; therefore, CPU scheduling delays will be minimal. The excessive number of waits for this event would indicate that there was a sudden spike in global cache requests and that the LMS process was not able to process the requests quickly enough. Memory starvation in the server also can lead to paging of the LMS process, affecting global cache performance.

You can review to see why LMS process is not able to process requests efficiently.

Block Types Served

The pattern of block types served can be used to understand global cache workload patterns. v$cr_block_server provides a breakdown of CR requests into various types of requests. For example, the output in Listing 10-11 shows that 51% of CR requests were for data block, 31% of CR requests for undo blocks, and 15% for undo header block. From this output, you can infer that application affinity will greatly reduce the GC traffic, as 50% of the blocks served is for undo blocks.

Listing 10-11.  Block Types Served

SELECT inst_id,
  TRUNC(data_requests /tot_req,2) * 100 data_per,
  TRUNC(undo_requests /tot_req,2) * 100 undo_per,
  TRUNC(tx_requests   /tot_req,2) * 100 tx_per,
  TRUNC(other_requests/tot_req,2) * 100 other_per
FROM
  (SELECT inst_id, cr_Requests + current_Requests tot_req,
    data_requests, undo_requests, tx_requests, other_requests
    FROM gv$cr_block_server
   )
ORDER BY inst_id;
 
   INST_ID   DATA_PER   UNDO_PER     TX_PER  OTHER_PER
---------- ---------- ---------- ---------- ----------
         1         51         31         15          0
         2         47         47          5          0
         3         49         42          7          0

GCS Log Flush Sync

When a session issues a commit, that action triggers LGWR to write log buffer entries to the redo log files. The FG process waits for LGWR to complete log file write. Until the LGWR process responds, the FG process will account wait time to log file sync event. The action of LGWR writes from the log buffer to the log file makes the transaction permanent. Even if the database crashes at that time, the transaction is not lost, as the transaction is permanently stored in the redo log file. Essentially, the durability in ACID properties of a database is satisfied with this mechanism (ACID = Atomicity, Consistency, Isolation, and Durability).

In RAC, since multiple buffer caches are fused, a mechanism similar to log file sync is employed by the LMS process to maintain durability. LMS will request a log flush before sending the block if undo records were applied to create a CR copy of the block or if the block has uncommitted recent transactions in the block. LMS process will wait for LGWR to complete the log flush and post LMS process to continue. LMS process accounts the wait time to gcs log flush sync wait event while waiting for LGWR process. The FG processes in other instances will be waiting for a global cache wait event while LMS process is waiting for LGWR process. Potentially, prolonged wait by LMS process explodes into massive waits for gc buffer busy waits.

Therefore, it is very important to have consistent LGWR performance in RAC. Any delay in LGWR processing will be magnified as an excessive amount of global cache waits.

If your database is suffering from high gc buffer busy waits or other RAC wait events, and if the average wait time for wait events is much higher, then probe to see if gcs log flush sync in other nodes is causing the problem, and also review the histogram of the wait time for gcs log flush sync wait event. Script event_histogram.sql in Listing 10-5 can be used to understand the waits by LMS process. The following output of the script shows that 91% of waits complete within 2 ms in a healthy database. (As always, use AWR report or custom script to review the histogram during a specific period of time.)

@event_histogram
Enter value for event_name: gcs log flush sync
 
   INST_ID EVENT                          WAIT_TIME_MILLI WAIT_COUNT        PER
---------- ------------------------------ --------------- ---------- ----------
         1 gcs log flush sync                           1  336241301      78.73
         1 gcs log flush sync                           2   58520524       13.7
         1 gcs log flush sync                           4   22517131       5.27
         1 gcs log flush sync                           8    7629771       1.78
         1 gcs log flush sync                          16    2103830        .49
         1 gcs log flush sync                          32      67629        .01
         1 gcs log flush sync                          64        608          0
         1 gcs log flush sync                         128         26          0

The following output shows a database with high wait time for gcs log flush event. This increase in wait time resulted in longer waits for the global cache waits from the perspective of application process.

@event_histogram.sql
Enter value for event_name: gcs log flush sync
INST_ID        EVENT                    WAIT_TIME_MILLI WAIT_COUNT   PER
---------- ---------------------------- --------------- ---------- -----
       1       gcs log flush sync                     1         28   .07
       1       gcs log flush sync                     2         24   .06
       1       gcs log flush sync                     4         31   .08
       1       gcs log flush sync                     8         33   .08
       1       gcs log flush sync                    16      35757 95.96
       1       gcs log flush sync                32       1378  3.69
       1       gcs log flush sync                64          6   .01
       1       gcs log flush sync               128          2     0

If LMS database processes suffers from high gcs log flush sync wait event, then you would need to understand why LGWR is not responding fast enough. In most cases, that would require a review of the output of script lfsdiag.sql supplied by Oracle Support to understand the breakdown of the wait time suffered by LGWR process.

Defending LMS Process

As the performance of LMS process is quite essential, there are few features protecting LMS process overworking.

As I/O events usually exceed 5 ms, and since global cache latency is in the order of a few milliseconds, an I/O event initiated by LMS process can induce longer latencies to the FG processes. So, LMS process does not initiate any I/O calls at all. If LMS process has to initiate an I/O, for example, to read undo blocks required for CR fabrication as those undo block may not be in the cache anymore, then LMS process will downgrade the lock on the BL resource and send the buffer to the requestor. The requesting FG process will perform I/O calls and complete CR fabrication. This feature is known as the “Light Works Rule.”

If a block is requested excessively, as detected by LMS process, then LMS process can downgrade the BL lock and transfer the block to the remote FG process. For example, if a block is requested in CR mode aggressively, then LMS process can downgrade the lock to a null mode and transfer the block to the FG process. This feature is known as “Fairness Down Convert.”

An overworked LMS process can trigger delays in global cache transfer. These features protect LMS process from working too hard due to an onslaught of excessive global cache requests. View gv$cr_block_server provide statistics on these features.

Unique Indexes

In a B-tree index, indexed column values are stored in (key[s], rowid) format in ascending or descending key column order. If the column values are populated using sequence-generated values, then all new rows will be populated in the rightmost leaf block of the index. This type of index growth is right-hand index growth. If many sessions are trying to insert rows into the table, then the rightmost leaf block will be a hot block. In the case of a single-instance database, this concurrency issue will cause waits for read by other session and buffer busy wait events. In a RAC database, this problem will be magnified as gc buffer busy waits. If the application inserts into that table from all instances, then the rightmost leaf block has to be transferred between the instances aggressively, similar to a Hot Potato game played by children in the United States.

Almost-unique indexes can cause gc buffer busy waits too; indexes on timestamp column are an example. Indexed column values inserted concurrently will have the same timestamp or timestamp values close enough that those values will be stored in fewer leaf blocks of the index B-tree. So, concurrent inserts into that table will induce contention for few leaf blocks.

You will also notice that contention will be moving from one block to another block, as the leaf block will be filled, and new blocks added to the index structure of their rightmost part of the index.

Right-hand growth index contention can be resolved by hash partitioning the index on key column values. By hash partitioning the index, values are distributed among multiple leaf blocks, thereby effectively spreading the activity to many blocks. From 10g onward, indexes can be partitioned even if the table is not partitioned.

Index unique scan and index range scan access paths on hash-partitioned indexes specifying, at least, all partitioning key column values will perform similar to a nonpartitioned index. However, if your application performs numerous index range scans without providing all partitioning key columns, then hash partitioning will increase logical I/O per execution.

Reverse key indexes also can be used to combat right-hand growth contention. But reverse key indexes do not allow a range scan access path, and so there are only a very few reasons as to why you would choose a reverse key index over hash-partitioning indexes. One of those reasons is that partitioning requires a license for partitioning option.

Table Blocks

If the object_type suffering from gc buffer busy wait event is a table block, then it is imperative that you identify the type of block inducing gc buffer busy wait event. Listing 10-6 provides a SQL statement querying gv$active_session_history data and identifies the blocks inducing gc buffer busy acquire waits.

After identifying the specific block, you must identify the type of the block suffering from contention. The output in Listing 10-12 shows that block with file_id=49 and block_id= 635413 belongs to the fnd_concurrent_processes table (a familiar E-Business suite table). You may have to dump the data block to identify the block type.

Listing 10-12.  Ash_gcwait_to_block.sql

col inst format 9999
col current_file# format 99999  head file
col current_block# format 9999999 head blk
WITH ash_gc AS
  (SELECT * FROM
    (SELECT /*+ materialize */ inst_id, event, current_obj#, current_file#,
      current_block#, COUNT(*) cnt
    FROM gv$active_session_history
    WHERE event=lower('&event')
    GROUP BY inst_id, event, current_obj#,
      current_file#, current_block#
    HAVING COUNT(*) >5
    )
  WHERE rownum <101
  )
SELECT * FROM
  (SELECT inst_id, owner, object_name, object_type, current_file#,
    current_block#, cnt
  FROM ash_gc a, dba_objects o
  WHERE (a.current_obj#  =o.object_id (+))
  AND a.current_obj#  >=1
  UNION
  SELECT inst_id, '', '', 'Undo Header/Undo block' ,
    current_file#, current_block#, cnt
  FROM ash_gc a
  WHERE a.current_obj#=0
  UNION
  SELECT inst_id, '', '', 'Undo Block' ,
    current_file#, current_block#, cnt
  FROM ash_gc a
  WHERE a.current_obj#=-1
  )
ORDER BY 7 DESC
/
Enter value for event: gc buffer busy acquire
 
     INST OWNER      OBJECT_NAME                      OBJECT_TYP   file     blk     CNT
---------- ---------- -------------------------------- ---------- ------ ------- -------
         1 APPLSYS    FND_CONCURRENT_PROCESSES         TABLE          49  635413    2039
         1 APPLSYS    FND_CONCURRENT_PROCESSES         TABLE         301  954692      18
         1 SYSTEM     AQ$_QUEUE_TABLES                 TABLE           4    1129      18
         1 APPLSYS    FND_CONCURRENT_QUEUES            TABLE         382  123951      17
         1                                             Undo Block      0       0      15
...

Dumping the data block using the following command⁹ and reading through trace file, you can identify the type of block. Oracle Support will help to read the trace file too. Table 10-1 shows possible block types and methods to resolve the contention.

alter system dump datafile 49 block 635413 block max 49 635413   ;

If your database is a 12c CDB, then join to cdb_objects to identify PDB associated with the object. The following script is useful in a database container to identify the block in a CDB.

Listing 10-13.  Ash_gcwait_to_block_12c.sql

col inst format 9999
col current_file# format 99999  head file
col current_block# format 9999999 head blk
WITH ash_gc AS
  (SELECT * FROM
    (SELECT /*+ materialize */ inst_id, con_id, event, current_obj#, current_file#,
      current_block#, COUNT(*) cnt
    FROM gv$active_session_history
    WHERE event=lower('&event')
    GROUP BY inst_id, event, current_obj#,
      current_file#, current_block#, con_id
    HAVING COUNT(*) >5
    )
  WHERE rownum <101
  )
SELECT * FROM
  (SELECT a.inst_id, a.con_id, owner, object_name, object_type, current_file#,
    current_block#, cnt
  FROM ash_gc a, cdb_objects o
  WHERE (a.current_obj#  =o.object_id (+) and a.con_id=o.con_id(+) )
  AND a.current_obj#  >=1
  UNION
  SELECT inst_id, a.con_id, '', '', 'Undo Header/Undo block' ,
    current_file#, current_block#, cnt
  FROM ash_gc a
  WHERE a.current_obj#=0
  UNION
  SELECT inst_id,a.con_id, '', '', 'Undo Block' ,
    current_file#, current_block#, cnt
  FROM ash_gc a
  WHERE a.current_obj#=-1
  )
ORDER BY 7 DESC
/

In summary, event waits for gc buffer busy acquire/release is a symptom of another problem, and you need to investigate the layers underneath to identify the root cause.

DRM Stages

LMON goes through various stages to complete DRM activity. One major phase of DRM activity is GRD freeze. While GRD is frozen, no BL locks can be acquired or released. Existing buffers in SGA can be accessed, but new global locking requests will be stuck waiting for gc remaster event. At the end of DRM activity, GRD is unfrozen and the global cache locking activity continues. This GRD freeze and unfreeze is performed in a synchronized fashion by all instances. Instance triggering the remastering event acts as a co-coordinator instance. It coordinates with other instances to complete a specific sequence of  tasks to complete the remastering event.

The following output from the LMON trace file shows an overview of DRM activity in a database. As a first step, GRD is frozen and no new BL locks can be acquired after the completion of freeze. Then, old locks and resources of the remastered objects are cleaned out. This cleanup activity must happen in all instances so that there is a synchronization mechanism after each step. The remastering instance waits for other instances to complete the task before proceeding to the next step. Finally, locks and resources are transferred to the instance that is about to receive the remastered objects.

*** 2011-08-12 15:24:33.146
* drm freeze : Quiesce- No new opens or converts
* drm cleanup: All old locks and resources closed for the objects
* drm sync 2: wait for sync from all nodes
* drm replay: transfer resource/lock states to new nodes
* drm sync 3: wait for sync
* drm fix writes

You have probably guessed the pain point of DRM processing. GRD freeze means that no new BL resources or locks can be acquired during the initial phases of DRM. This GRD freeze can induce a complete application hang.

GRD Freeze

GRD is frozen briefly during a resource remastering event. No new global resources and locks can be acquired while GRD is frozen, and in a few scenarios, this freeze can lead to a hung application. Worse yet, DRM can happen in cycles; for example, DRM started for object 1, at the completion of DRM, DRM started for object 2, etc. In a busy database, even a minute of GRD freeze can throw instability into application availability and performance. Further, as the size of SGA grows, the severity of GRD freeze increases. Typically, business-critical applications tend to have bigger SGA, and so the effect of GRD freeze can be severe in those business-critical applications. Databases with less activity might not suffer excessively due to GRD freeze.

The following output shows a few lines from an AWR report during DRM-related freeze. Event gcs drm freeze enter server indicates that the problem is due to DRM activity. The first process requesting a block will wait for gc remaster or gcs drm freeze enter server event. Subsequent processes accessing the same block will wait for gc buffer busy event, since there is an outstanding BL lock request already. So, in the following example, the actual impact of the application due to DRM activity should be considered 70% of response time. Note that average CR receive time is also at an elevated level of 17.1 ms and that elevated latency is due to DRM freeze.

Top Five Timed Events                                              Avg     %Total
∼∼∼∼∼∼∼∼∼∼∼∼∼∼∼∼∼∼∼∼∼                                              wait    Call
Event                           Waits           Time (s)           (ms)    Time    Wait Class
------------------------------  ------------    -----------        ------  ------  ----------
gc buffer busy                     1,826,073        152,415            83    62.0  Cluster
CPU time                                             30,192          12.3
enq: TX - index contention            34,332         15,535           453     6.3  Concurrency
gcs drm freeze in enter server        22,789         11,279           495     4.6  Other
enq: TX - row lock contention         46,926          4,493            96     1.8  Applicatio
 
Global Cache and Enqueue Services - Workload Characteristics
∼∼∼∼∼∼∼∼∼∼∼∼∼∼∼∼∼∼∼∼∼∼∼∼∼∼∼∼∼∼∼∼∼∼∼∼∼∼∼∼∼∼∼∼∼∼∼∼∼∼∼∼∼∼∼∼∼∼∼∼
Avg global enqueue get time (ms)                : 16.8
Avg global cache cr block receive time (ms)     : 17.1
Avg global cache current block receive time (ms): 14.9

Parameters

There are a few underscore parameters controlling the behavior of DRM activity. Parameter _gc_policy_minimum decides the minimum gc activity per minute of an object to be considered for resource mastering (default value, 1,500). Parameter _gc_policy_time governs the duration of an observation window (default value, 10 minutes). During the observation window, objects are observed, objects exceeding a minimum threshold are considered for resource mastering, and DRM is triggered.

There are many other underscore parameters controlling the behavior, and those parameters usually start with _lm_drm_ and do not normally need any adjustments. However, if you find that DRM activity is slower, it is possible to tune DRM speed using these parameters, but you must contact Oracle Support before changing these initialization parameters.

If your database performance suffers from excessive amount of DRM activity, you can tune that by adjusting _gc_policy_minimum parameter to a much higher value, such as 1 million. While DRM can be disabled by adjusting _gc_policy_time to zero, this choice disallows manual remastering too. So, the preferred action is to increase _gc_policy_minimum to a bigger value.

Changes in 12c

DRM has slight changes in release 12c, mostly for the PDB feature. Every PDB generates its own object_ids, and so pkey format has been changed to accommodate plugged-in databases. For example, remastering of an object with object_id=89911 uses pkey including con_id of plugged-in database. A snippet from LMD trace file follows.

*** 2013-03-17 21:57:49.718
Rcvd DRM(3) AFFINITY Transfer pkey 4.4.89911 to 1 oscan 1.1
ftd (30) received from node 1 (4 0.0/0.0)
all ftds received

In this example, pkey is 4.4.89911, where the first 4 is con_id of the object, my guess is that the second 4 is also a container_id, and 89911 is the object_id of the object. You can query v$container or cdb_pdbs to identify the plugged-in database of this object.

DRM and Undo

Undo segments also can be remastered. In RAC, every instance has its own undo tablespace. For example, if undo_tablespace parameter is set to undots1, then all undo segments onlined in instance 1 will be allocated in undots1 tablespace. So, by default, an instance onlining an undo segment will also be the resource master for that segment. But, if another instance accesses an undo segment excessively, then resource mastership for an undo segment can be remastered to another instance.

LMD0 trace file will show an object_id exceeding 4 billion. Here is an example of an undo remastering event:

Begin DRM(104) - transfer pkey 4294951403 to 1 oscan 0.0*** 2011-08-19

In version 12c, pkey format has been changed and pkey consists of two parts: value of 4 billion coloring the pkey as an undo segment, and 65541 identifying a specific undo segment.

*** 2013-03-17 19:29:41.008
Begin DRM(2) (swin 1) - AFFINITY transfer pkey4294967295.65541to 2 oscan 1.1
kjiobjscn 1
ftd (30) received from node 1 (4 0.0/0.0)
all ftds received

Troubleshooting DRM

If your database is suffering from excessive issues due to GRD freeze (and AWR wait events supports that assertion), then use the following guidelines to debug the problem.

1. Identify objects involved in DRM activity. Views gv$gcspfmaster_info and gv$policy_history can be used to identify objects triggered for DRM activity. LMD trace file will show object_id involved in a DRM with pkey as object_id. Verify if these object_ids are valid object_ids. In the following example, DRM is triggered on an object 27707121. Verify this object_id with dba_objects/cdb_objects(12c) and check if the object is valid. In 12c CDB, you need to identify the PDB associated with the object.

   Rcvd DRM(5) AFFINITY Transfer pkey 27707121.0 to 3 oscan 1.1

2. Understand if the activity on that object is justified by reviewing application workload pattern. If the activity is justified, then try to tune the SQL statement accessing those objects to reduce object access.
3. Identify the programs/application processes accessing those objects heavily. Colocate those programs to the same instance.
4. If none of these actions help, then contact Oracle Support and check if you can reduce DRM activity by tuning _gc_policy_minimum and _gc_policy_time parameters.

In summary, dynamic remastering is a good feature to improve performance of applications in a RAC database if the application affinity is in play. You can reduce the RAC tax to a minimal amount with this great feature. Also, in a RAC one-node database or a RAC cluster with active/passive instances, you can avoid RAC tax completely. It is a pity that this feature doesn’t work for all workloads and especially that this feature starts to be problematic if the SGA size exceeds 50GB+.