Oracle Enterprise Manager “dbjob_status” ile ilgili bilgileri toplamak için aşağıdaki sqlid ‘ye sahip ilgili sql çalıştırmaktadır.
Bu durumda DBSNMP kullanıcısı veri tabanında bu işi yaparken yüksek CPU tüketimine sebep oluyor.
SQL ID : 3hdkutq4krg4c
SQL Text : with last_run as
(
SELECT all_runs.OWNER,
all_runs.JOB_NAME,
all_runs.STATUS
FROM DBA_SCHEDULER_JOB_RUN_DETAILS all_runs,
(SELECT sub.OWNER,
sub.JOB_NAME,
MAX(sub.ACTUAL_START_DATE) AS START_DATE
FROM DBA_SCHEDULER_JOB_RUN_DETAILS sub
WHERE sub.job_name in (SELECT job_name FROM
DBA_SCHEDULER_JOB_RUN_DETAILS where status = 'FAILED')
GROUP BY sub.OWNER,JOB_NAME) latest_runs
WHERE all_runs.status = 'FAILED'
AND all_runs.OWNER= latest_runs.OWNER
AND all_runs.JOB_NAME= latest_runs.JOB_NAME
AND all_runs.ACTUAL_START_DATE=latest_runs.START_DATE
)
SELECT NVL(SUM(broken),0),
NVL(SUM(failed),0)
FROM (SELECT DECODE(broken, 'N', 0, 1) broken,
DECODE(NVL(failures,0), 0, 0, 1) failed
FROM dba_jobs
UNION ALL
SELECT DECODE(STATE,'BROKEN',1,0) broken,
DECODE(STATUS ,'FAILED',DECODE(STATE,'BROKEN',0,'DISABLED',0,1),0
) failed
FROM (SELECT all_jobs.OWNER,
all_jobs.JOB_NAME,
last_run.STATUS,
all_jobs.STATE
FROM last_run,
DBA_SCHEDULER_JOBS all_jobs
WHERE last_run.OWNER=all_jobs.OWNER
AND last_run.JOB_NAME=all_jobs.JOB_NAME))
Örnek bir görüntüsü aşağıda mevcut.
Bu sorunu aşağıdaki gibi çözebiliriz.
- SCHEDULER$_EVENT_LOG tablosunu temizleyebiliriz.
SQL>exec DBMS_SCHEDULER.PURGE_LOG();- Yada “dbjob_status” metriğini aşağıdaki adımları izleyerek devre dışı bırakabiliriz.
From EM Console, disable this metric collection ‘Database Job Status’ which consists of ‘Broken Job Count’ and ‘Failed Job Count’ metrics.
Target Database > Monitoring > Metric and Collection Settings
Click on the collection schedule and disable it
Save the changes.
