Database Reference
In-Depth Information
print sending-side metrics suffered by LMS process. In this example, average CR block flush time is 8.3 ms and only
27% of the block transfers waited for LGWR to complete a log flush. The following AWR report shows no sign of a
performance issue.
Global Cache and Enqueue Services - Workload Characteristics
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Avg global cache cr block receive time (ms) : 1.9
Avg global cache current block receive time (ms) : 1.8
Avg global cache cr block build time (ms) : 0.0
Avg global cache cr block send time (ms) : 0.0
Global cache log flushes for Cr blocks served % : 27.2
Avg global cache cr block flush time (ms) : 8.3
In contrast, the following AWR report was captured when there was a problem in the sending side. In this
example, the average CR block receive time is 222 ms in instance 1. Since the receive time in instance 1 is higher, we
need to review the sending-side metrics in other nodes; AWR report in instance 2 shows a glaring problem. Average
global cache flush time is very high, at an average of 15.8 s per CR block transfer. From this AWR report, we can infer
that the performance problems suffered by the LGWR process in instance 2 is affecting the receive time in other
instances, notably in instance 1.
Instance 1:
Avg global cache cr block receive time (ms) : 222.1
Avg global cache current block receive time (ms) : 27.5
Instance 2:
Avg global cache cr block build time (ms) : 0.0
Avg global cache cr block send time (ms) : 0.1
Global cache log flushes for cr blocks served % : 2.7
Avg global cache cr block flush time (ms) : 15879.9
You may need to review performance metrics in instance 2. AWR reports from instance 2 confirm that the log
writer was suffering from I/O-related latency, as the average latency for log file sync wait time is greater than 11 s.
(Normally, this event has a latency of a few milliseconds). In a nutshell, LMS process was waiting for LGWR to
complete log flush accounting wait time to
gc cr block flush time
statistics, and the FG process in instance 1 is
waiting for the LMS process to respond.
Top 5 Timed FG Events
~~~~~~~~~~~~~~~~~~~~~
Avg
wait % DB
Event Waits Time(s) (ms) time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
log file sync 2,054 23,720 11548 45.8 Commit
gc buffer busy acquire 19,505 10,382 532 20.0 Cluster
gc cr block busy 5,407 4,655 861 9.0 Custer
enq: SQ - contention 140 3,432 24514 6.6 Configurat
db file sequential read 38,062 1,305 34 2.5 User I/O
