I have been considering how to collect optimizer statistics for a PeopleSoft system running on an Oracle 11g database. Despite 11g being several years old, most of my current customers are still using 10g, though some are looking at the upgrade to 11g. I believe a slightly different approach is required.
In 2009, I wrote a series of blog postings on the subject of collecting statistics. However these were all based on Oracle 10g. I proposed a PL/SQL package that would use meta-data in a database table to determine how to collect statistics on a table, or deliberate supress collection of statistics.
I also recommended that statistics on tables created for use as temporary records in Application Engine programs should have their statistics deleted and locked to prevent system-wide jobs refreshing their statistics. I proposed a package that collected statistics according to meta-data defined in a table.
IN 2011, Oracle published document 1322888.1 “pscbo_stats - Improving Statistics in Oracle RDBMS for PeopleSoft Enterprise”. It takes a similar approach to my 10g package. A PL/SQL package is used to collect statistics. A number of tables control whether, when and how statistics are collected on each record. Oracle's package is also intended to be used to collect schema-wide statistics.
The psbo_stats package is a attempt to solve a genuine problem, and it has continued to evolve since its initial release. However, I have a number of objections to it.
I think that the 11g table preferences offer better control over collection of statistics.
Oracle considerably enhanced the delivered DBMS_STATS package in 11g. It became possible to specify default values for parameters in the dbms_stats.gather_table_stats program for for each table.
The guiding principle in 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 then just leave the default database and schema-wide procedures get on with the job of collecting statistics.
A document that describes the updated approach and the various scripts are available on the Go-Faste website at http://www.go-faster.co.uk/docs.htm#Managing.Statistics.11g.
CAVEAT: This document represents some experimental work that is in progress. It has not been tested against a customer system, let alone been used in production. I would welcome any feedback, and the opportunity to work with someone on a PeopleSoft system on Oracle 11g.