BEGIN EMD_NOTIFICATION.QUEUE_READY(:1, :2, :3); execute too many times in two hours

原创 2011年07月18日 14:19:02
 

问题:

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


Hi,if it is a RAC,shoud I change and run npkgbdy.sql on one node or on both?
Please it should be change at Both nodes.

相关文章推荐

begin UML in 24 hours

  • 2009年05月26日 17:50
  • 123KB
  • 下载

EXECUTE 后的事务计数指示 BEGIN 和 COMMIT 语句的数目不匹配。上一计数 = 0,当前计数 = 1。

执行事务报错: Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT stateme...

Learn Photoshop Elements In Under 2 Hours

. Don't Waste Another Second Trying to Learn Photoshop Elements On Your Own...   Dear Frie...

OBIEE Tips #5: Too many values in single select prompt

OBIEE’s single select prompt can’t display by default more than 256 values (It displays a “Too Many ...

nginx报错accept4() failed (23: Too many open files in system)

今天系统进不去了,用ssh连接服务器也非常慢,负载均衡显示后端连接异常,但是通过telnet命令查看后端端口是正常的,用其他的服务器telnet这台服务器的端口,不通,感觉很奇怪。首先自己先写了一个测...

亚马逊服务器(Linux 3.13.6)A very large skb can span too many pages (more than 16) to be put in the driver

Brendan Gregg's Blog  home Linux perf Rides the Rocket 11 Sep 2014 Riding the rocket ...

【Hibernate错误解决办法】Could not execute JDBC batch update; Data too long for column 'name' at row 1

刚开始学Hibernate
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:BEGIN EMD_NOTIFICATION.QUEUE_READY(:1, :2, :3); execute too many times in two hours
举报原因:
原因补充:

(最多只允许输入30个字)