Search

Top 60 Oracle Blogs

Recent comments

Oakies Blog Aggregator

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.

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.

PeopleSoft and the Oracle Recycle Bin

If you are running PeopleSoft on Oracle 10g, what do you do about the Recycle Bin? It is a new feature in Oracle 10g, and it is enabled by default. So you are using it, unless you have taken a decision to the contrary.

It works just like the Windows recycle bin. You can drop a table and then flash it back (they didn't call it UNDROP because Oracle marketing now calls everything Flashback). So when you drop a table, Oracle marks it as dropped, and renames it with a system generated name beginning with BIN$. You can look at the contents of the Recycle Bin through a catalogue view.

>create table t (a number);
>drop table t;
>select * from user_recyclebin

OBJECT_NAME ORIGINAL_NAME OPERATION TYPE
------------------------------ -------------------------------- --------- -------------------------
TS_NAME CREATETIME DROPTIME DROPSCN
------------------------------ ------------------- ------------------- ----------
PARTITION_NAME CAN CAN RELATED BASE_OBJECT PURGE_OBJECT SPACE
-------------------------------- --- --- ---------- ----------- ------------ ----------
BIN$ZCLja8iAA9LgRAAOf+3h5A==$0 T DROP TABLE
PSDEFAULT 2009-03-02:13:35:12 2009-03-02:13:35:14 9.7561E+12
YES YES 776642 776642 776642 4

If you don't want a table to go into the recycle bin when you drop it, you can code DROP TABLE ... PURGE - but PeopleSoft doesn't do this.

PeopleSoft alter scripts usually drop and recreate tables. In a production system this is often the best option, otherwise you run the risk of causing rows to migrate to other blocks when you add new columns. In one system, I found 17000 objects in the recycle bin, occupying 1.3Gb.

My Opinion

Personally, I would disable the recycle bin by setting the initialisation parameter RECYCLEBIN = OFF in all PeopleSoft environments, with the possible exception of the development environment.

The RECYCLEBIN parameter can also be set dynamically at session or system level. You could perhaps turn it on prior to upgrade/data migration procedures, and then when satisfied turn it off again and manually purge the recycle bin.

I think Oracle features should be used knowingly. It doesn't matter whether you decide to use a feature or not. It is important that in making that decision you have thought about how to deal with the implications, rather than be surprised later.

PeopleSoft and the Oracle Recycle Bin

If you are running PeopleSoft on Oracle 10g, what do you do about the Recycle Bin? It is a new feature in Oracle 10g, and it is enabled by default. So you are using it, unless you have taken a decision to the contrary.

It works just like the Windows recycle bin. You can drop a table and then flash it back (they didn't call it UNDROP because Oracle marketing now calls everything Flashback). So when you drop a table, Oracle marks it as dropped, and renames it with a system generated name beginning with BIN$. You can look at the contents of the Recycle Bin through a catalogue view.

>create table t (a number);
>drop table t;
>select * from user_recyclebin

OBJECT_NAME ORIGINAL_NAME OPERATION TYPE
------------------------------ -------------------------------- --------- -------------------------
TS_NAME CREATETIME DROPTIME DROPSCN
------------------------------ ------------------- ------------------- ----------
PARTITION_NAME CAN CAN RELATED BASE_OBJECT PURGE_OBJECT SPACE
-------------------------------- --- --- ---------- ----------- ------------ ----------
BIN$ZCLja8iAA9LgRAAOf+3h5A==$0 T DROP TABLE
PSDEFAULT 2009-03-02:13:35:12 2009-03-02:13:35:14 9.7561E+12
YES YES 776642 776642 776642 4

If you don't want a table to go into the recycle bin when you drop it, you can code DROP TABLE ... PURGE - but PeopleSoft doesn't do this.

PeopleSoft alter scripts usually drop and recreate tables. In a production system this is often the best option, otherwise you run the risk of causing rows to migrate to other blocks when you add new columns. In one system, I found 17000 objects in the recycle bin, occupying 1.3Gb.

My Opinion

Personally, I would disable the recycle bin by setting the initialisation parameter RECYCLEBIN = OFF in all PeopleSoft environments, with the possible exception of the development environment.

The RECYCLEBIN parameter can also be set dynamically at session or system level. You could perhaps turn it on prior to upgrade/data migration procedures, and then when satisfied turn it off again and manually purge the recycle bin.

I think Oracle features should be used knowingly. It doesn't matter whether you decide to use a feature or not. It is important that in making that decision you have thought about how to deal with the implications, rather than be surprised later.

Advert of sorts: Canada, UKOUG, Chile, Peru, Poland...

For all events between now and the end of the year, my speaking schedule is published on asktom.oracle.com. This post hi-lights the "international" (for me, if you live there - it is local of course...) trips I'll be doing. You can see all of the US only trips on asktom as well - and there are quite a few (Atlanta, Baton Rouge, Reston VA, Baltimore, San Francisco, Saint Louis, Richmond VA, Birmingham AL - to name a few).

I'm frequently asked "how often am I on the road" - the question behind the question being "how do you get to keep up with the database and stay hands on". I travel exactly 50% of the time - one week on, one week off. I'm getting ready for a week "on" right now. I spend the other 50% working locally (mostly at home) with the database and customers - preparing new material, getting caught up on questions on asktom and the like. From time to time - since they will not move events like Oracle OpenWorld to accommodate my schedule - I travel two weeks and stay off the road for two weeks. One of those "two weeks - no travel" is coming up and I'm very much looking forward to that.

Anyway - to the schedule and links to what's happening...

I'll be in Canada quite a few times this year - three times this week (Quebec, Toronto and Montreal). I'll be back in Canada - a little further west and a lot further north - in November for the ICE conference in Edmonton. I get back to Toronto (for the local user group) again in November as well.

I'll be hitting Chile and Peru in South America in November - again looking forward to that as I've never been down there at all. The user group events are something I really enjoy (more than a seminar - those are very very tiring to deliver. Physically tiring) and getting out to a place I've never been is always interesting.

After that - I'm off to the UKOUG. I've presented there many times now and always look forward to the event in Birmingham. A good time to catch up with a lot of people and much like IOUG in the US - it is a highly technical conference. Every single session is delivered by people using the Oracle software, and mostly delivered by people that use the Oracle software every single day. A lot of hands on knowledge is transferred. Always looking forward to that conference.

My last trip out of the US in 2009 will be to Warsaw, Poland right after the UKOUG. I'll be delivering a two day seminar there. I won't have much time to look around unfortunately as I fly in late Tuesday night from the UK and leave early Friday morning for home. I'll have to have a nice dinner or two out though to get a feel for the city.

Into the first half of 2010 - I'll be hitting places such as Croatia, Tokyo, Moscow, Hungary, two cities in South Africa, the Baltics and Turkey... More details on those to follow...