PeopleSoft uses
shared temporary tables, dedicated temporary tables, or both in batch
processes. These temporary tables will have few or no rows at the beginning of
the process; at the end of the process, these tables will again have few or no
rows. Keeping the statistics updated for these tables is somewhat challenging.
The life cycle of a temporary table is as follows:
·
All rows are automatically truncated
(if dedicated) or deleted (if shared) at the beginning of an AE execution.
·
Rows are populated.
·
All rows are truncated (if dedicated)
or deleted (if shared) at the end of an AE execution if the programmer has
requested. This action is not performed automatically.
Beginning with
PeopleSoft 8, an AE program can use the meta-SQL “%UpdateStats” after rows are populated to a temporary table. Doing so
would update statistics in the temporary table before the table is used in the
SQL statements that follow.
Note: Commit is required before executing the
%UpdateStats statement. Because of the implicit commit feature that Oracle uses
when performing DDL (statistics gathering is considered DDL), the AE will
ignore the %UpdateStats command after any uncommitted changes.
Allowing the implicit commit may affect the restart capability of the program.
Example
Command in SQL
Step of an Application Engine program:
%UpdateStats(%Table(PS_PERSON_TMP))
This meta-SQL
starting with Peopletools 8.48 will issue the Oracle database command at
runtime:
DBMS_STATS.GATHER_TABLE_STATS
(ownname=> [DBNAME], tabname=>[TBNAME], estimate_percent=>1, method_opt=>
'FOR ALL COLUMNS SIZE 1',cascade=>TRUE);
Turning Off %UpdateStats
Note: This change affects the entire process
scheduler; therefore, you should not turn this feature off.
Having the
update statistics at runtime incurs some overhead. If this feature is to be
turned off, Please check DBFLAG parameter in psprcs.cfg file at
process Scheduler
Here are
the details on the DBFLAGS setting:
DbFlags
DbFlags
The following values are valid for the DbFlags parameter:
Value Description
0 Enable the %UpdateStats meta-SQL construct.
1 Disable the %UpdateStats meta-SQL construct.
2 Ignore the Truncate command for DB2 LUW. Use Delete
instead.
Note: By using this option, when %Truncatetable is encountered in the app engine program, it will resolve to a DELETE, rather than an IMPORT FROM /dev/null. This may have a performance impact since the DELETE is normally slower than the IMPORT FROM /dev/null.
4 Disable a secondary database connection (used with the GetNextNumberWithGapsCommit PeopleCode function). This prevents the creation of a secondary database connection, bundling all SQL into a single unit of work. Without the additional database connection, the database row lock is held for a longer time, reducing concurrency in a multiple-user environment.
Note: Analytic instance
processing requires a secondary database connection, so if you're using
analytic servers, ensure that this value is not set.
8 Disable a persistent second database connection (used with the GetNextNumberWithGapsCommit PeopleCode function). This creates a second database connection in each GetNextNumberWithGapsCommit call, then immediately closes the second connection. This keeps the number of database connections to a minimum, but requires each call to create a new database connection on demand.
8 Disable a persistent second database connection (used with the GetNextNumberWithGapsCommit PeopleCode function). This creates a second database connection in each GetNextNumberWithGapsCommit call, then immediately closes the second connection. This keeps the number of database connections to a minimum, but requires each call to create a new database connection on demand.
Note: The performance impact
of making a new database connection is significant, especially in high volume
user production environments. Don't use this setting without carefully
considering its effect.
DbFlags uses a bit mask so that you can specify one or more of these values. You set this parameter to the total of the values that you want to apply. For example, to disable %UpdateStats and ignore the Truncate command, set DbFlags to 3 (setting bits one and two). The default is value is 1.
DbFlags uses a bit mask so that you can specify one or more of these values. You set this parameter to the total of the values that you want to apply. For example, to disable %UpdateStats and ignore the Truncate command, set DbFlags to 3 (setting bits one and two). The default is value is 1.
