One of the
ways to improve scalability and reduce the time taken for processing batch
programs is to run multiple instances of the same program on subsets of the
data in parallel. For example, instead of processing orders 1 to 1,000,000 in a
single instance, run five concurrent instances of the same batch program for
200,000 orders each. When running more than one instance of the program
concurrently, the most serious potential problem is data contention, which can
result in lock waits and deadlocks.
To reduce
contention, PeopleSoft Application Engine provides dedicated temporary tables.
These temporary tables are permanent with respect to the Application Engine
program definition. Only the data residing in these tables is temporary.
Temporary
tables also improve performance when a subset of data from a huge table is
referenced multiple times within the program. Temporary tables can:
• Store intermediate
data during the process.
• Minimize contention
when running in parallel.
• Ensure that
each process uses its own temporary table instance.
It is
important to allocate the correct number of dedicated temporary tables for your
environment. If the temporary tables are under-allocated, the total available
instances of a particular temporary table are less than the total number of
programs running at one time that use the temporary table. When this condition
occurs, the program either uses the base temporary table and continues to run,
or it aborts with an error, depending on whether the program property setting If
non-shared tables cannot be assigned is set to Continue or Abort,
respectively. These options may both be undesirable.
Following
are the drawbacks if the program uses the base (non-shared) temporary table:
• Multiple
instances may read or write into the same table, causing contention.
• Selecting
becomes dependent on ProcessInstance as a leading qualifier.
• DELETE is
performed instead of TRUNCATE TABLE. DELETE is far slower than TRUNCATE TABLE.
Important
reasons for PeopleSoft to choose regular tables as temporary tables instead of
using SQL Server’s temporary tables (#tables) include the following:
• A number
of Application Engine programs are restartable. This means that if a program
abends in the middle of the run, data stored in the temporary tables is
preserved. This enables the program to restart from the last commit point. This
is not possible if it uses #tables instead, because they will not be available
after the session terminates.
• In SQL
Server, this regular form of temporary table offers the same performance as the
#tables when they are allocated correctly.
Statistics
at Runtime for Temporary Tables
PeopleSoft
uses shared temporary tables or dedicated temporary tables in the batch
processes. These temporary tables will have few or no rows in the beginning of
the process and again few or no rows at the end of the process. Temporary
tables are populated during the process, and are deleted or truncated at the
beginning or end of the process. Because data in these tables changes so
radically, accurate statistics on these tables may help the SQL statements
significantly.
Beginning
with PeopleSoft 8, if the process is written in PeopleSoft Application Engine,
then %UpdateStats meta-SQL can be used in the program after the rows are
populated. This ensures that the statistics are updated before the selection
happens from that table.
Note. COMMIT is required prior to
executing this statement. Make sure to use the COMMIT statement immediately
following the previous step. If you do not do so, then this statement will be
skipped by Application Engine and will not be executed.
For
example, suppose you have the following meta-SQL command in an SQL step of an
Application Engine program:
%UpdateStats(INTFC_BI_HTMP)
This
meta-SQL issues the following command to the database at runtime:
UPDATE
STATISTICS PS_INTFC_BI_HTMP
Make sure
the temporary table statistics have been handled as shown above. If you find
that statistics created by AUTO UPDATESTATS is sufficient, you can disable
%UpdateStats in the program.
Disabling
Update Statistics
Update
Statistics (%UpdateStats) can be disabled in two ways.
• Program level:
Identify the steps that issue %UpdateStats and deactivate them. These steps can
be identified by the Application Engine trace (for example, set the trace flag
TraceAE=3). This is a program-specific setting.
•
Installation level: If there is a compelling reason to disable update
statistics for all batch programs, then the installation level setting can be
applied to disable %UpdateStats. The following parameter should be set in the
Process Scheduler configuration file psprcs.cfg to achieve this:
;-------------------------------------------------------------------------
; DbFlags
Bitfield
;
; Bit Flag
; --- ----
; 1 -
Ignore metaSQL to update database statistics (shared with COBOL)
DbFlags=1
Note. Carefully consider all the positive
and negative effects before setting this flag because it can adversely affect
performance.
