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.
- You are expected to replace the default automatic jobs that collect statistics with pscbo_stats, so it is a move away from standard default maintenance procedures.
- pscbo_stats is still fundamentally a 10g solution. It does not use 11g table preferences.
- It does use the Oracle automatic sample size in 11g if histograms are not to be collected. Otherwise, it defaults to the previous behaviour of either using 100% sample size for when called by %UpdateStats with the ‘high’ sample size, or a variety of fixed sample sizes based on internal rules and the number of rows in the table.
- The package contains a procedure that collects statistics on all objects in the schema that also refreshes statistics that are not stale but which have not been refreshed for a period of time determined by the size of the table.
- When it collects histograms it always sets the maximum bucket size of 254. This may not always be desirable for height balanced histograms.
- There is no support for collecting aggregated or incremental statistics on partitioned objects.
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-Faster website at http://www2.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.
Update 4.2.2015: I have put this solution into PeopleSoft systems at two customer systems last year, so it is no longer just theory.
Update 5.4.2017: PeopleTools 8.55 set the PeopleSoft owner ID (usually SYSADM) in variable [DBNAME] in lower case. Thus it is passed in lower case to the PL/SQL package has been updated to handle this.