The session is looking for a consistent read version of data but cannot find it in the local cache. The local instance has made a request to other RAC instances for the data and is waiting on its return.
Solutions
Collect more information about the average time the instances are waiting for this type of request:
select b1.inst_id, b2.value ‘GCS CR BLOCKS RECEIVED’,
b1.value ‘GCS CR BLOCK RECEIVE TIME’,
((b1.value / b2.value) * 10) ‘AVG CR BLOCK RECEIVE TIME (ms)’
from gv$sysstat b1, gv$sysstat b2
where b1.name = ‘global cache cr block receive time’
and b2.name = ‘global cache cr blocks received’
and b1.inst_id = b2.inst_id;
If the average cr block receive time is more than 15 milliseconds, this implies the session is waiting excessively for the block request to be satisfied by other instances. If the average time is less, that implies an excessive amount of block requests are occurring. In both cases the following are suggestions for decreasing wait times:
If a SQL statement is not tuned properly it may request more data blocks than necessary. Review the explain plan for the SQL and tune the statement accordingly.
Reduce the latency on the network interconnect between instances. Excessive latency could be caused by:
Slow network technology is being used for the interconnect or RAC has chosen the incorrect one. To verify the interconnect being used, search the alert.log file for “cluster interconnect”.
Under-configured network settings at the OS level.
Errors occurring on the interconnect.
If there are excessive waits on the remote instance’s buffer cache, increase the DB_CACHE_SIZE parameter on the remote database.
Tune the Lock Management System (LMS) being used to handle lock requests. If the system is heavily loaded or other scheduling delays for the LMS are present, consider increasing the number of LMS processes or increasing the priority so they get more CPU time. The parameter _LM_LMS can be used to set the number of LMS processes.
Distribute the workload across instances to achieve better localized block access.
If the average wait time for another wait event named “global cache null to x'” wait event is low (under 15ms) then you may be experiencing an Oracle statistics bug. This is a problem in the way statistics are reported and does not impact performance. More information can be found in Metalink Note: 243593.1. Also review Metalink Note: 181489.1 for other possible Oracle bugs.
Comment