Inaccessible database because of WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK!

Database was hanging with the error "WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK!" in the alert.log. There was only one solution in that time: kill the database and start it again.

I have noticed some more errors in the same time in the trace files:

ORA-00020: maximum number of processes 500 exceeded WARNING:Could not lower the asynch I/O limit to 160 for SQL direct I/O. It is set to -1.

Cause:

The hanging is due to the shared pool growth. While this is occurring all processes will wait till the SGA grows or shrinks. In this case it is growing.

Solution:

Following parameter can be set dynamically:

alter system set "_memory_broker_stat_interval"=999;

This will increase the time between resize to at least 999 seconds and thereby reducing the number of resize operations. what also helps is to switch off ASMM (Automatic shared memory management).

WARNING:Could not lower the asynch I/O limit to 160 for SQL direct I/O. It is set to -1.
Can be ignored based on the Oracle note.

Bug 9772888 - Needless "WARNING:Could not lower the asynch I/O limit to .. for SQL direct I/O It is set to -1" messages (Doc ID 9772888.8) Description Trace files may be seen with warning of the form: WARNING:Could not lower the asynch I/O limit to 160 for SQL direct I/O. It is set to -1 This bug is specifically for the case of messages showing "It is set to -1" in the message. Workaround Ignore the message - it is spurious and of no value. The fix only hides the message so that it does not produce "noise"