Search

Top 60 Oracle Blogs

Recent comments

Oakies Blog Aggregator

nonsense correlation

I was doing a little light reading on my Saturday night in my Oxford Dictionary of Statistics by Graham Upton and Ian Cook and came across this definition:nonsense correlation: A term used to describe a situation where two variables (X and Y, say) are correlated without being causally related to one another. The usual explanation is that they are both related to a third variable, Z. Often the

Spotting the Red Flags (Part 1 of n)

As a consultant I get to see many different systems, managed by different people. A large proportion of these systems are broken in exactly the same ways to others, which makes spotting the problems pretty straightforward! It occurred to me at some point that this is a bit like a crime scene – somebody had murdered the system, and it was my job to find out ‘whodunnit’, or at least ‘whatdunnit’. The ‘what’ is quite frequently one (or few) actions performed by the administrator, normally with good intention, that result in system performance or availability carnage. I call these actions ‘Red Flags’, and spotting them can save a lot of time.

A couple of years ago at the Hotsos conference, I did a small impromptu survey. It’s not all that often that you have 500 Oracle geeks in a room that you can solicit opinion from, so it seemed like a good chance to cast the net for Red Flags. I seeded the process with a few of my personal favourites and then started writing as the suggestions came in. Here are the ‘seed’ entries:

  • Any non-default setting for optimizer_index_cost_adj
  • Carefully configured KEEP and RECYCLE pools
  • Multiple block sizes
  • Some kind of spin_count configuration

Remember, these are not necessarily the wrong thing to do to your system, but they probably are. That’s why they attract my attention in the first instance.

I got a whole load of responses. Of those responses, some had missed the point and just identified something that was broken. This is more subtle than that – these are things that are forensic evidence of system homicide. Some of the decent ones I got back follow:

  • /*+ RULE */ hint in abundance (I know, lot’s of apps do this, and it sometimes makes sense)
  • Numeric data in VARCHAR2 columns
  • Indexes and tables on ‘separate disks’
  • All columns in a table NULLable

Of course, there were more cynical responses too, such as  (he he): cluster_database=true

I was going to write some kind of presentation about it, but I think this might work nicely as a blog entry, potentially multiple entries! If anyone has some good suggestions, please let me know and I’ll start adding them into part 2, along with any more I think of… :)

The amazing truth about the Red Flags, though, is that they are incredibly hard to dislodge. Once optimizer_index_cost_adj is set to anything but default, it is nigh on impossible to convince anyone that the system is probably now tuned on its head. Same with a multiple buffer pool configuration, and same with multiple block sizes.

The best flowchart ever

This was forwarded to me and I totally laughed out loud. I've seen a lot of flow charts (mostly of the boring technical sort), but this is my absolute all-time favorite! Zoom in on it or visit the originating site to see it better.

It's a flow chart of the song "Total Eclipse of the Heart".


Happy Birthday, OFA Standard

Yesterday I finally posted to the Method R website some of the papers I wrote while I was at Oracle Corporation (1989–1999). You can now find these papers where I am.

When I was uploading my OFA Standard paper, I noticed that today—24 September 2009—is the fourteenth birthday of its publication date. So, even though the original OFA paper was around for a few years before 1995, please join me in celebrating the birthday of the final version of the official OFA Standard document.

Resumes, interviews and truth in advertising

OK...what's the deal with resumes that say one thing (so that a candidate looks nearly perfect) and then you interview them and find out they can barely spell Oracle? I'd think that if your resume says you've been working with Oracle since 1988 and have worked extensively with PL/SQL, you'd know what a REF CURSOR is or maybe even know a bit about collections or something, right? I asked one candidate these questions and they said they'd never ran into those 'features'. So finally, just for fun, I asked "On a scale of 1-10, with 10 being expert, where would you rank yourself in terms of your PL/SQL proficiency?" The answer: "Well, I suppose it's a bit conceited to give yourself a 10, so I'll just be humble and say 9."

Are you kidding me? Really?

Personally, I think my resume is lacking overall. I seem to have a hard time distilling over 20 years of experience into a couple of pages and making the "real me" show up on paper. But, if you get me into the ballpark (i.e. an interview), I'll hit most every pitch you throw at me. But, I'd be terrified to try to over-sell myself and get caught unable to deliver the goods. So, it's just a bit scary for me to look at resumes and think "Wow!" and then talk to the person for 3 minutes and think "Yuck!"

At what point did this become the norm and not the exception? Or, am I just in the midst of some weirdly skewed tilt of the interview universe? It almost reminds me of my reaction when reading an ad that claims "World's Best" or "Indescribably Perfect" or some other line and knowing it's just a ploy. I'm not a fan of those that skirt the edges of "truth in advertising". But when it comes to selling yourself, I'd really hope the claims you make could be backed up. Sigh...

Here's hoping that the rest of this week's interviews are with folks who match their advertising.

Dynamically Switching PeopleSoft Temporary Records between Global Temporary and Normal Tables during Application Engine Programs

I am working on a Time & Labour system. We run the main T&L process in different modes. There is a company wide overnight batch process, but individual parts of the company can run the process for their section during the day to process exceptions and to generate schedules. This is done with a custom AE program that calls the delivered TL_TIMEADMIN.

Running on Oracle 10gR2, we have faced performance problems caused by contention between concurrent truncate operations in these processes (see Factors Affecting Performance of Concurrent Truncate of Working Storage Tables).

One way to evade this problem is to convert the working storage tables to be Global Temporary Tables (GTTs). The contention problem caused by serialisation of truncate operations does not occur with GTTs. Last year I wrote a blog entry (Global Temporary Tables and PeopleSoft Temporary Records) that discussed when it was possible to use GTTs in Application Engine (AE) programs. Essentially, you can only use GTTs when restart is disabled for all AE programs that reference a temporary records.

So we could consider running the small but frequent daytime processes with restart disabled. Then we can make the non-shared instances of the temporary records into Global Temporary tables for the non-restartable daytime processes. However, we want to continue to run the overnight processes with restart enabled so that we have the option to restart a process that fails overnight from where it crashed, rather than going back to the beginning. The same non-shared instances may need to be Global Temporary during the day but normal recoverable tables by night.

Previously, I have only converted tables to Global Temporary where they are not referenced by a restartable AE program. I have now devised a way of switching a table to being a GTT if it is allocated to a process for which restart is disabled, and switching it back to a normal table if not. This is the best of both worlds.

When a non-shared instance of a temporary record is allocated to a particular process instance, a row is inserted into PS_AETEMPTBLMGR. The value of the restart disable flag is also stored in PS_AETEMPTBLMGR. I have created a trigger that switches a non-shared instance of a temporary record from a normal table to a GTT if restart is disabled, or switches it back to a normal table if restart is enabled. The trigger will create the GTT and any indexes on it if necessary, and will rename the tables as necessary so that the correct version has the default name expected by the process.

So if, for example, I have instance number 11 of a record called WRK_SHIFT_TAO, then the table will be called PS_WRK_SHIFT_TAO11. If that is allocated to a non-restartable AE program, the trigger will check for a table called GT_WRK_SHIFT_TAO11. If it doesn't exist the trigger will create it, dynamically generating the DDL with the dbms_metadata package. Thus, the structure of the GTT will be identical to PS_WRK_SHIFT_TAO11, the leading PS will also be changed to GT. The same indexes will also be created. The original normal table PS_WRK_SHIFT_TAO11 will be renamed to XX_WRK_SHIFT_TAO11, and then the GTT, at that point called GT_WRK_SHIFT_TAO11 will be renamed to PS_WRK_SHIFT_TAO11. The indexes are not renamed. If later the same instance is allocated to a restartable process, the change will be reversed by renaming again. The GTT will not be dropped so that it does not need to be recreated again the next time the non-restartable program uses it.

All DDL generated and executed by the trigger is written to the Message Log for that process (click on the screen shot to enlarge).

The shared instance of a record (the one without a instance number suffix) is never rebuilt as a Global Temporary table because it is possible that a restartable and non-restartable process might both use the shared instance at the same time.

One complication is how to handle changes to the temporary records. Application Designer will only create normal tables. So, if the table is to be rebuilt, them it needs to be switched back to a normal table, and the corresponding GTT created by the trigger should then be dropped. The Application Designer can recreate the table in the usual way. Next time the non-restartable AE runs, it will recreate the GTT with the new structure.

An updated version of T_LOCK is available which handles PeopleSoft temporary records and prevent DDL on PS_ tables with corresponding GT_ tables. The commands which swap these tables back and forth are explicitly permitted by the new version of this trigger.

Dynamically Switching PeopleSoft Temporary Records between Global Temporary and Normal Tables during Application Engine Programs

I am working on a Time & Labour system. We run the main T&L process in different modes. There is a company wide overnight batch process, but individual parts of the company can run the process for their section during the day to process exceptions and to generate schedules. This is done with a custom AE program that calls the delivered TL_TIMEADMIN.

Running on Oracle 10gR2, we have faced performance problems caused by contention between concurrent truncate operations in these processes (see Factors Affecting Performance of Concurrent Truncate of Working Storage Tables).

One way to evade this problem is to convert the working storage tables to be Global Temporary Tables (GTTs). The contention problem caused by serialisation of truncate operations does not occur with GTTs. Last year I wrote a blog entry (Global Temporary Tables and PeopleSoft Temporary Records) that discussed when it was possible to use GTTs in Application Engine (AE) programs. Essentially, you can only use GTTs when restart is disabled for all AE programs that reference a temporary records.

So we could consider running the small but frequent daytime processes with restart disabled. Then we can make the non-shared instances of the temporary records into Global Temporary tables for the non-restartable daytime processes. However, we want to continue to run the overnight processes with restart enabled so that we have the option to restart a process that fails overnight from where it crashed, rather than going back to the beginning. The same non-shared instances may need to be Global Temporary during the day but normal recoverable tables by night.

Previously, I have only converted tables to Global Temporary where they are not referenced by a restartable AE program. I have now devised a way of switching a table to being a GTT if it is allocated to a process for which restart is disabled, and switching it back to a normal table if not. This is the best of both worlds.

When a non-shared instance of a temporary record is allocated to a particular process instance, a row is inserted into PS_AETEMPTBLMGR. The value of the restart disable flag is also stored in PS_AETEMPTBLMGR. I have created a trigger that switches a non-shared instance of a temporary record from a normal table to a GTT if restart is disabled, or switches it back to a normal table if restart is enabled. The trigger will create the GTT and any indexes on it if necessary, and will rename the tables as necessary so that the correct version has the default name expected by the process.

So if, for example, I have instance number 11 of a record called WRK_SHIFT_TAO, then the table will be called PS_WRK_SHIFT_TAO11. If that is allocated to a non-restartable AE program, the trigger will check for a table called GT_WRK_SHIFT_TAO11. If it doesn't exist the trigger will create it, dynamically generating the DDL with the dbms_metadata package. Thus, the structure of the GTT will be identical to PS_WRK_SHIFT_TAO11, the leading PS will also be changed to GT. The same indexes will also be created. The original normal table PS_WRK_SHIFT_TAO11 will be renamed to XX_WRK_SHIFT_TAO11, and then the GTT, at that point called GT_WRK_SHIFT_TAO11 will be renamed to PS_WRK_SHIFT_TAO11. The indexes are not renamed. If later the same instance is allocated to a restartable process, the change will be reversed by renaming again. The GTT will not be dropped so that it does not need to be recreated again the next time the non-restartable program uses it.

All DDL generated and executed by the trigger is written to the Message Log for that process (click on the screen shot to enlarge).

The shared instance of a record (the one without a instance number suffix) is never rebuilt as a Global Temporary table because it is possible that a restartable and non-restartable process might both use the shared instance at the same time.

One complication is how to handle changes to the temporary records. Application Designer will only create normal tables. So, if the table is to be rebuilt, them it needs to be switched back to a normal table, and the corresponding GTT created by the trigger should then be dropped. The Application Designer can recreate the table in the usual way. Next time the non-restartable AE runs, it will recreate the GTT with the new structure.

An updated version of T_LOCK is available which handles PeopleSoft temporary records and prevent DDL on PS_ tables with corresponding GT_ tables. The commands which swap these tables back and forth are explicitly permitted by the new version of this trigger.

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 will update these values. Oracle’s Applications (that it has developed itself), such as E-Business Suite do 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 a number of procedure 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.


#000000; padding-left: 4px; padding-right: 4px; background-color:#EEEEEE">
...
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 thus:


#000000; padding-left: 4px; padding-right: 4px; background-color:#EEEEEE">
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.

Screenshot from Oracle Enterprise Manager
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 useful nice if perhaps the Component name and PeopleCode context was written to MODULE and ACTION. But it isn’t.

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 will update these values. Oracle’s Applications (that it has developed itself), such as E-Business Suite do 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 a number of procedure 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.


#000000; padding-left: 4px; padding-right: 4px; background-color:#EEEEEE">
...
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 thus:


#000000; padding-left: 4px; padding-right: 4px; background-color:#EEEEEE">
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.

Screenshot from Oracle Enterprise Manager
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 useful nice if perhaps the Component name and PeopleCode context was written to MODULE and ACTION. But it isn’t.

Reducing Unnecessary Instances of Temporary Records

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.

#000000; padding-left: 4px; padding-right: 4px; background-color:#EEEEEE">
AE_APPLID    TEMPTBLINSTANCES
------------ ----------------
TL_PUB_TM1 5
TL_PUB_TM_AE 5

They share 8 temporary records in common.

#000000; padding-left: 4px; padding-right: 4px; background-color:#EEEEEE">
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 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.