Search

Top 60 Oracle Blogs

Recent comments

DBMS_APPLICATION_INFO

nVision Performance Tuning: 5. Additional Instrumentation of nVision

This blog post is part of a series that discusses how to get optimal performance from PeopleSoft nVision reporting as used in General Ledger.

One of the challenges of tuning and monitoring nVision is to be able to identify each report being run. Calls to Oracle instrumentation package dbms_application_info  were added to the component processor in PeopleTools 8.50, and to Application Engine in PeopleTools 8.52.  However, COBOL, nVision, and SQR were never instrumented.

One of my Favourite Database Things: DBMS_APPLICATION_INFO

Notes for my slot in session UGF2630: EOUC Database ACES Share Their Favorite Database Things: Part I – OpenWorld , Sunday 18th September 2016
DBMS_APPLICATION_INFO is probably one of the most undervalued and without any doubt the most underused of all the packages supplied with the database by Oracle. On the face it, it is a very simple package that does a few very simple things, but it is not possible to overstate its significance.

Undocumented Application Engine Parameter: EnableAEMonitoring

This is part of a series of articles about new features and differences in PeopleTools 8.54 that will be of interest to the Oracle DBA.

Oracle Support Document 1640355.1: E-AE: Performance Degradation When Using Do Loop Action to Process Large Amount of Data on PT 8.52 & PT 8.53 describes a performance problem in Application Engine when a small but frequently executed loop.  Application Engine calls DBMS_APPLICATION_INFO to set MODULE and ACTION on v$session each time it goes round the loop.

PeopleTools 8.52 Application Engine sets MODULE and ACTION

I have written and spoken often about the huge importance of instrumentation in an application. PeopleSoft introduced internal instrumentation to PeopleTools 8.44 for its own Performance Monitor that works on any platform. Since PeopleTools 7.53, when running on an Oracle database, PeopleTools also calls the Oracle supplied package dbms_application_info package to set the module and action for the session. The values set were not particularly useful, so I wrote a PL/SQL package (psftapi) and trigger to set the module and action attributes when a process started.

PeopleTools 8.50 uses DBMS_APPLICATION_INFO to Identify Database Sessions

I recently worked on a PeopleTools 8.50 system in production for the first time and was able to make use of the new Oracle specific instrumentation in PeopleTools.

PeopleTools now uses the DBMS_APPLICATION_INFO package to set module and action session attributes.  This data is then copied into the Active Session History (ASH).

PeopleTools 8.50 uses DBMS_APPLICATION_INFO to Identify Database Sessions

I recently worked on a PeopleTools 8.50 system in production for the first time and was able to make use of the new Oracle specific instrumentation in PeopleTools.

PeopleTools now uses the DBMS_APPLICATION_INFO package to set module and action session attributes.  This data is then copied into the Active Session History (ASH).

Using Oracle Enterprise Manager (Grid Control) with PeopleSoft

If you use Oracle Grid Control to monitor your PeopleSoft system, here is a simple tip that will help you identify batch processes.

Oracle provides two columns on the session information (v$session) to hold context information. They provide a PL/SQL package DBMS_APPLICATION_INFO, which has procedures to read and update these values. The idea is that application developers will instrument their programs and will update these values. Oracle’s Applications (that it has developed itself), such as E-Business Suite do this. PeopleSoft was rather slow to make use of this. They do set the module and action, but not to very useful values.

However, you can create a trigger on the Process Scheduler request table that will update these values when a process starts.

(Updated 19.4.2009) I have created a PL/SQL package psftapi that contains a number of procedure that I have used from triggers and other PL/SQL programs. It contains a function that sets the ACTION for the session with the process instance and the description of the status.


#000000; padding-left: 4px; padding-right: 4px; background-color:#EEEEEE">
...
PROCEDURE set_action
(p_prcsinstance INTEGER
,p_runstatus VARCHAR2
) IS
l_runstatus VARCHAR2(10 CHAR);
BEGIN
BEGIN
SELECT x.xlatshortname
INTO l_runstatus
FROM psxlatitem x
WHERE x.fieldname = 'RUNSTATUS'
AND x.fieldvalue = p_runstatus
AND x.eff_status = 'A'

Using Oracle Enterprise Manager (Grid Control) with PeopleSoft

If you use Oracle Grid Control to monitor your PeopleSoft system, here is a simple tip that will help you identify batch processes.

Oracle provides two columns on the session information (v$session) to hold context information. They provide a PL/SQL package DBMS_APPLICATION_INFO, which has procedures to read and update these values. The idea is that application developers will instrument their programs and will update these values. Oracle’s Applications (that it has developed itself), such as E-Business Suite do this. PeopleSoft was rather slow to make use of this. They do set the module and action, but not to very useful values.

However, you can create a trigger on the Process Scheduler request table that will update these values when a process starts.

(Updated 19.4.2009) I have created a PL/SQL package psftapi that contains a number of procedure that I have used from triggers and other PL/SQL programs. It contains a function that sets the ACTION for the session with the process instance and the description of the status.


#000000; padding-left: 4px; padding-right: 4px; background-color:#EEEEEE">
...
PROCEDURE set_action
(p_prcsinstance INTEGER
,p_runstatus VARCHAR2
) IS
l_runstatus VARCHAR2(10 CHAR);
BEGIN
BEGIN
SELECT x.xlatshortname
INTO l_runstatus
FROM psxlatitem x
WHERE x.fieldname = 'RUNSTATUS'
AND x.fieldvalue = p_runstatus
AND x.eff_status = 'A'