Implementing PSCBO_STATS for PeopleSoft Optimization

This section describes the key concepts that are introduced in PSCBO_STATS package.

The flexibility of PeopleTools Applications to run on a variety of database platforms is maintained by PeopleTool’s platform-independent SQL constructs that addresses the peculiarities of each RDBMS platform.

For example, the syntax for commands such as CREATE TABLE, and CREATE INDEX, and UPDATE STATISTICS differ between databases. The specific syntax for each platform is stored in the table PSDDLMODEL which acts as a lookup table used by various PeopleTools when executing certain functions, such as %UpdateStats() function.

During installation of the PSCBO_STATS package, the syntax of the %UpdateStats() function is changed from dbms_stats.gather_table_stats() to pscbo_stats.gather_table_stats(). This new PL/SQLprocedure is a wrapper around dbms_stats.gather_table_stats() optimizing it for gathering statistics for PeopleSoft Applications.

The PSCBO_STATS package improves on the default techniques of both PeopleSoft Applications and the Oracle Database by leveraging dynamic sampling for volatile working storage, minimizing impact to existing applications and providing a consistent methodology for gathering schema-wide or table-level statistics.

The PSCBO_STATS package uses gather_table_stats() procedure for gathering statistics. This procedure involves various criteria that determines whether statistics should be gathered, and if they are, in what manner.

The following factors decide the need for gathering statistics:

• Statistics Control Table

  The statistics control table is a list of tables under PSCBO_STATS control. If the requested table is present in this table, the procedure (in most cases) will ensure that the statistics is deleted to enable dynamic sampling. If the table is not listed in the statistics control table, then the statistics for that table are gathered.

• Statistics Locked State

  If the statistics of the table are locked, the procedure exits gracefully. This very simple mechanism indicates to the PSCBO_STATS package that a table is to be excluded from its processing.

• Speed and Accuracy of Samples

  In Oracle 10g, the sample size is altered as a function of table size in order to improve the execution speed while still gathering meaningful statistics. The Auto Sample Size algorithm is used in Oracle 11g since it represents an excellent balance between speed and accuracy.

• Histogram collection

  Due to the evolving of the optimizer capabilities, side effects of histogram use have stabilized in later patch sets of 11g. As a result, the use of histograms has become more widely accepted as beneficial. Therefore, in the PSCBO_STATS package histograms are gathered in Oracle 11g, but not collected in Oracle 10g. A Histogram Exception Table allows for fine grained control of how histograms will be collected. Additionally, in Oracle 11g, table preferences will be honored.

• Parallelism

  In Oracle 11g, table preferences DEGREE will be honored to tune the speed at which statistics are collected.

• Table Ordering

  Schema-wide statistics gathering is performed one table at a time, working from smallest to largest candidate table.

• Stale Statistics

  By default, only stale statistics are schema-level statistics are gathered.

• Index Statistics Collection

  When statistics for a table are gathered, the statistics for all of its associated indexes are gathered as well.

The PSCBO_STATS package can easily adapt to specific needs. Options that are provided include:

• Statistic Control Table

  All of the tables under the control of the PSCBO_STATS package are listed in the pscbo_stats_control table. Additional tables can be easily added to this table using the stats_control_ins() procedure.

• Statistics Locked State

  If the statistics are locked on a table, the gather_table_stats() procedure will exist gracefully and not take any actions. The PSCBO_STATS package will not issue locks on objects, but it will honor them if they exist. This new feature allows programs that use older syntax, such as ANALYZE TABLE, to be used without causing the program to ABEND.

• Override of Dynamic Settings

  Typically, if a table is inserted into the stats control table, it is intended that dynamic sampling be used for that table. If, for some reason, static statistics should be gathered on a table that previously was indicated for dynamic sampling, a control flag can be flipped in the statistics control table to indicate this. A brief reason for the change is required to be entered for basic auditing.

• Histogram Exceptions

  Control regarding how histograms are collected is still available. See histogram_col_ins() and histogram_col_del() procedures.

An environment having PSCBO_STATS installed before the performance issue will enhance the collection of additional helpful diagnostic information. Before beginning the implementation, verify that your system satisfies the following requirement:

• Install and Configure Oracle AWR or StatsPack.

• Install the SQLT package.

• 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. For more information on this section in psprcs.cfg, see Creating Push Notification Events. The following setting are required in each psprcs.cfg.

  TraceSQL = 128 <-- produces COBOL timing summary
  TraceAE = 128 <-- produces AE timing summaries

  Note: This step is not mandatory.

• 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;

  Note: Before you proceed with the installation process, ensure that these GRANTs do not compromise any existing corporate security policies. Each of these privileges must be in place for the PSCBO_STATS package to function properly.

  For any other user other than SYSADM, to manipulate the PSCBO_STATS package, the execute privileges needs to granted. Depending on the security configuration, the GRANTs listed above may also be necessary for that user.

• 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. For example, 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.

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

This section explains how to install and configure PSCBO_STATS. The following are the steps involved in installation and configuration:

1. Running 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. Populating 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;

3. Running 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:

   • Sets the system's OPTIMIZER_DYNAMIC_SAMPLING = 2.

   • Sets the system's STATISTICS_LEVEL = 'TYPICAL'.

   • Updates the SYSADM.PSDDLMODEL table to call the PSCBO_STATS package when %UpdateStats is called.

   Note: This step runs 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;

4. Reconfiguring the AUTOSTATS_TARGET to be ORACLE.

   The target of the AUTOSTATS job 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.

   Note: This step runs 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;
   EXEC DBMS_STATS.SET_PARAM('AUTOSTATS_TARGET','ORACLE');
   SELECT DBMS_STATS.GET_PREFS ('AUTOSTATS_TARGET') TARGET_AFTER FROM DUAL;
   SPOOL OFF;

5. Handling Previous Exclusion Exceptions.

   Note: This step need to be carried out only by the PSCBO_STATS v1 upgrade users.

   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 identified with the simple predicate,

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

   One easy way to lock tables that were previously excluded is to write 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.PS_PSCBO_LOG
   WHERE LINE LIKE 'PSCBO_EXCLUDE_TABLES';
   SPOOL OFF;

6. Configuring Custom Preferences and Exceptions.

   Note: Configuring custom preferences and exceptions is an optional step in PSCBO_STATS installation.

   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 only)

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

   Procedure provided:

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

   Table Preference - GRANULARITY (Oracle 11g 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');

7. Stopping and Restarting 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.

8. Performing 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.PS_PSCBO_LOG table to monitor activity during execution.

   Sample script (run as SYSADM):

   -- show recent pscbo_stats activity
   SELECT * FROM PS_PSCBO_LOG
   WHERE tstamp > (SYSDATE - 2/24) -- show last two hours
   --AND LINE_TYPE LIKE 'S' -- show only summary lines if desired
   ORDER by tstamp ASC

9. Granting access to the PSCBO_STATS package to non-SYSADM users.

   Note: This is an optional step. This need to be carried out only in case if the user doesn't have administrative privilege, but need to manipulate the PSCBO_STATS package.

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

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

    It is recommended to update sync_schema_stats() procedure on a daily basis.

    By default, the procedure performs the following:

    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

    Some mechanism needs to be employed to gather schema-level statistics on a regular basis. A stand alone, simple job can be scripted that will update the statistics nightly.

    If an enterprise scheduling solution is used, schedule the pscbo_stats.sync_schema_stats() procedure gather statistics for the PeopleSoft schema.

    Procedures provided:

    pscbo_stats.sync_schema_stats();

    Warning! Disable any schedules that run rdbms_stats.gather_schema_stats()!. If that procedure is run after installing the PSCBO_STATS package, simply run pscbo_stats.sync_schema_stats() to reset the statistics to a known state.

    Exclusion Exceptions Statistics

    In steps 5 and 6, statistics were locked on tables excluded from the PSCBO_STATS package control.

    Implement a custom approach to maintain these statistics as appropriate.

    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 4. No further change should be necessary if the task remains enabled and the target is ORACLE.

    Sample script (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 stats 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):

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

    Workload Statistics

    If a use case exists that demonstrates the value of explicitly gathering the Workload statistics, only then they should be gathered. Normally, the default values are adequate.

This section gives an overview of steps involved in removal of PSCBO_STATS package.

The following are the steps to be carried out for removing PSCBO_STATS package.

1. Restoring 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 PS_PSCBO_DDLHIST 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.

2. Restarting 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. Removing 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.PS_PSCBO_STATCTL PURGE;
   DROP TABLE SYSADM.PS_PSCBO_HISTCOL PURGE;
   DROP TABLE SYSADM.PS_PSCBO_LOG PURGE;
   DROP TABLE SYSADM.PS_PSCBO_DDLHIS PURGE;
   DROP VIEW SYSADM.PS_PSCBO_RECEDFN;
   QUIT;

4. Unlocking 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;
   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;

5. Revoking GRANTS as Appropriate.

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

   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.

   Note: Simply revoking all of the GRANTs from Step 1 of Installing and Configuring PSCBO_STATS 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.

6. Reinstating 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;

7. Regathering the Statistics Based on Previous Statistics Gathering Strategy.

   Gather the statistics for all objects in the schema based on the previous statistics gathering methodology. For example, the changes made during step 7 of Installing and Configuring PSCBO_STATS. 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.