Reducing Unnecessary Instances of Temporary Records

In a previous posting, I recommended moving temporary records, that are very frequently truncated by Application Engine programs, to a tablespace with a 32Kb block size, and using a larger uniform extent size (I chose 1Mb).

However, continuing the example for my last posting on this subject, TL_TIMEADMIN has 150 temporary records, that have 173 indexes (in HCM8.9). So you get 323 segments for every instance set in the Application Engine properties, and that would consume at least 323Mb of the 32Kb tablespace. If that space consumption is not a problem, then stop reading now.

However, I noticed that some temporary records are used by several Application Engine programs. This is usually because one program calls another and the temporary records are referenced in both. However, if both programs have a number of instances of temporary records defined in their properties, then temporary tables will be built for both.

Lets take an example TL_PUB_TM_AE calls TL_PUB_TM1. They are both delivered with 5 instances.

AE_APPLID    TEMPTBLINSTANCES
------------ ----------------
TL_PUB_TM1                  5
TL_PUB_TM_AE                5

They share 8 temporary records in common.

SELECT a.recname, a.ae_applid, b.ae_applid
FROM  psaeappltemptbl a
FULL OUTER JOIN psaeappltemptbl b
ON  a.recname = b.recname
AND b.ae_applid = 'TL_PUB_TM_AE'
WHERE a.ae_applid = 'TL_PUB_TM1'
ORDER BY 1
/

RECNAME         AE_APPLID    AE_APPLID
--------------- ------------ ------------
TL_PROF_LIST    TL_PUB_TM1
TL_PROF_WRK     TL_PUB_TM1
WRK_PROJ1_TAO   TL_PUB_TM1   TL_PUB_TM_AE
WRK_PROJ2_TAO   TL_PUB_TM1   TL_PUB_TM_AE
WRK_PROJ3_TAO   TL_PUB_TM1   TL_PUB_TM_AE
WRK_PROJ4_TAO   TL_PUB_TM1   TL_PUB_TM_AE
WRK_PROJ5_TAO   TL_PUB_TM1   TL_PUB_TM_AE
WRK_PROJ6_TAO   TL_PUB_TM1   TL_PUB_TM_AE
WRK_PROJ7_TAO   TL_PUB_TM1   TL_PUB_TM_AE
WRK_PROJ_TAO    TL_PUB_TM1   TL_PUB_TM_AE

5 temporary records are built by Application Designer for each Application Engine program. But TL_PUB_TM1 is never run on its own. So do you need the extra instances of those 8 temporary records? The temporary records defined on TL_PUB_TM_AE are a subset of TL_PUB_TM1. If you reduced the number of instances on TL_PUB_TM_AE to 0, you would still have the 5 instances defined on TL_PUB_TM_TM1. But that would enable you to drop 40 tables and their indexes.

So, I started to wonder if there was a general principle here. If the temporary tables on an Application Engine program are a subset of those on another program, then providing you make ensure the number of instances on the superset is not less than those of the subset, you could reduce the number of instances on the subset to 0.

This view reports Application Engine programs whose temporary records are a subset of those on another program, and also counts the number of records in the subset.

CREATE OR REPLACE VIEW gfc_aetemptbl_hier AS
SELECT
sup.ae_applid sup_applid, supa.temptblinstances sup_instances
,  sub.ae_applid sub_applid, suba.temptblinstances sub_instances
, (SELECT COUNT(*)
FROM   psaeappltemptbl supc, psaeappltemptbl subc
WHERE  supc.ae_applid = sup.ae_applid
AND    subc.ae_applid = sub.ae_applid
AND    subc.recname = supc.recname) num_records
FROM (SELECT DISTINCT ae_applid FROM psaeappltemptbl) sup
, (SELECT DISTINCT ae_applid FROM psaeappltemptbl) sub
, psaeappldefn supa
, psaeappldefn suba
WHERE sup.ae_applid != sub.ae_applid
AND supa.ae_applid = sup.ae_applid
AND suba.ae_applid = sub.ae_applid
AND EXISTS( /*a temporary record in common*/
SELECT 'x'
FROM psaeappltemptbl sup1
, psaeappltemptbl sub1
WHERE sub1.ae_applid = sub.ae_applid
AND sup1.ae_applid = sup.ae_applid
AND sup1.recname = sub1.recname
AND ROWNUM = 1)
/*there is no record in the subset that is not in the superset*/
AND NOT EXISTS(
SELECT  'x'
FROM psaeappltemptbl sub2
WHERE   sub2.ae_applid = sub.ae_applid
AND NOT EXISTS(
SELECT  'x'
FROM psaeappltemptbl sup2
WHERE   sup2.ae_applid = sup.ae_applid
AND sub2.recname = sup2.recname
AND ROWNUM = 1)
AND ROWNUM = 1)
/*there is a record in the subset that is not in the subset - so there is a difference*/
AND EXISTS(
SELECT  'x'
FROM psaeappltemptbl sup2
WHERE   sup2.ae_applid = sup.ae_applid
AND NOT EXISTS(
SELECT  'x'
FROM psaeappltemptbl sub2
WHERE   sub2.ae_applid = sub.ae_applid
AND sup2.recname = sub2.recname
AND ROWNUM = 1)
AND ROWNUM = 1)
ORDER BY 1,2;

This is the output from the view for the Application Engine programs in the example.

SUP_APPLID   SUP_INSTANCES SUB_APPLID   SUB_INSTANCES NUM_RECORDS
------------ ------------- ------------ ------------- -----------
…
TL_PUB_TM1               5 TL_PUB_TM_AE             5           8
TL_PUB_TM1               5 TL_PY_PUB_TM             5           5
TL_PUB_TM_AE             5 TL_PY_PUB_TM             5           5
…

I found that some Application Engine programs have identical sets of temporary records. This can happen when a program is cloned, which some customers do when they want to customise a vanilla program. This view reports on them.

CREATE OR REPLACE VIEW gfc_aetemptbl_eq AS
SELECT sup.ae_applid sup_applid, supa.temptblinstances sup_instances
, sub.ae_applid sub_applid, suba.temptblinstances sub_instances
, (SELECT COUNT(*)
FROM   psaeappltemptbl supc, psaeappltemptbl subc
WHERE  supc.ae_applid = sup.ae_applid
AND    subc.ae_applid = sub.ae_applid
AND    subc.recname = supc.recname) num_records
FROM (SELECT DISTINCT ae_applid FROM psaeappltemptbl) sup
, (SELECT DISTINCT ae_applid FROM psaeappltemptbl) sub
, psaeappldefn supa
, psaeappldefn suba
WHERE sup.ae_applid < ae_applid =" sup.ae_applid" ae_applid =" sub.ae_applid" ae_applid =" sub.ae_applid" ae_applid =" sup.ae_applid" recname =" sub1.recname" rownum =" 1)" ae_applid =" sub.ae_applid" ae_applid =" sup.ae_applid" recname =" sup2.recname" rownum =" 1)" rownum =" 1)" ae_applid =" sup.ae_applid" ae_applid =" sub.ae_applid" recname =" sub2.recname" rownum =" 1)" rownum =" 1)">

Here, three programs share the same set of temporary records.

SUP_APPLID   SUP_INSTANCES SUB_APPLID   SUB_INSTANCES NUM_RECORDS
------------ ------------- ------------ ------------- -----------
…
ELEC_TSCRPT             20 E_TSCRPT_BAT            20           2
ELEC_TSCRPT             20 E_TSCRPT_LIB            20           2
E_TSCRPT_BAT            20 E_TSCRPT_LIB            20           2
…

I can use these view to set the instances on the subsets to 0 and increase the instances on the supersets as necessary. There are examples of both subsets within subsets and more than two Application Engine programs that share the same set of temporary tables. So I do this repeatedly until all the subsets have zero instances.

This PL/SQL script makes the updates to the Application Engine programs (including maintaining PeopleSoft version numbers), and also creates an Application Designer project called GFC_TTI with all the programs and records. This project can then be used to migrate the Application Engine programs to another environment.

DECLARE
l_any BOOLEAN;
l_projectname VARCHAR2(30 CHAR) := 'GFC_TTI';
l_version_aem INTEGER;
l_version_pjm INTEGER;

PROCEDURE projitem(objecttype   INTEGER
,objectid1    INTEGER
,objectvalue1 VARCHAR2) IS
BEGIN
INSERT INTO psprojectitem
(projectname ,objecttype
,objectid1 ,objectvalue1 ,objectid2 ,objectvalue2
,objectid3 ,objectvalue3 ,objectid4 ,objectvalue4
,nodetype ,sourcestatus ,targetstatus ,upgradeaction ,takeaction ,copydone)
VALUES
(l_projectname,objecttype
,objectid1, objectvalue1, 0, ' '
, 0, ' ', 0, ' '
,0,0,0,0,1,0);
EXCEPTION WHEN dup_val_on_index THEN NULL;
END;

BEGIN
UPDATE psversion
SET version = version+1
WHERE objecttypename IN('SYS','AEM','PJM');

UPDATE pslock
SET version = version+1
WHERE objecttypename IN('SYS','AEM','PJM');

SELECT version
INTO   l_version_aem
FROM   psversion
WHERE  objecttypename = 'AEM';

SELECT version
INTO   l_version_pjm
FROM   psversion
WHERE  objecttypename = 'PJM';

l_any := TRUE;
WHILE l_any LOOP
l_any := FALSE;
FOR i IN(
SELECT *
FROM gfc_aetemptbl_hier a
WHERE a.sub_instances > 0
AND NOT EXISTS(
SELECT 'x'
FROM   gfc_aetemptbl_hier b
WHERE  b.sup_applid = a.sub_applid
AND    b.sub_instances > 0
AND    ROWNUM = 1)
ORDER BY 1
) LOOP
UPDATE psaeappldefn x
SET    temptblinstances =     
GREATEST(x.temptblinstances
,i.sub_instances,i.sup_instances)
,      version = l_version_aem
,      lastupddttm = SYSDATE
WHERE  ae_applid = i.sup_applid; 

projitem(33,66,i.sup_applid);

UPDATE psaeappldefn x
SET    temptblinstances = 0
,    version = l_version_aem
,      lastupddttm = SYSDATE
WHERE  ae_applid = i.sub_applid;

projitem(33,66,i.sub_applid);
l_any := TRUE;
END LOOP;
END LOOP;

l_any := TRUE;
WHILE l_any LOOP
l_any := FALSE;
FOR i IN(
SELECT *
FROM  gfc_aetemptbl_eq a
WHERE a.sub_instances > 0
AND NOT EXISTS(
SELECT 'x'
FROM   gfc_aetemptbl_eq b
WHERE  b.sup_applid = a.sub_applid
AND    b.sub_instances > 0
AND    ROWNUM = 1)
ORDER BY 1
) LOOP
UPDATE psaeappldefn x
SET    temptblinstances =     
GREATEST(x.temptblinstances
,i.sub_instances,i.sup_instances)
,      version = l_version_aem
,      lastupddttm = SYSDATE
WHERE  ae_applid = i.sup_applid;

projitem(33,66,i.sub_applid);

UPDATE psaeappldefn x
SET    temptblinstances = 0
,      version = l_version_aem
,      lastupddttm = SYSDATE
WHERE  ae_applid = i.sub_applid;

projitem(33,66,i.sub_applid);
l_any := TRUE;
END LOOP;
END LOOP;
END;

INSERT INTO psprojectitem
(projectname ,objecttype
,objectid1 ,objectvalue1 ,objectid2 ,objectvalue2
,objectid3 ,objectvalue3 ,objectid4 ,objectvalue4
,nodetype ,sourcestatus ,targetstatus ,upgradeaction ,takeaction ,copydone)
SELECT DISTINCT
l_projectname,0
, 1, recname, 0, ' '
, 0, ' ', 0, ' '
, 0,0,0,0,1,0
FROM   psaeappltemptbl t
,      psprojectitem i
WHERE  i.projectname  = l_projectname
AND    i.objecttype   = 33
AND    i.objectid1    = 66
AND i.objectvalue1 = t.ae_applid
AND NOT EXISTS(
SELECT 'x'
FROM   psprojectitem i1
WHERE  i1.projectname = l_projectname
AND    i1.objecttype  = 0
AND    i1.objectid1   = 1
AND    i1.objectvalue1 = t.recname
);

BEGIN
INSERT INTO psprojectdefn
(projectname,version,projectdescr,tgtservername,tgtdbname
,tgtoprid,tgtopracct,comprelease,srccompreldttm,tgtcompreldttm
,compreldttm,keeptgt,tgtorientation,comparetype,commitlimit
,reportfilter,maintproj,lastupddttm,lastupdoprid,releaselabel
,releasedttm,objectownerid,descrlong)
VALUES
(l_projectname,l_version_pjm,'Temporary Table Instances',' ',' '
,' ',' ',' ',NULL,NULL
,NULL,31,0,1,50
,16232832,0,SYSDATE,'PS',' '
,NULL,' ','Application Engine programs, and related Temporary Records, '
||'whose number of temporary table instances have been changed');
EXCEPTION WHEN dup_val_on_index THEN
UPDATE psprojectdefn
SET    version = (SELECT version FROM psversion
WHERE  objecttypename = 'PJM')
,      lastupddttm = SYSDATE
WHERE  projectname = l_projectname;
END;
END;

Conclusion

The effect on my demo HCM8.9 system was to reduce the total number of temporary table instances from 5942 to 5106, a 14% reduction. However, when I tried this on an HCM9.0 system, I got a reduction of only 7%. This shows that PeopleSoft has been more careful about specifying the number of temporary tables in the later version.

Then you can use the script in an earlier posting to remove the excess tables.

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 did need properly gathered statistics on an object. I modified my PL/SQL packaged procedure wrapper which 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.