DBMS_APPLICATION_INFO for Instrumentation September 3, 2012

I just wanted to put up a post about DBMS_APPLICATION_INFO. This is a fantastic little built-in PL/SQL package that Oracle has provided since Oracle 8 to allow you to instrument your code. i.e record what it is doing. I’m a big fan of DBMS_APPLICATION_INFO and have used it several times to help identify where in an application time is being spent and how that pattern of time has altered.

Some PL/SQL developers use it and some don’t. It seems to me that it’s use comes down to where you work, as most PL/SQL developers are aware of it – but not everyone uses it (a friend of mine made the comment recently that “all good PL/SQL developers use it“. I can understand his point but don’t 100% agree).

It is incredibly easy to use. You use the procedures SET_MODULE(module_name,action_name), SET_ACTION(action_name) and SET_CLIENT_INFO(client_info) to set the values of the corresponding columns in V$SESSION for your current session. This is done in memory, there is no inserting or updating of any table rows, so it is incredibly quick and light-weight. The below shows a subset of V$SESSION including the columns that get set:

The intention is that you set the MODULE as you enter a functional chunk of your application (be it a PL/SQL package or a chunk of JAVA, whatever {but be aware of the stateless nature of systems using middle tiers and pooled connections}) and then update the ACTION as you progress. You can set an action with no module, as demonstrated above, but I would recommend against it. On that topic I would make the following recommendations:

Set the MODULE at the start, eg as you enter a PL/SQL package’s main module

Set the MODULE before you do all the validation – you may wish to see if that validation is as quick as you think.

Use SET_ACTION to update the action prudently. ie when the activity of the code alters but not all the time. It will come clearer with use, but what you are after is to be able to judge the relative run durations of parts of the application. Too much detail can mask the overall picture. If you can alter code you can always add more instrumentation.

Clear the MODULE and ACTION explicitly as you exit a package. The activity is lightweight and you do not want any code that lacks this instrumentation to inherit values from calling code.

Do not set an ACTION without having set the MODULE earlier in the code.

Keep the text of these values short and specific.

You do not have to look at V$SESSION to see these values, which is good as you may lack the privs needed to do so. Since oracle 9 there have also been procedures to check what the values are set to. e.g:

So as you can see, these values can be set, they can be accessed via the data dictionary or the package itself and, if you believe my wild claims, they are very lightweight to use. However, these values really come into themselves with eg OEM’s performance screens. I’ll leave examples of that to a second post. Here I just wanted to cover how easy it is to set and get the information.

That does lead me onto what I think is the main reason that DBMS_APPLICATION_INFO is not as widely used as it could {and should} be. Developers often lack access to eg OEM and so do not see the full benefit of using it. Also, developers tend to be {note the get-out-clause) more concerned with making the code work and less with monitoring it after it is released. DBA-types tend to be more concerned with monitoring it. Thus the DBA types would want it in the code and developers would not want to spend the time coding it in. We are all one big team though, so some negotiation and beer-buying (or management enforcement) might well resolve that one.

You’re doing it again. I just spent an hour, 2 weeks ago, explaining DBMS_APPLICATION_INFO to a couple of DBA’s at my current client and showing how I had implemented it in some of the code so they could monitor current state, and thought to myself.. Hey, I’ll blog about that when I get back from Italy…

You can never put enough info into DBMS_APPLICATION_INFO. Just make sure you’re not overwriting useful information that may have been set by the connecting tool (e.g. SQL Dev).

Exposed dynamic autonomous instrumentation – the cream on the strawberries of your code.

“Warning:
Be aware that in version 11, the change in behavior, which is actually considered a bug fix, is required in order to accommodate new features in Enterprise Manager. The Forms’ module name now correctly appears in the v$session “module” column as expected. Changing the value set in the MODULE column of v$session for running Forms sessions will not be supported. Make such a change at your own risk.”

Thanks for that. Hmmm, obviously I think that setting the value in v$session (in my case via dbms_application_info) is a good thing and I think that defaulting it in forms 11 might be a good idea – but to say that setting it explicitly yourself is “at your own risk” seems a little harsh by Oracle corp.

Then again, I have not been a forms developer since something like 1999 so I am probably no longer in a position to hold an opinion!

As ‘a friend’, let me clarify what I meant by “all good PL/SQL developers use it”

If you think about the many discussions we’ve had, including with others, a proper interview question is ‘what Oracle-related websites, blogs, books etc do you like?”. Why? Not because it’s impossible to be a good developer without reading that stuff (particularly when you’re just getting going and don’t know where to look yet) but because, the really good developers all do to some extent. So my real question was – how could people who have worked with Oracle for many years and consider themselves to be good developers have ignored just how much talk there is in the Oracle community about the importance of instrumenting your code? Tom Kyte never tires of talking about it, for example, and you see this instrumentation cropping up in all of Oracle’s code!

Personally, I don’t care whether it’s dbms_application_info, a stupid little debug package, dbms_output or whatever. Good developers should understand the criticality of good instrumentation and I’m surprised when I go to some sites and don’t see it used much at all.

Then you go to other sites and it’s used extensively, usually because there have been one or true really good developers who are spreading the word.

I suppose the thing I find surprising is that it seems really basic stuff compared to some of the much more complex stuff which seems to be discussed endlessly in blogs and forums. Oh, and I used to spend a fair bit of time on this when teaching courses, but who goes on courses any more :-(

As you’re spreading the word, this post is a commendable thing indeed. I suppose I just sort of thought that everyone did this stuff already, but I’m starting to wonder if they’re not and we need more posts like this.

@doug, you make some very valid points about how good developers {and I’m expanding that to include DBAs, Sys Admins, designers and all technical IT people} look to the community for more information, other opinions on things and for what the latest technology can {and can’t) do. The more I ponder on it the more I think to myself that good technical people are not just smart with what they already have in their heads but also smart about going outside of what is in their heads to get better. It’s a shame you did not post your comment until this weekend as I think this is an excellent Friday Philosophy and I had no inspiration this week.

As @Tim says, you can show people what makes the application better – but some people refuse to take it on board. I agree with you both that it seems madness that instrumentation is not just included.

So a good developer will have been out there and come across instrumentation, even if they have not yet worked on a project where putting in instrumentation is simply What You Do.

I still think there are good developers out there who do not instrument – maybe as they are not given time to put it in, have somehow never experienced what it adds or are in an environment where Agile or similar development techniques are *abused* to say “only write code that does exactly what we need to make the app function”. That’s one of my bugbears about rapid development, things that pay dividends in the long run are dropped for the sake of quick turnaround.

I would hope though that potentially good developers would take on board instrumentation, properly reading documentation, code standards, design…all those things we used to do in the good old days :-)

Back to the topic of dbms_application_info. It is out there in the web sphere but with 44k hits on a google search on “DBMS_APPLICATION_INFO” compared to 250k on “DBMS_STATS”, 83k on “DBMS_RANDOM” and 33K on “DBMS_CRYPTO” {which I would judge to be a pretty specialist package} maybe we need to push it a little harder.

It’s not all about *good* or *bad* developer; Beginning design of a CRM System 6 years ago, something like an (Windows) Eventlog table with a package was implemented; and other ‘service packges'; so independent which develeoper is writing code it’s a standard to use that environment.
MODULE, etc. we use for internal simple auditing;
we had some issues with Oracle ODAC 11.2.0.3 because MODULE was passed as NULL due to an oracle client error using .NET
cheers
/Karl

In Oracle 12 (well 12.1.0.2 at least) MODULE and ACTION in V$SESSION, V$ACTIVE_SESSION_HISTORY etc have moved on from being VARCHAR2(48) and VARCHAR2(32) respectively in Oracle 11 to both being VARCHAR2(64)s.

Great I thought (as I have found 48/32 insufficient at times in the past),..,. until I discovered that one could still only set the first 48 or 32 characters respectively using DBMS_APPLICATION_INFO. So I was just doing a bit of Googling, to see if I could find any reason for the limitation continuing, or whether there was some new tool which allowed the rest of the 64 bytes to be used, when I found this blog of yours.

One thing which might be worth adding to your article, is that if using SQL*Plus one can use the SET APPINFO to set a value for MODULE, so for example
SET APPINFO ‘Playing with module/action’

I’m not aware of an ACTION equivalent in SQL*Plus (without calling DBMS_APPLICATION_INFO).

.. and I did try, using an Oracle 12 SQL*Plus client against Oracle 12.1.0.2 with a long “SET APPINFO” strings result in an error message like:
string beginning “‘Module In…” is too long. maximum size is 48 characters.

I love the idea of instrumenting code. But I have issues that I can’t figure out with DBMS_APPLICATION_INFO. If I write a PL/SQL proc and I set the module name at the beginning and clear the module name at the end – all is good. But if that proc calls another proc that also uses DBMS_APPLICATION_INFO then it seems to me that everything gets messed up. In the pseudo code below:

proc b
Set module name to “B”;
do stuff;
end;

proc a
Set module name to “A”;
do stuff;
Call subroutine B;
*** AT THIS POINT the MODULE NAME IS WRONG !!! ***
end;

So, it seems to me, that unless I wrap DBMS_APPLICATION_INFO in some kind of call stacking functionality – it is going to give me a lot of false information.

I don’t think so, though I have not checked – When I use DBMS_APPLICATION_INFO I expect the values for MODULE and ACTION to stay the same until something changes them, no matter how I move into and out of subroutines. So if I want them to be a specific value after I come back from a subroutine then I set it so – after all, the whole point of a subroutine is that it can potentially be called from several places.

I guess it would be nice if the values “unwound” as you exited procedures and functions but then Oracle would have to, in effect, keep the values in variables at each level and implicitly call DBMS_APPLICATION_INFO for you IF you had already set any of the three at that level.

A job for the developer, I think. You might want to see what Steve Feuerstesin other other PL/SQL experts say about it and perhaps offer in APIs

Thanks for the comments. I have tested dbms_application_info with subroutines and it is true. Unless you go to some kind of effort to stack the values as you call and return you will get mis-leading results, very quickly. I had thought about ILO but I think I need something that is extremely lite-weight to go into our application.

Our DBA team is constantly monitoring our system for SQL statements that are showing up as performance hits on the system. These can be on the Top SQL list or perhaps something that is causing contention issues or whatever. When they find this kind of thing they send out an email to the development group saying that “this SQL is causing xyz problems” – please fix it. They include the text of the SQL. Now we have to go searching through our system trying to find whre this SQL is coming from. Sometimes it is easy – sometimes it is not.

I want to be able to easily identify all the SQL statements in our application.

What if we just went simple and put a comment at the beginning of every SQL statement that identifies the package and procedure. For example:

Yes, you could do that, it would achieve your aim. This of course will only work if the SQL code is all “yours” and not from a 3rd part app or generated by SQL-generating tools.

There would be minimal overhead on parse time as the comment is ignored (as it is not a “hint”). However, where you have the same (as in identical) SQL statement in different places you would now have several individual statements due to the comment. The SQL ID is derived from the text of the SQL statement. If you know certain statements by their SQL_ID you would have to get used to the new IDs. Neither of these should be major issues but if you do have identical SQL statements across your code base then you could end up with a lightly less efficient library cache. Also here is a very small but real chance you could get the odd statement that now gets a bad plan as it was sharing a plan with another statement possibly executed with a different range of bind variables. Maybe I’m being a little edge-case there though!

It could be a lot of work to implement of course. Hopefully you have all your code under some sort of version control but as you occasionally find it hard to find some statements, maybe not all code is under version control? This might be an opportunity to do it.

I do wonder if you are over-using DBMS_APPLICATION_INFO. When I use it I set the module at something like package level and then action before key sections of the code, which could be a package level procedure or a SQL statement. If I have to investigate the code (for performance or bugs) I may end up adding more statements setting ACTION but I remove those that don’t help. I don’t set MODULE in library packages as I know the code there is supposed to be called from all over the place. I suppose I could collect the module and action on entry to the procedures/functions in the library and set them back on exit but I’d see that as a bit of an overhead.

A recent habit I have picked up from a colleague is to set CLIENT_INFO to a short piece of text and control or progress variables to held track activity but I don’t bother clearing up after it.

:-) Yeah, but as I was already using MODULE and ACTION to track the activity I just used CLIENT_INFO to keep this simple piece of information in the same “family” of columns. A purist like you might go to the trouble of using SET_SESSION_LONGOPS and filling all those parameters.
Besides, using v$session_longops may well have confused any DBAs looking after the system after I left…{“Dave?!?! How the hell did that get in there and why isn’t the SQL_ID set?”}