PS360 enhancement: Added report of DDL models

I have written several blogs and presentations recently about how and how not to collect statistics in PeopleSoft.

• Managing Cost-Based Optimizer Statistics for PeopleSoft recommends
• If you are going to continue to use DBMS_STATS in the DDL model then
• Do not specify ESTIMATE_PERCENT because it disables the hash-based number-of-distinct-values calculation, forcing it to go back to the COUNT(DISTINCT …) method that requires a sort, and may not produce accurate values because it only samples data.
• Do not specify METHOD_OPT='FOR ALL INDEXED COLUMNS SIZE 1' because this will not collect histograms on indexed columns, and will not update column statistics on unindexed columns.
• Do specify FORCE=>TRUE so that you can lock and delete statistics on temporary records.
• However, the alternative is to use GFCSTATS11 package to collects these statistics.  This package is controlled by a metadata table so you can defined statistics collection behaviour for specific records.
• How Not to Collect Optimizer Statistics in an Application Engine Program
• This blog explains why you should not explicitly code DBMS_STATS calls into Application Engine programs.

This has prompted me to add a new report to the PS360 utility that simply reports the various DDL models for Oracle.  Thus during a health check, I can see how statistics are collected during batch processes.

PS360 can be download from https://github.com/davidkurtz/ps360.