OEM queries consuming high CPU on 11.2 Database

High CPU Utilization from Queries Executed by EM Agent on 11.2 Database.

Findings:
Queries consuming resources:

/* OracleOEM */ DECLARE rept varchar2(4000); rref varchar2(4000); key_enable varchar2(20); autoimpl_enable varchar2(20); key_count number; TYPE data_cursor_type IS REF CURSOR; data_cursor data_cursor_type; BEGIN rref := dbms_report.build_report_reference_varg( 'sqltune', 'auto_summary', 'section', 'task_stats', 'validate', 'n'); rept := dbms_report.get_report(rref); WITH data AS (select xmltype(rept) rept_xml FROM dual) SELECT extractvalue(rept_xml, '/report/summary/statistics/task_stats/info_group/info[position()=1]'), extractvalue(rept_xml, '/report/summary/statistics/task_stats/info_group/info[position()=2]') INTO key_enable, autoimpl_enable FROM data; SELECT count(*) INTO key_count FROM table(xmlsequence(xmltype(rept).extract('//obj_id'))); OPEN data_cursor FOR SELECT key_count, key_enable, autoimpl_enable FROM dual; :1 := data_cursor; END;

SELECT /*+ leading(oe f r l) */ /* STN_REPT_TOP_PROF */ xmlelement( "top_profiles", xmlagg(xmlelement("obj_id", object_id))) FROM (SELECT task_id, exec_name, exec_id, exec_start, object_id, sql_id, parsing_schema, phv, obj_attr8 FROM (SELECT /*+ cardinality(o 58429) leading(e o) */ e.task_id task_id, e.execution_name exec_name, e.execution_id exec_id, e.execution_start exec_start, o.id object_id, o.attr1 sql_id, o.attr3 parsing_schema, to_number(nvl(o.attr5, '0')) phv, nvl(o.attr8, 0) obj_attr8, row_number() over (partition by o.attr1 order by bitand(o.attr7, 32) asc, e.execution_start desc) rn FROM (SELECT e.task_id, e.name execution_name, e.id execution_id, e.exec_start execution_start, e.status status# FROM (SELECT task_id, min(execution_name) keep (dense_rank first order by execution_start) bename, max(executio n_name) keep (dense_rank last order by execution_start) eename, min(execution_start) bestart, max(execution_start) eestart FROM (SELECT task_id, name execution_name, exec_start execution_start FROM wri$_adv_executions WHERE task_id = :tid and name in (:bename, :eename)) GROUP BY task_id) r, wri$_adv_executions e WHERE e.task_id = r.task_id and e.exec_start >= bestart and e.exec_start <= eestart and e.status IN (3, 4) and (bename <> eename OR e.name = bename)) e /* e */, wri$_adv_objects o WHERE o.task_id = e.task_id AND o.exec_name = e.execution_name AND o.type = :sqltyp) WHERE rn = 1) oe /* oe */, wri$_adv_findings f, wri$_adv_recommendations r, wri$_adv_rationale l WHERE oe.task_id = f.task_id AND oe.exec_name = f.exec_name AND oe.object_id = f.obj_id AND f.task_id = r.task_id AND f.exec_name = r.exec_name AND f.id = r.finding_id AND l.task_id = r.task_id AND l.exec_name = r.exec_name AND l.rec_id = r.id AND r.type = :pr ofiletyp AND l.type = :impltyp AND l.attr1 = :acceptdis AND NOT EXISTS (SELECT 1 FROM dba_sql_profiles p WHERE p.task_id = r.task_id AND p.task_exec_name = r.exec_name AND p.task_obj_id = oe.object_id AND p.task_fnd_id = r.finding_id AND p.task_rec_id = r.id)

Workaround:
This is just a workaround.For Solution please contact Oracle Support.
This is affecting only 11.2 target databases monitored by 11.1 or higher EM agents.

1. Backup and edit database.xmlp
cd $ORACLE_HOME/sysman/admin/default_collection
cp database.xmlp database.xmlp_19dec11

2. Comment the block using <!-- at the beginning and --> at the end. The commented block will look as follows:

<!-- commented as workaround
<CollectionItem NAME="key_profiles_collection" UPLOAD_ON_FETCH = "TRUE">
<ValidIf>
<CategoryProp NAME="VersionCategory" CHOICES="11gR2"/>
<CategoryProp NAME="MetricScope" CHOICES="DB"/>
</ValidIf>
<Schedule>
<IntervalSchedule INTERVAL="60" TIME_UNIT="Min"/>
</Schedule>
<MetricColl NAME="key_profiles"/>
</CollectionItem>
-->

3. Restart the agent
if this is a cluster installation, the operation must be performed on all the nodes.
emctl stop dbconsole
emctl start dbconsole