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.

Oracle10g数据库自动诊断监视工具(ADDM)使用指南

第一章ADDM简介 在Oracle9i及之前,DBA们已经拥有了很多很好用的性能分析工具,比如,tkprof、sql_trace、statspack、set event 10046&10053等...
  • zq9017197
  • zq9017197
  • 2013年11月26日 13:22
  • 2749

oracle命令大全

内容包括三大项: 1.oracle基本操作语句 2.SQLServer基本操作语句 3.各种数据库连接方法&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&...
  • guo_love_peng
  • guo_love_peng
  • 2011年08月09日 14:06
  • 3880

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

问题:1.一个EM的oracle进程占cpu50%左右2.AWR中,The following sql statement executed 2,031,351 times during 2 hour...
  • amethystqi
  • amethystqi
  • 2011年07月18日 14:19
  • 6175

Linux schedule 2、调度算法

2、调度算法linux进程一般分成了实时进程(RT)和普通进程,linux使用sched_class结构来管理不同类型进程的调度算法:rt_sched_class负责实时类进程(SCHED_FIFO/...
  • pwl999
  • pwl999
  • 2017年12月16日 02:23
  • 695

[翻译自mos文章 ]Shutdown Immediate 挂起/活动的processes 阻止shutdown

Shutdown Immediate 挂起/活动的processes 阻止shutdown 原文:Shutdown Immediate Hangs / Active Processes Prev...
  • msdnchina
  • msdnchina
  • 2014年07月09日 23:15
  • 839

Server redirected too many times

1. Problem When I access the REST API, the Tomcat server didn't receive any request, my client code...
  • Derek_Zhang_
  • Derek_Zhang_
  • 2015年08月06日 18:11
  • 2128

ORA-01033 错误(续)

 ORA-01033错误前几天发生的ORA-01033错误现在每次重启数据库都会出现。 所以有必要对这个错误进行一下深入的调查。从一个日文网站找到一个错误的解决方案 ORA-01033 ORACLE ...
  • kdnuggets
  • kdnuggets
  • 2008年01月17日 14:56
  • 2162

SHUTDOWN: Active processes prevent shutdown operation

在使用shutdown immediate关闭数据库时hang住,查看alert 日志,遭遇了SHUTDOWN: Active processes prevent shutdown operation...
  • vindak
  • vindak
  • 2014年01月09日 10:25
  • 1408

Server redirected too many times (20)解决之道

原因:因为跳到一个地址以后,因为没有验证通过,导致没有跳到下一个地址,还是返回当前地址。 这样的结果是,相同地址不断地跳回自己,变成死循环。20次以后,就报这个异常了。。 解决办法:conn.set...
  • u014294166
  • u014294166
  • 2016年02月26日 20:23
  • 867

ValueError: too many values to unpack (expected 2)

a = {'Time': '2017-09-19', 'News': '楚了何人之手。今日,楚天都市报'} for k,v in a: print('%s %s'%(k,v))返回了下面的错误...
  • m0_37693335
  • m0_37693335
  • 2017年09月20日 12:49
  • 7644
收藏助手
不良信息举报
您举报文章:BEGIN EMD_NOTIFICATION.QUEUE_READY(:1, :2, :3); execute too many times in two hours
举报原因:
原因补充:

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