Blog

SYMPTOM

There are more than one million  AQ$_PLSQL_NTFN_xxxxxxxx scheduler jobs in oracle database.  

sql hangs for querying dba_scheduler_jobs.

Please note there are a couple of bugs on this issue, the steps are not necessarily working for every database versions.

SQL>select count(*) from dba_scheduler_jobs;

WORKAROUND

1)set job_queue_processes =0

SQL>alter system set job_queue_processes=0 scope=memory;

2)Stop and Drop all those AQ$_PLSQL_NTFN scheduler jobs:

— add rownum<100000 into below sqls, if the sql hangs.

SQL>select 'EXEC DBMS_SCHEDULER.STOP_JOB ('''|| job_name ||''', force => TRUE);' 
    from dba_scheduler_jobs 
    where job_name like 'AQ$_PLSQL_NTFN_%'
    and state='RUNNING';


SQL> select ' exec dbms_scheduler.drop_job('''||job_name ||''');' 
     from dba_scheduler_jobs 
     where job_name like 'AQ$_PLSQL_NTFN_%';

3)

SQL>select count(*) from sys.aq$AQ_SRVNTFN_TABLE_1 
    where queue='AQ$_AQ_SRVNTFN_TABLE_1_E';

DECLARE 
po dbms_aqadm.aq$_purge_options_t; 
BEGIN 
po.block := TRUE; 
DBMS_AQADM.PURGE_QUEUE_TABLE( 
queue_table => 'SYS.AQ_SRVNTFN_TABLE_1', 
purge_condition => 'qtview.queue = ''AQ$_AQ_SRVNTFN_TABLE_1_E''', 
purge_options => po); 
END; 
/

select count(*) from sys.aq$AQ_SRVNTFN_TABLE_1 
where queue='AQ$_AQ_SRVNTFN_TABLE_1_E';

4) Collect stats.

SQL> exec dbms_stats.gather_fixed_objects_stats;

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_schema_stats ('SYSTEM');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_schema_stats ('SYS');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_dictionary_stats;

PL/SQL procedure successfully completed.

5) Rebuild indexes of DBA_SCHEDULER_JOBS

6) Bounce the instance.

SQL>shutdown immediate ;
SQL>startup;
SQL>alter system set job_queue_processes=1000 scope=memory;