Search

Top 60 Oracle Blogs

Recent comments

September 2009

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'

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'

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;

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;

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.

Forget I/O Bound, You’re Latency Bound, Bub

Since it’s been nearly ten years since I wrote my book, Scaling Oracle8i, I thought it was about time that I started writing again. I thought I would start with the new-fangled blogging thing, and see where it takes me. Here goes.

As some will know, I run a small consulting company called Scale Abilities, based out of the UK. We get involved in all sorts of fun projects and problems (or are they the same thing?), but one area that I seem to find myself focusing on a lot is storage. Specifically, the performance and architecture of storage in Oracle database environments. In fact I’m doing this so much that, whenever I am writing presentations for conferences these days, it always seems to be the dominant subject at the front of my mind.

One particular common thread has been the effect of latency. This isn’t just a storage issue, of course, as I endeavoured to point out in my Hotsos Symposium 2008 presentation “Latency and Skew”. Latency, as the subtitle of that particular talk said, is a silent killer. Silent, in that it often goes undetected, and the effects of it can kill performance (and still remain undetected). I’m not going to go into all the analogies about latency here, but let’s try and put a simple definition out for it:

Latency is the time taken between a request and a response.

If that’s such a simple definition, why is it so difficult to spot? Surely if a log period of time passes between a request and a response, the latency will be simple to nail? No.

mock-ups, prototypes and production builds

(I can't talk about planes going off on tangents but I've moved them all to the end this time. Read them in whatever order works for your brain - or skip 'em. They're not crucial to the post.)While I didn't get to work on the SR-71 or the F-117, I had the opportunity to work with men who did. One particular man, Walt Paskowietz, became something of a mentor/champion for me and taught me about

Brain Rules, multitasking and cubicles


One of my favorite books is Brain Rules by John Medina. The following passage is from the book's introduction:

Most of us have no idea how our brain works.