How Not to Collect Optimizer Statistics in an Application Engine Program

I was told about a PeopleSoft customer experiencing an Oracle error when collecting statistics during an Application Engine.

ORA-06533: Subscript beyond count 
ORA-06512: at "SYS.DBMS_STATS"…

It is possibly a manifestation of a database bug. The workaround was not to use AUTO_SAMPLE_SIZE, so instead, this customer initially coded an explicit call to DBMS_STATS.GATHER_TABLE_STATS with a specific sample size.
This blog is not about the bug, but how to manage the workaround.

DO NOT TRY THIS AT HOME!

I think that there are a number of problems with this approach

1. Using a fixed sample size rather than AUTO_SAMPLE_SIZE should only be considered as a temporary workaround. The new hash-based number-of-distinct-values (NDV) algorithm in Oracle 12c only works with AUTO_SAMPLE_SIZE, and it produces more accurate statistics and runs faster because saves a large sort operation. Coding a fixed sample size into an Application Engine requires a managed change to be made, tested and released into production, and then when the underlying problem is resolved the customisation needs to be removed by the same managed process. 
2. DBMS_STATS.GATHER_TABLE_STATS implies a commit. That can lead to problems that PeopleSoft avoids by only calling statistics via the %UpdateStats macro and controlling when that macro does and does not execute. 

Committing and Restart Checkpointing in Application Engine

If a restartable Application Engine program fails it rolls back to the last commit point and it can then be restarted from that point. Committing is managed by Application Engine at section and step levels where the program state record is updated accordingly. If an error occurs in a step after the implicit commit in DBMS_STATS, it can result in the data in the application tables being different to where the state record indicates the program can be restarted. The program may not restart, or it could conceivably execute but produce erroneous results.
Committing inside a do while loop, including any other Application Engine program called from inside the loop is suppressed at Application Engine section/step level and therefore the execution of %UpdateStats macro is also suppressed. Otherwise, you could get rogue ORA-01555 Snapshot Too Old errors. Suppression of %UpdateStats is reported in the Application Engine step trace.

-- 19:07:37.199 .(AEMINITEST.MAIN.LOOP) (Do Select)
%Select(recname) SELECT recname FROM psrecdefn WHERE recname = 'JOB'
/
-- Buffers:
--   1) JOB
-- 19:07:37.204 Iteration 1 of .(AEMINITEST.MAIN.LOOP) (Do Select) loop

-- 19:07:37.208 .(AEMINITEST.MAIN.LOOP) (SQL)
RECSTATS PS_JOB LOW
/
-- 19:07:37.211 UpdateStats ignored - COMMIT required

-- 19:07:37.212 .(AEMINITEST.MAIN.LOOP) (Do Fetch)

Even a previously uncommitted SQL step can lead to %UpdateStats being suppressed.

-- 19:07:35.205 .(AEMINITEST.MAIN.Step01) (SQL)
UPDATE PS_AETEMPTBLMGR SET AE_DEDICATED = 1 WHERE 0 = 1
/
-- Row(s) affected: 0

-- 19:07:35.213 .(AEMINITEST.MAIN.Step02) (PeopleCode)

-- 19:07:35.220 .(AEMINITEST.MAIN.US1) (SQL)
RECSTATS PS_JOB LOW
/
-- 19:07:35.227 UpdateStats ignored - COMMIT required

If you code DBMS_STATS.GATHER_TABLE_STATS explicitly, Application Engine will not recognise the step as having committed. In the following example, you can see the %UpdateStats on the last step has been suppressed because it Application Engine does not recognise that the update in the first step has been committed by the call to DBMS_STATS.

-- 19:12:06.690 .(AEMINITEST.MAIN.Step01) (SQL)
UPDATE PS_AETEMPTBLMGR SET AE_DEDICATED = 1 WHERE 0 = 1
/
-- Row(s) affected: 0
-- 19:12:06.696 .(AEMINITEST.MAIN.Step02) (PeopleCode)
-- 19:12:06.700 .(AEMINITEST.MAIN.GTS) (SQL)
%Execute(/) BEGIN 
dbms_stats.gather_table_stats('SYSADM','PS_JOB',estimate_percent=>1); END; /
/
-- Row(s) affected: 1

-- 19:12:09.871 .(AEMINITEST.MAIN.US1) (SQL)
RECSTATS PS_JOB LOW
/
-- 19:12:09.877 UpdateStats ignored - COMMIT required
/

Perhaps, the safest form of this workaround would be to have the step with DBMS_STATS and the immediately preceding step explicitly commit as in the following example.  I have also made the program restartable.  Now restart data is checkpointed, and the %UpdateStats macro executes at step US1.

Restart Data CheckPointed
/
COMMIT
/

-- 19:20:24.792 .(AEMINITEST.MAIN.GTS) (SQL)
%Execute(/) BEGIN 
dbms_stats.gather_table_stats('SYSADM','PS_JOB',estimate_percent=>1); END; /
/
-- Row(s) affected: 1
/ 
/
Restart Data CheckPointed
/
COMMIT
/

-- 19:20:29.153 .(AEMINITEST.MAIN.US1) (SQL)
RECSTATS PS_JOB LOW
/
-- Row(s) affected: 1
/

However, you have to consider the state the application data after an error, whether you wish to restart or cancel the Application Engine because you can no longer rollback.

Doing the Right Thing

I recommend that:

• You should only ever collect stats in Application Engine with the %UpdateStats macro that in turn executes the command in the DDL model.
• From Oracle 11g both PeopleSoft statistics gathering DDL models should be the same and should ultimately call DBMS_STATS without any parameters other than the table name. The default value of ESTIMATE_PERCENT is AUTO_SAMPLE_SIZE.

There are two DDL models in PeopleSoft because %UpdateStats can be invoked with a second parameter to collect the statistics HIGH or LOW. This dates back to Oracle's ANALYZE command that could either compute or estimate statistics (and other database platforms had similar options). Collecting optimizer statistics with ANALYZE has been deprecated for many years, but the command still has other valid uses.  It was superceded by DBMS_STATS in Oracle 8i (released in 1998).
Automatic sample size was introduced in Oracle 9i.  In Oracle 9i and 10g, it was usual to use automatic sample size in the high statistics gathering model and a small fixed sample size in the low model for use on very large tables.  The LOW parameter was specified on %Updatestats in Application Engine programs as necessary.
This approach became redundant from Oracle 11g with the introduction of table preferences. If you need to collect statistics with a specific rather than the automatic sample size or specify any other parameters, then a table preference should be created. Preferences apply wherever statistics are gathered on that table and not overridden in the call of DBMS_STATS., including schema and database-wide operations such as the maintenance window.  If there are multiple places where statistics are collected on a table, a preference assures that the statistics will always be collected will be consistently.
From Oracle 12c, as the new NDV algorithm only works with AUTO_SAMPLE_SIZE, you should always use the default unless you have an overarching reason to the contrary, and then you should use a table preference. This approach does not require any application code change because the preference is an attribute of a table in the database.
I recommend using GFCPSSTATS package, calling it from the DDL model (see previous blog Managing Cost-Based Optimizer Statistics for PeopleSoft). The package also includes a mechanism to specify table preferences in metadata, so that they are automatically instantiated when a table is created or altered by Application Designer, and are applied to every instance of a temporary record.

gfcpsstats11.ps_stats(p_ownname=>[DBNAME], p_tabname=>[TBNAME]);

Alternatively, just call DBMS_STATS with no additional parameters (other than FORCE, in case you lock statistics on temporary tables) but then you must manage table preferences manually.

DBMS_STATS.GATHER_TABLE_STATS(ownname=>[DBNAME], tabname=>[TBNAME], force=>TRUE);

Further Reading

• Oracle Optimizer Blog: How does AUTO_SAMPLE_SIZE work in Oracle Database 12c?
• Oracle Optimizer Blog: How does AUTO_SAMPLE_SIZE work in Oracle Database 11g
• Oracle Optimizer Blog: Understanding DBMS_STATS.SET_*_PREFS procedures  
• PeopleSoft DBA Blog: Oracle/PeopleSoft have mixed up DDL Models used by %UpdateStats from PeopleTools 8.48 
• PeopleBooks PT8.56: Development Tools -> Application Engine -> Using Application Engine MetaSQL -> %UpdateStats

Managing Cost-Based Optimizer Statistics for PeopleSoft

I gave this presentation to UKOUG PeopleSoft Roadshow 2018

PeopleSoft presents some special challenges when it comes to collecting and maintaining the object statistics used by the cost-based optimizer.

I have previously written and blogged on this subject.  This presentation focuses exclusively on the Oracle database and draws together the various concepts into a single consistent picture.  It makes clear recommendations for Oracle 12c that will help you work with the cost-based optimizer, rather than continually fight against it.

It looks at collecting statistics for permanent and temporary working storage tables and considers some other factors that can affect optimizer statistics.

This presentation also discusses PSCBO_STATS, that is going to be shipped with PeopleTools, and compares and contrasts it with GFCPSSTATS11.

Maintaining Optimizer Statistics on PeopleSoft on Oracle 11g (and beyond)

Update 20.4.2023: Nearly everything in this article also applies to later versions of Oracle too.

I have been considering how to collect optimizer statistics for a PeopleSoft system running on an Oracle 11g database.  Despite 11g being several years old, most of my current customers are still using 10g, though some are looking at the upgrade to 11g.  I believe a slightly different approach is required.

In 2009, I wrote a series of blog postings on the subject of collecting statistics.  However, these were all based on Oracle 10g.  I proposed a PL/SQL package that would use meta-data in a database table to determine how to collect statistics on a table, or deliberately suppress the collection of statistics.
I also recommended that statistics on tables created for use as temporary records in Application Engine programs should have their statistics deleted and locked to prevent system-wide jobs from refreshing their statistics. I proposed a package that collected statistics according to meta-data defined in a table.

IN 2011, Oracle published document 1322888.1 “pscbo_stats - Improving Statistics in Oracle RDBMS for PeopleSoft Enterprise”.  It takes a similar approach to my 10g package.  A PL/SQL package is used to collect statistics.  A number of tables control whether, when, and how statistics are collected on each record.  Oracle's package is also intended to be used to collect schema-wide statistics.

The psbo_stats package is an attempt to solve a genuine problem, and it has continued to evolve since its initial release.  However, I have a number of objections to it.

• You are expected to replace the default automatic jobs that collect statistics with pscbo_stats, so it is a move away from standard default maintenance procedures.
• pscbo_stats is still fundamentally a 10g solution. It does not use 11g table preferences.
• It does use the Oracle automatic sample size in 11g if histograms are not to be collected.  Otherwise, it defaults to the previous behaviour of either using 100% sample size for when called by %UpdateStats with the ‘high’ sample size, or a variety of fixed sample sizes based on internal rules and the number of rows in the table.
• The package contains a procedure that collects statistics on all objects in the schema that also refreshes statistics that are not stale but which have not been refreshed for a period of time determined by the size of the table. 
• When it collects histograms it always sets the maximum bucket size of 254. This may not always be desirable for height-balanced histograms.
• There is no support for collecting aggregated or incremental statistics on partitioned objects.

I think that the 11g table preferences offer better control over collection of statistics.

Oracle considerably enhanced the delivered DBMS_STATS package in 11g.  It became possible to specify default values for parameters in the dbms_stats.gather_table_stats program for each table.

The guiding principle in 11g, and one that is not specific to PeopleSoft, is that instead of calling dbms_stats.gather_table_stats with the desired parameters, we should set table preferences with the desired parameters and then just call dbms_stats without table-specific parameters. We can then just leave the default database and schema-wide procedures to get on with the job of collecting statistics.    

A document that describes the updated approach and the various scripts is available on the Go-Faster website at http://www2.go-faster.co.uk/docs.htm#Managing.Statistics.11g.

CAVEAT: This document represents some experimental work that is in progress.  It has not been tested against a customer system, let alone been used in production. I would welcome any feedback, and the opportunity to work with someone on a PeopleSoft system on Oracle 11g.

Update 4.2.2015: I have put this solution into PeopleSoft systems at two customer systems last year, so it is no longer just theory.

Update 5.4.2017: PeopleTools 8.55 set the PeopleSoft owner ID (usually SYSADM) in variable [DBNAME] in lowercase. Thus it is passed in lowercase to the PL/SQL package has been updated to handle this.

Statistics Management for Partitioned Objects in PeopleSoft

I have implemented partitioned objects in a number of PeopleSoft systems on Oracle. Recently, I was working on a system where a table was partitioned into weekly range partitions, and I encountered a performance problem when Oracle's automatic maintenance window job to collect statistics did not run between populating the new partition for the first time, and running a batch process that referenced that partition. Oracle, understandably produced a execution plan for a statement that assumed the partition was empty, but as the partition actually had quite a lot of data, the statement ran for a long time. The solution was to tell Oracle the truth by gathering statistics for that partition. However, I didn't want to refresh the statistics for the whole table. There were many partitions with historical data that has not changed, so I don't need to refresh those partitions. I only need to refresh just the stale partitions, and here is the problem. Unfortunately, dbms_stats package will let you gather stale and missing statistics for all tables in a given schema, or the whole database, but not for a named table. It is not completely unreasonable, if you are targeting a single table then you ought to know what needs to be refreshed. I have written a PL/SQL procedure to flush the table monitoring statistics to the data dictionary and determine whether the statistics on the table, any of its partitions and sub-partitions are stale or missing, and if so gather statistics on those segments. It uses (I believe) the same criteria as dbms_stats to determine stale objects: 10% change relative to last gathered statistics, or if the segment has been truncated. I have incorporated the new refresh_stats procedure into my PL/SQL packaged procedure wrapper which can be called by the %UpdateStats PeopleCode macro via a customised DDL model. The new procedure is only called for partitioned tables. All that is necessary it to use the %UpdateStats macro in an Application Engine program. This is all still work-in-progress, but so far, the results are encouraging.

Statistics Management for PeopleSoft Temporary Records in Application Engine Programs

(Updated 11.7.2014) Last year, I wrote about Oracle Optimizer Statistics and Optimizer Dynamic Sampling with PeopleSoft Temporary Records. Earlier this year, I encountered a situation where Optimizer Dynamic Sampling was not sufficient, and I needed properly gathered statistics on an object. I modified my PL/SQL packaged procedure wrapper that can be called by the %UpdateStats PeopleCode macro via a customised DDL model to collects statistics.

I still recommend locking statistics on PeopleSoft Temporary Record, so that table is omitted from schema-wide or database-wide operations to refresh statistics. If the statistics on a table are locked, and it is not a Global Temporary Table, then the wrapper package will force collection and update of statistics on the table (previously it suppressed gathering of statistics on tables with locked statistics).

However, when the Application Engine program completes, any statistics collected on those temporary tables are no longer needed. Worse, the statistics refer to data that will be deleted and replaced by some future program, and if the table were not reanalysed, the statistics would be misleading and could cause the database to produce an inappropriate execution plan. Some temporary records are shared by multiple programs, so you cannot guarantee that statistics will always be refreshed when the table is next used.

When an Application Engine program completes successfully, or when the process request is cancelled, specific instances of temporary records that were allocated when the program began are deallocated by deleting the row from PS_AETEMPTBLMGR. Therefore, I propose the following trigger that will delete the statistics for that record when that row is deleted.

CREATE OR REPLACE TRIGGER sysadm.gfc_deletetemptablestats
AFTER INSERT ON sysadm.ps_aetemptblmgr
FOR EACH ROW
WHEN (new.curtempinstance > 0)
DECLARE
 PRAGMA AUTONOMOUS_TRANSACTION;
 l_table_name VARCHAR2(30) := '';
 l_last_analyzed DATE := '';
 l_stattype_locked VARCHAR2(5) := '';
 table_doesnt_exist EXCEPTION;
 PRAGMA EXCEPTION_INIT(table_doesnt_exist,-20001);
BEGIN
 SELECT r.table_name, t.last_analyzed
 INTO   l_table_name, l_last_analyzed
 FROM ( 
        SELECT r.recname
        ,      DECODE(r.sqltablename,' ','PS_'||r.recname,r.sqltablename)||:new.curtempinstance table_name
        FROM   psrecdefn r
        ) r
        LEFT OUTER JOIN user_tables t
        ON t.table_name = r.table_name
 AND t.temporary = 'N'
 WHERE  r.recname = :new.recname;

 SELECT s.stattype_locked
 INTO   l_stattype_locked
 FROM   user_tab_statistics s
 WHERE  s.table_name = l_table_name
 AND    s.object_type = 'TABLE';

 IF l_last_analyzed IS NOT NULL THEN --only delete statistics if they exist
  dbms_stats.delete_table_stats(ownname=>'SYSADM',tabname=>l_table_name,force=>TRUE);
 END IF;
 IF l_stattype_locked IS NULL THEN --stats need to be locked, 21,11,2009
  dbms_stats.lock_table_stats(ownname=>user,tabname=>l_table_name);
 END IF;
 
EXCEPTION
  WHEN no_data_found THEN NULL;
  WHEN table_doesnt_exist THEN NULL;
END;
/
show errorss

NB: The trigger must use an autonomous transaction because dbms_stats also commits its updates.

You can test the trigger like this: First I will populate the control table with a dummy record, and collect statistics

INSERT INTO ps_aetemptblmgr
(PROCESS_INSTANCE, RECNAME, CURTEMPINSTANCE, OPRID, RUN_CNTL_ID, AE_APPLID
,RUN_DTTM, AE_DISABLE_RESTART, AE_DEDICATED, AE_TRUNCATED)
VALUES
(0,'TL_EXCEPT_WRK',24,'PS','Wibble','TL_TIMEADMIN',sysdate,' ', 1,0)
/
execute dbms_stats.gather_table_stats(ownname=>'SYSADM',tabname=>'PS_TL_EXCEPT_WRK24',force=>TRUE);

column table_name format a18
SELECT table_name, num_rows, last_analyzed
FROM user_tables where table_name = 'PS_TL_EXCEPT_WRK24'
/

TABLE_NAME           NUM_ROWS LAST_ANALYZED
------------------ ---------- -------------------
PS_TL_EXCEPT_WRK24          0 14:36:12 06/04/2009

Now I will delete the row, and the trigger will delete the statistics for me.

DELETE FROM ps_aetemptblmgr
WHERE process_instance = 0
and curtempinstance = 24
and recname = 'TL_EXCEPT_WRK'
/

SELECT table_name, num_rows, last_analyzed
FROM user_tables where table_name = 'PS_TL_EXCEPT_WRK24'
/

TABLE_NAME           NUM_ROWS LAST_ANALYZED
------------------ ---------- -------------------
PS_TL_EXCEPT_WRK24

Oracle/PeopleSoft have mixed up DDL Models used by %UpdateStats from PeopleTools 8.48

Last May, I wrote about Changes to DDL Models in PeopleTools 8.48. DDL models 4 and 5 are used by the %UpdateStats PeopleCode macro. Previously, PeopleSoft had delivered these models with ANALYZE TABLE commands. Now, in line with long standing Oracle RDBMS guidance, they call DBMS_STATS (see $PS_HOME/script/ddlora.dms). I certainly welcome that change.

However, I have recently noticed that the DDL models have been swapped over. I have commented on this elsewhere, but I felt it needed a separate posting.

I am certain that this is a mistake, but it is at least one that can be easily corrected by PeopleSoft customers. The problem is not obvious because the full compute DDL model actually only uses a 1% sample, and the automatic sample size calculated by Oracle is usually within an order of magnitude of this value, though it is often greater than 1%.

So let me be absolutely clear here that:

Model 4 is used by %UpdateStats([table],LOW);

Model 5 is used bt %UpdateStats([table],HIGH);

This can be easily verified. I wrote a simple Application Engine that collected statistics on two tables via the %UpdateStats macro. I implemented the delivery DDL models as specified in ddlora.dms. This is the Application Engine trace file.

...
-- 17.35.40 .(DMK.MAIN.Step01) (SQL)
RECSTATS PSLOCK LOW
/
-- Row(s) affected: 1
/
/
Restart Data CheckPointed
/
COMMIT
/
...
-- 17.35.41 .(DMK.MAIN.Step02) (SQL)
RECSTATS PSVERSION HIGH
/
-- Row(s) affected: 1
/
/
Restart Data CheckPointed
/
COMMIT
/
...

Unfortunately the %UpdateStats macro is not fully traced in the PeopleTools trace either (it also reports the same information as the AE Trace file). The only way I know to find out what is being submitted to the database is to enable Oracle SQL Trace, and look in the trace file.

...
=====================
PARSING IN CURSOR #2 len=155 dep=0 uid=39 oct=47 lid=39 tim=259825298401 hv=1993983003 ad='6c1382b4'
BEGIN DBMS_STATS.GATHER_TABLE_STATS (ownname=> 'SYSADM', tabname=> 'PSLOCK', estimate_percent=> 1, method_opt=> 'FOR ALL COLUMNS SIZE 1', cascade=> TRUE); END;
END OF STMT
PARSE #2:c=31250,e=386098,p=25,cr=105,cu=0,mis=1,r=0,dep=0,og=1,tim=259825298396
...
=====================
PARSING IN CURSOR #2 len=193 dep=0 uid=39 oct=47 lid=39 tim=259826057420 hv=2784637395 ad='6c138098'
BEGIN DBMS_STATS.GATHER_TABLE_STATS (ownname=> 'SYSADM', tabname=>'PSVERSION', estimate_percent=> dbms_stats.auto_sample_size, method_opt=> 'FOR ALL INDEXED COLUMNS SIZE 1', cascade=> TRUE); END;
END OF STMT
PARSE #2:c=0,e=2195,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=259826057415
...

So, you can see that RECSTATS HIGH corresponds to the default estimate, but RECSTATS LOW corresponds to the 1% sample size.
If you look in ddlora.dms you can see that model 4 is the 1% sample and model 5 is the default estimate.

...
4,2,0,0,$long
DBMS_STATS.GATHER_TABLE_STATS (ownname=> [DBNAME], tabname=> [TBNAME], estimate_percent=> , method_opt=> 'FOR ALL COLUMNS SIZE 1', cascade=> TRUE);
//
5,2,0,0,$long
DBMS_STATS.GATHER_TABLE_STATS (ownname=> [DBNAME], tabname=> [TBNAME], estimate_percent=> dbms_stats.auto_sample_size, method_opt=> 'FOR ALL INDEXED COLUMNS SIZE 1', cascade=> TRUE);
//
...

So you can also see how the DDL models have been confused, which as I have commented I consider to be a typographical error, and that should really have been 100% indicating a full compute. I think it would make sense to change the ddlora.dms script to read as follows:

......
INSERT INTO PSDDLMODEL (
STATEMENT_TYPE,
PLATFORMID,
SIZING_SET,
PARMCOUNT,
MODEL_STATEMENT)
VALUES(
:1,
:2,
:3,
:4,
:5)
\
$DATATYPES NUMERIC,NUMERIC,NUMERIC,NUMERIC,CHARACTER
...
4,2,0,0,$long
DBMS_STATS.GATHER_TABLE_STATS (ownname=> [DBNAME], tabname=> [TBNAME], estimate_percent=>dbms_stats.auto_sample_size, method_opt=> 'FOR ALL COLUMNS SIZE AUTO', cascade=> TRUE);
//
5,2,0,0,$long
DBMS_STATS.GATHER_TABLE_STATS (ownname=> [DBNAME], tabname=> [TBNAME], estimate_percent=>, method_opt=> 'FOR ALL COLUMNS SIZE AUTO', cascade=> TRUE);
//
/
...

Or, if you use the wrapper SQL that I proposed in %UpdateStats() -v- Optimizer Dynamic Sampling

...
4,2,0,0,$long
wrapper.ps_stats(p_ownname=> [DBNAME], p_tabname=> [TBNAME], p_estimate_percent=> 0);
//
5,2,0,0,$long
wrapper.ps_stats(p_ownname=> [DBNAME], p_tabname=> [TBNAME], p_estimate_percent=> 1);
//
...

I have kept the 1% sample size for the compute model, but there is no reason why you could not choose a larger value. If you wanted %UpdateStats([table],HIGH) to continue to mean a full compute, then the value really should be 100%. It is really a matter of how long you want to spend analysing statistics on tables during batch programs. However, a higher sample size will not necessarily produce statistics that will lead to a better execution plan!

Exactly how Oracle calculates the default sample size is not published. Values in the range 0.5% to 10% are typical. In a perverse sense, a 1% sample size will usually be smaller sample than the Oracle default sample size, so in the delivered DDL models, %UpdateStats([table],HIGH) will usually use a larger sample size that %UpdateStats([table],LOW)! However, I simply cannot believe that this is what was intended.

You can see the value that Oracle calculates for auto_sample_size by tracing the dbms_stats call, and looking for the sample clause in the recursive SQL. Eg.

... from "SYSADM"."PSPCMNAME" sample ( .9490170771) t

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

Changes to DDL Models in PeopleTools 8.48

A recent thread on Oracle-L led me to look at how Oracle has changed the way that PeopleTools 8.48 collects Oracle Cost-Based Optimiser statistics. It now uses DBMS_STATS instead of the ANALYZE command. This has also caused me to reconsider some options for managing statistics for a PeopleSoft system.

Application Engine programs can collect Cost-Based Optimiser statistics on specific tables by calling the %UpdateStats([,high/low]); PeopleCode macro. This uses one of two DDL models depending on whether the high or low option is specified. However, these DDL models only exist for Oracle and DB2/MVS. %UpdateStats() has no function on other platforms.

This was PeopleSoft’s platform generic solution (before their takeover by Oracle, and before Dynamic Sampling was available in the Oracle database) to the very real problem that occurs when statistics on a working storage or reporting table, that is emptied, repopulated and used during a batch process, do not accurately represent the content of the table and hence cause the optimiser to choose an inappropriate execution plan. PeopleSoft provided a method of refreshing the statistics during the process, and introduced new DDL models because each database platform would have its own command. However, this approach relies upon developers to add the %UpdateStats() macro for every occasion where data has changed sufficiently to require refreshing the statistics. Unfortunately, developers are not always best placed to make that decision. There are still plenty of places in delivered PeopleSoft code where this macro could be usefully added.

Up to PeopleTools 8.47, PeopleSoft delivered two DDL models that used the ANALYZE command. The %UpdateStats(,high) ran a full compute of the table:

ANALYZE TABLE [TBNAME] COMPUTE STATISTICS;

While %UpdateStats(,low) estimated statistics with the default sample size:

ANALYZE TABLE [TBNAME] ESTIMATE  STATISTICS;

From PeopleTools 8.48, these DDL models now call the Oracle supplied PL/SQL package DBMS_STATS. The high option still performs a full compute of statistics.

DBMS_STATS.GATHER_TABLE_STATS (ownname=> [DBNAME], tabname=>[TBNAME], estimate_percent=>1, method_opt=> 'FOR ALL COLUMNS SIZE 1',cascade=>TRUE);

While the low option estimates statistics with the sample size determined by the pseudo-variable. DBMS_STATS.AUTO_SAMPLE_SIZE.

DBMS_STATS.GATHER_TABLE_STATS (ownname=> [DBNAME], tabname=>[TBNAME], estimate_percent=> dbms_stats.auto_sample_size, method_opt=> 'FOR ALL INDEXED COLUMNS SIZE 1',cascade=>TRUE);

So it would appear that PeopleSoft now follow the recommendations that Oracle have been making since version 8i of the database to use DBMS_STATS instead of the ANALYZE command. This is certainly a step in the right direction. It also makes good sense to use the automatic sample size. ESTIMATE_PERCENT defaults to DBMS_STATS.AUTO_SAMPLE_SIZE from Oracle 10g. Previously it was NULL, which caused a full compute.

However, there is a problem.

PeopleSoft have chosen to specify the METHOD_OPT as FOR ALL INDEXED COLUMNS SIZE 1. If you have specified histograms on any of your columns, or generated them automatically with DBMS_STATS, the PeopleSoft command will remove them from indexed columns and will leave any histograms on unindexed columns unchanged, and potentially out of date.

The default in Oracle 9i is FOR ALL COLUMNS SIZE 1. This removes all histograms on all columns, although this is at least the same behaviour as the ANALYZE command.

In Oracle 10g, METHOD_OPT defaults to FOR ALL COLUMNS SIZE AUTO. The Oracle manual states that the database ‘…determines the columns to collect histograms based on data distribution and the workload of the columns’. So, Oracle may remove histograms if it judges that they are not necessary.

I have no hesitation in recommending that value for METHOD the delivered DDL models should be changed. I would suggest using FOR ALL COLUMNS SIZE REPEAT.

Addendum 12.6.2007: I have noticed that the DDL models change again in PT8.48.07. The full compute is now for ALL COLUMNS, but the estimate is still for ALL INDEXED COLUMNS! Closer, but still no cigar!