Why a large number of sessions appear resmgr:become active waiting in Oracle

This problem is found on a database of customer environments.

At that time customers for help, the database state is not normal, connected to the database can not operate normally, and there are times wrong ORA-12537 error.

Ora-12537:tns:connection closed

Cause: "End of file" condition has been reached; Partner has disconnected.

Action:none needed; This is a information message.

There are many reasons for this error, it is difficult to determine the cause of the problem, so through the remote login database to check the status of the system:

Sql> Select COUNT (*) from v$session;

COUNT (*)

----------

996

Sql> Select Event, COUNT (*) from V$session GROUP by event has count (*) > ORDER BY 2 desc;

EVENT COUNT (*)

------------------------- ----------

Resmgr:become Active 968

RDBMS IPC Message 12

The number of sessions in the database is close to 1000, and according to customer descriptions, the number of connections is normally less than 100. Even more strangely, almost all conversations are waiting for the resmgr:become active event.

Depending on the event name, this is a resource-managed wait event, querying for Metalink and finding that if the system resource zone is set to the INTERNAL_QUIESCE state, then all users outside of SYS and system will be in this waiting event, And you can't do anything about it. Metalink Document ID 396970.1 describes this behavior.

However, there are several differences between the present and the problem description. First of all, generally entering the Weeknight_window or Weekend_window window may cause this phenomenon, and the user is in the afternoon when the problem occurs, therefore excluding the switch window causes the resource manager to set the possibility of INTERNAL_QUIESCE. Second, when the wait event is found to be resmgr:become active, the V$rsrc_plan view is queried and the result is null, indicating that the current system does not have a resource management policy set.

Although the cause of the problem is not necessarily that the resource manager that the switch window causes is setting up a resource policy, the problem obviously has a direct relationship with the resource manager. Eygle has experienced similar phenomena before, presumably because the number of connected users may exceed a threshold within Oracle, which causes Oracle to automatically enable resource management policies.

Although the real reason for the problem is not clear, the solution to this problem is not complicated, although the user default Resource_manager_plan parameter is empty, but the resource manager may still be used internally by Oracle, so you can suppress the start of the resource manager by suppressing parameters:

After setting the initialization parameter "_resource_manager_always_on" =false, restart the database, the problem does not occur again.