Oracle SQL Scripts for PeopleSoft
| awr_snap_trigger.sql published 27.6.2008 | In PeopleSoft for the Oracle DBA (listing 9-39, pages 203-4), I proposed a trigger on PSPRCSRQST to collect Statspack snapshots exactly when PeopleSoft processes start and finish. This is a similar idea but for AWR snapshots. |
| colaudit.sql Updated 28.3.2004 | This script performs a series of checks on the PeopleTools tables that are not done by PeopleSoft's DDDAUDIT and SYSAUDIT reports. In particular it report on differences in the definitions, number or position of columns in tables, indexes or views. The script also generates a project in Application Designer that holds all the objects that may need to be rebuilt. |
| mvtrunc_lock.sql Updated 1.11.2006 | This script implements a DDL trigger to prevent TRUNCATE operations on tables with Materialized View logs.The script contains a test script that will check whether the trigger is working properly. See the PeopleSoft DBA Blog entry, 'Truncating a Table does not affect the Materialized View Log ' for a full description. |
| second_client_info.sql 1.7.2008 | This script implements an AFTER LOGON trigger to set the CLIENT_INFO on the secondary session on an application server or Application Engine process to the client info on the primary session. See the PeopleSoft DBA Blog entry, 'Sequence Number Allocation in PeopleSoft' for more details. |
| t_lock.sql Updated 13.11.2007 | This script implements a DDL trigger to prevent accidental lost of database objects that are not managed by PeopleTools, but which are related to tables specified in PeopleTools. It checks for indexes, triggers, primary key constraints, materialized view logs, and materialized views. It also checks whether the table is partitioned, clustered, index organised, or global temporary. A separate test script is provided to check whether the trigger is working properly. See the PeopleSoft DBA Blog entry, DDL Triggers to prevent loss of database objects not managed by PeopleTools for a full description. Updated 13.11.2007: PeopleSoft now creates descending indexes again. T_LOCK was incorrectly preventing indexes with descending columns from being dropped because it thought they were function-based indexes, further complicated by the issue of |
| trace_trigger.sql Updated 25.5.2008 | This is a new version of the trigger to enable and disable trace when a process begins so that all cursors are reports by Application Engine programs run by PSAESRV process. |
| wrapper848.sql Updated 18.12.2007 | Earlier this year I wrote about changes to the DDL Models in PeopleTools 8.48, and I proposed a method of selectively using Optimizer Dynamic Sampling as opposed to the %UpdateStats macro. More recently I have noticed that the Oracle appear to have swapped over the DDL models for estimating or computing statistics on a table. Surely another mistake in the DDL models. I have put these SQL and Data Mover scripts on the website, so that you can easily implement my recommendations for DDL models in PeopleTools >= 8.48. |
| gfpbuild.sql Updated 14.2.2008 | The presentation Configuring Global Payroll for Optimal Performance recommends physically partitioning Global Payroll result tables to match the logical streaming, and converting working storage tables to Oracle Global Temporary tables. Application Designer cannot generate the DDL for partitioned tables. This script will generate scripts that will rebuild payroll result tables with partitioning. This concept is explained in more detail in the document Configuring and Operating Streamed Processing in PeopleSoft Global Payroll, including a step-by-step guide on implementation. |
Other SQL Scripts
| fk_check_index.sql Updated 28.10.2007 | This script checks for foreign key constraints that do not have a supporting index. This can be a cause of TM lock contention. It also builds a create index script to add the missing indexes. See the Go-Faster Oracle Blog entry, ' TM locking: Checking for Missing Indexes on Foreign Key Constraints' for a full description. |
Other Unix Shell Scripts
| tuxcycle.sh Updated 14.8.2008 | This script recycles the PSAPPSRV processes within an application server. Hence it is possible to clear the cache without shutting down the whole application server. NB: This script does not recycle the publish and subscribe servers. See the PeopleSoft DBA Blog entry, 'How to Clear the Application Server Cache Without Shutting it Down' for a full description. |
Monitoring Scripts
The following scripts have been developed by Go-Faster to provide remote performance monitoring. They are provided on this website without explanation or support.
| tuxmon Updated 22.3.2005 | The tuxmon script monitors the behaviour of a Tuxedo application server running on a Unix system. It collects metrics only (no personal information) about the activity in a Tuxedo domain. The output can be emailed to a specified e-mail address. If you send the output to monitor@go-faster.co.uk I can provide a free analysis. NB: From PeopleTools 8.44, the data collected by Tuxmon can be obtained from the PeopleSoft Performance Monitor, and there is no point in using Tuxmon on these systems.. The scripts to receive and analyse the output are not available. latest updates: |
| tuxmon.nt Released 6.1.2005 | This is a primitive Windows version of the tuxmon script. It uses a simple batch script that can be run from the Windows scheduler. An awk program formats the output file. They can also be sent to monitor@go-faster.co.uk for analysis. |
| tkmailem | The tkmailem script is used to process and e-mail TKPROF output for batch processes that are set to trace by a trigger. |
Caveat: All the scripts available from this website are provided without warranty or support. SQL are designed to be run from SQL*Plus against a PeopleTools 8 database on Oracle. There is no guarantee that any of the content of this website is error free, rather that there are certainly errors and inconsistencies!
© Go-Faster Consultancy Ltd. 2008