EM 13c: DBSNMP Query Taking Very High CPU While Collecting the Metric "dbjob_status" Information on Target Database (Doc ID 2754249.1)

Last updated on JUNE 27, 2024

Applies to:

Symptoms

EM 13c agent is executing the following query to collect metric "dbjob_status" information.

Due to this, DBSNMP user is consuming very high CPU on the server.

Schema Name: DBSNMP

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))

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.