Oracle Session Tracing Part II
November 30, 2004
This part of the series is an extension to Part I and shows how to set two more very important session environment variables to make tracing more effective. Read on and learn how to set the module and action names.
In Part I of the Session Tracing Series we learned how to set the CLIENT_IDENTIFIER session variable and how we could query it from the V$SESSION view. We also covered how to look at the resources consumed in real-time for that CLIENT_IDENTIFER so that we could determine if large amounts of resources were attributed to a unique or group of client connections. If you remember from Part I, we invoked a logon trigger whenever a session connected and then queried the V$SESSION view. This gave us output similar to that of Listing 1. From this output, you can see a CLIENT_IDENTIFIER that is being set. I changed the logon trigger to put in the host name instead of the IP address in this example, and you can see that the module name did get populated for the applications I used to connect from (SQL*Plus and Oracle Administrative Assistant). Also, note that the action field is blank. Also notice that the there are two connections using SQL*Plus from host FINE-ALE and one connection from host PAULANER.
The setting of the CLIENT_IDENTIFIER was of great importance but if we want to get finer granularity on the actual amount of resources that are being consumed within certain modules of code or within certain business units of work, we need to look at the DBMS_APPLICATION_INFO package provided by Oracle. Most applications will give some form of module name when you see connections coming across but will leave the ACTION name unset. The setting of a module name is fine for some circumstances. However, to get a real indication of where in the code resources are being done, both fields should be considered when writing applications. This naming will allow you as a DBA or developer the ability to track down unique connections that you are concerned with in real-time or for latter tracing runs. This granularity is highly desirable when debugging a performance bottleneck and you will want to pin point which application module is consuming the most resources through the MODULE name and what is being done (ACTION) within the code. To do this, it is imperative, that the applications set the name of the module and the action being performed each and every time it enters a new module or the action of the transaction changes. The module name could be the section of code or procedure being executed, while the action could be a business unit of work or an individual statement. The purpose is to be able to tie resources used to a unit of work about which you are concerned.
Calling the DBMS_APPLICATION_INFO Package
Using the DBMS_APPLICATION_INFO package is quite straightforward. A simple call to SET_ACTION is all that is required to set the ACTION name. This call has the form of:
Therefore, if you wanted to set the ACTION to 'LOGON TRIGGER' you could construct the call as:
This action name should be very descriptive of the type of action being done within the code and it is always best to set this before the actual action is performed. That way when the action is being performed you will see the ACTION in the V$SESSION view and know that it is currently being done. When the transaction or specific action is complete within your code you should set it back to NULL or to the next action to be performed. This setting to NULL or a valid next transaction type is very important. If you do not adhere to this pattern, future resources will be attributed to the last ACTION and you will lose any true performance statistics.
The setting of the MODULE name is done through a call to the SET_MODULE procedure and has the form of:
All of the suggestions around setting the ACTION apply to setting the MODULE name as well. When setting this naming field, you want to set it before the actual execution within the code is started and you want to make sure to set the MODULE back to NULL or to the next MODULE to be executed so as to not miss-represent any statistics encountered after the true execution of the code is complete. As you might have recognized, this SET_MODULE procedure allows for the setting of the ACTION name also. You should use this to set your MODULE and ACTION name when first entering a new module of code but use the SET_ACTION procedure after, in the module. This will limit some of the overhead of setting these names, as I am sure there is some miniscule locking going on behind the scenes. Therefore, for our logon trigger if I wanted to set the actual MODULE and ACTION names it would look like the example in Figure 1. You should note that this is quite an extreme example of setting these session variables but I wanted to give you a flavor for how to do this. Notice that the MODULE name was set to 'LOGON_TRIGGER' and the ACTION is to indicate a SELECT from DUAL. Also, note that at the end of the trigger we set the MODULE and ACTION back to NULL so that we are certain not to mess up statistical collection. In addition, please note that I have put in a call to the DBMS_LOCK package to sleep so that I could see this setting of the MODULE and ACTION names.
Now when we query the V$SESSION view we can see the setting of these variables through Listing 2. After the DBMS_LOCK.SLEEP call is complete, the MODULE name returns to 'SQL*Plus' and the ACTION is NULL. I think this is a function of just using SQL*Plus and you should not rely on Oracle setting these back to the original call.
Proper setting of the ACTION and MODULE name are imperative for collecting statistics around specific code functions and transactions. Once they are set you can begin getting those fine grain details about the area of code that is performing poorly. Next time we will begin looking at how to pull that fine grain information out of current sessions, to zero in on where we need to concentrate our tuning efforts.