Troubleshooting these are too many scheduler jobs(owner sys) name like KWQICPOSTMSGDEL_nn in DB 11g

by

I faced a very interesting question today, An oracle database 11.2.0.3 RAC database ON hpux one had to many scheduler jobs , And the job’s owner is sys, All jobs name all like ‘KWQICPOSTMSGDEL_’, All these jobs has no start date as well no interval.

SQL> 
 select
     job_name      jobs_job_name
   , program_name  jobs_program_name
   , state         jobs_state
   , to_char(start_date, 'YYYY-MM-DD HH24:MI') start_date
   , to_char(next_run_date, 'YYYY-MM-DD HH24:MI') next_run_date
   , enabled
 from
     dba_scheduler_jobs
  
...
KWQICPOSTMSGDEL_1411382598    SCHEDULED       TRUE
KWQICPOSTMSGDEL_1411382393    SCHEDULED       TRUE
KWQICPOSTMSGDEL_1411381387    SCHEDULED       TRUE
KWQICPOSTMSGDEL_1411381181    SCHEDULED       TRUE

SQL> select count(*) from dba_scheduler_jobs;

  COUNT(*)
----------
    103236

SQL> select OWNER,count(*) from dba_scheduler_jobs group by owner;

OWNER                                                          COUNT(*)
------------------------------------------------------------ ----------
DBMT                                                                  2
ORACLE_OCM                                                            4
SYS                                                              103230

TIP:
KWQICPOSTMSGDEL scheduler jobs. This is an AQ feature posting a scheduler job to clear orphan queue messages. The job is dropped at the end of the execution and therefore there is no information about in the DBMS_SCHEDULER views, the job_queue_processes has some impact on the dbms_scheduler jobs.

SQL> @p job

NAME                                     VALUE
---------------------------------------- ----------------------------------------
job_queue_processes                      0
_job_queue_interval                      5
_optimizer_autostats_job                 TRUE
_srvntfn_jobsubmit_interval              3
_srvntfn_max_concurrent_jobs             20
_srvntfn_job_deq_timeout                 60

6 rows selected.

Solution
DOC 1360526.1 ,set job_queue_processes to appropriate value .

SQL> alter system set job_queue_processes=10;
System altered.
SQL>@p job

NAME VALUE
—————————————- —————————————-
job_queue_processes 10
_job_queue_interval 5
_optimizer_autostats_job TRUE
_srvntfn_jobsubmit_interval 3
_srvntfn_max_concurrent_jobs 20
_srvntfn_job_deq_timeout 60

#wait a moments

SQL> select OWNER,count(*) from dba_scheduler_jobs group by owner;
OWNER COUNT(*)
———————————————————— ————————-
ORACLE_OCM 2
SYS 3252

SQL> /
OWNER COUNT(*)
———————————————————— ————————-
ORACLE_OCM 2
SYS 2577

SQL> select OWNER,count(*) from dba_scheduler_jobs group by owner;

OWNER COUNT(*)
———————————————————— ———-
ORACLE_OCM 2
SYS 12