PeopleSoft for the Oracle DBA

Contents at a Glance......Page 3
Contents......Page 525
Author’s Preface to the Second Edition......Page 537
Foreword to the First Edition......Page 538
About the Author......Page 539
About the Technical Reviewers......Page 540
Acknowledgments......Page 541
Who Should Read This Book?......Page 4
Other Resources......Page 6
What Is PeopleSoft?......Page 8
Components of a PeopleSoft System......Page 10
The Database......Page 11
PeopleTools......Page 12
Single-Tier Monolithic Mainframe Architecture......Page 14
Two-Tier Client/Server Architecture......Page 15
Three-Tier Application Server Architecture......Page 16
Release History......Page 18
Component Connectivity......Page 20
The DBA......Page 21
Summary......Page 22
What Is Tuxedo?......Page 24
The Simple Client......Page 27
The Simple Tuxedo Domain......Page 29
Processes, Memory, and Messages......Page 30
IPC Resources......Page 35
tmadmin and ipcs......Page 38
PIA Servlets......Page 39
Integration Broker......Page 40
Summary......Page 42
PeopleSoft Database versus Oracle Database......Page 43
Connect ID (PEOPLE)......Page 44
PS Schema......Page 45
Oracle Database Roles......Page 46
PSADMIN Role......Page 47
Signing On to a PeopleSoft 8 Database......Page 50
Making the Initial Connection......Page 51
Using Direct Shared Memory Connections (UseLocalOracleDB)......Page 55
Determining the PeopleSoft Schema......Page 56
Checking the PeopleTools Release......Page 57
Obtaining the Access Password......Page 59
Reconnecting as Access ID......Page 60
Using PeopleSoft Access Profiles and Oracle Resource Profiles......Page 61
PeopleSoft Support for Oracle Active Data Guard......Page 65
Owner ID Password......Page 66
Summary......Page 67
PeopleSoft Database Structure: A Tale of Two Data Dictionaries......Page 68
Application and PeopleTools Tables......Page 69
Two Data Dictionaries......Page 70
PSRECDEFN: Record Definition......Page 72
PSRECFIELD: Record Field Definition......Page 75
PSDBFIELD: Field Definition......Page 80
PSSQLDEFN: Definition of SQL Objects......Page 86
PSSQLTEXTDEFN......Page 87
PSINDEXDEFN: Index Definition......Page 88
PSKEYDEFN: Index definition......Page 90
Recursive PeopleTools SQL......Page 91
Version Numbers and Caching......Page 93
Search Record......Page 94
Case-Insensitive Searching......Page 95
Component PeopleCode......Page 97
Record Definition......Page 99
Subrecords......Page 100
Record DDL......Page 101
Application SQL......Page 102
DDL Changes......Page 103
Column Difference Audit Script......Page 104
Summary......Page 105
What Is the Purpose of an Index?......Page 106
What Is a Constraint?......Page 107
What Is the Purpose of a Unique Constraint?......Page 108
Field Attributes and Application Behavior......Page 111
Field Attributes and System Index Definitions......Page 117
Custom Key Order......Page 129
User-Defined Indexes......Page 131
Views, Keys, and Indexing......Page 132
Suppressing Index Creation......Page 135
Discrepancy Between Platform Radio Button and Platform Flags......Page 138
Null Columns in PeopleSoft......Page 141
Index Organized Tables in PeopleSoft......Page 143
Summary......Page 146
PeopleSoft DDL and the DBA......Page 147
DDL Models......Page 148
Create Table......Page 149
Create Index......Page 152
Create Tablespace......Page 153
Maintaining Cost-Based Optimizer Statistics in Application Engine with %UpdateStats......Page 154
Application Designer......Page 162
Data Mover......Page 165
PSDDLMODEL (or PS_DDLMODEL_VW): DDL Model Statement......Page 166
PSDDLDEFPARMS (or PS_DDLDEFPARMS_VW): DDL Model Parameter......Page 167
PSTBLSPCCAT: Tablespace Catalogue......Page 168
PSIDXDDLPARM: Index DDL Parameters......Page 169
Additional DDL Parameters......Page 170
Global Temporary Tables......Page 174
Index Compression......Page 178
Grants......Page 179
Partitioned Tables......Page 180
Preserving Partitioning in Application Designer from PeopleTools 8.51......Page 181
Constraints......Page 182
Index Organized Tables......Page 183
Other DDL......Page 184
Alter Table by Re-creation......Page 185
PeopleSoft Temporary Tables......Page 187
Triggers......Page 188
What Should Be Fed Back Into PeopleTools?......Page 192
Summary......Page 194
Supplied Database Creation Scripts......Page 195
Oracle Database Configuration Assistant......Page 198
PeopleSoft Database Configuration Wizard......Page 199
Managing Tablespaces for PeopleSoft......Page 200
Creating Multiple Index Tablespaces......Page 201
Storage Options......Page 203
Summary......Page 204
Locking......Page 205
PIA Transactions......Page 207
Sequence Numbers and Concurrency......Page 211
Autonomous Transactions......Page 213
Summary......Page 214
Performance Metrics......Page 216
Online Monitoring and Metrics......Page 217
Application Server......Page 218
WebLogic Server Access Log......Page 226
Query Metrics......Page 234
Request Table......Page 237
Archiving Trigger......Page 244
Purging Application Engine Batch Timings......Page 246
Process Scheduler Configuration......Page 247
PeopleSoft Trace Files......Page 259
Application Designer and Client......Page 260
Application Server......Page 262
PIA Trace......Page 263
Analyzing PeopleSoft Trace Files......Page 266
Aggregating SQL Statements with Different Literal Values......Page 267
Summary......Page 270
Query Statistics......Page 271
Query Logging......Page 273
Scheduled Queries......Page 274
PeopleSoft Ping......Page 275
What Does Ping Measure?......Page 278
PeopleSoft Ping Case Study 1: Desktop/Browser Performance......Page 282
Conclusion......Page 284
Architectural Overview......Page 285
Metrics......Page 287
Performance Trace......Page 303
Analytic Components......Page 304
Transaction and Event Types......Page 306
Instrumentation......Page 320
Performance Tuning the Performance Monitor......Page 321
System Monitor Component......Page 322
Archive Process......Page 323
Performance Monitor Case Study: Diagnosing a SQL Performance Problem......Page 324
Summary......Page 332
Active Session History......Page 333
Module and Action in PeopleSoft......Page 335
Setting Module and Action for Batch Processes......Page 337
ASH vs. Extended SQL Trace......Page 341
Statspack......Page 342
Statspack Trigger......Page 343
Extracting Execution Plans from Statspack......Page 345
System and Session Statistics Trigger......Page 348
Oracle Initialization Parameters and SQL Trace......Page 352
Analyzing SQL Trace Files with TKPROF......Page 353
Enabling SQL Trace by Module, Action, or Client ID......Page 354
SQL Trace on Application Server Processes......Page 355
Enabling SQL Trace on the Process Scheduler......Page 358
Enabling SQL Trace Programmatically......Page 366
Component Processor......Page 369
PeopleCode......Page 373
Query......Page 374
COBOL......Page 376
SQR......Page 378
Application Engine......Page 380
Hints......Page 381
Disabling Indexes Without Using Hints......Page 382
Explicitly Coding Implicit Joins......Page 383
Query Block Naming......Page 386
Plan Stability (or Stored Outlines)......Page 387
Views......Page 390
Component Processor......Page 391
Query......Page 394
Summary......Page 407
Overview of Configuration Files......Page 408
psappsrv.ubx......Page 411
Features, Settings, and Ports Sections......Page 413
PS_DEFINES Section......Page 414
Main Tuxedo Section......Page 418
PS_ENVFILE Section......Page 434
Startup......Page 435
Security......Page 436
Workstation and Jolt Listeners......Page 437
Domain Settings......Page 438
Trace......Page 439
Cache Settings......Page 440
PeopleSoft Server Processes......Page 442
psappsrv.ubb......Page 444
psappsrv.env......Page 446
Configuration Template Files......Page 447
Tuxedo Administration Console......Page 448
Configuring the Tuxedo Administration Console for PeopleSoft......Page 449
Configuring PeopleSoft for the Tuxedo Administration Console......Page 451
Summary......Page 452
Sizing......Page 453
Spawning......Page 454
Too Few Server Processes?......Page 455
Too Many Server Processes?......Page 458
Multiple Queues......Page 465
Kernel Configuration......Page 468
Operating System Priority......Page 478
Load Balancing......Page 479
Calculating Service Loads......Page 481
Service Priority......Page 483
Unix User Accounts......Page 484
Cycling the Application Server Without Shutting It Down......Page 485
Reconfiguring Tuxedo with the Administration Console......Page 486
Summary......Page 487
The Process Scheduler......Page 488
Process Scheduler Architecture......Page 489
Master Process Scheduler......Page 490
Application Engine......Page 491
Process Monitor......Page 492
What Happens When a Process Is Scheduled?......Page 493
Process Scheduler Activity......Page 496
Purging the Process Scheduler Tables......Page 505
Lowering Operating System Priority of Batch Processes......Page 506
Mutually Exclusive Processing......Page 509
Application Engine Server Considerations......Page 510
Summary......Page 511
A......Page 512
D......Page 513
I......Page 514
M......Page 515
P......Page 516
S......Page 520
T......Page 521
W, X, Y, Z......Page 522