The pscbo_stats Package

Implementation Instructions

Table of Contents
1 - Pre-installation Considerations...............................................................................................................................................................2
1.1 - Leverage Automatic Upgrade of Existing pscbo_stats Package....................................................................................................2
1.2 - Install and Configure AWR or StatsPack.......................................................................................................................................2
1.3 - Install the SQLT package...............................................................................................................................................................2
1.4 - optional - Alter Process Scheduler Timing Summary....................................................................................................................2
1.5 - Confirm Required Privileges.........................................................................................................................................................3
1.6 - Prepare for Statistics-Gathering Jobs Changes..............................................................................................................................3
1.7 - Review “Advice for the PeopleSoft Oracle DBA.”.......................................................................................................................3
1.8 - Review “pscbo_stats FAQs.doc.”..................................................................................................................................................4
1.9 - Consider Upgrading Oracle to Current Patchset............................................................................................................................4
2 - Package Installation and Configuration Steps........................................................................................................................................5
2.1 - Install the pscbo_stats package......................................................................................................................................................5
2.2 - Populate the Statistics Control Table.............................................................................................................................................5
2.3 - Run the Quick Configuration Procedure........................................................................................................................................5
2.4 - Reconfigure the AUTOSTATS_TARGET to be ORACLE...........................................................................................................6
2.5 - pscbo_stats v1 upgrade users only: Handle Previous Exclusion Exceptions................................................................................6
2.6 - optional: Configure Custom Preferences and Exceptions.............................................................................................................7
2.7 - Stop and Restart All Process Schedulers and Application Servers................................................................................................7
2.8 - Perform Initial Setup of Schema Statistics....................................................................................................................................8
2.9 - optional: GRANT access to the pscbo_stats package to non-SYSADM users.............................................................................8
2.10 - Schedule Statistics Gathering......................................................................................................................................................8
3 - Package Removal Instructions..............................................................................................................................................................11
3.1 - Restore MetaSQL Used to Collect Table Statistics to the Default Values...................................................................................11
3.2 - Restart All Process Schedulers and Application Servers.............................................................................................................11
3.3 - Remove the Package, Package Body, and Tables........................................................................................................................11
3.4 - Unlock Table Statistics as Appropriate........................................................................................................................................11
3.5 - Revoke GRANTS as Appropriate................................................................................................................................................12
3.6 - Reinstate Previous Statistics Gathering Processes.......................................................................................................................12
3.7 - Regather the Statistics Based on Previous Statistics Gathering Strategy....................................................................................12

20161026 - pscbo_stats implementation instructions.doc 1 of 12

1 - Pre-installation Considerations
The steps in Section 1 are not actually required for installing the pscbo_stats package and its proper operation.
But it is strongly recommended that they be reviewed and considered before proceeding. Any subsequent
performance diagnostic effort will be greatly assisted by having these common tools in place. Moreover, having
them installed before a performance issue is observed will greatly enhance the collection of additional, helpful
diagnostic information.

1.1 - Leverage Automatic Upgrade of Existing pscbo_stats Package.

For upgrades/re-installations of pscbo_stats, it is not necessary to remove the previous installations.
Leaving the tables in place will leverage the history they contain with the new package.

All the tables and PL/SQL procedures associated with pscbo_stats are installed and/or upgraded by the
setup script. If tables associated with an earlier version of pscbo_stats are found, they and their contents
will be automatically upgraded to the current structures. It is safe to rerun the setup script over an
existing installation.

1.2 - Install and Configure AWR or StatsPack.

AWR will assist in instance-level analysis of performance issues. During testing, snapshots should be
taken every 30-60 minutes. AWR is a separately licensed product under the Diagnostics Pack - refer to
My Oracle Support for details. If access to AWR is not practical, install and configure StatsPack. For
more information, refer to My Oracle Support KM Doc ID 394937.1 - Statistics Package (STATSPACK)
Guide.

1.3 - Install the SQLT package.

Having SQLT available will greatly assist in SQL statement-level analysis of performance issues. It is an
excellent reporting tool that extracts detailed information related to the CBO's decisions regarding plan
generation and cursor execution detail. The archive generated by SQLT can be easily attached to an SR
and used for remote diagnosis of issues.

It is advantageous to have the SQLT package installed and current before testing since it will be able to
collect more information. The “SQLTxecute” method is helpful for extracting information “after the
fact” and is a very common report to provide Oracle Software Support.

SQLT does not require a separate license. For more information, refer to

SQLT (SQLTXPLAIN) - Tool that helps to diagnose a SQL statement performing poorly (Doc ID
215187.1)

1.4 - optional - Alter Process Scheduler Timing Summary.

Changing the tracing levels in the Process Scheduler configuration file will generate timing summaries
for COBOL and Application Engine processes each time that they run. This summary information is
useful for tracking performance of a given program over time, and to help identify specific areas of the
program that need additional attention. The gathering of this information has not been observed to
materially impact the run times of most batch processes.

Add/extend the following trace options to the psprcs.cfg file. See PeopleBooks for additional
information regarding these changes.

20161026 - pscbo_stats implementation instructions.doc 2 of 12

 Sample settings (required in each psprcs.cfg):

TraceSQL = 128 <-- produces COBOL timing summary

TraceAE = 128 <-- produces AE timing summaries

Note: Some customers have reported that the North American Global Payroll PayCalc process, and other
process with a large amount of looping, can be impacted using this TraceSQL setting. In that is the case,
then globally setting this value could be problematic. If that is the case, and you still want to trace a
specific set of processes, then refer to the follow note for details regarding tracing at the process level:

E-COB: How to Trace COBOL Processes Using the Process Definition (Doc ID 1470210.1)

1.5 - Confirm Required Privileges.

The pscbo_stats package must be installed with SYSDBA privileges since the script will grant certain
privileges to SYSADM. The pscbo_stats package will grant these privileges to SYSADM during the
installation procedure:

GRANT SELECT ON sys.dba_tables TO SYSADM;

GRANT SELECT ON sys.dba_tab_cols TO SYSADM;
GRANT SELECT ON sys.dba_tab_statistics TO SYSADM;
GRANT SELECT ON sys.dba_external_tables TO SYSADM;
GRANT SELECT ON sys.dba_indexes TO SYSADM;
GRANT SELECT ON sys.dba_scheduler_jobs TO SYSADM;
GRANT SELECT ON sys.dba_autotask_client TO SYSADM;
GRANT SELECT ON sys.v_$instance TO SYSADM;
GRANT SELECT ON sys.v_$parameter2 TO SYSADM;

Before you proceed with the installation process, ensure that these GRANTs do not compromise any
existing corporate security policies. Understand that each of these GRANTs must be in place for the
pscbo_stats package to function properly.

Additionally, if a user other than SYSADM needs to manipulate the pscbo_stats package, ensure that
he/she can be granted execute privileges to the pscbo_stats package. Depending on your security
configuration, the GRANTs listed above may also be necessary for that user.

1.6 - Prepare for Statistics-Gathering Jobs Changes.

The pscbo_stats package changes the way that statistics are gathered. Before installation, ensure that you
understand how schema-level statistics are currently gathered, e.g. how they are scheduled, so that those
procedures can be replaced by appropriate, new procedures.

Note: it is critical that the dbms_stats.gather_schema_stats() procedure NOT be used/scheduled

once the pscbo_stats package is in use, since it will gather the stats on the PeopleTools temp tables, thus
disabling the dynamic sampling feature for those tables.

1.7 - Review “Advice for the PeopleSoft Oracle DBA.”

Reviewing that document will clarify many common issues related to administration, performance, and
stability. See Note: 1445965.1 - Advice for the PeopleSoft Oracle DBA.

20161026 - pscbo_stats implementation instructions.doc 3 of 12

1.8 - Review “pscbo_stats FAQs.doc.”
A frequently asked questions document has been posted to Note 1322888.1 along with these installation
instructions and the pscbo_stats PL/SQL script. Be familiar with the content of that document as it will
help answer many common questions and alleviate concerns.

1.9 - Consider Upgrading Oracle to Current Patchset

Effectively managing statistics will only address issues related to statistics. It is highly recommended
that you run the current patchset of the Oracle RDBMS to take advantage of enhancements and fixes in
the product.

20161026 - pscbo_stats implementation instructions.doc 4 of 12

2 - Package Installation and Configuration Steps
The following section explains how to install and configure the pscbo_stats package.

Note: When a pluggable database is being used, alter the session to point to the correct container, e.g.
ALTER SESSION SET CONTAINER=<PDB>;

2.1 - Install the pscbo_stats package.

This setup script will generate an output file in the SQL/Plus default directory in a file named
“pscbo_stats.log.” After the package executes, review the log to ensure that the last section indicates that
the installation completed with no errors.

This step should run very quickly, typically completing in less than a minute.

Sample script (run as SYSDBA)

SQL> START pscbo_stats.sql

2.2 - Populate the Statistics Control Table.

The Statistics Control Table, SYSADM.PSCBO_STATS_CONTROL, is the master list of tables under the
control of the pscbo_stats package. In this step, it will be populated with an initial baseline of tables that
are candidates for dynamic sampling based on PeopleTools record type and Oracle table name. This
time this step takes to run can vary, but typically takes between one and five minutes.

Sample script (run as SYSADM):

SET SERVEROUT ON TRIMS ON LINES 1000 PAGES 50000;
SPO pscbo_stats-init_stats_control_table.log
EXEC pscbo_stats.sync_stats_control_table;
SPOOL OFF;

2.3 - Run the Quick Configuration Procedure.

Several settings need to be altered to allow the pscbo_stats package to function properly. Running this
procedure will perform the following actions:

• Set the system's OPTIMIZER_DYNAMIC_SAMPLING = 2

• Set the system's STATISTICS_LEVEL = 'TYPICAL'
• Update the SYSADM.PSDDLMODEL table to call the pscbo_stats package when %UpdateStats is
called.

This step should run very quickly, typically completing in a few seconds.

Sample script (run as SYSADM):

SET SERVEROUT ON TRIMS ON LINES 1000 PAGES 50000;

SPO pscbo_stats-quick_config.log
EXEC pscbo_stats.quick_config;
SPOOL OFF;

20161026 - pscbo_stats implementation instructions.doc 5 of 12

2.4 - Reconfigure the AUTOSTATS_TARGET to be ORACLE.
The AUTOSTATS target needs to be changed to “ORACLE” so that the task does not inadvertently
gather statistics on tables intended for dynamic sampling. The task can remain enabled, based on your
specific maintenance needs, but the target needs to be ORACLE.

This step should run very quickly, typically completing in a few seconds.

Sample script (run as SYSDBA):

SET SERVEROUT ON TRIMS ON LINES 1000 PAGES 50000;

SPO pscbo_stats-retarget_autotask.log

COL TARGET_BEFORE FORMAT A20

COL TARGET_AFTER FORMAT A20

SELECT DBMS_STATS.GET_PREFS ('AUTOSTATS_TARGET') TARGET_BEFORE FROM DUAL;

-- in 10g use DBMS_STATS.get_param
EXEC DBMS_STATS.SET_GLOBAL_PREFS('AUTOSTATS_TARGET','ORACLE');
-- in 10g use DBMS_STATS.set_param
SELECT DBMS_STATS.GET_PREFS ('AUTOSTATS_TARGET') TARGET_AFTER FROM DUAL;
-- in 10g use DBMS_STATS.get_param
SPOOL OFF;

2.5 - pscbo_stats v1 upgrade users only: Handle Previous Exclusion Exceptions.

In v1, the exclusion exception table contained the list of tables excluded from pscbo_stats control. The
mechanism has changed in this version and the exclusion exception table is no longer used. The
mechanism used in v2 and later to exclude a table from processing by the pscbo_stats package is to
simply lock the statistics on that table.

Tables that in v1 were excluded need to be locked in v2. During the initial installation, the table names
listed in the exclusion exception table are written to the log table PSCBO_LOG. These names can be be
identified with the simple predicate, e.g.

SELECT TABLE_NAME
FROM SYSADM.PSCBO_LOG
WHERE LINE LIKE 'PSCBO_EXCLUDE_TABLES'

One easy way to lock tables that were previously excluded is to “write a script that writes a script.” The
following sample query will generate a SQL script that, when run subsequently, will lock tables that
were previously excluded. Modify and execute this script as appropriate for your installation.

Sample script (run as SYSADM):

-- manually run output script to lock previously excluded tables

SET SERVEROUT ON TRIMS ON LINES 1000 PAGES 50000;
SPO pscbo_stats-lock_excluded_tables.sql;
SELECT 'exec DBMS_STATS.LOCK_TABLE_STATS (''SYSADM'', ''' ||TABLE_NAME
||''');'
AS SCRIPT_TEXT
FROM SYSADM.PSCBO_LOG
WHERE LINE LIKE 'PSCBO_EXCLUDE_TABLES';
SPOOL OFF;

20161026 - pscbo_stats implementation instructions.doc 6 of 12

2.6 - optional: Configure Custom Preferences and Exceptions
If you are already aware of a specific situation where the default collection of statistics would perform
poorly, make adjustments to the statistics gathering process before gathering the baseline statistics in
subsequent steps.

Suggested, optional modifications described in this step include:

• excluding tables from pscbo_stats control,
• altering the degree for known large tables,
• adjusting the granularity for partitioned tables

Excluding Tables from pscbo_stats Control

It may be appropriate to not gather the statistics on extremely large objects or on those with complex
partitioning strategies. To prevent a given table from being processed by the pscbo_stats package, simply
lock the statistics using the dbms_stats package.

Procedure provided:

dbms_stats.lock_table_stats('SYSADM','PS_MYSTATS');

Table Preference - DEGREE (Oracle 11g and later only)

Another very common approach to improving the speed of statistics gathering is to run the statistics
gathering process with parallelism enabled. Increasing the table preference DEGREE - to some value
less than the number of CPU's available - is usually appropriate for larger objects. Override the table
preference using the dbms_stats package.

Note: see pscbo_stats FAQ document, question #10 for additional suggestions.

Procedure provided:

exec dbms_stats.set_table_prefs('SYSADM','PS_BIGTBL','DEGREE','8');

Table Preference - GRANULARITY (Oracle 11g and later only)

Limiting the scope over which statistics are gathered on partitioned tables can improve performance. For
partitioned tables where the default granularity is not desired, override the table preference to the
appropriate level using the dbms_stats package.

Procedure provided:

exec dbms_stats.set_table_prefs(
'SYSADM','PS_MANYPART','GRANULARITY', 'GLOBAL AND PARTITION');

2.7 - Stop and Restart All Process Schedulers and Application Servers.
To ensure that the updated DDL model syntax is used, it is necessary to stop and restart all Process
Scheduler and Application Server domains when it is convenient. While the domains are down, it is
recommended, but not necessary, that the CACHE directories be purged.

20161026 - pscbo_stats implementation instructions.doc 7 of 12

2.8 - Perform Initial Setup of Schema Statistics.
This step will delete the statistics for all of the working storage used by Application Engine programs
and other working storage, and force the gathering of statistics for all other tables as a baseline.

Procedure provided:

pscbo_stats.setup_schema_stats()

Sample script (run as SYSADM):

SET SERVEROUT ON TRIMS ON LINES 1000;

SPO pscbo_stats-setup_schema_stats.log;
EXEC pscbo_stats.setup_schema_stats();
SPOOL OFF;

This process will take a significant amount of time - often several hours - depending on the size of the
database and modifications you've made in the previous steps.

Although no console output will generated until the procedure completes, it is possible to query the
SYSADM.PSCBO_LOG table to monitor activity during execution.

Sample script (run as SYSADM):

-- show recent pscbo_stats activity
SELECT * FROM PSCBO_LOG
WHERE tstamp > (SYSDATE - 2/24) -- show last two hours
ORDER by tstamp ASC

2.9 - optional: GRANT access to the pscbo_stats package to non-SYSADM users.

If the user that is responsible for manipulating the pscbo_stats package's configuration is not the schema
owner, e.g. SYSADM, GRANT privilege to the package so that user can execute the configuration
procedures within the package.

2.10 - Schedule Statistics Gathering.

Since each customer's environment is different, is it difficult to recommend a “best” method for
scheduling statistics gathering. In this step, make the necessary arrangements to address the regular
gathering of statistics of various components of the Oracle database as appropriate for your installation.

PeopleSoft Schema Statistics

We recommend the daily execution of the pscbo_stats.sync_schema_stats() procedure. By

default, the procedure:

1. validates that the temp table should not have statistics

2. gathers statistics that are stale
3. gathers statistics on non-stale tables that have reached their refresh interval (see the pscbo_stats
FAQ document, question #7.)

One easy approach is to use a job. An example is presented in the pscbo_stats FAQ document, question
#18.

20161026 - pscbo_stats implementation instructions.doc 8 of 12

 Procedures provided:

pscbo_stats.sync_schema_stats();

Warning: Remember to disable any schedules that run

rdbms_stats.gather_schema_stats()! If that procedure is
accidentally run after the pscbo_stats package has been installed,
simply run pscbo_stats.sync_schema_stats() to reset the statistics
to a known state.

Exclusion Exceptions Statistics

In Steps 2.5 and 2.6, statistics were locked on tables excluded from the pscbo_stats package control.
Implement a custom approach to maintain these statistics as appropriate. For additional input:

• The recommended syntax is discussed in the pscbo_stats FAQ document, question #10.
• A sample job that could be easily adapted to gather the statistics of certain tables is discussed in
the pscbo_stats FAQ document, question #18.

Dictionary Statistics

The statistics related to the data dictionary are normally maintained by the AUTOSTATS gathering job,
and the target was changed to “ORACLE” during step 2.4. No further change should be necessary if the
task remains enabled and the target is “ORACLE”.

Sample script to confirm target is “ORACLE” (run as SYSDBA):

SET SERVEROUT ON TRIMS ON LINES 1000 PAGES 50000;

SPO pscbo_stats-autotask_status.log

COL CLIENT_NAME FORMAT A40

COL STATUS FORMAT A20
COL AUTOSTATS_TARGET FORMAT A20

SELECT CLIENT_NAME, STATUS FROM DBA_AUTOTASK_CLIENT WHERE CLIENT_NAME LIKE

'auto optimizer stats collection';
SELECT DBMS_STATS.GET_PREFS ('AUTOSTATS_TARGET') AUTOSTATS_TARGET FROM
DUAL;

SPOOL OFF;

If data dictionary statistics do need to be gathered manually, i.e. if a large amount of changes have been
made to the Oracle data dictionary as the result of a major Application upgrade, gather the dictionary
statistics using the following command.

Sample script (run as SYSDBA)

20161026 - pscbo_stats implementation instructions.doc 9 of 12

 SET SERVEROUT ON TRIMS ON LINES 1000;
SPO gather_dict_stats.log;
EXEC dbms_stats.gather_dictionary_stats;
QUIT;

Workload Statistics

The merits gathering of workload statistics, and when they should be gathered, is beyond is the scope of
this document. Generally speaking, if a use case exists that demonstrates the value of explicitly
gathering them, then and only then they should be gathered. Normally, the default values are adequate.

20161026 - pscbo_stats implementation instructions.doc 10 of 12

3 - Package Removal Instructions
The following steps remove the pscbo_stats package.

3.1 - Restore MetaSQL Used to Collect Table Statistics to the Default Values
During installation, two rows were altered in the PSDDLMODEL table, specifically where
PLATFORMID=2 and STATEMENT_TYPE in ('4','5'). These need to be restored to the default values
before proceeding. There are several ways to restore this information:

• Have the PeopleSoft Administrator use DataMover to execute the script “ddlora.dms” provided
by PeopleTools to restore all of the default DDL syntax templates back into the PSDDLMODEL
table. The file is located in $PS_HOME/scripts/ddlora.dms.

• Review the before/after copy of the syntax was recorded in the

PSCBO_PSDDLMODEL_HISTORY table. This table was populated when the pscbo_stats
package was installed

• Copy the rows from a similar database's PSDDLMODEL table running an identical PeopleTools
release where pscbo_stats was not installed.

3.2 - Restart All Process Schedulers and Application Servers

To ensure that the updated DDL model syntax is used, it is necessary to restart all Process Scheduler and
Application Server domains. While the domains are down, it is recommended, but not necessary, that the
CACHE directories be purged as well.

3.3 - Remove the Package, Package Body, and Tables

Drop the Package, Package Body, and the four tables used by the pscbo_stats package.

Sample script (run as SYSDBA)

SET SERVEROUT ON TRIMS ON LINES 1000;

SPO pscbo_stats-drop_pscbo_stats_tables.log;
DROP PACKAGE BODY SYSADM.PSCBO_STATS;
DROP PACKAGE SYSADM.PSCBO_STATS;
DROP TABLE SYSADM.PSCBO_STATS_CONTROL PURGE;
DROP TABLE SYSADM.PSCBO_HISTOGRAM_COLS PURGE;
DROP TABLE SYSADM.PSCBO_LOG PURGE;
DROP TABLE SYSADM.PSCBO_PSDDLMODEL_HISTORY PURGE;
DROP VIEW SYSADM.PSCBO_RECDEFN;
QUIT;

3.4 - Unlock Table Statistics as Appropriate.

During the installation of the pscbo_stats package, some tables' statistics may have been locked to
prevent the package from processing those tables. Use the following syntax to identify tables with
statistics that are locked.

Sample script (run as SYSADM)

SET SERVEROUT ON TRIMS ON LINES 1000;

SPO pscbo_stats-locked_stats.log;

20161026 - pscbo_stats implementation instructions.doc 11 of 12

 select owner, table_name, stattype_locked
from dba_tab_statistics
where stattype_locked is not null;
QUIT;

Identify the tables that need their statistics unlocked and then use a form of the following script to unlock those
statistics, edited to be appropriate for a given database

SET SERVEROUT ON TRIMS ON LINES 1000;

SPO pscbo_stats-unlock_stats.log;
exec dbms_stats.unlock_table_stats('SYSADM','PS_TABLENAME');
QUIT;

3.5 - Revoke GRANTS as Appropriate

Revoke any GRANTS given to SYSADM that are no longer required.

Note: Simply revoking all of the GRANTs from Step 2.1 may not be appropriate since some may have
been present before the installation of the pscbo_stats package. Additionally, these privileges may be
required by other packages installed after the pscbo_stats package was installed. Use care when
removing these GRANTS.

Remember to revoke any GRANTS to other accounts that had access to the pscbo_stats package. If any
other users were granted privileges during the installation of the pscbo_stats package, revoke those
privileges as appropriate.

3.6 - Reinstate Previous Statistics Gathering Processes

Reset the AUTOSTATS_TARGET to the desired value, the default being AUTO. Additionally, this may
involve enabling the AUTOSTATS job. Remove the configuration around the scheduling of the
pscbo_stats.gather_schema_stats procedure and restore the techniques employed to schedule the
collection of statistics previous to installation of pscbo_stats.

Sample script (run as SYSDBA):

SET SERVEROUT ON TRIMS ON LINES 1000 PAGES 50000;

SPO pscbo_stats-reset_target_autotask.log

COL TARGET_BEFORE FORMAT A20

COL TARGET_AFTER FORMAT A20

SELECT DBMS_STATS.GET_PREFS ('AUTOSTATS_TARGET') TARGET_BEFORE FROM DUAL;

EXEC DBMS_STATS.SET_PARAM('AUTOSTATS_TARGET','AUTO');
SELECT DBMS_STATS.GET_PREFS ('AUTOSTATS_TARGET') TARGET_AFTER FROM DUAL;
SPOOL OFF;

3.7 - Regather the Statistics Based on Previous Statistics Gathering Strategy

Gather the statistics for all objects in the schema based on the previous statistics gathering methodology,
e.g. the changes made during step 2.7. Understand that the statistics may still reflect the pscbo_stats
package methodology and may not be “stale,” so the collection may need to be “forced.”

20161026 - pscbo_stats implementation instructions.doc 12 of 12