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 22.214.171.124 to 126.96.36.199 [Release 11.1 to 11.2]
Information in this document applies to any platform.
Grid Control 188.8.131.52
Grid Control Agent 184.108.40.206
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 220.127.116.11 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.
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