Understanding Oracle Administration
This appendix provides an overview of Oracle administration and discusses how to:
Monitor PeopleSoft client database connections.
Set the number of temporary tables.
Understanding Oracle Administration
This section discusses:
NET9i/10g.
PeopleSoft servers and the Oracle connection string.
NET9i/10gNET9i/10g offers peer to peer connectivity and a multi-protocol interchange (MPIC). The product is installed as two or more elements: the Transparent Network Substrate (TNS), the Oracle Protocol Adapter, and the multi-protocol interchange.
NET9i/10g uses a few configuration files (SQLNET.ORA and TNSNAMES.ORA). Configuration files can be created using a system editor, or with the NET9i/10g Assistant.
PeopleSoft Servers and the Oracle Connection StringThe format of the Oracle connect string used to connect to the database is userid/password@service_name for all PeopleSoft processes, including online, batch, and application server processes.
This makes setup and configuration easy for platform configurations that can support PeopleSoft batch server processes or application server processes. However, performance for the batch processes and application server processes on a server that also functions as the database server is slightly degraded, due to the overhead involved in routing through SQL*NET.
In the current release, PeopleSoft provides a mechanism for you to indicate which connect string to use. This mechanism is a flag that you set while configuring the application server or the Process Scheduler. The flag is set in the respective configuration files for application server or the Process Scheduler processes.
When configuring an application server or the Process Scheduler, you can modify the parameters in the Database Options section if desired.
Values for config section - Database Options UseLocalOracleDB=0 ;ORACLE_SID= EnableDBMonitoring=0 OracleDisableFirstRowsHint=0 Do you want to change any values (y/n)? [n]:
Following are descriptions of the Database Options parameters:
|
Parameter |
Description |
|
UseLocalOracleDB |
Indicates if the PS database that you are connecting to is in a Local Oracle SID. The default is 0, meaning that the DB you are connecting to is remote. The resultant connect string is in the following format: userid/password@service_name. If you set this to 1, then you are indicating to our processes to use the following connect string when attempting to connect to the target database: userid/password. This implies a local connection. If you decide to use UseLocalOracleDB, then you must add the BEQUEAH_DETACH=YES parameter to the SQLNET.ORA file of the machine where you are setting up the App or Batch servers. This enables Oracle to cleanup any zombie DB processes spawned on behalf of PeopleSoft transactions left over from aborted transactions |
|
Oracle_SID |
Indicates for a Local Oracle connection only, the name of the Local ORACLE_SID that you want the PeopleSoft processes to connect to. Many sites set up more than one ORACLE_SID on their servers. This parameter gives you the ability to choose which ORACLE_SID you wish to connect to when connecting in Local mode. |
|
EnableDBMonitoring |
This parameter enables or disables DB monitoring of three-tier connections. This feature is covered later in this chapter. |
|
OracleDisableFirstRowsHint |
This parameter enables or disables the inclusion of the Oracle FIRST_ROWS hint on PeopleSoft search pages that use the %First_Rows meta-SQL variable. For certain SQL constructs, the use of the FIRST_ROWS hint can severely degrade performance to an unacceptable degree. This performance reduction occurs primarily on search pages that are ultimately based on views with multi-table joins. Specify a value of 1 to apply this parameter and disable the FIRST_ROWS hint. SQL statements generated by search page PeopleCode that includes the %First_Rows meta-SQL variable won't contain FIRST_ROWS. The default value of this parameter is 0, which enables SQL containing the FIRST_ROWS hint to be generated. |
The following tables describe the relationship between the UseLocalOracleDB parameter and the ORACLE_SID environment variable.
|
UseLocalOracleDB Flag |
The target database is local |
The target database is remote |
|
0 is the default setting Internally Psoft will generate the following connect string when attaching to the target database: UID/PW@TNS_ALIAS |
Access will be made via TNSNAMES |
Access will be made via TNSNAMES |
|
1 is the setting you use if you intend to use a Local Oracle DB. Internally Psoft will generate the following connect string when attaching to the target database: UID/PW (Note the omission of the TNS_ALIAS.) |
Access will default to the Local DB as designated by the ORACLE_SID environment variable If the ORACLE environment variable TWO_TASK is set to a valid TNS_ALIAS, then this would also work. The existence of the TWO_TASK environment variable is in effect overriding the generated connect string. |
To choose this option does not make sense if it is your intention to use a Local Oracle DB. This combination will work if the ORACLE environment variable TWO_TASK is set to a valid TNS_ALIAS. You are in effect overriding the generated connect string. |
|
ORACLE_SID Parameter |
UseLocalOracleDB Flag |
UseLocalOracleDB Flag |
|
This parameter is delivered in the application server and Process Scheduler configuration file commented out. This indicates that the default setting is however the current ORACLE_SID environment variable is set. |
0 The target database is remote |
1 The target database is local The ORACLE_SID parameter is not enabled (commented out) therefore ORACLE_SID for this process will default to the current ORACLE_SID environment variable. |
|
ORACLE_SID=xxxxxxx where xxxxxxxx equals a valid ORACLE_SID for the server that this process is running on. |
If UseLocalOracleDB Flag is set to zero, then enabling ORACLE_SID is invalid since you are indicating a remote connection, the value associated with the ORACLE_SID parameter will be ignored. |
If UseLocalOracleDB Flag is set to one, and ORACLE_SID is enabled, the value associated with the ORACLE_SID parameter will be exported as an OS environment variable thus overriding the current ORACLE_SID environment variable. |
See Also
Monitoring PeopleSoft Client Database ConnectionsThis section provides an overview of PeopleSoft client database connections and discusses how to:
Use client monitoring.
Track PeopleSoft database connections by PeopleSoft User ID.
Understanding PeopleSoft Client Database ConnectionsPeopleSoft provides the ability to monitor PeopleSoft Window clients connected to the database server from both two-tier and three-tier workstations with much improved informational content.
Without this feature, an administrator could not obtain any specific information regarding the user and the associated transaction when a user accessed the database. This was mainly due to the fundamentals of our security implementation, and the inherent nature of three-tier architecture wherein many clients connect to one application server, but only the application server maintains connections to the database server. Consequently, when querying the database, an administrator would see application server processes and two-tier clients connected to the database as the schema owner, not by specific workstations or PeopleSoft operators ID’s.
To illustrate this concept, examine the following graphic. Notice the individual application server processes maintain the connection to the database server, not the individual client workstations.
PeopleSoft client database connections
Note. In this example, assume that the number of workstations is n, where n may equal anywhere from 10 to 1000 clients.
To illustrate this concept further, suppose one of your users has executed an extremely inefficient query that severely impacts the rest of the database users. A Database Administrator would want to identify that user and take appropriate action. Without an ability to monitor users you would probably have to terminate the physical connection, which in a three-tier scenario would mean dropping the connection between the application server and the database server. This could potentially affect hundreds of users.
We now associate the PeopleSoft User ID (and some other information, such as OSUserName, MachineName, AppServerDomainName, ProgramEexecutable) with each connection or transaction. This allows us to associate activity on the database server with a particular workstation and operator. This information is stored in the CLIENT_INFO column of the V$SESSION dynamic view. Monitoring enabled is the new default behavior for two-tier connections.
For three-tier connections, the PeopleSoft systems administrator has the option to enable this feature by setting the EnableDBMonitoring flag to ‘1’ in the Application Server configuration file. (PSAPPSRV.CFG).
Some possible uses of this feature include system-wide troubleshooting, performance monitoring, "chargeback" accounting, and security audits for your system.
Using Client MonitoringYou can use client monitoring for the following:
COBOL Programs are always monitored.
SQR programs are always monitored.
Process Scheduler is always monitored.
The two-tier client is always monitored.
For the three-tier client (whether it be a Window-based workstation or Browser) the ability to monitor is configurable through PSADMIN in the Database Options configuration section.
Tracking PeopleSoft Database Connections by PeopleSoft User IDThis 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 client connections.
Application server process connections.
Three-tier client connections on Windows.
Three-tier client connections on PIA clients.
Process Scheduler connections.
SQR connections.
COBOL connections
Window 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 for two-tier and three-tier connections. The following sections describe the information returned for various scenarios.
Legend
JZARATE (uppercase) is a NETWORK login ID for the window workstations and JZARATE123199 is the window client MACHINENAME.
TMJONES (uppercase) is a NETWORK login ID for the window workstations and TMJONES110299 is the window client MACHINENAME.
JRSMITH (uppercase) is a NETWORK login ID for the window workstations and JRSMITH031198 is the window client MACHINENAME.
PREILLY (uppercase) is a NETWORK login ID for the window workstations and PREILLY060499 is the window client MACHINENAME.
PT844P01 is the PS schema (PS SYSADM ID or Access ID).
PT81 is an example of a Tuxedo domain name.
PTDMO, VP1, and PS are PeopleSoft User IDs used to signon to the database from the various clients
oracle (lowercase) is the owner id of all of the Oracle processes
certora (lowercase) is the Unix login id of the PS administrator that started 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 used to monitor the client info from 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.
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 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 appserver processes connecting to the database as OPRID 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 Client Connections – Window Workstations
For the three-tier connections, you can retrieve the following client information: "%oprid%,%osusername%,%machinename%,%tuxedo_domain%,%executable%,""
When the three-tier client is connected, then you should see the application server process that is executing the transaction for the client. For example, the PSAPPSRV handles the large queries executed by three-tier clients. Let’s assume for this example that the PSAPPSRV is processing the current client request.
Adding to what was previously displayed, this a three-tier client workstation JRSMITH031198, signing on as PSOFT oprid 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.
Three-Tier Client Connections – PIA Clients
Similarly, for the three-tier PIA client connections, you can retrieve the following client information: "%oprid%,%osusername%,%machinename%,%tuxedo_domain%,%executable%,""
When the three-tier client is connected, then you should see the application server process that is executing the transaction for the client. For example, the PSAPPSRV handles the large queries executed by three-tier clients. 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 (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 window client and a PIA client.
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.
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 oprid 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,PSAPPSRV, 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.
For the SQR Program connections, you can expect to see the following information: "%oprid%,%spid%"
Adding to what was previously displayed, this is a SQR report run from client workstation JZARATE123199, submitted from PSOPRID 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.
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 process PTPTEDIT run from client workstation JZARATE123199, submitted from PSOPRID 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.
Window and Browser Connections Multithreading Through the Application Server
For the three-tier connections, you can retrieve the following client information: "%oprid%,%osusername%,%machinename%,%tuxedo_domain%,%executable%,""
The application server multithreads the incoming three-tier client through the application server processes already connected to the database. The next several displays illustrate a continual changing of the monitoring information displayed through the application server ‘thread’ based on incoming three-tier client activity.
Adding to what was previously displayed, accessing the database again from the three-tier window client JRSMITH031198 reflects a change in the User ID (VP1) and client machinename for the both 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, 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 three-tier (PIA) client PREILLY060499 illustrates a change in the User ID (PTDMO) and client machinename 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 window client JRSMITH031198 reflects a change in the User ID (VP1) and client machinename 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, accessing the database from the three-tier window client JRSMITH031198 executing some functional process that requires use of all of the application server processes. This is reflected in the change in the User (VP1) and client machinename for all of the application server processes. Note the absence of the SQR information. The SQR has completed so no information is 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.
Setting the Number of Temporary TablesNormally you will leave the number of temporary tables set to the default of three. You may need to change this setting for optimal performance, depending on various aspects of your implementation, including account transaction volumes, benchmark numbers for the current hardware and database platform, as well as your service-level requirements. Use the following procedure if you need to adjust the number of temporary tables to improve performance in your implementation.
To set the number of temporary tables:
Select PeopleTools, Utilities, Administration, PeopleTools Options.
Set the Temp Table Instances (Total) and Temp Table Instances (Online) fields to the desired settings.
Note. Temp Table Instances (Total) should always be set to the same values as Temp Table Instances (Online), unless you have been instructed otherwise in the application documentation.
Save your changes.
Note. The total number of instances generated consists of the allocations specified on the PeopleTools Options page plus the allocations
specified for each individual Application Engine program.
This chapter assumes that you have Oracle DBA experience, have attended Oracle’s Introductory DBA classes, or have read the
Oracle’s Server Concepts and DBA Manuals. You should also review the Oracle Flexible Architecture (OFA) standard, and adopt
as much of this as possible during your initial Oracle install.
See Also
Enterprise PeopleTools 8.49 PeopleBook: PeopleSoft Application Engine
PeopleSoft Red Paper “PeopleSoft Batch Performance Tuning on Oracle”
Using Locally Managed TablespacesPeopleSoft supports the latest Oracle locally managed tablespace (LMT) syntax to control segment space allocation. A Locally Managed Tablespace (LMT) is a tablespace that manages its own extents maintaining a bitmap in each data file to keep track of the free or used status of blocks in that data file. Each bit in the bitmap corresponds to a block or a group of blocks. When the extents are allocated or freed for reuse, Oracle changes the bitmap values to show the new status of the blocks. These changes do not generate rollback information because they do not update tables in the data dictionary (except for tablespace quota information), unlike the default method of Dictionary - Managed Tablespaces.
Benefits of using LMTs include:
Locally managed tablespaces do not record free space in the data dictionary, it reduces contention on these tables.
Local management of extents automatically tracks adjacent free space, eliminating the need to coalesce free extents.
Avoids recursive space management operations, which can occur in dictionary-managed tablespaces if consuming or releasing space in an extent results in another operation that consumes or releases space in a rollback segment or data dictionary table.
Sizes of extents that are managed locally can be determined automatically by the system. Alternatively, all extents can have the same size in a locally managed tablespace.
Changes to the extent bitmaps do not generate rollback information because they do not update tables in the data dictionary (except for special cases such as tablespace quota information).
Reduced fragmentation No coalescing required.
Specifically, the following scripts have been modified to use this syntax: UTLSPACE.SQL, PTUPGDDL.SQL, and xxDDL.SQL. (Where 'xx' is the product code).
For example:
CREATE TABLESPACE PSINDEX DATAFILE '/u04/oradata/<SID>/psindex.dbf' SIZE 64M EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO ;
The following guidelines intend to help you determine which tables to migrate to the appropriate ‘LARGE’ tablespaces based on table size during the move to production. If you change tablespace assignments, you first need to run SETASPACE.SQR to synchronize the PeopleSoft metadata with the changes made to the Oracle catalog with respect to any new table space assignments. Also, we recommend that you use LMTs with 1M extent size and ASSM for large objects (> 10000 blocks) and 128K extent size for smaller objects (<10000 blocks). To determine the size of any object (table or index) in blocks > 10000, execute the following SQL statement:
select segment_name, tablespace_name, blocks from user_extents where blocks > 10000 GROUP BY segment_name /
The following is an example of a large tablespace:
CREATE TABLESPACE PSLARGE DATAFILE '/u04/oradata/<SID>/pslarge.dbf' SIZE 64M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M SEGMENT SPACE MANAGEMENT AUTO;
The following is an example of a non-large tablespace:
CREATE TABLESPACE PSSMALL DATAFILE '/u04/oradata/<SID>/pssmall.dbf' SIZE 64M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K SEGMENT SPACE MANAGEMENT AUTO;