Resmgr: become active wait for a large number of sessions in Oracle technology

This problem is found in a database in the customer environment.

At that time, the customer asked for help, the database status is not normal, connected to the database can not operate normally, and sometimes error ORA-12537 error.

ORA-12537: TNS: connection closed

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

Action: None needed; this is an information message.

There are many causes of this error. It is difficult to determine the cause of the problem. Therefore, you can remotely log on to the database to check the system status:

SQL> select count (*) from v $ session;

COUNT (*)

----------

996

SQL> select event, count (*) from v $ session group by event having count (*)> 10 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. According to the customer's description, under normal circumstances, the number of connections is less than 100. Even more strange, almost all sessions are waiting for the resmgr: become active event.

According to the event name, this is a wait event related to the resource management area. After querying metalink, it is found that if the system resource management area is set to the status of INTERNAL_QUIESCE, all users other than SYS and SYSTEM will be in this waiting event and cannot perform any operations. The Metalink Document ID 396970.1 describes this phenomenon.

However, there are several differences with the problem description. First, this problem may occur when you enter the WEEKNIGHT_WINDOW or WEEKEND_WINDOW window, and the user encounters a problem in the afternoon. Therefore, the possibility of setting the resource manager to INTERNAL_QUIESCE due to the switching window is ruled out. Second, when the wait event is found to be resmgr: become active, the v $ rsrc_plan view is queried. The result is null, indicating that the current system has not set a resource management policy.

Although the cause of the problem is not necessarily that the resource manager has set a resource policy due to the switch window, the problem is obviously directly related to the resource manager. Eygle has previously encountered a similar phenomenon. According to his estimation, it may be because the number of connected users is too large, which exceeds a certain threshold in Oracle. As a result, Oracle automatically enables the resource management policy.

Although the real cause of the problem is still unclear, it is not complicated to solve the problem. Although the resource_manager_plan parameter is empty by default, the resource manager may still be automatically used in Oracle, therefore, you can use implicit parameters to disable Resource Manager startup:

Restart the database after setting the initialization parameter "_ resource_manager_always_on" = false.