Monitoring PeopleSoft Database Connections
This section provides an overview of PeopleSoft database connections and discusses how to monitor 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.
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).
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.
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
Note: You must set the EnableAEMonitoring configuration setting to 1 to populate the MODULE and ACTION fields in V$SESSION and V$SQL views. By default, EnableAEMonitoring is set to 0 (disabled). To change the EnableAEMonitoring setting for an Application Server domain or a Process Scheduler domain, use the PSADMIN utility or manually modify the PSPRCS.CFG configuration file or the PSAPPSRV.CFG configuration file, then restart the respective servers.
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'.<AE prognam name>.<PID> |
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;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,