BEGIN EMD_NOTIFICATION.QUEUE_READY(:1, :2, :3); execute too many times in two hours_emd notification.queue ready(:1, :2,:3);-CSDN博客

问题：

1.一个EM的oracle进程占cpu50%左右

2.AWR中，The following sql statement executed 2,031,351 times during 2 hours:BEGIN EMD_NOTIFICATION.QUEUE_READY(:1, :2, :3); END;

=== ODM Research ===

Note: This is INTERNAL ONLY research. No action should be taken by the customer on this information.
This is research only, and may NOT be applicable to your specific situation.

=== ODM Cause Determination ===
Enterprise manager internally uses AQ mechanism for notification.
The EMD_NOTIFICATION.QUEUE_READY essentially does a blocking dbms_aq.listen call.
Setting the proper timeout should resolve the issue.

=== ODM Action Plan ===

The issue may happen in any of the database version in 10g to 11g while using dbconsole.
The issue could occur even when the database is idle.

BEGIN EMD_NOTIFICATION.QUEUE_READY(:1, :2, :3); END; consumes high CPU usage. Cause Enterprise manager internally uses AQ mechanism for notification.
The EMD_NOTIFICATION.QUEUE_READY essentially does a blocking dbms_aq.listen call.
Setting the proper timeout should resolve the issue.
Solution 1. Login as Oracle software owner :

cd ORACLE_HOME/sysman/admin/emdrep/sql/core/latest/notification

copy notification_pkgbodys.sql npkgbdy.sql => (to avoid to change the original file)

2. Edit: npkgbdy.sql

3. Change the following line :

DBMS_AQ.LISTEN (agents, qtimeout_in, agent);
to :
DBMS_AQ.LISTEN (agents, 1200, agent);

4. Connect to sqlplus as sysman:

5. Execute:

SQL> @ npkgbdy.sql

Please wait for some time and notice that the CPU consumption will go down.

For details:
Problem: DBConsole BEGIN EMD_NOTIFICATION.QUEUE_READY Consuming High CPU (Doc ID 734536.1)

The agent (regardless if its an emagent or part of the dbconsole) is using enqueue & dequeue mechanism for metrics & alerts.

Basically in 10g database and further the database is capable of generating its own alerts. They're so called server-generated alerts. These alerts are generated and put into waiting queues (enqueue process). The agent then comes and picks them up by submitting to such Q (dequeue process).

DBSNMP is the user that performs these operations of dequeue (so reading from the queues). In this case EMD_NOTIFICATION.QUEUE_READY is used for reading alerts. The 'delete' statement doesn't look like something dbconsole generates and by looking in knowledge base it looks like it belongs to Oracle Spatial (see note 1052322.1 & note 1263778.1).