SYS.SYS$SERVICE_METRICS_TAB Size Grows Big and Affects AQ Performance

SYMPTOMS

1)Table SYS.SYS$SERVICE_METRICS_TAB keeps growing everyday.

SQL> select count(*) from SYS$SERVICE_METRICS_TAB;

COUNT(*)
----------
46946104

SQL> SELECT TRUNC(enq_time), COUNT(*)
  FROM sys$service_metrics_tab
 GROUP BY TRUNC(enq_time)
 ORDER BY 1 ;

TRUNC(ENQ   COUNT(*)
--------- ----------
07-JAN-19      41052
08-JAN-19      63338
09-JAN-19      63096
10-JAN-19      63338
11-JAN-19      62128
12-JAN-19      63338
13-JAN-19      63316
14-JAN-19      63316
...
..
.

2) Oracle background process ora_cjq — Job scheduler coordinator slows and hangs when running below SQL with “enq: TQ – DDL contention” contention.

begin dbms_aqadm_sys.remove_subscriber (
                            queue_name => 'SYS.SYS$SERVICE_METRICS', 
                 subscriber => sys.aq$_agent(:1, NULL, NULL)); end;

3) Oracle background process Qnnn: Queue cleanup processes hang with “library cache pin” contention.

4) oraagent.bin@host program also has “library cache pin” contention”.

5) Manageability Monitor Process MMON background slave process ora_m000 has “library cache lock” contention.