Database Reference
In-Depth Information
Event
gc cr grant 2-way
is a lightweight event. It involves a round-trip message to a remote LMS process and
the remote LMS process responding with a message to read the block from the disk. Both messages are typically
300 bytes in size. The average wait time for this event is usually less than 2 ms. If the distribution of wait time for this
event indicates that 90% of waits are less than 2 ms, then you can safely say that network
may
not be a problem.
If the wait time is much higher than 2 ms, then it could be a network problem or GC processing problem. This event
measures the baseline performance of a small packet transfer.
Another such event is
gc current block 2-way or gc cr block 2-way
. Time is accounted to these two events
if a database block was transferred from one instance to the FG process by an LMS process without incurring any
additional performance delays. Typically, the average wait time for this event is 2-4 ms. If the event histogram for
this event is mostly in the lower wait buckets, then you can safely eliminate the network as a problem. The message
size for these two wait events is usually big, as these messages will transfer one database block. As these two events
generally transfer big packets (~1 database block), they will serve to verify if the problem is with the Jumbo Frames
setup (if Jumbo Frames is in play).
Identify Problem Instances
When you have a performance problem, you can identify problem instances by reviewing gv$instance_cache_transfer
view. This view provides detailed map of receive time by class, receiving, and sending instance level. Using the query
listed in Listing 10-9, you can quickly identify the instance causing elevated GC latencies. In the following output,
there is no problem: both average CR receive time and average CUR receive time have the approximately the same
values in all instances.
Listing 10-9.
Script: gc_problem_instance.sql
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
Gv$instance_cache_transfer view is an ultracritical view to quickly identify the instance suffering from the
performance issue. If there are problems with a huge cluster, I usually drill down to the instance suffering from
performance issues quickly using this view.
