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.
@go-faster.co.uk on BlueSky
Posts
