| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: How does Oracle Apps/Dev Tools use DBMS_APPLICATION_INFO?
Cary's and other comments below describe the dbms_application_info
.set_module and set_action calls as very worthwhile instrumentation to =
have
in an application; the number of db calls could be an issue with high =
volume
usage, but 10g makes improvements in that area.
Using the calls populates v$session.module and .action, as well as
v$sqlarea.module and .action, with whatever arbitrary "tokens" are =
passed on
the set calls. Assuming that a reasonable convention such as "token =3D =
name
of the routine calling the SQL statement" is used in the set calls, then =
a
very valuable diagnostic becomes available. For example, by looking at
v$sqlarea sql_text and module, a statistically "large" SQL statement of
interest can be tracked back to its owning module. =20
In case anyone is interested to know particularly how this is done =
within
the Oracle Apps, I offer the following notes below...
The Oracle Applications set module and action in at least two areas of =
which
I'm aware. The first is in the Concurrent Manager, and the second is in =
the
Forms 6i online Apps Forms master template subroutine appcore.pll.
For the Concurrent Manager, it was release 11.0 that began to populate
module and action for each of the batch jobs run by the managers. This
feature came about as bug 865350 from the work done inside Oracle during =
the
early days of the Global Single Instance consolidation project. By
convention, Conc Mgr sets module =3D the concurrent program short name =
of the
program being run (eg, GLPPOS) and action =3D "Concurrent Request" to =
signal
that the value of module is a batch job name. Apps 11i made this =
mechanism
a standard feature.
For online Apps Forms, in release 11.0 the appcore.pll was modified to =
call
dbms_application_info to set module =3D name of the form about to be run =
(eg,
OEXOEMOE) and action =3D "Online - rrr" to signal that module is a form =
name,
and rrr was the user responsibility under which the form was used. I'm =
not
aware of a patch having been made available to appcore.pll in 11.0, but =
in
11i this feature became standard.
In summary the Apps found "clever" places in the tech stack to inject dbms_application_calls, without requiring the user to add calls on their own. =20
A nice side effect of set_module is that the set value is populated into =
the
header or "preamble" of raw trace files. The trace file line beginning =
with
"APPNAME=3D" will show the value of the Apps form or batch job whose =
trace
activity has produced a given trace file. For example, a SQL trace file
generated from tracing the GL Posting module GLPPOS would have in its =
header
a line "APPNAME=3DGLPPOS". A grep thru udump on APPNAME makes it easy =
to find
a particular trace file of interest.
The Apps use of dbms_application_info is a good start, but more work
remains:
* other aspects of the Apps are not yet similarly instrumented (DBI =
Disco
Workbooks, for example)
* module and action once set, can become null if the Apps form or batch =
job
calls a stored procedure. =20
Hope this helps; thanks...
Larry Klein
Hotsos Enterprises, Ltd.
http://www.hotsos.com
630-513-8010 (Chicago office)
630-240-1190 (cellphone)
Upcoming events:
- Performance Diagnosis (3 day class) starting on/in
04/06 Seattle, 05/05 Dallas, 05/18 New Jersey,
06/22 Pittsburgh
- SQL Optimization (4 day class) starting on/in=20
04/19 Denver, 05/03 Boston, 05/24 San Diego
06/14 Chicago
-----Original Message-----
From: oracle-l-bounce_at_freelists.org =
[mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of Cary Millsap
Sent: Sunday, May 09, 2004 9:17 PM
To: oracle-l_at_freelists.org
Subject: RE: How does Oracle Apps/Dev Tools use DBMS_APPLICATION_INFO?
The /only/ problem I see with DBMS_APPLICATION_INFO is something that's =
=3D
no
longer a problem in 10g. Having said "problem," however, I still agree
wholeheartedly with Stephane: USE IT!!
The "problem" is that calling SET_* motivates extra database calls. But
check out the new 10g OCI documentation. Instead of separate dbcalls for =
=3D
an
app to identify itself, you can set the OCI_ATTR_MODULE, =3D
OCI_ATTR_ACTION,
and OCI_ATTR_CLIENT_INFO attributes on existing parse, bind, execute, =
=3D
fetch,
etc. calls. So in 10g apps, an app can identify itself with just a tiny
incremental consumption of network bandwidth, but no additional dbcalls =
=3D
are
necessary.
Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com
* Nullius in verba *
Upcoming events:
- Performance Diagnosis 101: 5/18 Edison NJ, 6/22 Pittsburgh, 7/20 =3D
Boston
- SQL Optimization 101: 5/24 San Diego, 6/14 Chicago, 6/28 Denver
-----Original Message-----
From: oracle-l-bounce_at_freelists.org =3D
[mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of Stephane Faroult
Sent: Sunday, May 09, 2004 2:37 PM
To: oracle-l_at_freelists.org
Subject: Re: How does Oracle Apps/Dev Tools use DBMS_APPLICATION_INFO?
David Kurtz wrote:
>=3D20 > Hello >=3D20 > Can anyone tell me how and when Oracle Apps or the Oracle Developmenttools
> use the DBMS_APPLICATION_INFO package. >=3D20 > There are four producedures in this package > SET_CLIENT_INFO > SET_MODULE > SET_ACTION > SET_SESSION_LONGOPS >=3D20 > I only work with PeopleSoft and so never see these tools, and I would ==3D
>=3D20 > How much help are they? Do they have any performance overhead? >=3D20 > _________________________ > David Kurtz > Go-Faster Consultancy Ltd. > tel: +44 (0)7771 760660 > fax: +44 (0)7092 348865 > mailto:david.kurtz_at_go-faster.co.uk > web: www.go-faster.co.uk > PeopleSoft DBA Forum: http://groups.yahoo.com/group/psftdba >=3D20
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
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------Received on Sun May 09 2004 - 22:17:36 CDT
 |
 |