| 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. |
| deltempstats.sql Updated 6.4.2006 | This script creates a trigger that will delete optimizer statistics on a table when that table is deallocated from an instance of an Application Engine program. |
| gfc_temp_table_type.sql Published 25.6.2009 | This script creates a trigger that switches a non-shared instance of a temporary record from a normal table to a Global Temporary Table is restart is disabled or switches it back to a normal table if restart is enabled. The trigger will create the Global Temporary Table and any indexes on it if necessary, and will rename the tables as necessary.- see blog entry: Dynamically Switch PeopleSoft Temporary Records between Global Temporary and Normal Tables
NB: This package requires psftapi package. The T_LOCK trigger is highly recommended. |
| globtemp.sql globtemp-undo.sql Updated 9.12.2007 | These scripts add and remove the PeopleSoft DDL overrides required for Application Designer to generate DDL to build temporary records as Oracle Global Temporary Tables. |
| locktemprecstats.sql Updated 5.2.2009 | This script deletes and locks optimiser statistics on tables that correspond to PeopleSoft temporary records. |
| 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. |
| psftapi.sql Updated 17.4.2009 | This script creates a PL/SQL package with a number of procedures, including- store the current process instance in a package variable,
- write free-format messages to the PeopleSoft message log,
- update the session module and action with the Oracle supplied package DBMS_APPLICATION_INFO, so that it can be seen in in monitoring tools such as Oracle Grid Control
There is also a trigger to set the module and action to the process name, process instance and current status. |
| second_client_info.sql Published 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 t_lock_test.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 |
| temptblinstances.sql Updated 5.2.2009 | This script reports on missing and excess instances of PeopleSoft temporary tables. |
| tr_moreinst.sql Updated 26.2.2009 | When an Application Engine program fails to obtain a private instance of a temporary record and is forced to use the shared instance it writes a entry to the message log. This query reports on records where more temporary instances may be required. |
| trace_trigger.sql Updated 17.4.2009 | 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 ddlora-wrapper.dms 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. Updated 2.4.2009: On partitioned tables, the wrapper flushes monitoring statistics to the database and only collects statistics on partitions where the statistics are stale.
Updated 12.2.2009: Wrapper now collects statistics on locked permanent tables with FORCE=>TRUE option. |
| wrappermeta848.sql Published 25.6.2009 | Enhanced version of wrapper848.sql, that permits different parameters to specified to the Oracle dbms_stats package for different PeopleSoft Records. NB: This package requires psftapi package. |
| gfcbuild.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. |
This spreadsheet connects directly to a database to query the wait event information from DBA_HIST_WAIT for the last 7 days (this is the information collected by collected by AWR). The data is used to create a graph. Click on the thumbnail on the left for an example.