Oracle Statistics History Retention in PeopleSoft

I have been working on a system where many Application Engine programs are running throughout the day, and are frequently collecting Optimizer statistics with the %UpdateStats macro on many working storage tables. Concurrent calls to dbms_stats are typical.

There are two new behaviours in Oracle 10g RDBMS that can in extreme cases, in combination with a system that calls dbms_stats very frequently, create a significant performance overhead.

From Oracle 10g, histograms may, by default, be collected automatically. That means that rows are concurrently deleted from and inserted into histgrm$ and hist_head$, leading to contention and consistent read.

• Also from Oracle 10g, every time you collect statistics on a table the old statistics are retained in the SYS.WRI$_OPTSTAT%HISTORY tables. If histograms have previously been collected, these are also copied. DBMS_STATS has the additional overhead of writing this history. I found in excess of 10,000 versions of previous statistics for some tables, because the batch processes have updated statistics on working storage tables that many times.
• dbms_stats also appears to be responsible for purging history older than the retention limit. The default retention period is 31 days. I have seen concurrent calls to dbms_stats blocked on row level locks on the statistics history tables. For me, this occurred 31 days after the system went live on a significantly increased volume.
  

SELECT dbms_stats.get_stats_history_retention FROM dual;
GET_STATS_HISTORY_RETENTION 
--------------------------- 
                         31

Statistics history was designed to work in conjunction with schema wide statistics jobs that only refreshed stale statistics. There is an option on gather_schema_stats to collect only statistics on tables where the current statistics are stale. However, there is no such option on gather_table_stats. If you have decided to call this procedure for a particular table, then it is assumed you know you need to refresh the statistics. However, by calling dbms_stats from a batch program you can end up calling it much more frequently than is really necessary.

Recommendations

• Disable statistics history by using dbms_stats.alter_stats_history_retention to set the retention period to zero. Unfortunately this can only be set at database level. The statistics history is there in case you want to revert to a previous version of the statistics should a new set of statistics produce a problem, but it is only used rarely, and I think this is a necessary sacrifice.

EXECUTE dbms_stats.alter_stats_history_retention(retention=>0);

Use Oracle Optimizer Dynamic Sampling. However, I suggest increasing the level from the default of 2 to 4 to increase the situations in which it is used.

Introduce the new version of the PL/SQL wrapper package for dbms_stats so that you can specify the records for which statistics will be explicitly collected, and whether histograms are to be collect. Thus you can reduce the number of calls to dbms_stats.

If you have allowed the statistics history to grow before you disable history retention, then you might like to read John Hallas' posting on Purging statistics from the SYSAUX tablespace.

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 <= 100) 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 /*+LEADING(o i r v)*/ t.table_name, t.last_analyzed, t.num_rows
  ,      s.stattype_locked
  FROM pstemptblcntvw i
    INNER JOIN psrecdefn r
    ON r.recname = i.recname
    AND r.rectype = '7'
  , psoptions o
  , user_tables t
     LEFT OUTER JOIN user_tab_statistics s
     ON  s.table_name = t.table_name
     AND s.partition_name IS NULL
  , (SELECT rownum row_number
     FROM   psrecdefn 
     WHERE  ROWNUM <= 100) v                 
  WHERE  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,100,'',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…)
-----------------------------------------------------------------------*/
  AND   (/*  t.num_rows        IS NOT NULL --not analyzed 
        OR   t.last_analyzed   IS NOT NULL --not analyzed
        OR*/ s.stattype_locked IS     NULL --stats not locked
        ) 
) LOOP
  IF x.last_analyzed IS NOT NULL THEN --delete stats
   dbms_output.put_line('Deleting Statistics on '||user||'.'||x.table_name);
   dbms_stats.delete_table_stats(ownname=>user,tabname=>x.table_name,force=>TRUE);
  END IF;
  IF x.stattype_locked IS NULL THEN --lock stats
   dbms_output.put_line('Locking Statistics on '||user||'.'||x.table_name); 
   dbms_stats.lock_table_stats(ownname=>user,tabname=>x.table_name);
  END IF;
 END LOOP;
END;
/

Updated 11.2.2009: I have updated my advice on the use of Optimiser Dynamic Sampling (see %UpdateStats -v- Optimizer Dynamic Sampling. I still consider this to be a useful feature, but I have found scenarios where Oracle has not chosen a better plan that it did choose with explicitly gathered statistics. Therefore, I still suggest locking statistics on temporary working storage record, but where batch programs have been coded to explicitly update statistics then dbms_stats should be called with the force option to override the lock. I have updated my
DDL model wrapper script accordingly.

The scripts in this posting can be downloaded from my website

%UpdateStats() -v- Optimizer Dynamic Sampling

My previous post about the changes to DDL models in PeopleTools 8.48 made me to think about whether %UpdateStats() PeopleCode macro is the best solution to managing statistics on working storage tables in Oracle.

Optimizer Dynamic Sampling was introduced in Oracle 9.0.2. as a solution to the same problem. When a query is compiled Oracle can collect some optimiser statistics based upon a small random sample of blocks for tables that do not have statistics and that meet certain other criteria depending upon the parameter OPTIMIZER_DYNAMIC_SAMPLING. In Oracle 10g the default value for this parameter changed from 1 to 2 and so dynamic sampling applies to ALL unanalysed tables.

Thus, it should be possible to resolve the problem of incorrect statistics on a working storage table without explicitly collecting statistics during an Application Engine program, and therefore without needing a code change to add %UpdateStats(). Instead, simply delete statistics from the table, and lock them. A subsequent GATHER_SCHEMA_STATS will skip any locked tables. When a query references the table it will dynamically sample statistics and use them in determining the execution plan.

However, there is one more problem to overcome. GATHER_TABLE_STATS will raise an exception on a table with locked statistics. If you want to use Dynamic Sampling on a table where %UpdateStats() is already used to update the statistics, the PeopleCode macro will raise an exception that will cause Application Engine programs to terminate with an error. The workaround is to encapsulate GATHER_TABLE_STATS in a procedure that handles the exception, and reference the procedure in the DDL model. It is not possible to put a PL/SQL block in DDL model.

CREATE OR REPLACE PACKAGE BODY wrapper AS
PROCEDURE ps_stats(p_ownname VARCHAR2, p_tabname VARCHAR2, p_estpct NUMBER) IS
 table_stats_locked EXCEPTION;
 PRAGMA EXCEPTION_INIT(table_stats_locked,-20005);
 l_temporary VARCHAR2(1 CHAR);
 l_force BOOLEAN := TRUE;
BEGIN
 BEGIN
  SELECT temporary
  INTO   l_temporary
  FROM   all_tables
  WHERE  owner = p_ownname
  AND    table_name = p_tabname
  ;
 EXCEPTION WHEN no_data_found THEN
  RAISE_APPLICATION_ERROR(-20001,'Table '||p_ownname||'.'||p_tabname||' does not exist');
 END;

 IF l_temporary = 'Y' THEN
  l_force := FALSE; --don't force stats collect on GTTs
 ELSE
  l_force := TRUE; --don't force stats collect on GTTs
 END IF;

 IF p_estpct = 0 THEN
  sys.dbms_stats.gather_table_stats
  (ownname=>p_ownname
  ,tabname=>p_tabname
  ,estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE
  ,method_opt=>'FOR ALL COLUMNS SIZE AUTO'
  ,cascade=>TRUE
  ,force=>l_force
  );
 ELSE
  sys.dbms_stats.gather_table_stats
  (ownname=>p_ownname
  ,tabname=>p_tabname
  ,estimate_percent=>p_estpct
  ,method_opt=>'FOR ALL COLUMNS SIZE AUTO'
  ,cascade=>TRUE
  ,force=>l_force
  );
 END IF;
EXCEPTION
 WHEN table_stats_locked THEN NULL;
END ps_stats;
END wrapper;
/

At this time I have no data to determine which method is more likely to produce the better execution plan. However, when performance problems occur in production they are instinctively routed to the DBA, who is likely to have difficulty introducing a code change at short notice. Dynamic Sampling has some clear advantages.

Update 12.2.2009: Since writing this note I have come across some scenarios on Oracle 10.2.0.3 where the optimiser does not chose the best execution plan with dynamically sampled statistics, even with OPTIMIZER_DYNAMIC_SAMPLING set to the highest level, and I have had to explicitly collect statistics on working storage tables.

I have adjusted the wrapper procedure to call dbms_stats with the force option on permanent tables (so it collects statistics on tables whose statistics are locked and doesn't raise an exception, although it does not use the force option on Global Temporary Tables.

I still recommend that statistics should still be locked on tables related to PeopleSoft temporary records. The rationale for this is that you don't want any schema- or database-wide process that gathers statistics to process any working storage tables. Either the data in the table at the time the schema-wide statistics are gathered is not the same as when process runs and so you still want to use dynamic sampling, or else the process that populates the table has been coded to explicitly call %UpdateStats, and the new version of the wrapper will update these statistics.