Environment details:
==========================================
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /data1/app/oracle/product/11.2.0/dbhome_1
System name: Linux
Node name: tango.dmotorworks.com.au
Release: 2.6.32-200.13.1.el5uek
Version: #1 SMP Wed Jul 27 21:02:33 EDT 2011
Machine: x86_64
==========================================
Hello everyone,
I am using the standard oracle queue defined for scheduler jobs to monitor scheduler job running statuses.
Basically all scheduler jobs those to be looked after raise notifications (JOB_STARTED, JOB_FAILED, JOB_FINISHED and so on) and I defined subscribers those pull the event messages from the queue and react accordingly.
A month ago noticed strange waitings in Enterprize manager charts identified as "Others" and after investigations found that table SYS.SCHEDULER$_EVENT_QTAB grew more than 500M in size. I tried to figure out what was happening and found that I had more than 1M messages in the queue in the EXPIRED status.
I couldn't workout why the expired messages are not cleared over time by themselves (checked jobs(oracleDB_j00X_<PID>.trc) logs, job coordinator(oracleDB_q00X_<PID>.trc) process log file & alert log but couldn't find much that could point to the cause).
Found some pages on support.oracle.com (i.e. Doc ID 1271426.1) without much on what can be done and eventually decided to go truncate the queue table.
What I did was:
1. Disabled and made sure no scheduler job is running
2. truncated SYS.SCHEDULER$_EVENT_QTAB using dbms_aqadm.purge_queue_table procedure
3. reenabled all jobs
Once I cleaned the queue Oracle db started generating trace files for every scheduler jobs with following line at the end
============================
purge queue_table SYS.SCHEDULER$_EVENT_QTAB
============================
So now I am having thousands trace files generated every day.
The other thing is the queue (AQ$_SCHEDULER$_EVENT_QTAB_E) shows that the number of expired messages is still more than 1M messages while the table SYS.SCHEDULER$_EVENT_QTAB size dropped to zero once I truncated it.
Please advise what can I do to solve this issue: stop generating the trace files and bring the queue message statistics in order
Thanks a lot in advance,
Akhmed