nVision Performance Tuning: 6. Logging Selector Usage

This blog post is part of a series that discusses how to get optimal performance from PeopleSoft nVision reporting as used in General Ledger.

Static selectors are tracked by entries in the PSTREESELCTL table.  It is maintained after the tree is extracted to the selector table.  The version number on PSTREESELCTL is compared with the corresponding version number on the PSTREEDEFN to determine whether the extract of the tree to the selector table is still valid, or whether it needs to be reextracted because the tree has been updated.  Selectors that do not have an entry in PSTREESELCTL are therefore dynamic.
Static selectors are left in the PSTREESELECTnn table after the report completes so that they can be reused.  However, many customers periodically create new effective dated versions of many trees, and so static selectors on old effective dated versions of the tree will accumulate as there is nothing to purge them.
Dynamic selectors are extracted every time the report runs.  They should normally be deleted by nVision before the report finishes.  However, if a report crashes dynamic selectors can be left behind.  That creates a number of problems

• The size of the selector tables will tend to increase over time as old static and dynamic selectors are left behind.
• That in turn affects the statistics on these tables.  The range of values for SELECTOR_NUM will become wider faster than the number of rows in the table grows.  The minimum value will either be the oldest static selector number, or the oldest dynamic selector left after a crash.  The maximum value will be the last selector number inserted when statistics were collected.

Therefore, it is useful to be able to track creation and deletion of dynamic selectors by the various nVision reports and queries.  I have therefore created a logging table PS_NVS_TREESLCTLOG (see nvision_dynamic_selectors.sql), a PL/SQL package XX_NVISION_SELECTORS and compound DML triggers on every tree selector table.
The column names in the log table have been chosen for compatibility with the PeopleSoft data dictionary, so that a record can be defined in Application Designer.

Column Name	Data Type	Description
SELECTOR_NUM	NUMBER	Unique identifier for tree selector records.
PROCESS_INSTANCE	NUMBER	PeopleSoft process instance number for nVision/Query
LENGTH	NUMBER	Length of tree selector
NUM_ROWS	NUMBER	Number of rows inserted into tree selector.  Counted by the AFTER ROW part of the triggers.
TIMESTAMP	TIMESTAMP	Time when rows inserted
MODULE	VARCHAR2(64)	Module attribute of session inserting selector rows.
APPINFO_ACTION	VARCHAR2(64)	Action attribute of session inserting selector rows
CLIENTINFO	VARCHAR2(64)	CLIENT_INFO attribute of session inserting selector rows.  This will include: PeopleSoft Operator ID. Name of the application server or process scheduler domain. Name of the machine where the client process is executing. Name of the client executable process.
STATUS_FLAG	VARCHAR2(1)	I=Selectors Inserted S=Static Selectors Inserted D=Selectors Deleted X=Selectors Deleted and Partition Dropped
TREE_NAME	VARCHAR2(18)	Name of the tree from which selector extracted. Obtained by querying statement from V$SQL.
OWNER_ID	VARCHAR2(8)	Schema under which nVision report run
PARTITION_NAME	VARCHAR2(128)	Name of partition where selectors stored
JOB_NO	NUMBER	Database Job number to collect statistics on the partition.

All of the logic is kept in the PL/SQL package because it is common to the triggers on all the tree selector tables.  Insert triggers track population of selectors and delete triggers track the successful removal of dynamic selectors.  After row triggers track the selector number and count the number of rows inserted.  After statement triggers call the logging procedures.

CREATE OR REPLACE TRIGGER sysadm.pstreeselect10_insert
FOR INSERT ON sysadm.PSTREESELECT10 compound trigger
  l_err_msg VARCHAR2(100 CHAR);
AFTER EACH ROW IS
BEGIN
  sysadm.xx_nvision_selectors.rowins(:new.selector_num,:new.range_from_10,:new.range_to_10);
EXCEPTION WHEN OTHERS THEN NULL;
END after each row;

AFTER STATEMENT IS
BEGIN
  sysadm.xx_nvision_selectors.logins(10,'SYSADM');
EXCEPTION WHEN OTHERS THEN
  l_err_msg := SUBSTR(SQLERRM,1,100);
  dbms_output.put_line('Error:'||l_err_msg);
END after statement;
END;
/

CREATE OR REPLACE TRIGGER sysadm.pstreeselect10_delete
FOR DELETE ON sysadm.PSTREESELECT10 compound trigger
  l_err_msg VARCHAR2(100 CHAR);
AFTER EACH ROW IS
BEGIN
  sysadm.xx_nvision_selectors.rowdel(:old.selector_num);
EXCEPTION WHEN OTHERS THEN NULL;
END after each row;

AFTER STATEMENT IS
BEGIN
  sysadm.xx_nvision_selectors.logdel(10);
EXCEPTION WHEN OTHERS
THEN
  l_err_msg := SUBSTR(SQLERRM,1,100);
  dbms_output.put_line('Error:'||l_err_msg);
END after statement;
END;
/

Once the decision to bear the overhead of triggers on the selector tables is made, there are then various pieces of additional information that can be captured and stored in the logging table for very little additional overhead.  It is easy to record the current session attributes such as module, action, and client_info.  The process instance number is captured on startup and can then be read by the psftapi package.  The tree is identified by scanning the V$SQL for the SQL that fired the triggers.
It is also possible to maintain statistics on the selector tables.

Purging Selectors

The selector log can be used to drive purging of selectors not cleared by nVisions that failed to complete.  The package includes a purge procedure to clear selectors that are not already marked as having been deleted either when the logged process instance is no longer running, or if there is no known process instance then if it is more than 2 days since the selector was inserted.  If the selector table is also interval partitioned, then the partition will be dropped.
A trigger on PSTREESELCTL (see pstreeselctl.sql) tracks the logs static selector maintenance.
The purge process is invoked by a trigger on PSPRCSRQST that fires when nVision process status is changed away from processing.  The selector purge process should also be scheduled to run daily.
The selector log itself is not purged as it contains useful information about tree usage.

Recommendations

• If the tree selector tables are not partitioned create a histogram on SELECTOR_NUM.  However, up to Oracle 11g, this will not be effective as the number of distinct values reaches the maximum number of buckets, 254.  Dynamic selectors should be purged before this happens.  From Oracle 12c the maximum number of buckets is 2048, and hybrid histograms can be used.