One of the things that can cause poor application performance is not having enough connections to the database available. SInce Oracle SOA Suite is a heavily database intensive application, it can cause significant performance problems, particularly if using lots of transient SOA BPEL processes (see Chapter 8, BPEL and BPMN Engine Tuning, for more on this topic). In this recipe, we will look at how to use the WebLogic console to view a number of available database connections.
For this recipe, you will need to know the URL and login credentials for the WebLogic administration console that runs on the admin server in your SOA Suite domain. The default URL for the console is http://<servername>:7001/console. The username and password will have been specified when you created the domain.
You will need SOA Suite installed and running, and to have loaded it with representative load or live data, or be able to create a realistic load on the application.
Connect to the WebLogic administration console at
http://localhost:7001/console; replacelocalhostand7001with the hostname and port of the server if it is not running locally on7001. If this is the first time you have accessed the WebLogic console since starting the server, WebLogic will first deploy the console application, which may take a few minutes.Log in to the console by using your administration credentials.
Open the Services category on the left-hand navigation pane, and select Data Sources.
This will display a list of all the data sources in the main panel.
Select the first of these data sources, which will load its properties page. Select the Monitoring tab and the Statistics subtab.
This will display the statistics for the data source. If you see nothing on this page, then it is likely that none of the servers on which this data source exists are running.
Click on the Customise this table button, and add the following columns to the view: Active Connections Current Count, Active Connections High Count, Current Capacity, Number Available, Waiting for Connection Current Count, and Waiting For Connection High Count. Click on Apply.
Now, when you view the Statistics panel, it should show the following new statistics:
Check the statistics for any problems, see the How it Works... secion of this recipe for more details about what to look for.
Repeat steps from 5 through 9 for each data source.
The Oracle WebLogic application server exposes management and monitoring metrics using the JMX standard, and the Oracle WebLogic console makes use of these to display information on components such as the database connection pool sizes. It is worth noting that most connection pools have an initial size of zero. So if you view the pool size before the application has been used, then you will see zero for all of the statistics we are monitoring. If this is the case, then you should run some load through your application and check back again.
These connection pools are used by SOA Suite components to talk to the database for many purposes including storing process instance state, restoring state, storing logging information, and so on. If WebLogic runs out of these connections, then requests have to queue, waiting for a connection to become available, eventually timing out if no connection becomes available. By monitoring the number of connections in use, you can increase the maximum number of connections in the pool, if you notice that they are running out.
We are looking for a number of different things in the connection statistics:
Active Connections Current Count equal to Current Capacity, which indicates that every connection from the pool is currently in use. This means that there are no free connections. If the Current Capacity is the maximum number of connections that the pool will hold, then new requests will have to wait for connections.
Active Connections High Count equal to Current Capacity, which indicates that at some point, every connection from the pool was in use. Active Connections High Count gives the highest value that Active Connections Current Count has had since the server was restarted (or the statistics reset), so can be useful for diagnosing the cause of performance problems that happened in the past, and the system has now recovered.
Number Available equal to zero, which indicates that the pool is empty (unless the pool has not been initialized yet). This will usually occur when all the other conditions are true.
Waiting for Connection Current Count or Waiting for Connection High Count not equal to zero, indicating that at some point there have been threads waiting to get a connection from the pool. As threads will only wait for connections if there are none in the pool, then we would expect to only see this if all of the other conditions are also true.
These are all indicators of the same underlying problem, which is that there are insufficient available connections to the database. This is usually caused by one of three things—either a sudden burst of traffic has used up more than normal, the database is running slower than normal, or there is a connection leak in the application. To find out which of these is the case, you should look at the output of the thread dumps and database statistics.
As well as being able to see this information via the WebLogic console, it is also made available via the JMX standard and via the WebLogic scripting tool, which is based on Jython, allowing you to use many tools to obtain and process connection pool sizes and more. We will discuss more about JMX in Chapter 2, Monitoring Oracle SOA Suite, and look at some of the tools that can be used for obtaining JMX metrics.
The Identifying slow-running database queries and Identifying performance problems with JStack recipes
Chapter 2, Monitoring Oracle SOA Suite