Dear All,
My database is 11.2.0.4 64-Bit on windows 2012 64-Bit platform.
In some scenario, when DBMS_ALERT.SIGNAL() is issued it updates DBMS_ALERT_INFO table thousands of time within a fraction of second.
This heavy DML generates lots of redo and the LGWR process, while archiving the redo files consumes 100% of CPU. It also block other sessions and the system hangs.
When the incident happens, lots of archive log files are generated. If I log mine the archive logs I can see thousands of update queries only updating DBMS_ALERT_INFO.
Even when the database is normal and CPU is OK, DBMS_ALERT_INFO is locked by at least 3-4 sessions. Although when i query the current running queries on my database none of the session is querying or writing to this table. The lock type is Row-X (SX) and it is not blocking other sessions though, but i feel it strange.
Have tried the following: Please add if something is missing that can be tried to get rid of this situation.
1. increase initrans parameter for the table DBMS_ALERT_INFO
2. Add new index DBMS_ALERT_INFO (SID)
3. Add commit in between if multiple DBMS_ALERT.SIGNAL are issued.
4. Use DBMS.REMOVEALL at the end of the same code where signal is registered.
5. Trunacte table DBMS_ALERT_INFO on daily basis, although it have only 40 records.
Kindly help if we are missing something.
Regards,