Enterprise Manager Database Control: High Number of SYS or DBSNMP Sessions Selecting from gv$buffered_subscribers Affecting Database Performance or Contributing to Hung Database
Last updated on FEBRUARY 02, 2018
Applies to:Enterprise Manager for Oracle Database - Version 184.108.40.206 to 220.127.116.11 [Release 11.1 to 11.2]
Information in this document applies to any platform.
Grid Control 18.104.22.168
Grid Control Agent 22.214.171.124
Target Database 11.2.x
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:
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:
- Active sessions wait events: library cache lock
- High number of cursor: pin S wait on X wait events.
The database is being monitored by Grid Control via an 126.96.36.199 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.
Sign In with your My Oracle Support account
Don't have a My Oracle Support account? Click to get started
My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms