A lot of enterprises rely on the Oracle database for their data layer. Although the licenses are costly, Oracle provides a proven product in terms of performance and scalability and very good support, so many people find it to be a good trade-off. However, every product will have certain limits. If Oracle is being used to serve data by busy applications, the number of parallel database connections will often cause bottlenecks. This can lead to high CPU usage on the Oracle side. It can also starve other applications from getting connections, leading to functional issues. So it becomes critical for developers to understand not just the number of connections their applications are consuming but also how effectively they are being used.
Before we dive in, there is one prerequisite for the below analysis to work. We need to ensure that an appropriate value is set for the module attribute. One of the ways to do it is during the connection creation. The application can set the initSql attribute to call dbms_application_info.set_module('<module-name>','<action-name>') . This will help us map the database connection to a certain application while looking at the oracle database.