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
  • 2721

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

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

Poco 的通知和事件

1,Notification // POCOEvent.cpp : 定义控制台应用程序的入口点。 // #include "stdafx.h" #include "Poco/Notifi...
  • haifengzhilian
  • haifengzhilian
  • 2015年12月04日 14:32
  • 1720

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
  • 6041

goldengate 故障及解决方法汇总

1.数据库thread与sid不对应问题描述:GGS ERROR  500  抽取进程extu1起不来,提示找不到thread2的归档(没有保留哪天具体的报错信息)问题分析:rac数据库曾经删除又重建...
  • amethystqi
  • amethystqi
  • 2011年07月11日 16:36
  • 11541

java.net.ProtocolException: Server redirected too many times (20)

在做网页爬虫的时候,之前一切正常,后来可能是网站做了cookie校验处理,报异常:java.net.ProtocolException: Server redirected too many time...
  • qq_34670293
  • qq_34670293
  • 2017年09月20日 15:03
  • 547

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
  • 1983

goldengate日常管理命令

Oracle GoldenGate的常用命令 1.skipstart repu1 SKIPTRANSACTION2.查看trailinfo exttrail * 3.查看oracle thread和g...
  • amethystqi
  • amethystqi
  • 2011年07月11日 15:10
  • 3996

SHUTDOWN: Active processes prevent shutdown operation

在使用shutdown immediate关闭数据库时hang住,查看alert 日志,遭遇了SHUTDOWN: Active processes prevent shutdown operation...
  • robinson_0612
  • robinson_0612
  • 2013年07月25日 11:56
  • 4472

too many arguments to function call, expected 0, have 3 错误‘解决办法

引用MJRefresh报错的解决办法: too many arguments to function call, expected 0, have 3  这是编译的错 Build Setting--...
  • iOS_Pop
  • iOS_Pop
  • 2015年09月01日 14:56
  • 1125
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:BEGIN EMD_NOTIFICATION.QUEUE_READY(:1, :2, :3); execute too many times in two hours
举报原因:
原因补充:

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