How many open connections are made by database cartridges to the database and Operating System? (4309987)

Oracle, DB2, and SQL Server agents configured for remote monitoring instances. What is a rough estimate on the overhead of these agents in terms of CPU and memory utilization on the monitored instances?

How many constant and dynamic connection the DB cartridge uses against the monitored instance?

What is the number of connection used by the DB cartridge for OS collections?

There are too many OS connections opened when multiple instances reside on the same server

The DB cartridge uses JDBC connection in order to address the monitored instance, and OS connections in order to retrieve OS metrics from the monitored host.
Each connection serves a collection, where the collection runs by its preset frequency.

JDBC Connections

For the DB2 and Oracle cartridges, 6 constant connections will always be opened and maintained by the DB cartridge 

For the SQL Server cartridge, 7 constant connections will always be opened and maintained by the DB cartridge 

Each connection serves a collection, and the connections are re-used and recycled by the DB cartridge.

DB cartridge re-use connection for call-back (on demand collections that are triggered when the user is actively viewing certain screens) collections.

An exception in the number of connections can be seen if RMI collection is required.

• For example, when the user actively enters the Session screen.
• The DB cartridge will open a new connection to answer the RMI request.

In a scenario where multiple users are requesting RMI collections, more than six/Seven (6/7) open connections can be observed.

OS connections

For Windows and UNIX platforms, the database cartridge will request a WMI/SSH connection from the FGLAM. The connections are maintained by the FGLAM.

• WMI connections:  only runs one command in one connection. Thus means there will be several connections when running with parallel commands running. 
• SSH connections: for ssh connection , it can support multiple sessions in one connection (the session number is configurable in the ssh configuration), so for one host and one purpose there will only one connection to the target host. If the session number is more than allowed, there will be an error in the FGLAM log.

How does the DB cartridge recycle open connections?

For the non-constant connections, the DB cartridge mechanism will end the connection life cycle should the connection has been idle for more than 10 minutes.

The constant connections will keep on running until the Agent is deactivated or the FglAM itself is shutdown.

The idle connections (which are not constant) will be closed after 10 minutes (controlled by the property in Agent Status Properties).

Multiple instances over the same server

That scenario means that all agents will open OS connections to the same host and will impact on the total open connections number over the host.
since all agents address the same host, a single agent can collect the OS data on behalf of the other agents.
The expected open connections would be:

Number of agents * 1 + 2 constant OS connection for the collecting agent)

for example, 15 agents that monitor 15 instances on the same host will open:
15*1+2 =17 open OS connections.

How is CPU and memory used in Connection Pooling?

The database agent uses connection pooling to maintain constant connections to the database with the connections being reused and recycles by the cartridge. This is done to prevent many connections from being left hanging on the remote server and because more connections require more resources. Opening and closing connections is very time consuming, your don't want to open and close hundreds of connections per minute.

When a query is not running, a sleeping process for each active pool connection, they are waiting to respond quickly when needed without logging again because the FglAM does not close the connection after the work is done.

SPIDs consume a little bit of memory allocated to hold structures related to a session but do not consume CPU when they are in a sleeping state. The CPU number is high because the application uses connection pooling with the same processes so the CPU value is cumulative.

For example if there is one connection from the FglAM to the database and it is running a days since backup query and it took cpu as 20. If there is connection pooling then it will use the same connection to the database to run additional queries (like deadlocks) from the application then the cpu value will increase from 20 to 40 where as the actual CPU consumed for just the deadlock query was 20. The value was added to the existing CPU value of 20. If the connection then run

Please note:

Beginning with 5.6.5 versions of the DB2 and Oracle database cartridges, A new ASP was added to the DB2/Oracle cartridge called: "Use Single OS Collector?". By changing this ASP value to true (to all relevant agents), the agents will start to use a common OS collector, and therefore only 2-5 SSH connections should be opened to the monitored host.

Please review KB 102815 for additional information related to CPU issues on the monitored host caused by disconnected Oracle sessions.