Skip to Main Content
Return to Navigation

Monitoring PeopleSoft Database Connections

This section provides an overview of PeopleSoft database connections and discusses how to:

Understanding PeopleSoft Database Connections

PeopleTools provides the ability to monitor connections to the database server from Windows workstations (two-tier and three-tier connections) and browser connections. Some possible uses of monitoring database connections include system-wide troubleshooting, performance monitoring, "chargeback" accounting, and security audits for your system.

Administrators can obtain specific information regarding the user and the associated transaction when a user is connected to the database. A PeopleSoft system has many clients and user sessions connecting to one application server (directly, or indirectly through the web server), with only the application server maintaining connections to the database server.

Suppose one of your users has executed an extremely inefficient query that severely impacts the rest of the system. A Database Administrator would want to identify that user and take appropriate action. However, without an ability to monitor users you would probably have to terminate the physical connection, which would mean dropping the connection between the application server and the database server, which could potentially affect hundreds of users.

However, while only the application server maintains the actual database connection, PeopleTools records various information associated with each user connection so that client information can be monitored. Monitoring client information enables an administrator to collect information from two-tier connections, three-tier connections, and browser connections alike.

Associated with each connection and transaction is the following set of user information:

  • PeopleSoft user ID

  • OSUserName

  • MachineName

  • AppServerDomainName

  • ProgramExecutable

This information allows the system to associate activity on the database server with a particular workstation and user. This information is stored in the CLIENT_INFO column of the V$SESSION dynamic view.

Administrators are also often interested in compiling performance metrics based on the system usage per application. For this type of monitoring the PeopleSoft system populates the MODULE and ACTION fields of the V$SESSION dynamic view.

Oracle products, such as Oracle Enterprise Manager and Oracle Audit Vault use information stored in the CLIENT_IDENTIFIER column of V$SESSION.

Enabling Database Connection Monitoring

Database monitoring is always enabled for:

  • COBOL programs.

  • SQR programs.

  • Processes run through Process Scheduler.

  • Two-tier Windows workstation connections.

For connections handled by the application server (browser and three-tier Windows connections) the PeopleSoft systems administrator has the option to enable this feature by setting the EnableDBMonitoring parameter to '1' in PSADMIN or the application server configuration file (PSAPPSRV.CFG).

Tracking PeopleSoft Database Connections by PeopleSoft User ID

This section provides an overview of tracking database connections by user ID, a legend for interpreting illustrations, and discusses the following:

  • Oracle process connections.

  • Two-tier Windows client connections.

  • Application server process connections.

  • Three-tier Windows client connections.

  • Browser (PIA) connections.

  • Process Scheduler connections.

  • SQR connections.

  • COBOL connections

  • Windows and browser connections multithreaded through the application server.

Understanding Tracking PeopleSoft Database Connections by PeopleSoft User ID

To view the information associated with client connections, sign on to SQLPlus for the appropriate SID and execute the following SQL Query:

Note: This is a sample query that ties the OS PID and PeopleSoft CLIENT_INFO to the process connected to the Oracle database.

set linesize 200
select p.spid,
   substr(s.osuser,1,10) osuser,
   substr(s.username,1,8) username,
   substr(s.program,1,24) program,
   substr(s.client_info,1,60) ClientInfo
from v$session s, v$process p
where s.paddr=p.addr
and s.osuser is not null
order by s.osuser
/

The result of this query will differ somewhat per connection type. The following sections describe the information returned for various scenarios.

Legend

ID

Description

JZARATE (uppercase)

NETWORK login ID for Windows workstation.

JZARATE123199

Windows client MACHINENAME.

TMJONES (uppercase)

NETWORK login ID for Windows workstation.

TMJONES110299

Windows client MACHINENAME.

JRSMITH (uppercase)

NETWORK login ID for Windows workstation.

JRSMITH031198

Windows client MACHINENAME.

PREILLY (uppercase)

NETWORK login ID for Windows workstation.

PREILLY060499

Windows client MACHINENAME.

PT844P01

PeopleSoft schema (PS SYSADM ID or Access ID).

PT81

Tuxedo domain name.

PTDMO, VP1, and PS

PeopleSoft user IDs used to signon to the database from the various clients.

oracle (lower case)

Owner ID of all of the Oracle processes.

certora (lowercase)

UNIX login ID of the PeopleSoft administrator starting the application server and Process Scheduler.

Oracle Process Connections

Execution of the sample query noted above shows the Oracle Processes for the SID in which PeopleSoft database PT844P01 resides and this SQL*Plus session used to monitor the client info. There is no client info because no PeopleSoft client connections currently exist.

Oracle Processes and this SQLPLUS session are used to monitor the client info for network user JZARATE.

SQL> /
SPID     OSUSER  USERNAME PROGRAM                       CLIENTINFO        
           
-------- ------- -------- ----------------------------- ------------------
15276    JZARATE PT844P01 SQLPLUSW.EXE                                     
           
8364     oracle           oracle@st-sun01 (PMON)                          
           
8366     oracle           oracle@st-sun01 (DBW0)                          
           
8368     oracle           oracle@st-sun01 (LGWR)                          
           
8370     oracle           oracle@st-sun01 (CKPT)                          
           
8372     oracle           oracle@st-sun01 (SMON)                          
           
8374     oracle           oracle@st-sun01 (RECO)                          
           

7 rows selected.

Two-Tier Client Connections

For the two-tier connection, you can expect to monitor the following client information:

"%oprid%,%osusername%,%machinename%,,%executable%,"

Adding to what was previously displayed, this is a two-tier client connection from workstation TMJONES110299, Peoplesoft OPRID PS, executing PSIDE.

SQL> /
SPID     OSUSER  USERNAME PROGRAM                       CLIENTINFO                         
-------- ------- -------- ----------------------------- ------------------------------------
15387    TMJONES PT844P01 pside.exe                     PS,TMJONES,TMJONES110299,,pside.exe,
15276    JZARATE PT844P01 SQLPLUSW.EXE                                                      
8364     oracle           oracle@st-sun01 (PMON)                                      
8366     oracle           oracle@st-sun01 (DBW0)                                      
8368     oracle           oracle@st-sun01 (LGWR)                                      
8370     oracle           oracle@st-sun01 (CKPT)                                      
8372     oracle           oracle@st-sun01 (SMON)                                      
8374     oracle           oracle@st-sun01 (RECO)                                      

8 rows selected.

Application Server Process Connections

For the application server connection, you can retrieve the following information from the database:

"%oprid%,%osusername%,%machinename%,%tuxedo_domain%,%executable%,"

Adding to what was previously displayed, this shows the application server process connections for Domain PT81, from server st-sun01, using UNIX login ID certora, with the application server processes connecting to the database as user ID PTDMO.

Keep in mind that each application server process maintains an individual connection to the database. If your application server is up and running, you should see the following information after executing the session query:

SQL> /
SPID     OSUSER  USERNAME PROGRAM                       CLIENTINFO                           
-------- ------- -------- ----------------------------- -------------------------------------
15387    TMJONES PT844P01 pside.exe                     PS,TMJONES,TMJONES110299,,pside.exe, 
15276    JZARATE PT844P01 SQLPLUSW.EXE                                                       
15395    certora PT844P01 PSAPPSRV@st-sun01 (TNS V1-V3) PTDMO,certora,st-sun01,PT81,PSAPPSRV,
15409    certora PT844P01 PSSAMSRV@st-sun01 (TNS V1-V3) PTDMO,certora,st-sun01,PT81,PSSAMSRV,
15402    certora PT844P01 PSAPPSRV@st-sun01 (TNS V1-V3) PTDMO,certora,st-sun01,PT81,PSAPPSRV,
8364     oracle           oracle@st-sun01 (PMON)                                             
8366     oracle           oracle@st-sun01 (DBW0)                                             
8368     oracle           oracle@st-sun01 (LGWR)                                             
8370     oracle           oracle@st-sun01 (CKPT)                                             
8372     oracle           oracle@st-sun01 (SMON)                                             
8374     oracle           oracle@st-sun01 (RECO)                                             

11 rows selected.

Three-Tier Connections – Windows Workstations

For the three-tier connections, you can retrieve the following client information:

"%oprid%,%osusername%,%machinename%,%tuxedo_domain%,%executable%,""

When the three-tier workstation is connected, then you should see the application server process that is executing the transaction for the client. For example, the PSAPPSRV server process handles the majority of the requests. Let’s assume for this example that the PSAPPSRV is processing the current client request.

Adding to what was previously displayed, this is a three-tier workstation JRSMITH031198, signing on as PSOFT with a user ID of VP1, to Domain PT81 and utilizing two application server processes (PSAPPSRV).

SQL> /
SPID     OSUSER  USERNAME PROGRAM                       CLIENTINFO                           
-------- ------- -------- ----------------------------- -------------------------------------
15387    TMJONES PT844P01 pside.exe                     PS,TMJONES,TMJONES110299,,pside.exe, 
15276    JZARATE PT844P01 SQLPLUSW.EXE                                                       
15395    certora PT844P01 PSAPPSRV@st-sun01 (TNS V1-V3) VP1,,JRSMITH031198,PT81,PSAPPSRV,    
15409    certora PT844P01 PSSAMSRV@st-sun01 (TNS V1-V3) PTDMO,certora,st-sun01,PT81,PSSAMSRV,
15402    certora PT844P01 PSAPPSRV@st-sun01 (TNS V1-V3) VP1,,JRSMITH031198,PT81,PSAPPSRV,    
8364     oracle           oracle@st-sun01 (PMON)                                             
8366     oracle           oracle@st-sun01 (DBW0)                                             
8368     oracle           oracle@st-sun01 (LGWR)                                             
8370     oracle           oracle@st-sun01 (CKPT)                                             
8372     oracle           oracle@st-sun01 (SMON)                                             
8374     oracle           oracle@st-sun01 (RECO)                                             

11 rows selected.

Browser Connections – (PIA)

For browser connections (PIA connections), you can retrieve the following client information:

"%oprid%,%osusername%,%machinename%,%tuxedo_domain%,%executable%,""

When the user is connected, you should see the application server process that is executing the transaction for the browser. For example, the PSAPPSRV handles the large queries executed by user connections. Let's assume for this example that the PSAPPSRV is processing the current client request.

Adding to what was previously displayed, this is a PIA client, PREILLY060499 (connecting through a web browser), signing on as PSOFT/PTDMO, to Domain PT81 and utilizing two application server processes (PSAPPSRV).

From a monitoring perspective, there is no difference between a three-tier windows connection and a PIA browser connection.

SQL> /
SPID     OSUSER  USERNAME PROGRAM                       CLIENTINFO                           
-------- ------- -------- ----------------------------- -------------------------------------
15387    TMJONES PT844P01 pside.exe                     PS,TMJONES,TMJONES110299,,pside.exe, 
15276    JZARATE PT844P01 SQLPLUSW.EXE                                                       
15395    certora PT844P01 PSAPPSRV@st-sun01 (TNS V1-V3) PTDMO,,PREILLY060499,PT81,PSAPPSRV,  
15409    certora PT844P01 PSSAMSRV@st-sun01 (TNS V1-V3) PTDMO,certora,st-sun01,PT81,PSSAMSRV,
15402    certora PT844P01 PSAPPSRV@st-sun01 (TNS V1-V3) PTDMO,,PREILLY060499,PT81,PSAPPSRV,  
8364     oracle           oracle@st-sun01 (PMON)                                             
8366     oracle           oracle@st-sun01 (DBW0)                                             
8368     oracle           oracle@st-sun01 (LGWR)                                             
8370     oracle           oracle@st-sun01 (CKPT)                                             
8372     oracle           oracle@st-sun01 (SMON)                                             
8374     oracle           oracle@st-sun01 (RECO)                                             

11 rows selected.

Process Scheduler Connections

For the Process Scheduler connection, you can expect to see the following information:

"%oprid%,%osusername%,%machinename%,,%executable%,"

Adding to what was previously displayed, this is the Process Scheduler running, started by OSUSER certora, from server st-sun01, logged in as PSOFT with a user ID of PTDMO.

SQL> /
SPID     OSUSER  USERNAME PROGRAM                       CLIENTINFO                          
-------- ------- -------- ----------------------------- ------------------------------------
15387    TMJONES PT844P01 pside.exe                     PS,TMJONES,TMJONES110299,,pside.exe,
15276    JZARATE PT844P01 SQLPLUSW.EXE                                            
15435    certora PT844P01 psprcsrv@st-sun01 (TNS V1-V3) PTDMO,certora,st-sun01,,psprcsrv,
15395    certora PT844P01 PSAPPSRV@st-sun01 (TNS V1-V3) PTDMO,,PREILLY060499,PT81,PSAPSRV,
15402    certora PT844P01 PSAPPSRV@st-sun01 (TNS V1-V3) PTDMO,,PREILLY060499,PT81,PSAPPSRV,
15409    certora PT844P01 PSSAMSRV@st-sun01 (TNS V1-V3) PTDMO,certora,st-sun01,PT81,PSSAMSRV,
8364     oracle           oracle@st-sun01 (PMON)                                             
8366     oracle           oracle@st-sun01 (DBW0)                                             
8368     oracle           oracle@st-sun01 (LGWR)                                             
8370     oracle           oracle@st-sun01 (CKPT)                                             
8372     oracle           oracle@st-sun01 (SMON)                                             
8374     oracle           oracle@st-sun01 (RECO)                                             

12 rows selected.

SQR Connections

For the SQR program connections, you can expect to see the following information:

"%oprid%,%spid%"

Adding to what was previously displayed, this is an SQR report run from the workstation JZARATE123199, submitted from the user ID PS, and having a PID of 15449.

SQL> /
SPID     OSUSER  USERNAME PROGRAM                       CLIENTINFO                           
-------- ------- -------- ----------------------------- -------------------------------------
15387    TMJONES PT844P01 pside.exe                     PS,TMJONES,TMJONES110299,,pside.exe, 
15276    JZARATE PT844P01 SQLPLUSW.EXE                                                       
15449    JZARATE PT844P01 sqrw.exe                      PS,15449                             
15435    certora PT844P01 psprcsrv@st-sun01 (TNS V1-V3) PTDMO,certora,st-sun01,,psprcsrv,    
15395    certora PT844P01 PSAPPSRV@st-sun01 (TNS V1-V3) PTDMO,,PREILLY060499,PT81,PSAPPSRV,  
15409    certora PT844P01 PSSAMSRV@st-sun01 (TNS V1-V3) PTDMO,certora,st-sun01,PT81,PSSAMSRV,
15402    certora PT844P01 PSAPPSRV@st-sun01 (TNS V1-V3) PTDMO,,PREILLY060499,PT81,PSAPPSRV,  
8364     oracle           oracle@st-sun01 (PMON)                                             
8366     oracle           oracle@st-sun01 (DBW0)                                             
8368     oracle           oracle@st-sun01 (LGWR)                                             
8370     oracle           oracle@st-sun01 (CKPT)                                             
8372     oracle           oracle@st-sun01 (SMON)                                             
8374     oracle           oracle@st-sun01 (RECO)                                             

13 rows selected.

COBOL Connections

For the COBOL program connections, you can expect to see the following information:

"%oprid%,%osusername%,%machinename%,,%executable%,"

Adding to what was previously displayed, this a COBOL program PTPTEDIT, run from the workstation JZARATE123199, submitted from PeopleSoft user ID PS.

SQL> /
SPID     OSUSER  USERNAME PROGRAM                       CLIENTINFO                           
-------- ------- -------- ----------------------------- -------------------------------------
15387    TMJONES PT844P01 pside.exe                     PS,TMJONES,TMJONES110299,,pside.exe, 
15276    JZARATE PT844P01 SQLPLUSW.EXE                                                       
15449    JZARATE PT844P01 sqrw.exe                      PS,329                               
15451    JZARATE PT844P01 PTPTEDIT.exe                  PS,JZARATE,JZARATE123199,,PTPTEDIT,  
15435    certora PT844P01 psprcsrv@st-sun01 (TNS V1-V3) PTDMO,certora,st-sun01,,psprcsrv,    
15395    certora PT844P01 PSAPPSRV@st-sun01 (TNS V1-V3) PTDMO,,PREILLY060499,PT81,PSAPPSRV,  
15409    certora PT844P01 PSSAMSRV@st-sun01 (TNS V1-V3) PTDMO,certora,st-sun01,PT81,PSSAMSRV,
15402    certora PT844P01 PSAPPSRV@st-sun01 (TNS V1-V3) PTDMO,,PREILLY060499,PT81,PSAPPSRV,  
8364     oracle           oracle@st-sun01 (PMON)                                             
8366     oracle           oracle@st-sun01 (DBW0)                                             
8368     oracle           oracle@st-sun01 (LGWR)                                             
8370     oracle           oracle@st-sun01 (CKPT)                                             
8372     oracle           oracle@st-sun01 (SMON)                                             
8374     oracle           oracle@st-sun01 (RECO)                                             

14 rows selected.

Windows Workstation and Browser Connections Multithreading Through the Application Server

For multithreaded connections, you can retrieve the following client information:

"%oprid%,%osusername%,%machinename%,%tuxedo_domain%,%executable%,""

The application server multithreads incoming three-tier Windows or PIA browser connections through the application server processes already connected to the database. The next several examples illustrate a continual changing of the monitoring information displayed through the application server "thread" based on user activity and incoming requests.

Adding to what was previously displayed, accessing the database again from the three-tier Windows workstation JRSMITH031198 reflects a change in the user ID VP1 and client machine name for the both application server processes.

SQL> /
SPID     OSUSER  USERNAME PROGRAM                       CLIENTINFO                           
-------- ------- -------- ----------------------------- -------------------------------------
15387    TMJONES PT844P01 pside.exe                     PS,TMJONES,TMJONES110299,,side.exe,  
15276    JZARATE PT844P01 SQLPLUSW.EXE                                                       
15449    JZARATE PT844P01 sqrw.exe                      PS,329                               
15451    JZARATE PT844P01 PTPTEDIT.exe                  PS,JZARATE,JZARATE123199,,PTPTEDIT,  
15435    certora PT844P01 psprcsrv@st-sun01 (TNS V1-V3) PTDMO,certora,st-sun01,,psprcsrv,    
15395    certora PT844P01 PSAPPSRV@st-sun01 (TNS V1-V3) VP1,,JRSMITH031198,PT81,PSAPPSRV,    
15402    certora PT844P01 PSAPPSRV@st-sun01 (TNS V1-V3) VP1,,JRSMITH031198,PT81,PSAPPSRV,    
15409    certora PT844P01 PSSAMSRV@st-sun01 (TNS V1-V3) PTDMO,certora,st-sun01,PT81,PSSAMSRV,
8364     oracle           oracle@st-sun01 (PMON)                                             
8366     oracle           oracle@st-sun01 (DBW0)                                             
8368     oracle           oracle@st-sun01 (LGWR)                                             
8370     oracle           oracle@st-sun01 (CKPT)                                             
8372     oracle           oracle@st-sun01 (SMON)                                             
8374     oracle           oracle@st-sun01 (RECO)                                             

14 rows selected.

Adding to what was previously displayed, accessing the database from the browser on machine PREILLY060499 illustrates a change in the user ID PTDMO and client machine name for one of the application server processes.

SQL> /
SPID     OSUSER  USERNAME PROGRAM                       CLIENTINFO                           
-------- ------- -------- ----------------------------- -------------------------------------
15387    TMJONES PT844P01 pside.exe                     PS,TMJONES,TMJONES110299,,pside.exe, 
15276    JZARATE PT844P01 SQLPLUSW.EXE                                                       
15449    JZARATE PT844P01 sqrw.exe                      PS,329                               
15451    JZARATE PT844P01 PTPTEDIT.exe                  PS,JZARATE,JZARATE123199,,PTPTEDIT,  
15435    certora PT844P01 psprcsrv@st-sun01 (TNS V1-V3) PTDMO,certora,st-sun01,,psprcsrv,    
15395    certora PT844P01 PSAPPSRV@st-sun01 (TNS V1-V3) PTDMO,,PREILLY060499,PT81,PSAPPSRV,  
15409    certora PT844P01 PSSAMSRV@st-sun01 (TNS V1-V3) PTDMO,certora,st-sun01,PT81,PSSAMSRV,
15402    certora PT844P01 PSAPPSRV@st-sun01 (TNS V1-V3) VP1,,JRSMITH031198,PT81,PSAPPSRV,    
8364     oracle           oracle@st-sun01 (PMON)                                             
8366     oracle           oracle@st-sun01 (DBW0)                                             
8368     oracle           oracle@st-sun01 (LGWR)                                             
8370     oracle           oracle@st-sun01 (CKPT)                                             
8372     oracle           oracle@st-sun01 (SMON)                                             
8374     oracle           oracle@st-sun01 (RECO)                                             

14 rows selected.

Adding to what was previously displayed, accessing the database from the three-tier Windows workstation JRSMITH031198 reflects a change in the user ID VP1 and client machine name for one of the application server processes.

SQL> /
SPID     OSUSER  USERNAME PROGRAM                       CLIENTINFO                           
-------- ------- -------- ----------------------------- -------------------------------------
15387    TMJONES PT844P01 pside.exe                     PS,TMJONES,TMJONES110299,,pside.exe, 
15276    JZARATE PT844P01 SQLPLUSW.EXE                                                       
15449    JZARATE PT844P01 sqrw.exe                      PS,329                               
15451    JZARATE PT844P01 PTPTEDIT.exe                  PS,JZARATE,JZARATE123199,,PTPTEDIT,  
15435    certora PT844P01 psprcsrv@st-sun01 (TNS V1-V3) PTDMO,certora,st-sun01,,psprcsrv,    
15395    certora PT844P01 PSAPPSRV@st-sun01 (TNS V1-V3) VP1,,JRSMITH031198,PT81,PSAPPSRV,    
15409    certora PT844P01 PSSAMSRV@st-sun01 (TNS V1-V3) PTDMO,certora,st-sun01,PT81,PSSAMSRV,
15402    certora PT844P01 PSAPPSRV@st-sun01 (TNS V1-V3) PTDMO,,PREILLY060499,PT81,PSAPPSRV,  
8364     oracle           oracle@st-sun01 (PMON)                                             
8366     oracle           oracle@st-sun01 (DBW0)                                             
8368     oracle           oracle@st-sun01 (LGWR)                                             
8370     oracle           oracle@st-sun01 (CKPT)                                             
8372     oracle           oracle@st-sun01 (SMON)                                             
8374     oracle           oracle@st-sun01 (RECO)                                             

14 rows selected.

Adding to what was previously displayed, the following illustrates accessing the database from the three-tier Windows workstation JRSMITH031198 executing a functional process that requires use of all of the application server processes. This is reflected in the change in the user ID VP1, and client machine name for all of the application server processes.

Note: Because the SQR program has completed running, there is no SQR information available.

SQL> /
SPID     OSUSER  USERNAME PROGRAM                       CLIENTINFO                           
-------- ------- -------- ----------------------------- -------------------------------------
15387    TMJONES PT844P01 pside.exe                     PS,TMJONES,TMJONES110299,,pside.exe, 
15276    JZARATE PT844P01 SQLPLUSW.EXE                                                       
15451    JZARATE PT844P01 PTPTEDIT.exe                  PS,JZARATE,JZARATE123199,,PTPTEDIT,  
15435    certora PT844P01 psprcsrv@st-sun01 (TNS V1-V3) PTDMO,certora,st-sun01,,psprcsrv,    
15395    certora PT844P01 PSAPPSRV@st-sun01 (TNS V1-V3) VP1,,JRSMITH031198,PT81,PSAPPSRV,    
15409    certora PT844P01 PSSAMSRV@st-sun01 (TNS V1-V3) VP1,,JRSMITH031198,PT81,PSSAMSRV,    
15402    certora PT844P01 PSAPPSRV@st-sun01 (TNS V1-V3) VP1,,JRSMITH031198,PT81,PSAPPSRV,    
8364     oracle           oracle@st-sun01 (PMON)                                             
8366     oracle           oracle@st-sun01 (DBW0)                                             
8368     oracle           oracle@st-sun01 (LGWR)                                             
8370     oracle           oracle@st-sun01 (CKPT)                                             
8372     oracle           oracle@st-sun01 (SMON)                                             
8374     oracle           oracle@st-sun01 (RECO)                                             

13 rows selected.

Adding to what was previously displayed, the application server has been shut down and the COBOL process PTPTEDIT has completed. All clients have logged off. The Process Scheduler is still active.

SQL> /
SPID     OSUSER  USERNAME PROGRAM                       CLIENTINFO                           
-------- ------- -------- ----------------------------- -------------------------------------
15276    JZARATE PT844P01 SQLPLUSW.EXE                                                       
15435    certora PT844P01 psprcsrv@st-sun01 (TNS V1-V3) PTDMO,certora,st-sun01,,psprcsrv,    
8364     oracle           oracle@st-sun01 (PMON)                                             
8366     oracle           oracle@st-sun01 (DBW0)                                             
8368     oracle           oracle@st-sun01 (LGWR)                                             
8370     oracle           oracle@st-sun01 (CKPT)                                             
8372     oracle           oracle@st-sun01 (SMON)                                             
8374     oracle           oracle@st-sun01 (RECO)                                             

8 rows selected.

Monitoring PeopleSoft MODULE and ACTION Information

In addition to the CLIENT_INFO field, PeopleTools also populates the MODULE and ACTION fields of the V$SESSION and V$SQL dynamic views. This provides increased monitoring capabilities if you use Oracle performance monitoring utilities, including:

  • Oracle Enterprise Manager

  • Oracle Database Resource Manager

  • Oracle Automatic Workload Repository

By monitoring MODULE and ACTION values you can:

  • Provide more specific PeopleSoft information for several Oracle performance monitoring tools.

  • View and analyze performance and system resource usage for selected PeopleSoft application modules.

  • Write custom SQL to aggregate PeopleSoft performance and system usage information based on the MODULE, ACTION, and CLIENT_INFO values.

Depending on the type of connection, or the PeopleTools feature being used, the system populates the MODULE and ACTION fields with the information described in the following table.

PeopleSoft Technology

MODULE Value

ACTION Value

application server (browser connections)

PeopleSoft component name

PeopleSoft page name

Integration Broker

service operation name

PeopleCode event

Application Engine

'PSAE'

Application Engine program name, section name, step, and type.

Each SQL statement in V$SQL has a MODULE field populated based on the MODULE field of the session that first submitted the SQL. You can write additional SQL to obtain valuable performance information aggregated based on the values of these fields.

Keeping in mind that the usage of the MODULE and ACTION values is intended mainly to be used within the context of Oracle performance monitoring utilities, to become familiar with the type of information provided you can issue SQL queries, such as the following samples:

select module, action, client_info from v$session;

or

set linesize 200
select p.spid,
   substr(s.osuser,1,10) osuser,
   substr(s.username,1,8) username,
   substr(s.program,1,24) program,
   substr(s.client_info,1,60) ClientInfo,
   substr(s.module,1,48) module,
   substr(s.action,1,32) action
from v$session s, v$process p
where s.paddr=p.addr
and s.osuser is not null
order by s.osuser;

Exposing PeopleSoft User Information Through the CLIENT_IDENTIFIER Column

Additional monitoring information was included enhancing the availability of PeopleSoft user information in Oracle products like Oracle Audit Vault and Oracle Enterprise Manager. PeopleSoft user ID information is also stored in the CLIENT_IDENTIFIER column of the V$SESSION table.

The CLIENT_IDENTIFIER column contains only the user ID, whereas the CLIENT_INFO column also contains the user ID value, but it is typically accompanied by other user information, like machine name for example. In some cases, a monitoring application may only need the user ID information. To get this information from the CLIENT_INFO column would require programmatic transformation and parsing of the CLIENT_INFO string. Displaying only the user ID in the CLIENT_IDENTIFIER column, simplifies the retrieval of the user ID by products like Oracle Audit Vault and Oracle Enterprise Manager. No further transformation or parsing of the string is required.

Database administrators can also retrieve the information directly from the database with queries similar to the following:

SQL> select module, client_identifier, client_info from v$session where module = 'pside.exe';

MODULE          CLIENT_IDENTIFIER       CLIENT_INFO
--------------- ----------------------  ------------------------------
pside.exe       QEDMO                   QEDMO,bng2,BENG-PC,,pside.exe,

The example above displays the User ID information in both the CLIENT_INFO and the CLIENT_IDENTIFIER columns. The latter can be used by Oracle Audit Vault. The user ID information can be retrieved from the following connection types:

  • two-tier connections.

  • three-tier connections.

  • programs run through Process Scheduler.

The following sections provide sample queries and results.

Example: Working with CLIENT_IDENTIFIER Information and Three-Tier Connections

The following query displays the user ID information associated with a three-tier connections:

SQL> select module, client_identifier, client_info from v$session where client_identifier like 'QEDMO%;

MODULE                                        CLIENT_IDENTIFIER           CLIENT_INFO
--------------------------------------------  --------------------------  -------------------------------
PSAPPSRV@sp-lnx07.peoplesoft.com (TNS V1-V3)  QEDMO                       QEDMO,,10.138.230.162,FS850U02,PSAPPSRV,

In this example the user ID information is available under CLIENT_INTO and CLIENT_IDENTIFIER, however CLIENT_IDENTIFIER only stores the user ID information while CLIENT_INFO stores other information, like the client connection details.

Example: Working with CLIENT_IDENTIFIER Information and Process Scheduler

It is also possible to monitor the user ID for programs running through Process Scheduler. In the following example an Application Engine program ran through Process Scheduler using the user ID QESS. By running the following query, it is possible to display the user ID information.

SELECT module, client_identifier, client_info 
FROM v$session 
WHERE client_identifier like 'QESS%';

MODULE          CLIENT_IDENTIFIER               CLIENT_INFO
--------------  ------------------------------  -------------------------------------
PSAE            QESS                            QESS,,sp-lnx07.peoplesoft.com,,PSAESRV,