Re: How does Oracle Apps/Dev Tools use DBMS_APPLICATION_INFO?
- From: Stephane Faroult <sfaroult@xxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Sun, 09 May 2004 21:37:10 +0200
David Kurtz wrote:
>
> Hello
>
> Can anyone tell me how and when Oracle Apps or the Oracle Development tools
> use the DBMS_APPLICATION_INFO package.
>
> There are four producedures in this package
> SET_CLIENT_INFO
> SET_MODULE
> SET_ACTION
> SET_SESSION_LONGOPS
>
> I only work with PeopleSoft and so never see these tools, and I would like
> to compare how they are used (and maybe put in an enhancement request).
>
> How much help are they? Do they have any performance overhead?
>
> _________________________
> David Kurtz
> Go-Faster Consultancy Ltd.
> tel: +44 (0)7771 760660
> fax: +44 (0)7092 348865
> mailto:david.kurtz@xxxxxxxxxxxxxxx
> web: www.go-faster.co.uk
> PeopleSoft DBA Forum: http://groups.yahoo.com/group/psftdba
>
David,
Whenever I can, I preach to developers 'use these functions'. They
make life infinitely easier for support people and performance
troubleshooters.
What do they do? They fill in-memory areas with information. Performance
overhead for me is null.
So, what are the benefits of this? Actually, the information can be seen
in V$SESSION and it can be VERY helpful. There is, in V$SESSION, a
PROGRAM column which can somewhat help identifying in which context some
particularly painful statement is issued, but unfortunately the
information in PROGRAM is rarely useful. For instance, a client-server
program may appear as the name of the DLL which connects to Oracle. When
you have several such programs hitting the same tables, it's not very
easy to track which module is the culprit. It's even worse with n-tier
Java application, where you see something such as 'jdbc 1.0' as the only
'program'.
DBMS_APPLICATION_INFO is THE means for an application to brandish a flag
saying 'it's me (MODULE) and I am doing that (ACTION)'
(SET_SESSION_LONGOPS is used to set the equivalent of a progress bar). I
can assure you that when you are tracking down some rotten piece of SQL
code it's extremely useful, especially if the statement has been
dynamically built (as it often happens) which makes grepping the code
useless.
I have also seen it put to very good usage in a case where all
authentification was managed by the program - all users were connected
under the same Oracle user, however they had an individual
username/password which was checked and managed in the application (many
n-tier applications are close to this pattern too). From time to time
there was some locking problem, because one user was forgetting to
commit a change. It was a nightmare for support people, because they
were unable to say WHO was holding the lock, and killing offending
sessions was not making them very popular. It was solved by using
CLIENT_INFO to hold the name of the actual user, once he or she had
successfully authenticated him or herself. As soon as something wrong
was spotted, it became easy for them to give a ring to the user, or
denounce him or her to the angry colleagues.
HTH,
Stephane Faroult
Oriole
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
