Oracle Optimizer Statistics and Optimizer Dynamic Sampling with PeopleSoft Temporary Records

PeopleSoft Temporary Records are used for working storage during Application Engine programs. Typically, AE programs truncate and repopulate the tables before using them. PeopleSoft recognised the need to keep the statistics on these tables in line with the data that they contain, and so used the %UpdateStats macro in many places in delivered programs to update the statistics.

However, frequently gathering statistics on even small tables can become time consuming. Recently, I have been working on PeopleSoft Time and Labor. This makes heavy use of temporary records. In a single execution of TL_TIMEADMIN, several tables associated with temporary records are truncated, repopulated and analyzed many times. I discussed the problem with excessive use of truncate elsewhere giving rise to Local Write Wait.

Oracle also recognised this problem, and in version 9 of the database they introduced Optimizer Dynamic Sampling, where the database samples the data to generate statistics at statement parse time.

I am still testing, but on Oracle 10gR2 (version 10.2.0.3) I have obtained improvements in performance and stability of T&L AE processes by:

1. Deleting optimizer statistics on tables associated with temporary record in order to force the optimizer to sample at parse time
2. Locking optimizer statistics to prevents the %UpdateStats macro from putting them back on. Tables with locked statistics are also omitted by GATHER_SCHEMA_STATS and GATHER_DATABASE_STATS (unless the force option is specified) and hence also by the delivered maintenance window job to refresh stale statistics.
3. Implementing alternative DDL model that uses a PL/SQL packaged function to suppress the error when attempting to collect statistics on table whose statistics are locked (see %UpdateStats() -v- Optimizer Dynamic Sampling). This also addresses the the mix-up in the DDL models
4. The final piece of the puzzle has been to set OPTIMIZER_DYNAMIC_SAMPLING to 4 at instance level. I certainly have had problems with this parameter set to the default of 2.

The dynamic sampling levels are described in the Performance Tuning Guide 14.5.6.4.

• Level 2: Apply dynamic sampling to all unanalyzed tables.
• Level 3: As Level 2, plus all tables for which standard selectivity estimation used a guess for some predicate that is a potential dynamic sampling predicate.
• Level 4: As Level 3, plus all tables that have single-table predicates that reference 2 or more columns.

So the next stage is to identify working storage records and their associated tables.
I started off looking for tables that had recently been analysed.

ttitle 'Candidate for Locking - based on recent batches'
SELECT DISTINCT r.recname
FROM   psrecdefn r
,       (
  SELECT A.RECNAME
  ,      SUM(B.TEMPTBLINSTANCES) TEMPTBLINSTANCES
  FROM   PSAEAPPLTEMPTBL A
  ,      PSAEAPPLDEFN B
  WHERE  A.AE_APPLID = B.AE_APPLID
  AND    A.AE_APPLID LIKE 'TL%'
  GROUP BY A.RECNAME
  ) /*pstemptblcntvw*/ i
  , psoptions o
  , user_tables t
  , (SELECT rownum-1 row_number
     FROM   all_objects WHERE ROWNUM <= 50) v
  WHERE  r.rectype IN(0,7)
  AND    r.recname = i.recname
  AND    v.row_number <= i.temptblinstances + o.temptblinstances
  AND    t.table_name 
     = DECODE(r.sqltablename,' ','PS_'||r.recname,r.sqltablename)                             
     ||DECODE(v.row_number*r.rectype,0,'',LTRIM(TO_NUMBER(v.row_number))) 
  AND    t.last_analyzed > SYSDATE - 1
/
ttitle off

The following script identifies all instances of temporary tables associated with temporary records, and then deletes and locks the statistics. I started by restricting it to list of specific tables, but I think it would be perfectly reasonable to take this approach with all temporary records.

BEGIN
FOR x IN (
  SELECT t.table_name
  FROM   psrecdefn r
  , (
    SELECT A.RECNAME
    ,      SUM(B.TEMPTBLINSTANCES) TEMPTBLINSTANCES
    FROM   PSAEAPPLTEMPTBL A
    ,      PSAEAPPLDEFN B
    WHERE  A.AE_APPLID = B.AE_APPLID
    GROUP BY A.RECNAME
    ) /*pstemptblcntvw*/ i
  , psoptions o
  , user_tables t
  , (SELECT rownum-1 row_number
     FROM   all_objects WHERE ROWNUM <= 50) v                 
  WHERE  r.rectype IN(0,7)
  AND    r.recname = i.recname
  AND    v.row_number <= i.temptblinstances + o.temptblinstances
  AND    t.table_name 
     = DECODE(r.sqltablename,' ','PS_'||r.recname,r.sqltablename)                                      
     ||DECODE(v.row_number*r.rectype,0,'',LTRIM(TO_NUMBER(v.row_number)))
/*---------------------------------------------------------------------            
--AND    r.recname IN('TL_PMTCH1_TMP' --TL_TA000600.SLCTPNCH.STATS1.S…
--                   ,'TL_PMTCH2_TMP' --TL_TA000600.CALC_DUR.STATS1.S…)
-----------------------------------------------------------------------*/
) LOOP
  --delete stats
  dbms_stats.delete_table_stats(ownname=>'SYSADM', tabname=>x.table_name
                               ,force=>TRUE);
  --lock stats
  dbms_stats.lock_table_stats(ownname=>'SYSADM', tabname=>x.table_name);
END LOOP;
END;
/