Controlling How %UpdateStats Collects Optimizer Statistics

I have written a number of entries on this blog about updating database statistics on tables during Application Engine processes.

• Statistics Management for PeopleSoft Temporary Records in Application Engine Programs, discussed management of statistics on GTTs, and the use of Optimizer Dynamic sampling.
• Statistics Management for Partitioned Objects in PeopleSoft discussed an enhancement to the wrapper so that it only gathered stale statistics on partitioned objects.
• %UpdateStats() -v- Optimizer Dynamic Sampling noted that I had found some situations where Oracle's Optimizer Dynamic Sampling, even at the highest level, does not produce the best execution plan and there is no alternative but to collect statistics on temporary tables.

I proposed a PL/SQL package (wrapper.sql) called from the DDL model to intercept the call from the %UpdateStats macro in Application Engine. By default it

• Gathers statistics on regular tables.
• Refreshed only stale statistics on partitioned tables.
• Does not gather statistics on Global Temporary Records.

I have now published an enhanced version: wrapper848meta.sql.

• A table PS_GFC_STATS_OVRD holds meta-data to override the default behaviour of the script for certain records. The meta-data can also specify the size of the sample, and the options to control the collection of histograms.
• If a private instance of a Temporary Records is a Global Temporary Tables, the wrapper may still collect statistics (normally this would be suppressed because of the risk of one session using the statistics collected by another session, but this will not happen for these tables).

Now, it is possible to specify the few tables where statistics must still be explicitly gathered, or whether to do this only if the current statistics on the table are stale. The DBA is probably the person best placed to decide whether and how to collect statistics on which tables, and these decisions can be implemented with the meta-data, but without code change.

Thus, it is possible to

• Reduce the number of calls to dbms_stats,
• to reduce the overhead of the remaining calls
• and at least preserve, if not improve performance of batch processes without making any code changes.