Thursday, June 25, 2009

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


Joseph Thvedt said...

The link on your scripts page is broken -- you've got ...meta848.sql instead of ...848meta.sql.

I really enjoy your work. I learn something from each blog entry. Thanks.

David Kurtz said...

Link fixed. Many thanks.