jump to navigation

DBMS_APPLICATION_INFO for Instrumentation September 3, 2012

Posted by mwidlake in development, Instrumentation, performance.
Tags: , , , ,
trackback

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:

desc v$session
Name Null? Type
----------------------------------------- -------- ----------------
SADDR RAW(8)
SID NUMBER
SERIAL# NUMBER
AUDSID NUMBER
PADDR RAW(8)
USER# NUMBER
USERNAME VARCHAR2(30)
COMMAND NUMBER
...
MODULE VARCHAR2(64)
MODULE_HASH NUMBER
ACTION VARCHAR2(64)
ACTION_HASH NUMBER
CLIENT_INFO VARCHAR2(64)
...

Just something odd to note. MODULE, ACTION and CLIENT_INFO are shown at VC(64) but if you check the documentation you will see that:

MODULE is limited to 48 bytes
ACTION is limited to 32 bytes
CLIENT_INFO can be set to the whole 64 bytes

I have no idea why MODULE and ACTION are limited in this way but it might be historic, backward compatibility with prior versions.

As a quick example of their use:

test_11_2> --demo_dai.sql
test_11_2> -- quick demo of dbms_application_info
test_11_2> COL sid form 9999
test_11_2> col username form a12
test_11_2> col module form a18
test_11_2> col action form a22
test_11_2> col client_info form a15
test_11_2> -- set module and action
test_11_2> EXEC DBMS_APPLICATION_INFO.SET_MODULE(MODULE_NAME=>'SALES_LOAD',ACTION_NAME=>'VALIDATE_ALL_PRICES')
test_11_2> select sid,username,module,action,client_info from v$session where sid=650;

SID USERNAME MODULE ACTION CLIENT_INFO
----- ------------ ------------------ ---------------------- ---------------
650 ERIC SALES_LOAD VALIDATE_ALL_PRICES
test_11_2> --
test_11_2> -- Update the action within a module
test_11_2> EXEC DBMS_APPLICATION_INFO.SET_ACTION(ACTION_NAME=>'Update all Prices')
test_11_2> select sid,username,module,action,client_info from v$session where sid=650;

SID USERNAME MODULE ACTION CLIENT_INFO
----- ------------ ------------------ ---------------------- ---------------
650 ERIC SALES_LOAD Update all Prices

test_11_2> -- clear them
test_11_2> EXEC DBMS_APPLICATION_INFO.SET_MODULE(null,null)
test_11_2> select sid,username,module,action,client_info from v$session where sid=650;

SID USERNAME MODULE ACTION CLIENT_INFO
----- ------------ ------------------ ---------------------- ---------------
650 ERIC

test_11_2> -- you can set just an action
test_11_2> EXEC DBMS_APPLICATION_INFO.SET_action(ACTION_NAME=>'orphan process')
test_11_2> select sid,username,module,action,client_info from v$session where sid=650;

SID USERNAME MODULE ACTION CLIENT_INFO
----- ------------ ------------------ ---------------------- ---------------
650 ERIC orphan process

test_11_2> -- set them to something sensible
test_11_2> EXEC DBMS_APPLICATION_INFO.SET_MODULE(MODULE_NAME=>'RETURNS_LOAD',ACTION_NAME=>'MATCH_TO_SALE')
test_11_2> select sid,username,module,action,client_info from v$session where sid=650;

SID USERNAME MODULE ACTION CLIENT_INFO
----- ------------ ------------------ ---------------------- ---------------
650 ERIC RETURNS_LOAD MATCH_TO_SALE

test_11_2> -- client_info can be longer
test_11_2> EXEC DBMS_APPLICATION_INFO.SET_client_info(client_info=>'entered this module at '||sysdate)
test_11_2> select sid,username,module,action,client_info from v$session where sid=650;

SID USERNAME MODULE ACTION CLIENT_INFO
----- ------------ --------------- --------------- ----------------------------------------
650 ERIC RETURNS_LOAD MATCH_TO_SALE entered this module at 03-SEP-2012 13:07

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:

test_11_2> BEGIN
2 DECLARE
3 V_MODULE VARCHAR2 (64);
4 V_ACTION VARCHAR2 (64);
5 begin
6 dbms_application_info.READ_MODULE(V_MODULE,V_ACTION);
7 DBMS_OUTPUT.PUT_LINE('Module IS '||V_MODULE||' Action IS '||V_ACTION);
8 END;
9 END;
10 /

Module IS RETURNS_LOAD Action IS MATCH_TO_SALE
test_11_2>

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.

About these ads

Comments»

1. oraclebase - September 3, 2012

The Method-R peeps have a nice little wrapper on DBMS_APPLICATION_INFO that solves some of its annoyances.

http://sourceforge.net/projects/ilo/

Cheers

Tim…

mwidlake - September 3, 2012

Thanks Tim.

Your page on dbms_appliation_info is still relevant too – and judging by the google ranking, still popular as well.

http://www.oracle-base.com/articles/8i/dbms_application_info.php

It’s been around for soooo long (you write this for 8i) and is still not used as much as I would expect.

oraclebase - September 3, 2012

I saw Karen Morton do a great instrumentation talk and she mentioned ILO, which I how I know about it. Have a little play with it. You might like it. :)

2. Dom Brooks - September 3, 2012

And you can get at module, action & client_info via the USERENV context.

mwidlake - September 3, 2012

Oh yes! I’d forgotten that, thanks Dom. I suspect I make far less use of “sys_context (‘userenv’,…” than I should.

3. Neil Chandler - September 3, 2012

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.

mwidlake - September 3, 2012

“great minds think alike” or “Martin has access to my laptop webcam”. I let you decide Neil. BTW congrats on the epic journey, mate.

4. Roy A. J. - September 3, 2012

Also, be aware that Oracle Forms 11g sets the module-information.
Se https://support.oracle.com/epmos/faces/ui/km/DocContentDisplay.jspx?_afrLoop=823535315341152&id=744908.1&_afrWindowMode=0&_adf.ctrl-state=wdlbllb0f_104

“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.”

mwidlake - September 3, 2012

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!

5. oracledoug.com - September 9, 2012

Hi Martin,

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.

Cheers,

Doug

Tim Hall - September 9, 2012

Doug: You can lead a horse to water, but that doesn’t make it a duck!

We have all been banging on about instrumentation for years, but like documentation, it is seen as a waste of time for many developers. Pity.

Cheers

Tim…

mwidlake - September 9, 2012

@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.

6. Karl R. - October 26, 2012

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

7. What are my favourite Oracle books? « Oracle Frontovik - October 28, 2012

[...] This post was inspired by a comment on┬áMartin Windlake’s blog. [...]


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 159 other followers

%d bloggers like this: