My Oracle Support Banner

Enterprise Manager Database Control: High Number of SYS or DBSNMP Sessions Selecting from gv$buffered_subscribers Affecting Database Performance or Contributing to Hung Database (Doc ID 1372977.1)

Last updated on NOVEMBER 16, 2022

Applies to:

Enterprise Manager for Oracle Database - Version 11.1.0.1 to 11.2.0.3 [Release 11.1 to 11.2]
Information in this document applies to any platform.
Grid Control 11.1.0.1
Grid Control Agent 11.1.0.1
Target Database 11.2.x



Symptoms

A target 11.2.0.x Database is either hung or exhibiting poor performance. There are a high number of SYS or DBSNMP sessions in the database, and a high number of blocking sessions.  These sessions have the same SQL_id associated with the sql listed below. Checks against database views v$session or gv$session (if RAC) shows that the large number of sessions in the database connected as 'SYS'  or 'DBSNMP' are running the following sql:

select propagation_name, 'BUFFERED', num_msgs ready, 0 from
gv$buffered_subscribers b,dba_propagation p, dba_queues q, dba_queue_tables t
where b.subscriber_name = p.propagation_name and b.subscriber_address =
p.destination_dblink and b.queue_schema = p.source_queue_owner and
b.queue_name = p.sour
ce_queue_name and p.source_queue_name = q.name and p.source_queue_owner =
q.owner and q.queue_table = t.queue_table and b.inst_id=t.owner_instance


The following may also be observed:


The database is being monitored by Grid Control via an 11.1.0.1 management agent. The database has not been configured for streams.    The user that owns these sessions is likely to be 'DBSNMP' or 'SYS'.   This is the user that has been configured as the "monitor username"  when configuring the database for Grid Control.  In most cases this will be 'DBSNMP' but may be the 'SYS' user (or a user with 'sysdba' rights) if for example in a dataguard setup where a standby may be monitored.

 

 

Cause

To view full details, sign in with your My Oracle Support account.

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

In this Document
Symptoms
Cause
Solution
References

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.