Disclaimer
Note: the topic of this post is for advanced Oracle users only. If done incorrectly, you can break the DPA monitoring for the instance and you will not be supported. However, at the bottom of the attached script are two commands to help you recover back to default installation.
Also, once these changes have been done, this change will not officially be supported by SolarWinds technical support.
For questions and help with this, please leave comments and someone will get back to you.
Introduction
DPA for Oracle uses synonyms and views on the monitored target to collect data and the views can be modified to collect other data than the default. The view is named X$KSUSE and this is the underlying system fixed table that sits underneath the GV$SESSION view. This is one of the main views DPA uses during its Quickpoll to collect activity and it includes things like SID, Username, Machine, Program, SQL_ID, Plan Hash Value, etc for each active session.
Module and Action Data
Some customers have applications that populate the MODULE and ACTION columns in this view, and DPA can be made to collect it by changing the option named ORACLE_ERP (named this because Oracle ERP is a popular application that uses this) to true for the instance. See this documentation for help making changes to options.
The change will take effect immediately, but it might take several minutes for the Module and Action tabs to appear.
Collecting Service Name
To collect the Oracle service name used on the database connection, we can override the X$KSUSE view to collect it rather than the action name. This requires you to enable the ORACLE_ERP option first as described above and then we can change the view to collect service name.
The attached script can be used to recreate the X$KSUSE view and cause DPA to collect other data as needed. For collecting service name, replace the action column in the view (ksuseact) with the service name column (ksusesvc). This change has already been done, so this script is ready to go if that's the data you want to collect. However, the GUI will still show the tab named Action, but just remember that it really represents the Oracle service name in this example.
Collecting Other Data
Other data can also be collected and both the Module and Action columns can be safely overridden with other values. Here are a common subset of other data that can be collected:
- CLIENT_INFO - ksusecli
- RESOURCE_CONSUMER_GROUP - ksusegrp
- CLIENT_IDENTIFIER - ksuseclid
- WAIT_CLASS - ksledclass
- PLSQL_OBJECT_ID - decode(s.ksusepco,0,to_number(null),s.ksusepco)
