Managing Statistics on Oracle databases for PeopleSoft

Collecting statistics on Oracle databases for PeopleSoft is unique, due to the way PeopleSoft is designed to be "database agnostic". As result the built-in means to collect statistics on Oracle databases is not known to work well with PeopleSoft installations. Therefore, some custom settings are recommended. See Oracle documentation E-ORA Methods to Gather Statistics on PeopleSoft Oracle Databases (Doc ID 1345124.1).

Basic Setup

This is a high level walk through of statistics collection for PeopleSoft on Oracle database 11g+. First, disable the built in job called the ”auto optimizer stats collection”.  To disable it, run the SQL below:  (this assumes your schema is SYSADM)

BEGIN
  DBMS_AUTO_TASK_ADMIN.DISABLE(
    client_name => 'auto optimizer stats collection',
    operation => NULL,
    window_name => NULL);
END;
/

Next, we need to setup some database preferences. According to Oracle Doc ID 1345124.1, we should set our preferences as follows and then drop/re-create statistics. To accomplish, run the SQL below:

--to set Global/default preferences so that all existing/future tables get the preference
EXEC DBMS_STATS.SET_GLOBAL_PREFS ('estimate_percent', 'dbms_stats.auto_sample_size');
EXEC DBMS_STATS.SET_GLOBAL_PREFS ('CASCADE', 'TRUE');
EXEC DBMS_STATS.SET_GLOBAL_PREFS ('NO_INVALIDATE', 'FALSE');
EXEC DBMS_STATS.SET_GLOBAL_PREFS ('degree', '8');
EXEC DBMS_STATS.SET_GLOBAL_PREFS ('granularity', 'AUTO');
EXEC DBMS_STATS.SET_GLOBAL_PREFS ('method_opt', 'FOR ALL COLUMNS SIZE AUTO');

--then drop and recreate stats for SYSADM owned tables
exec DBMS_STATS.DELETE_SCHEMA_STATS ('SYSADM');
exec DBMS_STATS.GATHER_SCHEMA_STATS (ownname=> 'SYSADM')

Next we need to create a scheduled job to run the statistics. If you want to schedule the job with the built in scheduler, run the SQL below.

-- Create the Stats schedule to run at 10pm daily.

BEGIN
  DBMS_SCHEDULER.create_job (
    job_name        => 'GATHER_SYSADM_SCHEMA_STATS',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN DBMS_STATS.GATHER_SCHEMA_STATS (ownname=> ''SYSADM''); END;',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'freq=daily;byhour=22;byminute=0;bysecond=0',
    enabled         => TRUE);
END;
/

If you want to run the scheduled job manually, use this SQL

EXEC dbms_scheduler.run_job('GATHER_SYSADM_SCHEMA_STATS');

Temp Tables

Stated in Oracle Doc ID 1345124.1, you should only collect statistics on required tables, to avoid collecting statistics on PeopleSoft temp tables. I am not a big fan of this approach since it requires you to manually identify all of your tables, and then write a stats collection job that names each table individually. This can be time consuming and would need to be re-visited, each time you did an update or added a new customization. To avoid this, you can allow the job to collect statistics on the entire schema as configured above and then afterwards, drop statistics on all PeopleSoft defined temp tables. SQL to drop  temp table stats below:

BEGIN
    FOR rec IN (SELECT * from sysadm.psrecdefn where rectype=7)
    LOOP
        dbms_stats.delete_table_stats(ownname=>'SYSADM', tabname=> 'PS_'||rec.recname);
    END LOOP;
END;
/

In practice, I haven't seen significant issues from having stats collected on temp tables, since the %UpdateStats job will run them as needed, however it does take longer to collect stats. 

Table Preferences

Additionally, you may need to exclude histograms from some tables that do not perform well with histograms in place (i.e. large tables). To prevent histogram collection on a table in Oracle 11G and newer, use the new dbms_stats.set_table_pref procedure to set a specific value for the method_opt parameter for the table. 

You should begin by dropping the stats on the table (in this example PS_JOB). You can do this by using the dbms_stats.delete_table_stats procedure.

EXEC dbms_stats.delete_table_stats(ownname=>'SYSADM', tabname=>'PS_JOB');

Now set the no histogram option for the table. This is controlled by the “method_opt” parameter.

EXEC dbms_stats.set_table_prefs('SYSADM', 'PS_JOB','METHOD_OPT', 'FOR ALL COLUMNS SIZE 1');

To view the new/exisiting preferences set to the table PS_JOB

SELECT DBMS_STATS.GET_PREFS('METHOD_OPT', 'SYSADM', 'PS_JOB') FROM   DUAL;

At this point you should re-run the gather stats job.

PSCBO_STATS?

Improving Statistics in Oracle RDBMS for PeopleSoft Enterprise (Doc ID 1322888.1)

Members of the Oracle PeopleSoft team have also made available a community supported package called "PSCBO_STATS" which is designed to do most this work and more for you. Unfortunately in my case, some of our major processes were 6 times slower with this package in place. That said, I recommend giving it a try, as others have had improved performance. On a side note, I was told in a recent SR, that a new version will be delivered in PeopleTools 8.56.