Managing Collection of Cost-Based Optimizer Statistics for PeopleSoft

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 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