Applies to:
Oracle Database - Enterprise Edition - Version 11.2.0.4 and later
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Cloud Exadata Service - Version N/A and later
Information in this document applies to any platform.
Symptoms
Periodic errors "ORA-00060" deadlocks while the daily "auto optimizer stats collection" maintenance task is running to update statistics:
ORA-12012: error on auto execute of job "SYS"."ORA$AT_OS_OPT_SY_7589"
ORA-00060: deadlock detected while waiting for resource
ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 148
ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 7664
ORA-06512: at "SYS.DBMS_STATS", line 28795
ORA-06512: at "SYS.DBMS_STATS", line 31599
The following recursive SQL may be involved in the deadlock
delete /*+ dynamic_sampling(4) */ from sys.wri$_optstat_histgrm_history where savtime < :1 and rownum <= NVL(:2, rownum)
Cause
This is due to following bug:
unpublished internal Bug 19460897 - AUTO-PURGE SLAVE ACTION ENCOUNTERS ENQ: TX - ROW LOCK CONTENTION
Which is closed as a duplicate of:
Bug 18608261 - ORA-12751 FROM MMON AUTO-PURGE
Solution
1. The bug is fixed in 12.2.0.1 or later.
2. Download and apply Patch 18608261 for the version and platform where the issue is observed.
