PPM Support Tip: What is a Connection Correlation? And How to interpret it?
Knowledge base article: KM00958015
Relate to this new parameter ENABLE_CONNECTION_CORRELATION, introduced in Project and Portfolio management version 9.12.
What is Connection Correlation? And How to interpret it?
First of all, we need to explain how Database connections Pooling work in PPM (and in most Enterprise Applications actually).
There's a Database Connection pool in PPM Server (well, technically there's 3 connection pools, but let's consider there's only one for the sake of simplicity). A connection pool stores open connections to the database for reuse, because opening a new connection from PPM Server to Database Server is quite time consuming, and we want PPM code to be able to quickly run SQL on the database whenever it needs to, without having to establish a new connection every time.
So whenever some PPM code wants to run a SQL, it will ask for an open connection to the DB Connection Pool. If there's a "free" open DB connection sitting in the connection pool, it will be handled to the PPM code, and if there's none and that we don't have all the connections currently busy, we will open a new DB connection and handle it over to the PPM code.
Whenever the PPM code has finished running its SQL, it will hand over the DB connection to the connection pool, and the open connection will be put back into the pool, waiting for another piece of PPM code to request for it.
The thing is, there's no saying how long a connection will wait in the pool before another piece of PPM code retrieves it to run some SQL. The connection pool might repeatedly hand out other connections and let one free connection sitting in the pool for quite some time, until the connection eventually reaches its max life time and gets closed (all connections gets closed eventually).
A connection leaks occurs when a piece of PPM code requests a DB Connection, but doesn't hand it back to the connection pool. The DB Connection then stays open but idle, out of the pool, and will never get closed (unless it reaches some time out and is forcibly closed by the DB Server). No other piece of PPM code will be able to use it, because it's not sitting in the pool anymore. But as it's still out there, it consumes one "Connection Slot", and if you have too many leaked connections, the DB Connection pool will run out of connections, and you'll start experiencing very nasty errors in PPM. Needless to say, connection leaks is very bad, and if it occurs in a piece of code that's frequently run, your PPM Server will quickly run out of connections and crash.
In order to troubleshoot DB Connection leaks, PPM R&D introduced the "connection correlation" page in PPM 9.12. On this page are listed all the DB connections from the Connection pool(s) (both connections waiting in the pool and connections "in use", i.e. out of the pool). For each connection, you have the Oracle Session ID using it, and most importantly, the time it was last retrieved from the pool, and the stack trace of where in PPM code this connection was requested.
This won't be able to magically point out if there's a connection leak, but if you know how to look at this data, you can quickly identify a connection leak with the following patterns:
- There are many connections (more than 10) that have been last retrieved from the Pool a very long time ago (many minutes, if not hours from the current time).
- Most of these "long opened connections" have a stack trace that point to the same piece of PPM code, giving us a hint at where the connection leak occurs.
Keep in mind that it's normal to have a few "long opened" DB connections in this screen: there are some piece of PPM code (in the Dashboard for example) where we need to recurrently issue some SQL, all the time, and for this reason we get one DB connection and don't let it go, ever (until it times out, at which moment we get another one and resume).
So no need to worry if you see a handful (let's say less than 10) long running connections, especially if they point at different parts of the PPM code.
However, if you see more than 10 long lasting opened connections with the same stacktrace (meaning they were requested by the same piece of PPM code), and that these connections never get re-acquired by another piece of PPM code over time, then it's very likely you're experiencing a connection leak.
-- Remember to give Kudos to answers! (click the KUDOS star)
"If you find that this or any post resolves your issue, please be sure to mark it as an accepted solution.”