Too Many Refreshcbostats_job jobs

Products

• Data Loss Prevention Enforce
• Data Loss Prevention

Issue/Introduction

Database performance is running slow and there may be lack of memory issues (PGA) relative to the database.

In mature databases that have gone through multiple DLP upgrades (not database upgrades), the refreshcbostats_job may be running redundantly, that is to say that multiple copies may be running at the same time.  This takes up a great deal of Oracle allocated memory and system resources.

Run the following script from SQLPlus as protect (or the schema owner) to find out how many are running.  There should be only one "refreshcbostat_job" entry under the WHAT column:

select job,last_date, broken, what from dba_jobs;

There is no need to worry about jobs that have a "Y" in the BROKEN column; these are not running.  The same removal instructions apply to these as well as the "unbroken" jobs.

Resolution

There should be one and only one refreshcbostats_job running.  If there are multiples running, select one job to leave running and remove the rest.  The remaining job should not be broken.

To remove a job, type the following from SQLPlus as protect (or the schema owner):

exec dbms_job.remove(<the job number>);

For example:

       JOB LAST_DATE B WHAT
---------- --------- - ------------------------------------------
         1 05-DEC-11 N refreshCBOStats_Job(JOB,NEXT_DATE);
         2 05-DEC-11 N refreshCBOStats_Job(JOB,NEXT_DATE);
        48 05-DEC-11 N refreshCBOStats_Job(JOB,NEXT_DATE);
        50 05-DEC-11 N refreshCBOStats_Job(JOB,NEXT_DATE);

exec dbms_job.remove(2);

exec dbms_job.remove(48);

exec dbms_job.remove(50);

 JOB LAST_DATE B WHAT
---------- --------- - ------------------------------------------
1 05-DEC-11 N refreshCBOStats_Job(JOB,NEXT_DATE);