In a previous posting, I recommended moving temporary records used 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 call 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.
They share 8 temporary records in common.
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.
This is the output from the view for the Application Engine programs in the example.
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.
Here, three programs share the same set of temporary records.
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.
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 have 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.