暂无图片
暂无图片
暂无图片
暂无图片
暂无图片

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

张维照 2019-05-31
3018

问题描述

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


「喜欢这篇文章,您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容,转载时必须标注文章的来源(墨天轮),文章链接,文章作者等基本信息,否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容,欢迎发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

评论