Database Reference
In-Depth Information
The following output shows that instance 3 is causing global cache slowdown. Blocks received from instance
3 have an average wait time of about 20 ms, while the average wait time for the blocks received from other instances
is ~2 ms. You should probe instance 3 to identify the reason for slow block transfers from that instance.
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
Again, SQL statements shown in Listing 10-9 are accessing gv$instance_cache_transfer view, a view of metrics
from the start of an instance. We need to review metrics in a more granular fashion, so review AWR report or use my
script gc_instance_cache_tfr.sql in the downloadable script format.
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.
5
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
5
Notice that I am querying the block from a different instance. There is a RAC optimization by which block locks are kept local and
globalized only when the block is accessed in a different instance. So, if you access the block in an instance, global cache structures
may not be set up yet. That's the reasoning behind accessing the block in a different instance.
