Code instrumentation is a necessity if you really want to find and fix Oracle SQL performance issues. Instrumenting your code is simply a way for you to identify portions of your code that are important to you. I'd even go so far as to say that even if some parts of your code aren't that important to you now, instrumenting them now may save you a lot of headache in the future if/when those pieces of code do become important (i.e. they are performing sub-optimally).
Oracle provides supplied packages to help make it easier for you to instrument your code. In this post, I want to briefly discuss DBMS_APPLICATION_INFO (to help you mark your code). In the next post I'll also discuss DBMS_MONITOR, another supplied package used to help you trace the sections of code you've instrumented.
The Oracle PL/SQL Packages and Types Reference defines the DBMS_APPLICATION_INFO (I'll abbreviate as DAI moving forward for brevity) package as follows:
"Application developers can use the DBMS_APPLICATION_INFO package with Oracle Trace and the SQL trace facility to record names of executing modules or transactions in the database for later use when tracking the performance of various modules and debugging."
The key element of using DAI is that it provides a way to assign names to portions of code by marking those portions with a module and/or action name. Once the module/action names are assigned, they can be used to trace that portion of the code. In addition to making it easy to trace specific portions of your code, whenever the instrumented portions of your code are executed, the module/action names used will be visible in views such as V$SESSION, V$SQLAREA and a host of others. That means that the activity identified by these module/action pairs can be easily located and reviewed within tools such as Enterprise Manager. Having this information can help your DBA, and you, identify how much time and resources key tasks within your application utilize.
SET_MODULE, SET_ACTION, and SET_CLIENT_INFO
In order to identify the sections of your application that are important to you, you can set one or more identifying values such as a module name, an action name, or an informational string called client info. These values are set using procedures with the DAI package.
SET_MODULE and SET_ACTION
Using the SET_MODULE procedure will allow you to specify both a module name and an action name in a single call. The syntax is
dbms_application_info.set_module(module_name=>'your name', action_name=>'your name')
Choose the naming schemes that best suit your needs to identify and track key sections of your application. For instance, if you wanted to identify the section of code in your payroll application where various paycheck deductions are made, you could use the following:
You can store module values that contain up to 48 characters and action values that contain up to 32 characters.
Using the SET_ACTION procedure will allow you to specify only an action name. You may use this instead of SET_MODULE if you want to keep the already established module name but change the action. Using our payroll example, if we had already set the module to "Payroll", we could subsequently change the action for each different element of the payroll process. For example:
… and so on
When these calls are executed, you can see the active session information with the assigned module/action names you have set by querying V$SESSION:
select * from v$session where module = 'Payroll' and action = 'benefit deductions';
The SET_CLIENT_INFO procedure allows you a bit more flexibility to store additional information you can use to identify sections of your code. The client info value can contain up to a 64 character string. You can use it to show things like input variable values used in your SQL or any other identifying bit of information that might be helpful to you. For example,
dbms_application_info.set_client_info(client_info=>'Processing dept #42')
When using DAI, you must remember to reset the module, action and client info values when the section of code they represent is complete. You can either use the SET* procedures to change the values to something else or simply set the values to null. For example,
Another thing to keep in mind is if you have bits of code that call other sections that you've identified using DAI. When you begin the called section, you will need to capture the value of the module, action, and client info values that are currently set. Once captured, you can save the values in variables, change the values as needed for the current section, then use the stored values to flip back to the previous values when the active section completes. To evaluate and capture the values set by DAI you can use the READ_CLIENT_INFO or READ_MODULE calls.
This can get a bit tricky to manage the call chain if the calls span multiple procedures with multiple different DAI calls. If you're not in the mood to reinvent the wheel and would like to investigate a wrapper package that has all the smarts for managing the call chain and makes using DAI even easier, check out the Instrumention Library for Oracle (ILO). The ILO is an open source set of packages written and maintained by the folks at Method R. You can read more about ILO at http://method-r.com/software/ilo.
Taking the time to identify key tasks within your application using the DBMS_APPLICATION_INFO package will help you monitor your code and make it easier to troubleshoot in the event of trouble. Taking the time to wrap your key tasks with this simple bit of extra code will be a valuable addition to your application performance diagnostic tools.
In my next post, I'll show you how you can use the instrumention you've added with DAI to easily turn extended SQL trace on and off.
Excellent topic. By the way, Toad for Oracle uses this package to register itself. As a toad user just open the session browser, select a toad.exe session, look at the tab named "Session" and look at the column named "Module". You'll see that toad calls dbms_application_info.set_module and registers itself (i.e. TOAD 220.127.116.11). I've also written some blogs on how you cna leverage this to control Toad usage - i.e. maybe setup so only N people can run Toad since we only bought N copies.
Look at bullet item #6 - even has some code examples to do this :)
Nice article to show a great way to use module and action! That's just another example of why using instrumentation is so important. Once you've registered your app with DAI, you can use that information in some really cool ways. Thanks for the link!