The guiding principle since Oracle 11g, and one that is not specific to PeopleSoft, is that instead of calling dbms_stats.gather_table_stats with the desired parameters, we should set table preferences with the desired parameters and then just call dbms_stats without table-specific parameters. We can generally leave the default database-wide procedures to get on with the job of collecting statistics and only intervene exceptionally.
When it comes to applying this principle in PeopleSoft, we have a few challenges to overcome, but nothing that is impossible.
- When using PeopleSoft’s Application Designer to migrate record changes, it is typical to rebuild a table in order to add or change a column. Any related objects will be lost. Therefore table statistics preferences are also lost when a table is dropped. So we need to hold the preferences as meta-data in a table. Then we can apply the preferences to the tables as they are created using a DDL trigger.
- PeopleSoft temporary records can correspond to many tables that are used as non-shared temporary working storage tables. The same table preferences will need to be applied to all the temporary table instances. So the meta-data needs to be defined for each PeopleSoft record.
- The PeopleSoft %UpdateStats macro is used to collect statistics during Application Engine programs. This uses the DDL model to call the dbms_stats PL/SQL procedure. The simplistic approach would be to change the delivered DDL model to remove the sample_size and method_opt parameters but to specify force=>TRUE. However, I have retained the functionality developed in the original wrapper package, discussed in my book, to optionally suppress the collection of statistics, or only to collect statistics when they are stale.
- In the case of COBOL programs, the %UpdateStats macro can be put into stored statements. These can be changed to call the same PL/SQL package as the DDL models.
See Also
- Document: Managing Optimizer Statistics for PeopleSoft on Oracle (11g or higher).
- Source Code on Github: https://github.com/davidkurtz/gfcpsstats
- PeopleSoft DBA Blog: Maintaining Optimizer Statistics on PeopleSoft on Oracle 11g