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

Last updated on MARCH 23, 2017

Applies to:

Enterprise Manager for Oracle Database - Version 11.1.0.1 to 11.1.0.1 [Release 11.1]
Information in this document applies to any platform.

Symptoms

One or more of the following queries show high CPU utilization in Enterprise Manager (EM) 11g Grid Control for a target 11.2 database. This can happen on single instances or RAC (Cluster) databases.

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_i
d,
parsing_schema, phv, obj_attr8
FROM (SELECT /*+ cardinality(o 30064) 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 execut
ion_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(execution_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 = :profiletyp 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)

 

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;

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms