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

Automatically Granting Privileges on Newly Created Tables (continued)

Following this posting it was put to me that you could get Application Designer to build scripts with the commands to add the privilege by adding a second command to the create table DDL model, like this:

CREATE TABLE [TBNAME] ([TBCOLLIST]) TABLESPACE [TBSPCNAME] STORAGE (INITIAL **INIT** NEXT **NEXT** MAXEXTENTS **MAXEXT** PCTINCREASE **PCT**) PCTFREE **PCTFREE** PCTUSED **PCTUSED**;
GRANT SELECT ON [TBNAME] TO psreadall;

Yes, this does work when creating the table. The additional command is put into the create table script generated by Application Designer

DROP TABLE PS_PERSON
/
CREATE TABLE PS_PERSON (EMPLID VARCHAR2(11) NOT NULL,
BIRTHDATE DATE,
BIRTHPLACE VARCHAR2(30) NOT NULL,
BIRTHCOUNTRY VARCHAR2(3) NOT NULL,
BIRTHSTATE VARCHAR2(6) NOT NULL,
DT_OF_DEATH DATE,
LAST_CHILD_UPDDTM DATE) TABLESPACE HRLARGE STORAGE (INITIAL 40000
NEXT 100000 MAXEXTENTS UNLIMITED PCTINCREASE 0) PCTFREE 10 PCTUSED 80
/

GRANT SELECT ON PS_PERSON TO PSREADALL
/

However, the second command does not appear in the alter script.

CREATE TABLE PSYPERSON (EMPLID VARCHAR2(11) NOT NULL,
BIRTHDATE DATE,
BIRTHPLACE VARCHAR2(30) NOT NULL,
BIRTHCOUNTRY VARCHAR2(3) NOT NULL,
BIRTHSTATE VARCHAR2(6) NOT NULL,
DT_OF_DEATH DATE,
LAST_CHILD_UPDDTM DATE) TABLESPACE HRLARGE STORAGE (INITIAL 40000
NEXT 100000 MAXEXTENTS UNLIMITED PCTINCREASE 0) PCTFREE 10 PCTUSED 80
/
INSERT INTO PSYPERSON (
     EMPLID,
 BIRTHDATE,
 BIRTHPLACE,
 BIRTHCOUNTRY,
 BIRTHSTATE,
 DT_OF_DEATH,
 LAST_CHILD_UPDDTM)
SELECT
     EMPLID,
 BIRTHDATE,
 BIRTHPLACE,
 BIRTHCOUNTRY,
 BIRTHSTATE,
 DT_OF_DEATH,
 LAST_CHILD_UPDDTM
FROM PS_PERSON
/
DROP TABLE PS_PERSON
/
RENAME PSYPERSON TO PS_PERSON
/

So if you alter a table by create, rename and drop, you will lose the granted privileges.

Using Oracle Enterprise Manager (Grid Control) with PeopleSoft

If you use Oracle Grid Control to monitor your PeopleSoft system, here is a simple tip that will help you identify batch processes.

Oracle provides two columns on the session information (v$session) to hold context information. They provide a PL/SQL package DBMS_APPLICATION_INFO, which has procedures to read and update these values. The idea is that application developers will instrument their programs and update these values. Oracle’s Applications (that it has developed itself), such as E-Business Suite does this. PeopleSoft was rather slow to make use of this. They do set the module and action, but not to very useful values.

However, you can create a trigger on the Process Scheduler request table that will update these values when a process starts.

(Updated 19.4.2009) I have created a PL/SQL package psftapi that contains several procedures that I have used from triggers and other PL/SQL programs. It contains a function that sets the ACTION for the session with the process instance and the description of the status.

...
PROCEDURE set_action
(p_prcsinstance INTEGER
,p_runstatus VARCHAR2
) IS
l_runstatus VARCHAR2(10 CHAR);
BEGIN
BEGIN
 SELECT x.xlatshortname
 INTO   l_runstatus
 FROM   psxlatitem x
 WHERE  x.fieldname = 'RUNSTATUS'
 AND    x.fieldvalue = p_runstatus
 AND    x.eff_status = 'A'
 AND    x.effdt = (
  SELECT MAX(x1.effdt)
  FROM   psxlatitem x1
  WHERE  x1.fieldname = x.fieldname
  AND    x1.fieldvalue = x.fieldvalue
  AND    x1.effdt <= SYSDATE);  EXCEPTION    WHEN no_data_found THEN l_runstatus := 'Status:'||p_runstatus;         END;      sys.dbms_application_info.set_action(   action_name => SUBSTR('PI='||p_prcsinstance||':'||l_runstatus,1,32) );
END set_action;
...

This procedure can be called from a trigger:

CREATE OR REPLACE TRIGGER sysadm.psftapi_store_prcsinstance
BEFORE UPDATE OF runstatus ON sysadm.psprcsrqst
FOR EACH ROW
WHEN ((new.runstatus IN('3','7','8','9','10') OR
    old.runstatus IN('7','8')) AND new.prcstype != 'PSJob')
BEGIN
IF :new.runstatus = '7' THEN
 psftapi.set_prcsinstance(p_prcsinstance => :new.prcsinstance);
 psftapi.set_action(p_prcsinstance=>:new.prcsinstance
                   ,p_runstatus=>:new.runstatus
                   ,p_prcsname=>:new.prcsname);
ELSIF psftapi.get_prcsinstance() = :new.prcsinstance THEN
 psftapi.set_action(p_prcsinstance=>:new.prcsinstance
                   ,p_runstatus=>:new.runstatus);
END IF;
EXCEPTION WHEN OTHERS THEN NULL; --exception deliberately coded to suppress all exceptions
END;
/

What is the benefit? The MODULE and ACTION show up in Grid Control. So now you can immediately identify the name and Process Instance of those expensive processes.

Unfortunately, it is not possible to do anything similar for sessions created by the Application Server. So all you know is what session belongs to what kind of server process. The Client Information is set at the top of each service, so you know the PeopleSoft Operator ID, but that is all.

It would be nice if perhaps the Component name and PeopleCode context was written to MODULE and ACTION. But it isn’t.

Updated 9.9.11: PeopleTools 8.50 does exactly this, there is another posting on this subject.

Minimum Number of Application Server Processes

I have had two conversations recently about what happens if you have only a single PSAPPSRV process in a domain. One of which was on the DBA Forum.

Basically, you should always have at least two instances of any server process that has a non-zero recycle count.

It is rare to see only one PSAPPSRV process in Application Server domains that support the PIA, but customers who use the Integration Broker often have separate Application Server domains for the publication and subscription servers. These domains are often not heavily used, in which case they have been configured with just one of each server process.

This advice applies to the PSAPPSRV, PSQRYSRV, PSBRKHND, PSSUBHND, PSANALYTICSRV servers

The exceptions are

• PSSAMSRV is only used by Windows clients in 3-tier mode (nVision and PS/Query)
• PSMSGDSP, only a single process can be configured
• PSAESRV, because in the Process Scheduler each PSAESRV has its own queue.

The problem occurs when the server process recycles. This occurs when the number of services handled reaches the recycle count. When the only remaining server process on a shared queue shuts down the queue is also deleted, and the advertised services are removed from the Tuxedo Bulletin Board. If a service requests arrives in the application server domain before the new server process has started, and updated the bulletin board with advertised processes, the Jolt handler (JSH) will determine that the service request is not advertised and will raise an error.

It is quite simple to demonstrate this in PeopleSoft. In my demo system, I set the recycle count on PSAPPSRV to just 10 and the minimum number of servers to 1.

[PSAPPSRV]
;=========================================================================
; Settings for PSAPPSRV
;=========================================================================

;-------------------------------------------------------------------------
; UBBGEN settings
Min Instances=1
Max Instances=2
Service Timeout=300

;-------------------------------------------------------------------------
; Number of services after which PSAPPSRV will automatically restart.
; If the recycle count is set to zero, PSAPPSRV will never be recycled.
; The default value is 5000.
; Dynamic change allowed for Recycle Count
Recycle Count=10

It is not long until the PSAPPSRV process recycles, and you get this message in the application server log.

PSAPPSRV.2140 (10) [03/11/09 06:55:15 PTWEBSERVER@GO-FASTER-4](0) Recycling server after 10 services

You can also see in the shutdown message in the TUXLOG file.

The last line is the error message from the JSH process that cannot enqueue the service request because the Application Server is down. If you suspect that you have been getting this problem, look for that error message.

065655.GO-FASTER-4!BBL.2444.1760.0: LIBTUX_CAT:541: WARN: Server APPSRV/1 terminated
065655.GO-FASTER-4!BBL.2444.1760.0: LIBTUX_CAT:550: WARN: Cleaning up restartable server APPSRV/1
065655.GO-FASTER-4!cleanupsrv.1756.1204.-2: 03-11-2009: Tuxedo Version 8.1, 32-bit
065655.GO-FASTER-4!cleanupsrv.1756.1204.-2: CMDTUX_CAT:542: ERROR: Cannot find service to which to forward request
065655.GO-FASTER-4!cleanupsrv.1756.1204.-2: server APPSRV/1: CMDTUX_CAT:551: INFO: server removed
065655.GO-FASTER-4!JSH.2192.4860.-2: JOLT_CAT:1043: "ERROR: tpcall() call failed, tperrno = 6"

Hence, you should always have at least two PSAPPSRV processes, so that the queue is not removed, and the other server(s) can handle requests. Of course, there is a small chance that two servers could recycle at the same time, but that is very unlikely.