Resolved: High CPU Load on DB Server

 Hi Everyone, 

Today we have encountered interesting issue, CPU consumption went very high on one of the production box. OEM graph looked with high Average Active Sessions. Load Average was reading "load average: 52.96, 52.36, 51.37" Allocated CPU count on the box was 4.

By Reviewing the Top modules and Top Actions, I came to the conclusion that it is coming out of OEM monitoring. ah!! monitoring tool causing DB performance issues? yes. 

SQL that was causing load.

SELECT TO_CHAR(END_TIME, 'YYYY-MM-DD HH24:MI:SS'), ROUND((SYSDAT

E-END_TIME)*24*60,2), INPUT_BYTES, SESSION_KEY, SESSION_RECID, S

ESSION_STAMP FROM (SELECT END_TIME, INPUT_BYTES, SESSION_KEY, SE

SSION_RECID, SESSION_STAMP FROM V$RMAN_BACKUP_JOB_DETAILS WHERE

STATUS LIKE 'COMPLETED%' AND INPUT_TYPE='ARCHIVELOG' AND OUTPUT_

DEVICE_TYPE IN ('SBT_TAPE', '*') ORDER BY END_TIME DESC) WHERE R

OWNUM = 1

I found some relevant notes as solution. Below is one of the note that talked about the way to disable metric collection to resolve the issue. Another option was to disable on GUI. 

High CPU Utilization from Queries Executed by the Enterprise Manager 11g Grid Control Agent on 11.2 Target Database (Doc ID 1386774.1)

I went ahead and disabled the Metric Collection and disabled template Overriding.

Time Since Last Successful Full Backup (hours)

Time Since Last Successful Archived Log Backup (minutes)

All sorted out after disabling those metric collection. I had to kill the current sessions that were connected as DBSNMP user causing the load.