Global nVision performance options

Another back door into PeopleSoft: I've been working on nVision recently. In PeopleTools 8.x, the nVision performance options are stored on the tree definition table, PSTREEDEFN (in previous versions, they were stored in the layout). The performance options should be set as follows

• Use Literal Values: The SQL generated by nVision uses a list of litteral values instead of a joining to one or more tree selector tables. This simplifies the query by reducing the number of tables queried, although the query will be much longer because there will be many criteria on litteral values select from those same tree selector tables. The fewer tables in the from clauses, the fewer way the optimizer will have to execute the statement incorrectly!
• Use Static Selectors: nVision queries data from the tree selector tables rather than build an extract of part of the trees. Sometimes nVision doesn't clear out this temporary data, usually when it errors for some reason. Hence, debris builds up in the tree selector tables over time an this degrades the performance of the queries.
• Use Between on the Selection Criteria: This doesn't improve performance, but merely shortens the length of the SQL statement.

spool nvperfopts
SELECT  T.SETID, T.TREE_NAME, T.EFFDT
,       T.TREE_ACC_SELECTOR --static selctors
,       X1.XLATSHORTNAME
,       T.TREE_ACC_SEL_OPT --between
,       X2.XLATSHORTNAME
,       T.TREE_ACC_METHOD --literals
,       X3.XLATSHORTNAME
FROM    PSTREEDEFN T
,       PSXLATITEM X1
,       PSXLATITEM X2
,       PSXLATITEM X3
WHERE   (T.TREE_ACC_SELECTOR != 'S'
OR       T.TREE_ACC_SEL_OPT != 'B'
OR       T.TREE_ACC_METHOD != 'L')
AND     X1.FIELDNAME = 'TREE_ACC_SELECTOR'
AND     X1.FIELDVALUE = TREE_ACC_SELECTOR
AND     X2.FIELDNAME = 'TREE_ACC_SEL_OPT'
AND     X2.FIELDVALUE = TREE_ACC_SEL_OPT
AND     X3.FIELDNAME = 'TREE_ACC_METHOD'
AND     X3.FIELDVALUE = TREE_ACC_METHOD;

/*increment the version numbers*/
UPDATE PSLOCK
SET    VERSION = VERSION + 1
WHERE  OBJECTTYPENAME IN('SYS','TDM');

UPDATE PSVERSION
SET    VERSION = VERSION + 1
WHERE  OBJECTTYPENAME IN('SYS','TDM');

/*update nvision flags and version number on trees*/
UPDATE PSTREEDEFN
SET    TREE_ACC_SELECTOR = 'S' --static selctors
,      TREE_ACC_SEL_OPT = 'B' --between
,      TREE_ACC_METHOD = 'L' --literals
,      VERSION = (SELECT VERSION
                 FROM PSLOCK WHERE OBJECTTYPENAME = 'TDM')
WHERE  TREE_ACC_SELECTOR != 'S'
OR     TREE_ACC_SEL_OPT != 'B'
OR     TREE_ACC_METHOD != 'L';

SELECT T.SETID, T.TREE_NAME, T.EFFDT
,      T.TREE_ACC_SELECTOR --static selctors
,      X1.XLATSHORTNAME
,      T.TREE_ACC_SEL_OPT --between
,      X2.XLATSHORTNAME
,      T.TREE_ACC_METHOD --literals
,      X3.XLATSHORTNAME
FROM   PSTREEDEFN T
,      PSXLATITEM X1
,      PSXLATITEM X2
,      PSXLATITEM X3
WHERE  (T.TREE_ACC_SELECTOR = 'S'
OR      T.TREE_ACC_SEL_OPT = 'B'
OR      T.TREE_ACC_METHOD = 'L')
AND    X1.FIELDNAME = 'TREE_ACC_SELECTOR'
AND    X1.FIELDVALUE = TREE_ACC_SELECTOR
AND    X2.FIELDNAME = 'TREE_ACC_SEL_OPT'
AND    X2.FIELDVALUE = TREE_ACC_SEL_OPT
AND    X3.FIELDNAME = 'TREE_ACC_METHOD'
AND    X3.FIELDVALUE = TREE_ACC_METHOD;

SETID TREE_NAME          EFFDT               T XLATSHORTN T XLATSHORTN T XLATSHORTN
----- ------------------ ------------------- - ---------- - ---------- - ----------
      QUERY_TREE_WF      00:00:00 01/01/1990 S Static     B Between    L Literal
      QUERY_TREE_PT      00:00:00 01/01/1900 S Static     B Between    L Literal
      QUERY_TREE_OLAP    00:00:00 01/01/1900 S Static     B Between    L Literal
SPN01 SPANISH_ACCOUNT    00:00:00 01/01/1900 S Static     B Between    L Literal
SHR03 ALL_PURCHASE_ITEMS 00:00:00 01/01/1900 S Static     B Between    L Literal
SHR02 ALL_PURCHASE_ITEMS 00:00:00 01/01/1900 S Static     B Between    L Literal
SHARE VENDOR_TYPE1       00:00:00 01/01/1900 S Static     B Between    L Literal
SHARE USER_LIMITS        00:00:00 01/01/1900 S Static     B Between    L Literal
SHARE TR_INSTRUMENTS     00:00:00 01/01/1900 S Static     B Between    L Literal
SHARE TR_CASH_POSITION   00:00:00 01/01/1900 S Static     B Between    L Literal
SHARE STOCK_AVAILABILITY 00:00:00 01/01/1900 S Static     B Between    L Literal
SHARE SALES_PERSON       00:00:00 01/01/1900 S Static     B Between    L Literal

spool off

Global nVision performance options

Another back door into PeopleSoft: I've been working on nVision recently. In PeopleTools 8.x, the nVision performance options are stored on the tree definition table, PSTREEDEFN (in previous versions, they were stored in the layout). The performance options should be set as follows

• Use Literal Values: The SQL generated by nVision uses a list of litteral values instead of a joining to one or more tree selector tables. This simplifies the query by reducing the number of tables queried, although the query will be much longer because there will be many criteria on litteral values select from those same tree selector tables. The fewer tables in the from clauses, the fewer way the optimizer will have to execute the statement incorrectly!
• Use Static Selectors: nVision queries data from the tree selector tables rather than build an extract of part of the trees. Sometimes nVision doesn't clear out this temporary data, usually when it errors for some reason. Hence, debris builds up in the tree selector tables over time an this degrades the performance of the queries.
• Use Between on the Selection Criteria: This doesn't improve performance, but merely shortens the length of the SQL statement.

spool nvperfopts
SELECT  T.SETID, T.TREE_NAME, T.EFFDT
,       T.TREE_ACC_SELECTOR --static selctors
,       X1.XLATSHORTNAME
,       T.TREE_ACC_SEL_OPT --between
,       X2.XLATSHORTNAME
,       T.TREE_ACC_METHOD --literals
,       X3.XLATSHORTNAME
FROM    PSTREEDEFN T
,       PSXLATITEM X1
,       PSXLATITEM X2
,       PSXLATITEM X3
WHERE   (T.TREE_ACC_SELECTOR != 'S'
OR       T.TREE_ACC_SEL_OPT != 'B'
OR       T.TREE_ACC_METHOD != 'L')
AND     X1.FIELDNAME = 'TREE_ACC_SELECTOR'
AND     X1.FIELDVALUE = TREE_ACC_SELECTOR
AND     X2.FIELDNAME = 'TREE_ACC_SEL_OPT'
AND     X2.FIELDVALUE = TREE_ACC_SEL_OPT
AND     X3.FIELDNAME = 'TREE_ACC_METHOD'
AND     X3.FIELDVALUE = TREE_ACC_METHOD;

/*increment the version numbers*/
UPDATE PSLOCK
SET    VERSION = VERSION + 1
WHERE  OBJECTTYPENAME IN('SYS','TDM');

UPDATE PSVERSION
SET    VERSION = VERSION + 1
WHERE  OBJECTTYPENAME IN('SYS','TDM');

/*update nvision flags and version number on trees*/
UPDATE PSTREEDEFN
SET    TREE_ACC_SELECTOR = 'S' --static selctors
,      TREE_ACC_SEL_OPT = 'B' --between
,      TREE_ACC_METHOD = 'L' --literals
,      VERSION = (SELECT VERSION
                 FROM PSLOCK WHERE OBJECTTYPENAME = 'TDM')
WHERE  TREE_ACC_SELECTOR != 'S'
OR     TREE_ACC_SEL_OPT != 'B'
OR     TREE_ACC_METHOD != 'L';

SELECT T.SETID, T.TREE_NAME, T.EFFDT
,      T.TREE_ACC_SELECTOR --static selctors
,      X1.XLATSHORTNAME
,      T.TREE_ACC_SEL_OPT --between
,      X2.XLATSHORTNAME
,      T.TREE_ACC_METHOD --literals
,      X3.XLATSHORTNAME
FROM   PSTREEDEFN T
,      PSXLATITEM X1
,      PSXLATITEM X2
,      PSXLATITEM X3
WHERE  (T.TREE_ACC_SELECTOR = 'S'
OR      T.TREE_ACC_SEL_OPT = 'B'
OR      T.TREE_ACC_METHOD = 'L')
AND    X1.FIELDNAME = 'TREE_ACC_SELECTOR'
AND    X1.FIELDVALUE = TREE_ACC_SELECTOR
AND    X2.FIELDNAME = 'TREE_ACC_SEL_OPT'
AND    X2.FIELDVALUE = TREE_ACC_SEL_OPT
AND    X3.FIELDNAME = 'TREE_ACC_METHOD'
AND    X3.FIELDVALUE = TREE_ACC_METHOD;

SETID TREE_NAME          EFFDT               T XLATSHORTN T XLATSHORTN T XLATSHORTN
----- ------------------ ------------------- - ---------- - ---------- - ----------
      QUERY_TREE_WF      00:00:00 01/01/1990 S Static     B Between    L Literal
      QUERY_TREE_PT      00:00:00 01/01/1900 S Static     B Between    L Literal
      QUERY_TREE_OLAP    00:00:00 01/01/1900 S Static     B Between    L Literal
SPN01 SPANISH_ACCOUNT    00:00:00 01/01/1900 S Static     B Between    L Literal
SHR03 ALL_PURCHASE_ITEMS 00:00:00 01/01/1900 S Static     B Between    L Literal
SHR02 ALL_PURCHASE_ITEMS 00:00:00 01/01/1900 S Static     B Between    L Literal
SHARE VENDOR_TYPE1       00:00:00 01/01/1900 S Static     B Between    L Literal
SHARE USER_LIMITS        00:00:00 01/01/1900 S Static     B Between    L Literal
SHARE TR_INSTRUMENTS     00:00:00 01/01/1900 S Static     B Between    L Literal
SHARE TR_CASH_POSITION   00:00:00 01/01/1900 S Static     B Between    L Literal
SHARE STOCK_AVAILABILITY 00:00:00 01/01/1900 S Static     B Between    L Literal
SHARE SALES_PERSON       00:00:00 01/01/1900 S Static     B Between    L Literal

spool off

SQL Tips for Developers: Performance Improvements with the APPEND hint

In all PeopleSoft products, it is common to find batch programs populating large working storage or reporting tables. When using Oracle RDBMS, the append hint can be used to reduce the overhead and thus improve the performance of the insert operation. Thus

INSERT /*+ APPEND*/ INTO ... SELECT ...

It works by permitting the process to insert the data directly into new data blocks at the end of the object, rather than using space currently allocated to the object. So the new data is appended to the table. This is much faster because it permits Oracle to by-pass some of the processes that occur for each row inserted, such as index maintenance which only occurs when the insert is committed. Significantly, the hint also reduces the volume of redo written.

Every time a piece of data is changed in an Oracle database, the new data is written to the redo logs. This information can then be used to reapply changes to a database during media recovery, or it can be applied to a standby database as a part of a disaster recover solution (Oracle call this technique 'Physical DataGuard'). Every change, even to working storage tables and including indexes, generates redo information.

The reduction in redo can be very significant in systems using DataGuard across a WAN. Bandwidth constraints can cause a backlog of redo information waiting to be transmitted. In most systems the majority of redo is generated by overnight batch processes and it can take time during the day for that backlog to clear and for the DR system to catch up with production. Hence, reducing redo is important.

Therefore the APPEND hint should be used in insert statements when both of the following criteria are met:

1. The table has been created or truncated prior to the insert. It is not sufficient to delete all the rows because the empty blocks remain allocated to the table.
2. The table should be populated in a single monolithic insert statement. Multiple statements should be combined using the UNION ALL operator between the queries. Otherwise most of the benefit will be lost during multiple index maintenance phases.
   

It is easy to demonstrate this with some a simple test. I will create a simple table with a unique index, just as PeopleSoft would. Then I will truncate it and populate it, first without the APPEND hint, and then with it.

CREATE TABLE t
(a NUMBER
,b VARCHAR2(200));

CREATE UNIQUE INDEX t ON t(a);

TRUNCATE TABLE t;
INSERT INTO t
SELECT ROWNUM, RPAD('42',200,'.')
FROM dba_objects;

TRUNCATE TABLE t;
INSERT /*+ APPEND*/ INTO t
SELECT ROWNUM, RPAD('42',200,'.')
FROM dba_objects;

The session statistics show the savings in a number of areas.

Session Stats               No Hint Append Hint
------------------------ ---------- -----------
db block changes            202,926       3,192
db block gets               159,148       4,796
db block gets direct          1,413
db block gets from cache    159,148       3,383
DB time                       1,308         828
redo entries                104,842       1,853
redo size                32,391,936   2,838,148

Fewer blocks are changed because they are not written and then rewritten as each row is inserted. Table blocks are only written once during the insert and the index is only maintained when the insert is committed.

Other recommendations:

• When using the APPEND hint, the insert should be committed immediately. It must be committed before the table can be referenced, otherwise an error will be generated.


• The data in the table will have been completely replaced by this point, so it is almost certainly appropriate to refresh the optimiser statistics. In Applicaition Engine use the %UpdateStats MetaSQL.


• The APPEND hint cannot be used in conjuction with the %InsertSelect MetaSQL because the hint MUST be placed between the 'insert' and 'into' keywords. NB: The statement will not fail if you get the hint wrong.

PeopleTools Platform Flags on Indexes

I have noticed on databases that have been upgraded to PT8.45, that most of the indexes say they will build on some database platforms when they will actually build on either all of them, or none of them.

This is because PSINDEXDEFN still contains flags for platforms that are no longer supported by PeopleSoft/Oracle and can no longer be set individually in Application Designer.

• PLATFORM_SBS: SQL Base
• PLATFORM_ALB: Allbase
• PLATFORM_DB4: DB2/AS400

However, Application Designer still checks the values of these flags and if any flags have different values, then the SOME radio button is selected. However, all six platform flags have the same value.

This is a nuisance because PeopleSoft releases indexes for specific platforms, and some indexes are suppressed on certain platforms. It is not easy to see from the indexes view of Application Designer whether this index will be built. It would be easier if it said ALL or NONE when it means that.

I have fixed this by updating PeopleTools tables as follows. The first query reports on the indexes where the supported platform flags all have the same value and one of the unsupported platform flags are different.

column RECNAME format a15
column INDEXID format a5 heading 'IndexID'
column DDLCOUNT format 999 heading 'DDLCnt'
column CUSTKEYORDER format 9999 heading 'CustKeyOrder'
column KEYCOUNT format 999 heading 'KeyCnt'
column PLATFORM_SBS format 999 heading 'SBS'
column PLATFORM_DB2 format 999 heading 'DB2'
column PLATFORM_ORA format 999 heading 'ORA'
column PLATFORM_INF format 999 heading 'INF'
column PLATFORM_DBX format 999 heading 'DBx'
column PLATFORM_ALB format 999 heading 'ALB'
column PLATFORM_SYB format 999 heading 'SYB'
column PLATFORM_MSS format 999 heading 'MSS'
column PLATFORM_DB4 format 999 heading 'DB4'
column ACTIVEFLAG Format 999 heading 'Active'
column CLUSTERFLAG format 999 heading 'ClustFlag'
column UNIQUEFLAG format 999 heading 'UniqueFlag'
column INDEXTYPE format 999 heading 'IndexType'
column IDXCOMMENTS format a60

spool fixindexdefn
SELECT *
FROM PSINDEXDEFN
WHERE PLATFORM_DB2=PLATFORM_DBX
AND PLATFORM_DBX=PLATFORM_INF
AND PLATFORM_INF=PLATFORM_ORA
AND PLATFORM_ORA=PLATFORM_SYB
AND PLATFORM_SYB=PLATFORM_MSS
AND (PLATFORM_ORA!=PLATFORM_SBS
  OR PLATFORM_ORA!=PLATFORM_ALB
  OR PLATFORM_ORA!=PLATFORM_DB4)
;

                                                 Cust
                Index Index Unique Clust          Key  Key  DDL
RECNAME         ID     Type   Flag  Flag Active Order  Cnt  Cnt  SBS  DB2  ORA  INF  DBx  ALB  SYB  MSS  DB4
--------------- ----- ----- ------ ----- ------ ----- ---- ---- ---- ---- ---- ---- ---- ---- ---- -
IDXCOMMENTS
------------------------------------------------------------
PSGATEWAYDEL    A         4      0     0      1     1    1    0    1    1    1    1    1    1    1    1    0
for clearlisting

PSLOCALEOPTNDFN _         1      1     1      1     0    3    0    1    1    1    1    1    1    1    1    0


PSLOCK          _         1      1     1      1     0    1    0    1    1    1    1    1    1    1    1    0

The following SQL commands set the SQL flags for the unsupported platforms to the value for the supported platforms. The version number on the record definition is updated so that PeopleSoft reaches the object.

UPDATE PSVERSION
SET VERSION = VERSION + 1
WHERE OBJECTTYPENAME IN('SYS','RDM');

UPDATE PSLOCK
SET VERSION = VERSION + 1
WHERE OBJECTTYPENAME IN('SYS','RDM');

UPDATE PSRECDEFN
SET VERSION = (
  SELECT VERSION
  FROM PSVERSION
  WHERE OBJECTTYPENAME = 'RDM')
WHERE RECNAME IN (
  SELECT RECNAME
  FROM PSINDEXDEFN
  WHERE PLATFORM_DB2=PLATFORM_DBX
  AND PLATFORM_DBX=PLATFORM_INF
  AND PLATFORM_INF=PLATFORM_ORA
  AND PLATFORM_ORA=PLATFORM_SYB
  AND ( PLATFORM_ORA!=PLATFORM_SBS
    OR PLATFORM_ORA!=PLATFORM_ALB
    OR PLATFORM_ORA!=PLATFORM_DB4)
);

UPDATE psindexdefn
SET PLATFORM_DB4=PLATFORM_ORA
WHERE PLATFORM_DB2=PLATFORM_DBX
AND PLATFORM_DBX=PLATFORM_INF
AND PLATFORM_INF=PLATFORM_ORA
AND PLATFORM_ORA=PLATFORM_SYB
AND PLATFORM_SYB=PLATFORM_MSS
AND PLATFORM_ORA!=PLATFORM_DB4;

UPDATE psindexdefn
SET PLATFORM_ALB=PLATFORM_ORA
WHERE PLATFORM_DB2=PLATFORM_DBX
AND PLATFORM_DBX=PLATFORM_INF
AND PLATFORM_INF=PLATFORM_ORA
AND PLATFORM_ORA=PLATFORM_SYB
AND PLATFORM_SYB=PLATFORM_MSS
AND PLATFORM_ORA!=PLATFORM_ALB;

UPDATE psindexdefn
SET PLATFORM_SBS=PLATFORM_ORA
WHERE PLATFORM_DB2=PLATFORM_DBX
AND PLATFORM_DBX=PLATFORM_INF
AND PLATFORM_INF=PLATFORM_ORA
AND PLATFORM_ORA=PLATFORM_SYB
AND PLATFORM_SYB=PLATFORM_MSS
AND PLATFORM_ORA!=PLATFORM_SBS;

COMMIT;
spool off

The platform flags now say 'ALL'. Not a tremendous change, but at least I can immediately see that these indexes do build on all platforms without having to open each one.

Additional Batch Timings from Application Engine

I am doing some Application Engine development at the moment as a part of a project to replatform to Oracle. I go through an iterative process of changing SQL Server specific SQL (in a custom AE program) to run on Oracle, and then running the process until it fails again. However, not only must the processes run correctly on Oracle, but the new code needs to be efficient as possible in order to meet performance targets. So, I need to know how long each step in the AE program took to execute.

Application Engine batch timings are very useful metrics when tuning processes, but they are only written when the program ends, and they are only written to the timings tables in the database when a program completes successfully. I am repeatedly restarting the same process, gradually moving towards the end, so I will never get a complete timings report.

However, Application Engine programs writes a status string to table PSPRCSRQSTSTRNG which contains the step about to be executed. The value of this string can be seen in Process Monitor in the Process Detail page.

desc PSPRCSRQSTSTRNG
Name                 Null?    Type
-------------------- -------- ---------------
PRCSINSTANCE         NOT NULL NUMBER(38)
RQSTSTRINGTYPE       NOT NULL VARCHAR2(2)
PRCSRQSTSTRING       NOT NULL VARCHAR2(254)

I created a table to which a trigger logs what steps are executed. I have also used a sequence number to produce a reliable primary key on that table because values in the string will repeat when Application Engine is in a loop or when a process is restarted process in which case it keeps the same process instance number. The sequence number is generated by an Oracle Sequence to avoid locking problems caused by using a table to generate a sequence (as PeopleSoft generally does). I am not worried by gaps in the sequence.

CREATE TABLE dmk_prcsrqststrng_log
(prcsinstance   NUMBER NOT NULL
,sequence_nbr   NUMBER NOT NULL
,rqststringtype VARCHAR2(2) NOT NULL
,prcsrqststring VARCHAR2(254) NOT NULL
,datestamp      DATE NOT NULL
,datetimestamp  TIMESTAMP NOT NULL
,CONSTRAINT dmk_prcsrqststrng_log_pk
PRIMARY KEY (prcsinstance, sequence_nbr)
);

CREATE SEQUENCE dmk_prcsrqststrng_seq;

A trigger on PSPRCSRQSTSTRNG writes new values for PRCSRQSTSTRING to the log table. I have used an autonomous transaction in case the update rolls back after an error, and so that I can see the log record before Application Engine commits. I am also storing two timestamps when the audit record is written. The Oracle date type is only accurate to the second, but I have also used the newer timestamp datatype. On Oracle 9.2 (on Windows) I get millisecond accuracy, but on Oracle 10.2 I get microsecond accuracy.

CREATE OR REPLACE TRIGGER dmk_prcsrqststrng_log
AFTER INSERT OR UPDATE OR DELETE
ON sysadm.psprcsrqststrng
FOR EACH ROW
DECLARE
PRAGMA autonomous_transaction;
BEGIN
IF INSERTING OR UPDATING THEN
INSERT INTO dmk_prcsrqststrng_log
  (prcsinstance
  ,sequence_nbr
  ,rqststringtype
  ,prcsrqststring
  ,datestamp
  ,datetimestamp)
VALUES
  (:new.prcsinstance
  ,dmk_prcsrqststrng_seq.nextval
  ,:new.rqststringtype
  ,:new.prcsrqststring
  ,SYSDATE
  ,SYSTIMESTAMP);
ELSIF DELETING THEN
INSERT INTO dmk_prcsrqststrng_log
  (prcsinstance
  ,sequence_nbr
  ,rqststringtype
  ,prcsrqststring
  ,datestamp
  ,datetimestamp)
VALUES
  (:old.prcsinstance
  ,dmk_prcsrqststrng_seq.nextval
  ,:old.rqststringtype
  ,''
  ,SYSDATE
  ,SYSTIMESTAMP);
END IF;
COMMIT;
EXCEPTION
WHEN OTHERS THEN NULL;
END;
/
show errors

Unfortunately, the status record is not always updated or deleted when the Application Engine program ends. Therefore, another trigger is required to delete the process record when the application updates its status to no longer processing.

CREATE OR REPLACE TRIGGER dmk_prcsrqststrng_del
AFTER UPDATE OF runstatus ON sysadm.psprcsrqst
FOR EACH ROW
WHEN (new.runstatus != 7 AND old.runstatus = 7
AND   new.prcstype IN ('Application Engine'))
DECLARE
PRAGMA autonomous_transaction;
BEGIN
DELETE FROM psprcsrqststrng WHERE prcsinstance = :new.prcsinstance;
COMMIT;
EXCEPTION WHEN OTHERS THEN NULL;
END;
/
show errors

This table can be used is various ways.

i) A simple report can be written to show the last 10 steps for each process instance, and their duration. I can run this while the program is executing to see how far it has got, and how long the current step has been executing.

COLUMN ranking FORMAT 990 HEADING 'Rank'
COLUMN sequence_nbr FORMAT 990 HEADING 'Seq'
COLUMN prcsrqststring FORMAT a25
COLUMN ratio          FORMAT 90.0           HEADING '%'
COLUMN sum_duration   FORMAT 999,990.000000 HEADING 'Total|Duration'
COLUMN duration       FORMAT  99,990.000000
COLUMN avg_duration   FORMAT  99,990.000000 HEADING 'Average|Duration'
COLUMN max_duration   FORMAT  99,990.000000 HEADING 'Maximum|Duration'
COLUMN datetimestamp  FORMAT a25
COLUMN executions     FORMAT 990            HEADING 'Execs'
BREAK ON REPORT ON PRCSINSTANCE SKIP 1

SELECT y.prcsinstance
,      y.sequence_nbr
,      y.prcsrqststring
,      y.datetimestamp
,      (TO_DATE(TO_CHAR(y.nextdatetimestamp,'YYYYMMDDHH24MISS'),'YYYYMMDDHH24MISS')
   -TO_DATE(TO_CHAR(y.datetimestamp,'YYYYMMDDHH24MISS'),'YYYYMMDDHH24MISS'))*86400
   +TO_NUMBER(TO_CHAR(y.nextdatetimestamp,'xFF'))
   -TO_NUMBER(TO_CHAR(y.datetimestamp,'xFF')) duration
FROM   (
   SELECT x.prcsinstance
   ,      x.sequence_nbr
   ,      x.prcsrqststring
   ,      x.datetimestamp
   ,      CASE WHEN x.prcsrqststring = '' THEN x.nextdatetimestamp
               ELSE NVL(x.nextdatetimestamp,SYSTIMESTAMP)
          END as nextdatetimestamp
   , x.ranking
   FROM   (
          SELECT x.prcsinstance
          ,      x.sequence_nbr
          ,      x.prcsrqststring
          ,      x.datetimestamp
          ,      LEAD(x.datetimestamp,1) OVER (PARTITION BY x.prcsinstance ORDER BY x.sequence_nbr) AS nextdatetimestamp
          ,      RANK() OVER (ORDER BY x.datetimestamp desc) AS ranking
          FROM dmk_prcsrqststrng_log x
          ) x
   ) y
WHERE  y.ranking <= 10  ORDER BY prcsinstance, sequence_nbr;  PRCSINSTANCE  Seq PRCSRQSTSTRING                 DATETIMESTAMP             DURATION  ------------ ---- ------------------------------ ------------------------- --------------        242797  688 Main                           15-JUN-06 09.56.49.000721      15.227588                689 XXXIF_PAY_FI.WRITEOUT.Step01   15-JUN-06 09.57.04.228309      15.233425                690 XXX_GEN_MD5.MAIN.Step01        15-JUN-06 09.57.19.461734      45.355060                691 XXXIF_PAY_FI.WRITEOUT.Step03   15-JUN-06 09.58.04.816794   2,738.654582         242814  681 XXXIF_PAYA.Z_DelOld.?          14-JUN-06 18.00.14.747175       8.575205               682                     14-JUN-06 18.00.23.322380

  242815  683 Main                           14-JUN-06 18.01.15.606713      19.816770
          684 XXXIF_PAYH.Insert.Ins01        14-JUN-06 18.01.35.423483      15.516303
          685 XXXIF_PAYH.Insert.Ins04        14-JUN-06 18.01.50.939786      15.160971
          686 XXXIF_PAYH.Process.Hire        14-JUN-06 18.02.06.100757     163.917761
          687                     14-JUN-06 18.04.50.018518

ii) I can also aggregate the time for all the steps to determine what steps account for the longest execution time.

SELECT *
FROM   ( /*rank lines in report, also calculate ration to report*/
SELECT RANK() OVER (ORDER BY sum_duration DESC) AS ranking
,      d.*
,      100*ratio_to_report(sum_duration) OVER () AS ratio
FROM   ( /*calculate sum and durations*/
SELECT c.prcsrqststring
,      SUM(c.duration) sum_duration
,      COUNT(*) executions
,      AVG(c.duration) avg_duration
,      MAX(c.duration) max_duration
FROM   (
  SELECT b.prcsrqststring
  ,      (TO_DATE(TO_CHAR(b.nextdatetimestamp
                 ,'YYYYMMDDHH24MISS'),'YYYYMMDDHH24MISS')
         -TO_DATE(TO_CHAR(b.datetimestamp
                 ,'YYYYMMDDHH24MISS'),'YYYYMMDDHH24MISS'))*86400
         +TO_NUMBER(TO_CHAR(b.nextdatetimestamp,'xFF'))
         -TO_NUMBER(TO_CHAR(b.datetimestamp,'xFF')) duration
  FROM   (
    SELECT a.prcsinstance
    ,      a.sequence_nbr
    ,      a.prcsrqststring
    ,      a.datetimestamp
    ,      LEAD(a.datetimestamp,1)
           OVER (PARTITION BY a.prcsinstance
                 ORDER BY a.sequence_nbr) AS nextdatetimestamp
    ,      RANK() OVER (ORDER BY a.datetimestamp desc) AS ranking
    FROM dmk_prcsrqststrng_log a
    ) b
  WHERE  b.nextdatetimestamp IS NOT NULL
  AND    prcsrqststring != ''
  ) c
GROUP BY c.prcsrqststring
HAVING SUM(c.duration) > 0
) d
) e
WHERE ranking <= 10 ;

                                    Total                 Average        Maximum
Rank PRCSRQSTSTRING                 Duration        Execs Duration       Duration       %
---- ------------------------------ --------------- ----- -------------- -------------- -----
  1 XXXIF_PAYC.ADDR_LD.060           11,509.840101    15     767.322673     980.808154  29.0
  2 XXXIF_PAYC.ADDR_LD.050            7,476.656270    15     498.443751     679.516699  18.8
  3 XXXIF_PAYC.ADDR_LD.020            3,669.344222    14     262.096016     669.283625   9.2
  4 XXXIF_PAYC.ASGN_LD.076            3,076.882638    13     236.683280   2,729.356869   7.7
  5 XXXIF_PAYC.ASGN_LD.010            2,721.361198    15     181.424080     332.187057   6.8
  6 XXXIF_PAYC.PURGE.020              2,644.848544     1   2,644.848544   2,644.848544   6.7
  7 XXXIF_PAYC.ASGN_LD.020              624.892364    15      41.659491     155.971344   1.6
  8 XXXIF_PAYC.ADDR_LD.010              623.354434    14      44.525317     136.919156   1.6
  9 XXXIF_PAYC.ASGN_LD.030              583.222200    10      58.322220     425.172493   1.5
 10 XXXIF_PAYC.ASGN_LD.060              543.455165    14      38.818226     125.473966   1.4

This method, although useful, is not foolproof. The status string is only updated if AE Restart is enabled, and then not for for every step followed by a COMMIT. I haven't yet been able to work out all the criteria for this.