MetaSolv Solution - How to Configure WebLogic Server Data Sources to Identify/Monitor JDBC Connections on the Oracle Database
Last updated on FEBRUARY 02, 2018
Applies to:Oracle Communications MetaSolv Solution - Version 6.2.0 and later
Information in this document applies to any platform.
Applications hosted on middleware like Oracle WebLogic Server use JDBC data source connection pools to manage database transactions. These JDBC resources often times connect to the database with an underlying user ID that does not represent a physical end user of the application. For Oracle Communications MetaSolv Solution, those user IDs are APP_MSLV, APP_API, and APP_INT.
This presents troubleshooting challenges when attempting to monitor end user activity on the database. These challenges are amplified by environments having multiple WebLogic server instances hosted on a single machine. This document provides information to facilitate more effective troubleshooting of the Oracle Communications MetaSolv Solution.
The diagram below represents a basic Oracle WebLogic Integration Server domain with a single managed server instance hosted on a single machine. Directly below the diagram is a query followed by explanations of various results.
The results below shows similar information in a blocking/blocked transaction scenario which include each of their locked objects. Note that blocking lock was achieved by issuing an UPDATE statement against an ASAP.CUST_ACCT.CUST_ACCT_ID from a SQL Developer session (SID = 2194).
Another benefit of the configuration outlined in this document is the data sources are named in SQL Module column throughout the AWR report. The example images below are the same "SQL ordered by Elapsed Time" section of an AWR report showing the differences.
Prior to the configuration, the SQL Module simply lists "JDBC Thin Client". In a highly customized environment where many custom applications are running, the process could be associated to any one of them. In this case, SQL Id is a major problem but it cannot be immediately associated to a a more specific process (application, etc).
The new configuration makes it much easier to identify what application a problem SQL Id is associated with. Using the same SQL Id as an example, we can now see it is associated with the mslvNoneTxDataSource SQL Module indicating it is a MetaSolv Solution application process (reference table in Doc ID 1577030.1). Customers can also take advantage of this by configuring their custom data sources in the same manner. The example in the image below shows SQL Id 9g7jkbjndxvuj was executed using the OSSDataSource which has been associated to a custom application. This facilitates more effective troubleshooting by separating processes which can then be assigned to the appropriate resource for investigation and resolution.
Sign In with your My Oracle Support account
Don't have a My Oracle Support account? Click to get started
Million Knowledge Articles and hundreds of Community platforms