UKOUG Apps 18 Conference, Liverpool

Friday, January 30, 2009

Managing Changes to the Number of Instances of Temporary Tables used in Application Engine Programs

When you run multiple copies of either the same Application Engine program, or different Application Engines that happen to use the same temporary work records, you need to worry about how many instances of the program or programs are likely to run concurrently, and hence how many instances of the temporary table to build.

But how do you manage these tables when you change the number of instances in the Application Engine properties?

How many tables are built for each Temporary Record?

The number of instances of each Application Engine program is set in the properties for that program (and stored on the PeopleTools table PSAEAPPLDEFN in the column TEMPTBLINSTANCES).

This screenshot is the Application Engine Program Properties for TL_TIMEADMIN. It is delivered configured for up to 10 concurrent instances.


The number of tables that are built for each Temporary Record is the sum of the instances in all the Application Engine programs in which the record is used, plus the number of Global Instances (in set up on the PeopleTools Options page.

Let’s take the record TL_PROF_LIST as an example. It is defined as a Temporary Record in 8 different Application Engine programs (in my HCM8.9 demo system).

SELECT a.recname, a.ae_applid, b.temptblinstances
FROM psaeappltemptbl a, psaeappldefn b
WHERE a.ae_applid = b.ae_applid
AND a.recname = 'TL_PROF_LIST'
ORDER BY a.recname
/

RECNAME AE_APPLID TEMPTBLINSTANCES
--------------- ------------ ----------------
TL_PROF_LIST TL_AGG_SECTN 1
TL_PROF_LIST TL_OUTTCD 5
TL_PROF_LIST TL_PUB_TM1 5
TL_PROF_LIST TL_SCHHRSRPT 5
TL_PROF_LIST TL_SCHRES_AE 9
TL_PROF_LIST TL_ST_LIB 5
TL_PROF_LIST TL_TIMEADMIN 10
TL_PROF_LIST TL_TRPROFILE 10

So, across all the programs 50 temporary tables are required.

SELECT * FROM pstemptblcntvw 
WHERE recname = 'TL_PROF_LIST'
/

RECNAME TEMPTBLINSTANCES
--------------- ----------------
TL_PROF_LIST 50

The system has three global instances.

SELECT temptblinstances
FROM psoptions
/

TEMPTBLINSTANCES
----------------
3

So, Application Designer will build 54 tables based on this record.
  • 3 ‘global’ instances for Application Engines without a Process Instance number. The table names will be suffixed with numbers 1 to 3.
  • 50 ‘private’ instances for the number of requested instances of the Application Engines. The table names will be suffixed with numbers 4 to 53
  • 1 ‘shared’ version without a suffix (in other words the usual name) which is used if there is no instance no already allocated to a process instance.
And this is the top and bottom of the script to build the tables only generated by Application Designer.

CREATE TABLE PS_TL_PROF_LIST (PROCESS_INSTANCE DECIMAL(10) NOT NULL,
EMPLID VARCHAR2(11) NOT NULL,
EMPL_RCD SMALLINT NOT NULL,
START_DT DATE,
END_DT DATE) TABLESPACE TLWORK STORAGE (INITIAL 40000 NEXT 100000
MAXEXTENTS UNLIMITED PCTINCREASE 0) PCTFREE 10 PCTUSED 80
/

CREATE TABLE PS_TL_PROF_LIST53 (PROCESS_INSTANCE DECIMAL(10) NOT NULL,
EMPLID VARCHAR2(11) NOT NULL,
EMPL_RCD SMALLINT NOT NULL,
START_DT DATE,
END_DT DATE) TABLESPACE TLWORK STORAGE (INITIAL 40000 NEXT 100000
MAXEXTENTS UNLIMITED PCTINCREASE 0) PCTFREE 10 PCTUSED 80
/


Are there any temporary tables that have not been built but that should be built?

If you increase the number of instances of temporary tables on an Application Engine program then you may need to build the extra temporary tables. This query reports the missing tables.

SELECT r.recname
, n.n instance
, c.temptblinstances
+ o.temptblinstances temptblinstances
FROM pstemptblcntvw c
, psrecdefn r
, (SELECT rownum-1 n FROM psrecdefn
WHERE rownum <= 100) n
, psoptions o
WHERE r.recname = c.recname
AND n.n <= c.temptblinstances+o.temptblinstances
AND NOT EXISTS(
SELECT 'x'
FROM user_tables t
WHERE t.table_name =
DECODE(r.sqltablename, ' ', 'PS_'||r.recname,
r.sqltablename) ||DECODE(n.n,0,'',n.n)
)
ORDER BY 1,2
/

I dropped PS_TL_PROF_LIST42, but I also increased the number of instance of TL_TIMEADMIN from 10 to 11. So the query reports that instances 42 and 54 of this table are missing, and all the other temporary tables for TL_TIMEADMIN also report a missing instance.

RECNAME           INSTANCE TEMPTBLINSTANCES
--------------- ---------- ----------------

TL_PMTCH_TMP1 19 19
TL_PMTCH_TMP2 19 19
TL_PROF_LIST 42 54
TL_PROF_LIST 54 54
TL_PROF_WRK 38 38
TL_PT_FINAL 29 29



The remedy is very simple. Application Designer will build a script for just the missing tables.

CREATE TABLE PS_TL_PROF_LIST42 (PROCESS_INSTANCE DECIMAL(10) NOT NULL,
EMPLID VARCHAR2(11) NOT NULL,
EMPL_RCD SMALLINT NOT NULL,
START_DT DATE,
END_DT DATE) TABLESPACE TLWORK STORAGE (INITIAL 40000 NEXT 100000
MAXEXTENTS UNLIMITED PCTINCREASE 0) PCTFREE 10 PCTUSED 80
/

Are there any temporary tables built that should not be built?

If you have reduced the number of temporary tables, then you may need to drop the excess tables.

This query reports tables that are beyond the number required.

SELECT r.recname
, n.n instance
, c.temptblinstances
+ o.temptblinstances temptblinstances
, t.table_name
FROM pstemptblcntvw c
, psrecdefn r
, (SELECT rownum-1 n FROM psrecfield
WHERE rownum <= 100
) n
, user_tables t
, psoptions o
WHERE r.recname = c.recname
AND t.table_name =
DECODE(r.sqltablename, ' ', 'PS_'||r.recname,
r.sqltablename) ||DECODE(n.n,0,'',n.n)
AND n.n > c.temptblinstances+o.temptblinstances
ORDER BY r.recname, n.n
/


I built the missing tables for the previous example, but then I reduced the number of instances on TL_TIMEADMIN back to 10. Now, the query reports that there is an extra table for each record beyond the number defined.

RECNAME         INSTANCE TEMPTBLINSTANCES TABLE_NAME
------------- -------- ---------------- ------------------

TL_PMTCH_TMP1 19 18 PS_TL_PMTCH_TMP119
TL_PMTCH_TMP2 19 18 PS_TL_PMTCH_TMP219
TL_PROF_LIST 54 53 PS_TL_PROF_LIST54
TL_PROF_WRK 38 37 PS_TL_PROF_WRK38
TL_PT_FINAL 29 28 PS_TL_PT_FINAL29


The problem is that Application Designer will not generate the DDL to drop any of these tables. If you reduce the number of temporary table instances, you will have to drop these tables yourself. However, now, it is easy to adjust the above query to produce the DDL to drop the tables.

SELECT 'DROP TABLE '||t.table_name||' PURGE;' cmd
FROM …

The query then produces these commands.


DROP TABLE PS_TL_PMTCH_TMP119 PURGE;
DROP TABLE PS_TL_PMTCH_TMP219 PURGE;
DROP TABLE PS_TL_PROF_LIST54 PURGE;
DROP TABLE PS_TL_PROF_WRK38 PURGE;
DROP TABLE PS_TL_PT_FINAL29 PURGE;


The scripts in this posting can be downloaded from my website.

4 comments :

Ganesh said...

Good one.

I want to find the locked and free temprory table instances by query.

how can i do that ?

After an AE goes to Success is their any way to find which temp table insatance that process used.

David Kurtz said...

That is recorded in PS_AETEMPTBLMGR.

Daryn Lowe said...

Hi David, great blog post as usual however I wonder if you could clarify something for me.

When Application waits are seen due to the contention updating ps_aeonlineinst oracle recommends increasing the online temp instance count in psoptions. Doc ID 651316.1, but it states that ALL Temp Tables need to be rebuilt and I couldn't understand why as this would essentially require an outage, however looking at your blog post I see you mention the following:

3 ‘global’ instances for Application Engines without a Process Instance number. The table names will be suffixed with numbers 1 to 3.

On seeing this I thought perhaps the first x number of instances for online AEs were created without the PROCESS_INSTANCE column and therefore full rebuild of TAOs was required due to the different table structure used for AEs run via CallApEngine, but I see the TAO structures are all identical. My query therefore is why can the operation not be done via the following procedure rather than rebuilding all the TAOs as oracle state?

a) Determine quantity of Temp tables required for online+batch usage and alter accordingly in App designer for each AE utilizing Temp tables
b) Create project with all affected TAOs and run build using create table. This will create & run the SQL just for the missing instances from current count to new count, leaving currently existing and possibly in use tables untouched. (No outage)
c) Increase the online temp ae count accordingly

Is there something I'm missing in the whole process? I thought we could just create the additional n TAO instances via above procedure and then increase online temp instances by n.

Many thanks in advance.

David Kurtz said...

Daryn, it’s a Catch-22 situation.
You cannot build the extra tables with Application Designer until you have increased the number of temporary table instances, and as soon as you increase the number of temporary table instances, an Application Engine initiated by the Process Scheduler will assume the tables have been created and will attempt to use them if necessary.
Remember that the number of on-line temporary table instances is applied to every temporary record in the database. In Financials, you will have thousands of tables to build across all modules, whether you use the module or not. So you can really only manage this with Application Designer.
If you are using stand-alone PSAE processes the change is recognised immediately, but if you are using PSAESRV server processes it will be picked up as the server processes recycle, or when the scheduler is restarted. Similarly the new on-line temporary table instances will be used as application server processes recycle, or when the application server is restarted.
A similar problem exists with the number of non-shared temporary tables used by a scheduled Application Engine program, but that setting is an attribute of the Application Engine program and is migrated with Application Designer.
The problem is how avoid a production outage rather than in a development or test system.
You could
1. Increase the number on-line temporary table instances on a test system
2. Use Application Designer on that test system to create the DDL script to create the additional tables
3. Apply that script in production before
4. Increase the number of on-line temporary table instances.
5. As a final test, try to build the create table script again with Application Designer. If this doesn’t create any tables then the process worked successfully.