5 Oracle Problem Finishing

Enq:ms–contention Wait Event
The wait event occurs in the materialized view refresh operation, and during the refresh process, the process that is responsible for the refresh frequently locks the materialized view Log table sys.mlog$ to synchronize the materialized view and base table data, during which session attempts to modify the base table on which the materialized view depends , it will also attempt to modify the Materialized View Log table, which will result in waiting enq:ms–contention, because this wait event is a enqueue type, so the more time you wait for DML operations on the base table, the more The waiting times will be longer.
And this process is the basic principle of online redefinition, temporary table is a materialized view, and dbms_redefinition. The sync_interim_table operation is the refresh of the materialized view, during which a large number of enq:ms–contention waits are generated due to the frequent indexing of the materialized view log by the sync operation, resulting in a session of DML operations on the original table.
The following is the official explanation for Ms Enqueue: monitoring Locks During materialized View refreshes (Doc ID 258258.1)
MS is a special type of enqueue it is introduced with Oracle9. This enqueue was obtained during setup on the master table associated with the snapshot being refreshed.
For example:
Resource Holder State
Enq ms-00004071-00000000 300:waiting for ' PL/SQL lock timer '
Enq tx-00380019-00aac396 366:366:is waiting for 300:
Enq tx-00830016-002216ee 375:375:is waiting for 300:
?
And from the SystemState DUMP, you could see something like:
Waiting for ' enq:ms-contention ' blocking sess=0x17c048eaf0 seq=9657 wait_time=0 seconds since wait started=20
It is therefore known that a large number of enq:ms–contention wait events in the database are related to DML operations on the original table, and the severity of the wait event is related to the concurrency of the DML operation.

ORA-600 [529]
DB instance 1 triggers ORA-600 when executing EXPDP [529], [0X700000000035E20], [Memory Queue Subscriber], export success on RAC2 The problem is caused by a bug that was triggered when the EXPDP was executed 22022679 (Base Bug 16619152) resulted in the creation of the "Memory Queue Subscriber" sub-latch, which failed because the maximum limit of the child latch was reached. Workaround (1) Oracle maintains the creation and release of sub-latch at the instance level, and the temporary solution is to clean up the problematic latch by restarting the instance. (2) Apply hotfix 16619152.

Search MOS repositories based on problem and call stack, can match to bug 22022679:ora-600[529] [0x14b27d620] [MEMORY QUEUE subscriber] occured executing EXPD P.
The base bug for Bug22022679 is 16619152, Patch is present on AIX platform, 11.2.0.3 database version.
Base BUG 16619152 Describes the problem, that is, when the KSL layer creates or reclaims child latch, the parent latch is forced to hold the parent KQQBT when the KGQM and latch layers are created or reclaimed latch, This can lead to this problem. According to the function called by call stack, the latch of the KGQM layer is called when executing EXPDP.

5 Oracle Problem Finishing