Transcript Using Oracle Data Guard to protect your database.

PeopleSoft for the Oracle
DBA
Mark Riley
Technical Director
Oracle
04/15/2005
Agenda
 Overview – What is PeopleSoft
 PeopleSoft’s Application Server (BEA Tux)
 Database Connectivity
 PeopleSoft Database Structure
 Keys and Indexing
 DDL
 Tablespaces
 Locking, Transactions and Concurrency
 Performance metrics
 Questions
Reference
Material
 Presentation is based on “PeopleSoft for the Oracle DBA”
David Kurtz (ISBN:1-59059-422-3)
 PSFT documentation is difficult to come by: PeopleBooks,
white papers, … have to be a customer to get documentation
–
PSFT Customer Connection – support web site
–
PSFT “Red Papers” – technical docs – best practices
 BEA eSupport: http://support.bea.com – sign up for free
access
 BEA Tux & WebLogic: http://e-docs.bea.com - free
 PSFT DBA forum: http://groups.yahoo.com/psftdba
PeopleSoft Guiding Principle
 PeopleSoft founded in 1987 and rode the crest of the internet
boom
 From inception PSFT was designed to be PORTABLE across
database platforms
 Oracle, SQL Server, DB2, Sybase, Informix – Oracle is
largest marketshare
 The PSFT data model is uniform, some variations in column
definition, there can be differencing in indexes
 PSFT is platform agnostic
What is PeopleSoft
 2003 JD Edwards purchase – btw: not included in
this presentation
 The product that were originally PSFT are now
labeled: “PeopleSoft Enterprise”
 The products that were formerly JDE are now called
“PeopleSoft Enterprise One” & “PeopleSoft World”
PeopleSoft Product Lines
 Campus Solutions – universities, higher education
 CRM:
–
PSFT used Vantive CRM internally in 1997
–
Liked it so much the bought the company in 1999
–
Initially sold along side PSFT, then rewritten to run under PeopleTools, rereleased as PeopleSoft CRM 8
 Financial Management: complete fin apps, and accounting
 Human Capital Management (HCM):
–
Formerly labeled HRMS until v8.1
–
Traditionally strongest PSFT product
–
Time & Labor, Benefits, Student administration,
–
Payroll (North America + other payroll interfaces for other countries)
PeopleSoft Product Lines
 Service Automation:
–
Provides self service foundation for various modules
–
From v8.44 support for offline mobile clients
 Supplier Relationship Management - purchase and procurement
 Supply Chain Management: B2B
 Enterprise Tools & Technology:
–
bundling of all PSFT proprietary technology and development tools (often
referred to as PeopleTools)
–
Delivered as part of standard product, so standard app can be customized
–
Can be licensed separately
PSFT Architecture
 Architecture has evolved over the years
–
Two tier up to v6 – tools (SQL*Net in Oracle case) had to be installed
on every desk top
 PIA – PeopleSoft Internet Architecture – pure internet
architecture: HTML, Java Servlet
 App Server: BEA Tux (introduced in V7, Best of breed
selection)
 Multiple databases: Database independence means no
stored procedures
 PeopleCode is proprietary procedural language (executed on
client 2-tier and app server 3-tier/4-tier
PeopleTools
 Consolidates many separate tools prior to V7
 Define Records: entire data model is defined using
Application Designer
–
A record can correspond to a table or view on the database
or a set of working storage variables
–
Indexes are defined
–
Application Designer will either build the objects or
generate DDL (column defs will differ dependent on the
database)
–
Different databases mean different physical DDL parms
PeopleTools
 Creating PeopleCode:
–
Proprietary, portable procedural programming language
–
Used to customize PSFT online and batch processes
–
In PSFT V8 App Designer can also be used as an interactive
debugging tool, where you can step through PeopleCode program
execution
 Defining Pages (formerly called panels):
–
Originally drawn in graphical design tool
–
What you see is what you get
–
In PSFT v8 @ runtime HTML pages are generated by the app server
and delivered to a browser
PeopleTools
 Defining Menus: define and maintain menu
navigation
 Upgrading:
–
App Designer is used to migrate sets of source code
(projects)
–
Projects can be exported and imported
–
Used during initial installation to deliver/apply patches
Other Tools
 DataMover:
–
Capable of importing and exporting PSFT data
–
During installation imports all base objects and data
–
Can be used to migrate data from one PSFT instance to
another – may not be fast enough for large objects
–
2-tier connection
 Upgrade Assistant: v8 feature to automate upgrades
and patches
Other Tools
 Application Engine:
–
Proprietary batch processor utility
–
Was rewritten in v8 (C++) so that it could execute
PeopleCode
 SQR
–
Procedural language
–
Licensed from Hyperion
–
Used for reporting and batch processing
Other Tools
 PSFT Query Tool
–
Still a windows based client utility
 Functionality available in the PIA
- Develop and run ad hoc queries (don’t need SQL
knowledge)
 Can be migrated with App Designer
 Segate Crystal Reports
–
Used for reports that require a sophisticated look and feel
–
Connects via ODBC to the database
–
Only runs on Windows
PeopleSoft Release History
 PSFT applications and PeopleTools have different version numbers that
change independently
 Some Enterprise releases must be considered major upgrades (v7.5 &
v8.4) – effectively new releases
 PeopleTools up to V5.x were 16-bit Windows apps (v5.1 has not been
supported since 1999)
 PeopleTools 6 is 32 bit Windows app (Win95 – 32b) – effectively still a 2tier app
 PeopleTools v7 released Sept. 1997 (first 3-tier release)
 PeopleTools v7.5 release May 1998 saw consolidation on the application
server, new Tux services combined several discreet service calls
PeopleSoft Release History
 PeopleTools v8.0 released late 1999 (pure internet client – JavaScript
rendering)
 PeopleTools V8.1 released August 2000
–
Windows 2-tier client still delivered though not supported
–
Application development is done via 2-tier & 3-tier modes
–
Query (ad hoc reporting) and nVision (reporting plug in for Excel) still exist as
Windows executables – alternative to PIA
 PeopleTools V8.4
 Second release of PIA
 ‘breadcrumb’ navigation replaced with portlet
 No longer includes windows 2-tier client (pstools.exe)
 With exception of Query and nVision only way to get at app is via PIA
Developing and Administering
PeopleSoft Systems
 The database has always been fundamental to PSFT
applications: must be efficient and solid
 Application developers and DBA's view the system differently
 Dev Tools provide a consistent, across all supported
platforms, mechanism to manage development and
upgrading (both a strength and a weakness)
–
Advantage: a PSFT developer can work on an app regardless of the
tech stack
–
Disadvantage: while developer defines the basic application, much of
the SQL is dynamic – does not appear in final form in the app code!!!
DBA identifies poorly performing SQL and developer can’t correlate to
source code
Developing and Administering
PeopleSoft Systems
 As far as DBA’s is concerned the application is a black box
 A large part of a DBA’s normal tasks must be done (sometime
retrofitted) via the PeopleSoft Tools, e.g. Application Designer
 DBA’s need to learn about the PSFT tools or they will be in for
some nasty surprises
–
If an index is added to a table it should be specified via the App
Designer or it could be lost (tale of two data dictionaries)
–
Changes to schema passwords must be done with PSFT tools or
synch’ed
–
Application server connects to database as ONE application user –
how can you track who’s running what in the database
Developing and Administering
PeopleSoft Systems
 “Some of the most successful PSFT
implementations are those were a DBA is dedicated
to the development to, fully integrated into the PSFT
project.”
PeopleSoft Application Server: BEA
Tuxedo
 BEA documentation” “middleware for building scalable multi-tier
client/server applications in a heterogeneous distributed environments”
 Tuxedo: Transactions Under Unix Extended for Distributed Operations
 Tuxedo has been around for ever – process activation, static database
connections, reduces resource consumption on database – highly
scalable
 Application Server does more of the work in V8
 Application Server executes all application server code and submits SQL
statements to database
 Java Servlets provide presentation layer, unpacking Tux messages,
writing JavaScript and HTML to web server file system and passing HTML
 PSFT has used Tux v6.5 since PSFT V7.5, PSFT v8.44
leverages Tux v8.1
PeopleSoft Application Server: BEA
Tuxedo
 Tux application server domain consists of a number of server processes
that communicate via shared memory segments and message queues
 Follows Unix interprocess communication model, where processes are
created and managed with standard Unix ipc functions
 No concept of protected and shared memory on windows, thus BEA
process manager service (Tux IPC helper service – tuxipc.exe) to mimic
UNIX ipc mechanism
 When Tux boots up the BBL bulletin board process is first –list available
services, alloc’s shared memory segment (MIB), some standard message
queues, PSFT domain PSTUXCFG and two semaphores – BBL hold
definition of the server domain and controls behavior
PeopleSoft Application Server: BEA
Tuxedo
 On Windows 3-tier:
 WSL – WorkStation listener is configured to listen on a
specified IP address/port for incoming connections from Tux
clients
 WSL spawns at least one WSH – WorkStation Handler
process that can be configured to spawn more WSH’s
 WSH will listen for incoming service requests on the same IP
address, and unless configured to use a specified range of
ports, will use the next available port(s) past the WSL
PeopleSoft Application Server: BEA
Tuxedo
 Initially a client connects to the WSL who then
assigns the client to a specific WSH.
 Clients must be configured to find the WSL.
 Can be configured to failover or load balance
 GUI configuration tool “Workstation Configuration
Manager”
PeopleSoft Application Server: BEA
Tuxedo
 PSFT delivers various server processes, each new release
adds more
 In PSFT v8.4 the following four servers are mandatory:
–
PSAPPSVR
–
PSSAMSRV
–
PSMONITORSRV
–
PSWATCHSRV
 When Application server is booted min number of these are
started
Database Connectivity
 All but one database object is contained in a single Oracle
schema
 All processes that connect to the database use the standard
PSFT login procedure, there after security is handle by the
application
 Database is often used by PSFT to refer to the collection of
tables in the administrative schema within an Oracle
database.
 PSFT recommends that each PSFT database be created in a
separate Oracle database – each instance can be started,
stopped, backed up & tuned independently
Database Connectivity
 Oracle database users: every process that makes a
2-tier connection to the database identifies itself with
a PSFT user or operator ID
 Database user accounts/schemas:
–
Owner/Access ID: contains most of the application objects
–
Connect ID: low security database user is used by the login
process
–
PS: contains a table that describes which PSFT databases
are contained in the Oracle database
Database Connectivity
 Owner ID/Access ID (SYSADM)
–
Schema contains nearly all the database objects
–
Keys to the kingdom
–
Privileges granted via role PSADMIN
 OwnerID is the schema that contains the objects
 AccessID is the database user that connects and
references the schema
 Standard installation there is one AccessID, which is
the same a OwnerID
Database Connectivity
 ConnectID, usually named ‘PEOPLE’
 From v8 on the first connection that each process
makes to the database is via this user
 Only has CREATE SESSION privilege via the
PSUSER role and SELECT on PSSTATUS,
PSOPRDEFN, PSACCESSPRFL.
 After successful password validation the process
connects as AccessID
Database Connectivity
 The PS Schema is used to hold the PSDBOWNER which maps the name
of the PSFT database to the schema in the database that holds it
 PS Schema and PS.PSDBOWNER are created during the installation
process (dbowner.sql):
–
GRANT CONNECT, RESOURCE, DBA TO PS IDENTIFIED BY PS;
–
CONNECT PS/PS;
–
CREATE TABLE PSDBOWNER (DBNAME VARCHAR2(8) NOT NULL, OWNERID VARCHAR2(8)
NOT NULL) TABLESPACE PSDEFAULT;
–
GRANT SELECT ON PSDBOWNER TO PUBLIC;
–
CONNECT SYSTEM/MANAGER;
–
REVOKE CONNECT, RESOURCE, DBA FROM PS;
 If you duplicate a PSFT database, using Oracle IMPORT/EXPORT the
PSDBOWNER must exist in the target database before the import is run
 PSDBOWNER table allows PSFT to manage multiple PSFT databases
within a single Oracle database
Database Connectivity
 The application server, COBOL programs, and the application
engine all connect to the database via this low level security
connection process.
 Direct shared memory connections – bequeath improves
performance
 Consider this if the process schedulers (concurrent
managers) are resident to the database
 You can determine the PSFT schema by: select ownerID from
ps.ownerdb where dbname = :1 (HR88)
 PSFT major and minor release can be obtained from
PSSTATUS table
Database Connectivity
 Operator encrypted passwords are stored in the
PSOPRDEFN table, retrieved and validated in the
connection process
 Crystal reports (standard, re-branded) connects via
ODBC – provides logion and row level security.
 SQR reports, originally form SQL solutions connects
directly to the PSFT schema (usually as SYSADM).
A wrapper PSFT process ‘pssqr’ controls report
formatting
PeopleSoft Database Structure
 A tale of two dictionaries …
 One of the ways PSFDT can deliver the same application to
any database platform is to have it’s own data dictionary!!!
(this is why it is so important to use application designer)
 Except when building DDL scripts and performing audits
PSFT never interrogates Oracles data dictionary
 PSFT/Oracle database contains:
–
The Oracle database dictionary
–
PSFT tables (includes most of the PSFT code and metadata!!!)
–
Application tables: user application data
PeopleSoft Database Structure
 It’s all in one schema
 Naming conventions are not rigorously followed
 In general, PeopleTools record names are prefixed
with ‘PS’ and the SQLTABLENAME is explicitly set in
PSRECDEFN to be the same as this name
 Application tables generally do not have their names
overridden
PeopleSoft Database Structure
 PSFT does not create or use Oracle database
privileges, synonyms or referential constraints!
 All objects and row level security are handled in the
application layer!
 The is good documentation on how to customize
PSFT, but there is no official explanation on the
meaning an use of PeopleTools objects.
PeopleSoft Database Structure
 Dictionary mapping:
–
DBA_TABLES, DBA_VIEWS = PSRECDEFN (<v7.x) or
PSRECDEFN & PSRECTBLSPC (>v8)
–
DBA_TAB_COLUMNS = PSRECFIELD (v7)
PSRECFIELDDB & PSRECFIELD (v8)
–
DBA_VIEWS = PSVIEWTEXT (v7) PSSQLDEFN &
PSSQLTEXTDEFN
–
DBA_INDEXES = PSINDEXDEFN
–
DBA_IND_COLUMNS = PSKEYDEFN
–
DBA_USERS = PSOPERDEFN
PeopleSoft Database Structure
 Unicode support introduced in PeopleTools v8.1, interesting implications
 Non-unicode DB, EMPLID is 11 chars field in PSFT and in Oracle DB
 If DB uses unicode (PSSTATUS.UNICODE_ENABLED=1) then EMPLID
is still defined by the Application Designer as a varchar2(33) and then
adds a constraint to enforce the original length
 The result: ~165K length constraints in vanilla HCM and ~500K in
Financials!!!
 Woops: constraints have to be loaded in the library cache and can add as
much as 4x times more time at parse!
 Don’t use Unicode unless you have a good business reason to do so.
PeopleSoft Database Structure
 Recursive PeopleTools SQL
 Normally we think of recursion as the SQL generated
by the parsing that queries the catalog
 Well PSFT does the same thing only with it’s own
catalog!
PeopleSoft Database Structure
 A system of version numbering and caching is used to
determine if an object is update
 PSVERSION holds a global version number and a version
number for each of the PSFT object types (records, panels,
…)
 When an object is changed and saved the object ver num is
incremented as well the global ver num is incremented.
 In a PSFT system you will frequently see selects on
PSVERSION
 BTW: PSFT does not use Oracle sequences – platform
independence
PeopleSoft Database Structure
 PSFT dynamically generates SQL to reference application
data definitions in the PeopleTools tables, in a similar manner
to Oracle recursively querying it’s catalog.
 It is essential that you use the Application Designer to make
all DDL changes.
 Changing them in the Oracle dictionary is going to lead to
trouble.
 Some Oracle specific object types that cannot be built by App
Designer: (partitioned, global temp, IOT’s)
 PSFT provides two diagnostic reports to help identify
discrepancies between the two data dictionaries (DDDAUDIT,
SYSAUDIT)
Keys and Indexing
 PSFT uses ‘keys’ defined in the PSFT data
dictionary to generate SQL for the application and to
generate the indexes
 PSFT never uses explicitly defined constraints in
Oracle (except unicode length checks)
 Field validation and referential integrity is defined in
the PSFT data dictionary and executed/enforced in
the PIA.
Keys and Indexing
 In Application Designer, fields can be assigned
certain ‘key’ attributes (key, duplicate order key,
alternate search, list box item
 They control the behavior of the PeopleSoft
application and the SQL that get generated.
 BTW: PSFT is parse heavy
 Produces indexes that result in good performance
 It also produces lots of index data, typically index
extents are 105% of the base data
Keys and Indexing
 Key attribute uniquely identifies a row therefore
PSFT builds a unique index
 Indexes generated by Application Designer follow
the naming convention: INDEX_NAME = ‘PS’ ||
<index_id> || <PSFT record name>
 Index_id = ‘_’ PSFT key index
 Index_id = 1-9 alternate search key index
 Index_id = ‘#’ list index per v7.5
 Index_id = ‘A-Z’ user specified index
Keys and Indexing
 Key attribute can be applied to fields on any type of
record
 It is possible to suppress creation of an index by the
Application Designer for some or all platforms
(add/edit index dialog)
 In PSFT finapps much of the batch processing is
status driven – index status columns – histograms
make sense because status is relatively recent
 Majority of indexes are system generated directly
from the definition of the app
DDL
 App Designer dynamically builds DDL statements
 Physical attributes can be defined using ‘models’
 Legitimately DBA’s will want to:
–
add, remove or change an index,
–
Move an object to a different tablespace
–
Change the physical params (PCTFREE, PCTUSED, …)
 PSFT upgrade process may include generating a script to
alter a table definition, often re-creating it.
 What happens if the DBA has made changes (moves a
growing table to another Tablespace) directly to the objects?
DDL Models
 App Designer and Data Mover utilities build DDL
statements according to ‘models’
 PSFT defines 5 DDL models:
–
Create Table
–
Create Index
–
Create Tablespace
–
Analyze Table Estimates Stats (new in PeopleTools v8.1.x)
–
Analyze Table Compute Stats (new in PeopleTools v8.1.x)
DDL Models
 Each DDL model consists of a string containing an
outline of a SQL command
 Variables delimited by ‘[‘ ’]’ are PeopleTool internal
variables
 Variables delimited by double asterisks ‘**’ are
explicitly declared additional variables – defined
independently per RDBMS
DDL Model
 Create Index example:
–
CREATE [UNIQUE] INDEX **BITMAP** INDEX [IDXNAME] ON
[TBNAME] ([IDXCOLIST]) TABLESPACE **INDEXSPEC** STORAGE
(INITIAL **INDEXSPEC** …
 Models can be edited in the application designer
 Create tablespace model is only used by the Data Mover
when it create an entire database (installation or migration
from another platform)
 Typically ultspace.sql & xxddl.sql (eg. hrddl.sql) scripts are
used to create default tablespaces – i would want to look at
these before executing
DDL
 Analyze models/statements are new to v8 and only
available for Oracle and DB2 (not DB2 Unix)
 Overriding the models is accomplished with the App
Designer.
 Data Mover encodes DDL models in the export files
(like import and export)
 Models are stored in the database (PSDDLMODEL,
…)
Overriding DDL Model
 It’s flexible – goal should be to build correct/good
DDL right the first time
 You can alter existing DDL parameters and add
additional parameters
 If you do create additional variables you might want
to customize settable.sqr & setindex.sqr – these
SQR processes feedback the phys parms back from
USER_TABLES, USER_INDEXES into PeopleTools
metadata
DDL
 PSFT delivers Dictionary Managed Tablespaces –
should be locally managed – exercise left to the
reader
 DDL model means on size fits all
 It’s difficult to implement Global Temp Tables with
Application Designer.
Other DDL
 Some DDL statements do not wholly or partly use
DDL models:
–
Alter table in place
–
Alter table by recreation
–
Create views command are hand coded
 PSFT Temporary tables are permanent tables in
Oracle used for temp working storage
 Multiple instance of the application engine use
different temp tables to eliminate contention
Tablespaces
 Again did not have access to the PSFT
documentation
 Has always and continues to provide, SQL scripts to
build tablespaces
 V8.4 introduces a PSFT DBCA wizzard
 PSTEMP has always been the temp tablesapce
gen’ed from utlspace.sql
 Until 8.15 PSTEMP was a permanent table
Tablespaces
 PSDEFAULT – default tablespace
 PSFT database wizard creates automatic undo for rollback
(PSRBS)
 Default DBCA template:
–
Unicode database with UTF-8 char set
–
Dictionary managed SYSTEM tablespace
–
Automatic Undo UNDOTBS
–
Temporary tablespace w/ temp file
–
A number of locally managed tablespaces (Users, INDX, TOOLS, …)
that will never be used
Tablespaces
 Mo better template/DB build
–
Entire database, including SYSTEM and RBS, are locally
managed, with auto extent
–
A single byte char set if you can get away with it (business
requirements decides)
–
Don’t build unused tablespace
–
Custom database initialization parms
Tablespaces
 PSFT builds a number of tablespaces for each
product (eg. BN – base benefits, FS – Financials,
GP – global payroll,) with a post fix of APP, LARGE
or WORK.
 Tablespaces with APPx are where most of the tables
data is placed.
 A tale of two tablespace strategies:
–
Data is stored in lots of discreet tablespaces
–
Indexes for the entire application are stored in a single
tablespace!!!
Tablespaces
 Why the difference in strategies? Duh
 PSINDEX is in hot backup for a long time.
 Difficult to move data and index – management is an
issue – not really a performance issue
Locking, Transactions &
Concurrency
 PSFT mostly relies on implicit row-level locking to
protect data
 PSFT rarely locks a table explicitly (Oracle specific
feature)
 Rows are selected FOR UPDATE OF
–
If someone else has a lock, then user blocks
 Temp table usage, by multiple instance of the
application engine are explicitly control by writing
records in the PS_AETEMPTBLMGR
Sequence Nums & Concurrency
 PSFT does not use Oracle Sequences
 The platform agnostic alternative that PSFT employs
is to increment a value, stored in a table as part of a
transaction. (RAC implications)
 Sequences are serially allocated.
 Different sequences are derived from different tables
 HCM: at least 17 sequences are derived from from a
single row in PS_INSTALLATION table
 Moderate system activity will exhibit contention.
Performance Metrics
 Same strategy – reduce response time
 Online monitoring and metrics, batch metrics, and
trace files
 Build a timeline and work on longest pole
Online Performance Metrics
 Proxy server access log
 Web server access log
 Tuxedo ‘tmadmin’ script (determines queuing)
 Tuxedo service trace (expensive)
 Oracle Trace
Performance Metrics
 Application server performance online metrics are generated
from Tux
 From PeopleTools 7.53 PSFT on introduced the
EnableDBMonitoring to the application server config file
(psappsrv.cfg) and the process scheduler config file
(psprcs.cfg) (removed in v8.4 because it is enabled by
default.
 Enabling this parm causes the app server to write a string to
the app session info
DBMS_APPLICATION_INFO.SET_CLIENT_INFO
 While it would be useful to know which component or code
module is being executed, PSFT does not use
SET_MODULE or SET_ACTION – only can find the user
Performance Metrics
 tmadmin is the admin util for Tux
–
‘pclt’ or printClient: lists clients of the BB
–
‘pq’ or printqueue: lists queus within the application server and the
number of requests queued
–
‘psr’ or printserver: lists application servers processes and how many
service requests they have processed
–
‘q’ or quit: scripts that call tmadmin must explicitly exit or hang
 Monitoring scripts should use the ‘-r’ switch – read-only mode
 Only one instance of tmadmin can connect to a Tux Domain
Tuxedo Service Trace
 If Application Server is booted with ‘-r’ switch then Tux logs every service
request that passes through the server
 Output is written to strerr, unless ‘-e’ option is spec’ed
 Overhead of Tux service trace is low and probably OK for a production
instance
 Cannot be enabled/disabled dynamically
 Has low significance in PeopleTools v8 as services have been collapsed
into a single service ICPanel service (PIA uses this service the most)
 Watch out for time units (milliseconds on Windows and centisecond on
Unix)
 Log file can be read/reported by using txrpt utility – produces hour-byhour summary of the app server performance
Batch Metrics
 Process Scheduler agent is responsible for starting
most batch and reporting jobs
 Every time a process is scheduled to run via the PIA
processes scheduler, as request record is created
on a number of tables, most notably
PSPRCSQRQST
–
Who ran what
–
When and how long
 Need to periodically purge the the Process
Scheduler request table
Performance Metrics
 Statspack triggers can be defined to determine if a
process is being adversely affected by someone
else.
 Message Log: the application engine and COBOL
processes write time stamped records to the
message log table (PS_MESSAGE_LOG).
–
Message can be viewed in the process monitor
–
When a process terminates abnormally the process
scheduler may write records on behalf of the dead
processes
Performance Metrics
 Same
Performance Metrics
 PSFT extensively uses COBOL for most of it’s batch
processing (especially FinApps and Global Payroll
calculation)
 Some SQL statements are hard coded, but many SQL
statements are read from the PS_SQLSTMT_TBL and then
executed dynamically
 COBOL statement timings: Setting the TraceSQL flag in the
process scheduler produces a detail elapsed time for each
SQL statement
 Timings in PSFT reports are elapsed as measured in the
client process (thus includes database waits)
PSFT Trace files
 All PeopleTools client processes that connect to the
database can produce PeopleTool trace files
 Configuration manager enables Application Designer
and Data Mover tracing (options)
 Application server can trace SQL (psappsrv.cfg)
 PIA tracing: occasionally you will want to isolate
metrics for a single PSAPPSRV process: means you
have to configure a PIA domain that references only
that application server/service (not quick)
Performance Metrics
 Same
PeopleSoft Purchase & Oracle Consulting
 PeopleSoft documentation is very difficult to get:
–
PeopleBooks
–
Red Papers
 How will PSFT and 11i come together? Personal Rdb experience.
 We should expect PSFT to support Oracle application server.
 While majority of PSFT installations are based on Oracle, we should expect
a ‘safe switch’ program.
–
Should be easy right?
–
Opportunity to gain share.
 HA/DR opportunities. Real interesting question: Does PSFT use any nonsupported logical data types? If not then we can build near-real time, open
for business standby’s.
 We need to get plugged in, when appropriate to the development integration
strategy.
PeopleSoft Purchase & Oracle Consulting
 We will walk into clients that do not have Oracle technology: BEA, nonOracle database, Security. Need to spin up in a couple of areas:
–
Application Designer Class for developers AND DBA’s
 Performance (Analysis, SQL statement tuning and tech stack)
 DDL modifications
–
BEA Tux (configuration, tuning, analysis)
–
Amend consulting skills sets
 Are there any PSFT ‘platform’ consultants? Are they going to get pulled
across?
 PSFT & RAC (sallie mae)
 How can we get product? (to install, work with) This also includes BEA. Are
there any existing arrangements between PSFT and BEA like an enterprise
license?
PeopleSoft Purchase & Oracle Consulting
 There are many releases and a lot of detail – it going to take a while to get
our heads bent around this.
 Perhaps we should build a PSFT tuning, performance best practices on
Oracle accelerator (evaluation) and service offering/guide
 Need to review commercially available packages that monitor performance in
a PSFT application environment.
QUESTIONS
ANSWERS