Database Reference
In-Depth Information
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
6
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
6
Essentially, queries request a specific version of the block by specifying query SCN as block version. The combination of
cr_scn_bas and cr_scn_wrp specifies SCN version of a CR buffer.
