AUTO_SPACE_ADVISOR_JOB执行期间报Global Enqueue Services Deadlock detected

raysuen

请问刘大，下面的报错是为什么，如何规避和解决？
------------------
alter:

Wed Sep 07 22:36:25 2016
Global Enqueue Services Deadlock detected. More info in file
/u01/app/oracle/diag/rdbms/kcptdg/kcptdg2/trace/kcptdg2_lmd0_29234.trc.
Wed Sep 07 22:45:12 2016
Global Enqueue Services Deadlock detected. More info in file
/u01/app/oracle/diag/rdbms/kcptdg/kcptdg2/trace/kcptdg2_j000_26996.trc.
Wed Sep 07 22:45:12 2016
Dumping diagnostic data in directory=[cdmp_20160907224512], requested by (instance=2, osid=26996 (J000)), summary=[abnormal process termination].
Wed Sep 07 22:47:00 2016
Global Enqueue Services Deadlock detected. More info in file
/u01/app/oracle/diag/rdbms/kcptdg/kcptdg2/trace/kcptdg2_j000_26996.trc.
Wed Sep 07 22:47:00 2016
Dumping diagnostic data in directory=[cdmp_20160907224700], requested by (instance=2, osid=26996 (J000)), summary=[abnormal process termination].
Wed Sep 07 22:55:53 2016
Global Enqueue Services Deadlock detected. More info in file
/u01/app/oracle/diag/rdbms/kcptdg/kcptdg2/trace/kcptdg2_lmd0_29234.trc.
Wed Sep 07 23:01:11 2016
Global Enqueue Services Deadlock detected. More info in file
/u01/app/oracle/diag/rdbms/kcptdg/kcptdg2/trace/kcptdg2_lmd0_29234.trc.
Wed Sep 07 23:01:22 2016
Global Enqueue Services Deadlock detected. More info in file
/u01/app/oracle/diag/rdbms/kcptdg/kcptdg2/trace/kcptdg2_j000_26996.trc.
Wed Sep 07 23:01:22 2016
Dumping diagnostic data in directory=[cdmp_20160907230122], requested by (instance=2, osid=26996 (J000)), summary=[abnormal process termination].
Wed Sep 07 23:03:05 2016
Global Enqueue Services Deadlock detected. More info in file
/u01/app/oracle/diag/rdbms/kcptdg/kcptdg2/trace/kcptdg2_j000_26996.trc.
Wed Sep 07 23:03:05 2016
Dumping diagnostic data in directory=[cdmp_20160907230305], requested by (instance=2, osid=26996 (J000)), summary=[abnormal process termination].
Wed Sep 07 23:03:32 2016
Thread 2 advanced to log sequence 190382 (LGWR switch)
  Current log# 16 seq# 190382 mem# 0: +DATA/kcptdg/redo06b.dbf
Wed Sep 07 23:03:42 2016
Archived Log entry 429905 added for thread 2 sequence 190381 ID 0x3103a67a dest 1:
Wed Sep 07 23:03:58 2016
Errors in file /u01/app/oracle/diag/rdbms/kcptdg/kcptdg2/trace/kcptdg2_j000_26996.trc:
ORA-12012: error on auto execute of job "SYS"."AUTO_SPACE_ADVISOR_JOB"
ORA-20000: ORU-10027: buffer overflow, limit of 20000 bytes
ORA-06512: at "SYS.DBMS_ADVISOR", line 201
ORA-06512: at "SYS.DBMS_SPACE", line 2465
ORA-06512: at "SYS.DBMS_SPACE", line 2538

---------
trace:

Trace file /u01/app/oracle/diag/rdbms/kcptdg/kcptdg2/trace/kcptdg2_j000_26996.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1
System name:    Linux
Node name:      kcptdb2
Release:        2.6.18-308.el5
Version:        #1 SMP Tue Feb 21 20:06:06 EST 2012
Machine:        x86_64
Instance name: kcptdg2
Redo thread mounted by this instance: 2
Oracle process number: 181
Unix process pid: 26996, image: oracle@kcptdb2 (J000)


*** 2016-09-07 22:02:34.380
*** SESSION ID:(4971.2573) 2016-09-07 22:02:34.380
*** CLIENT ID:() 2016-09-07 22:02:34.380
*** SERVICE NAME:(SYS$USERS) 2016-09-07 22:02:34.380
*** MODULE NAME:(DBMS_SCHEDULER) 2016-09-07 22:02:34.380
*** ACTION NAME:(AUTO_SPACE_ADVISOR_JOB) 2016-09-07 22:02:34.380

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

DEADLOCK DETECTED

  Performing diagnostic dump and signaling ORA-00060

  Complete deadlock information is located in the trace file of process (pid: 10, osid: 29230, DIA0)
    trace file: /u01/app/oracle/diag/rdbms/kcptdg/kcptdg2/trace/kcptdg2_dia0_29230.trc

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

*** 2016-09-07 22:02:34.380
-------------------------------------------------------------------------------
HUNG PROCESS DIAGNOSTIC DUMP BEGIN:

  dump requested by process (pid: 10, osid: 29230, DIA0)
    trace file: /u01/app/oracle/diag/rdbms/kcptdg/kcptdg2/trace/kcptdg2_dia0_29230.trc
-------------------------------------------------------------------------------
----- Current SQL Statement for this session (sql_id=22w4adu41x3fm) -----
drop table "KCPT".DBMS_TABCOMP_TEMP_UNCMP purge
----- PL/SQL Stack -----
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
0xb4ed60960       790  package body SYS.PRVT_COMPRESSION
0xb4ed60960       774  package body SYS.PRVT_COMPRESSION
0xb3f475298       214  package body SYS.DBMS_COMPRESSION
0xb37720b80       812  SYS.WRI$_ADV_OBJSPACE_TREND_T
0xb37720b80      1953  SYS.WRI$_ADV_OBJSPACE_TREND_T
0xb47a7f970       587  package body SYS.PRVT_ADVISOR
0xb47a7f970      2655  package body SYS.PRVT_ADVISOR
0xb3fa2b7d8       241  package body SYS.DBMS_ADVISOR
0xb3fa2b7d8       196  package body SYS.DBMS_ADVISOR
0xb53759340      2465  package body SYS.DBMS_SPACE
0xb53759340      2538  package body SYS.DBMS_SPACE

Liu Maclean(刘相兵

        Bug 15975838 - ORA-60 between MMON slave and auto space advisor (Doc ID 15975838.8)


Bug 15975838  ORA-60 between MMON slave and auto space advisor

This note gives a brief overview of bug 15975838.
The content was last updated on: 29-NOV-2013
Click here for details of each of the sections below.
Affects:

Product (Component)        Oracle Server (Rdbms)
Range of versions believed to be affected        (Not specified)
Versions confirmed as being affected       
11.2.0.3
Platforms affected        Generic (all / most platforms affected)
Fixed:

The fix for 15975838 is first included in       
12.2 (Future Release)
12.1.0.2 (Future Patch Set)

Interim patches may be available for earlier versions - click here to check.
Symptoms:

Related To:

Deadlock
Error May Occur
ORA-60
WRI$_SEGADV_OBJLIST
Description

An ORA-60 deadlock can occur in Auto Space Advisor.

Rediscovery Notes:
  If you observe "TX" deadlocks with SYS.WRI$_SEGADV_OBJLIST involved, then you have
  likely hit this bug.
  
Workaround:  
Deactivate AUTO_SPACE_ADVISOR_JOB by
EXECUTE DBMS_SCHEDULER.DISABLE('AUTO_SPACE_ADVISOR_JOB');