Any opinions on DBMS_APPLICATION_INFO package?

Hi all,

I am looking for opinions about whether or not using the DBMS_APPLICATION_INFO.SET_MODULE and DBMS_APPLICATION_INFO.SET_ACTION procedures are helpful in tracking down which SQL statements are having performance problems and which code is executing them. I've never used them before, so I would appreciate any constructive comments based upon your experiences.

The organization in which I work is about to make a serious attempt at determining why our application is "slow". I have been asked to make some suggestions about what we can do to determine exactly what operations perform the worst before they spend a lot of effort fixing those areas. So I have been looking at things that we can do to help determine which portions of our code need to be evaluated because executing them results in a lot of work in the database or the execution of poorly tuned SQL. I stumbled across the DBMS_APPLICATION_INFO package and have been playing around with it. I am not sure how useful the information that it is providing will be.

Here is what I have observed:
       I can see the module and action information in V$SQLAREA, so that can help give me an idea of which statements currently in the shared pool belong to which functional area of the application. However, this is only meaningful as long as the statements are in the shared pool. This may be helpful if we monitor things as many people are running the application. I don't think this will help much for studying the results afterwards.

       I can see the module and action information in any Oracle trace logs that I generate. However, this information does not seem to be reported in any way if I TKPROF the log file. So, the only way that I can see to link a bad SQL to a module/action is to dig through the .trc file for the bad SQL and see what the last defined modeul and action are. This does not seem like fun since reading the .trc files makes my head hurt. :)

So, am I missing something with using the procedures in the DBMS_APPLICATION_INFO package? Is using it really going to be helpful at all? Or should I just take the tact of timestamping the start and end of each functional area in the application and just look at it from that end?

Thanks in advance,

Heath

Heath,

That's about all DBMS_APPLICATION_INFO does as
fas as I know.... just adds some info to the MODULE and ACTION columns in V$SQL and V$SQLAREA.

They are not extremely helpful, but if you know that your
app is running against a database with a lot of other
stuff, different versions, etc, then it can be a good way to
tell which queries are coming from which version of your app.


-John