The PeopleSoft DBA Blog

Controlling How PeopleSoft Cobol Collects Statistics

2009-11-25T08:23:00.002Z

In previous postings, I have proposed locking statistics on temporary working storage tables and changing the DDL model for %UpdateStats to call my own PL/SQL Package. That works for Application Engine programs, but PeopleSoft COBOL can also update object statistics, and they use a different mechanism.

In the case of the Global Payroll calculation engine, GPPDPRUN, the run control component has a secondary page with a check box to enable statistics collection during the process.

However, to get streamed processing in Payroll (where the population of employees is broken into ranges of employee IDs that are each processed by a different concurrent process) to work effectively I change the working storage tables to be Global Temporary Tables, and then because the different physical instances would still share statistics (http://blog.go-faster.co.uk/2009/10/global-temporary-tables-shared.html) I delete and lock the statistics on these tables.

If the payroll calculation is run with the Update Statistics option it generates the following error.

Application Program Failed
 Action Type     : SQL UPDATE
 In Pgm Section  : SQLRT: EXECUTE-STMT                                 
 With Return Code: 38029 
 Error Message   : ORA-38029: object statistics are locked
 Stored Stmt     : GPPSERVC_U_STATS  
 SQL Statement   : ANALYZE TABLE PS_GP_PYE_STAT_WRK ESTIMATE STATISTICS

COBOL issued an ANALYZE command, and did not use the DDL model defined in the table PSDDLMODEL. However, the command came from a stored statement, in this case GPPSERVC_U_STATS. The stored statement is defined as follows in the gppservc.dms.

STORE GPPSERVC_U_STATS
%UPDATESTATS(PS_GP_PYE_STAT_WRK)
;

So, the expansion of %UPDATESTATS in the stored statement to the ANALYZE command is hard coded somewhere in the delivered executable code. I would not suggest attempting to change that.

However, it is perfectly possible to change the stored statement to call the wrapper package (www.go-faster.co.uk/scripts/wrapper848meta.sql).

STORE GPPSERVC_U_STATS
BEGIN wrapper.ps_stats(p_ownname=>user, p_tabname=>'PS_GP_PYE_STAT_WRK'); END;;

In all there are 5 statements in HR 9.0 that call %UPDATESTATS that all relate to GPPDPRUN. I built a data mover script to replace them with calls to my replacement package with the following SQL.

set head off feedback off long 5000
spool updatestats_after.dms
select 'STORE '||pgm_name||'_'||stmt_type||'_'||stmt_name
||CHR(10)
||'BEGIN wrapper.ps_stats(p_ownname=>user,p_tabname=>'''
||substr(stmt_text
 , INSTR(stmt_text,'(')+1
 , INSTR(stmt_text,')')-INSTR(stmt_text,'(')-1
 )
||'''); END;;'
from ps_sqlstmt_tbl
where stmt_text like '%UPDATESTATS(%'
/
spool off

I have only tested this against HR9.0. Note that long columns such as PS_SQLSTMT_TBL.STMT_TEXT only become CLOBs when the application version reaches 9.0, you can use the LIKE operation on the Long column.

Database Links and PeopleSoft

2009-10-28T08:19:00.000Z

I have seen Oracle database links used in conjuction with PeopleSoft at a number of customer sites over the years. I think some scenarios are examples where it is reasonable to use a database link, some are not.
In Oracle RDBMS, a database link (see also the Oracle Concepts Manual) specifies how a database server can create a session on another database in order to perform a remote operation. The remote database may be another Oracle database, or another type of database. The remote operation may be a query, or it may modify remote data (DML operation), in which case a two-phase commit mechanism ensures transaction integrity across the databases.

PeopleSoft does not use database links between databases because they are a database platform specific technology. Instead, the PeopleSoft methodology is to replicate data between databases with an application message. This works well with small data sets, and with larger data sets that change slowly. However, there are scenarios with very large data volumes where Application Messaging will struggle to keep up, and Oracle RDMBS specific replication technologies are an appropriate alternative.

Data can be replicated between databases with Materialised Views (formerly known as Snapshots). This is a SQL based technology. Materialised Views Logs on the source database track changes in the source table. An incremental or full refresh process is run on the target, usually as a scheduled job.

Oracle introduced an alternative replication technology in Oracle 9i called ‘Streams’. This uses supplementary redo logging on the source database which can then be applied to the target. Additional database server processes transmit, receive and apply this redo stream. This technology is aimed a large volume replication for data warehouses. I know of one site where several hundred database tables are replicated from PeopleSoft HR to a data warehouse.

I think the clearest way is to explain the good and bad use of links is by example.

At two different sites with HR and Finance, I have seen similar examples of both good and bad use of database links (one was on PeopleTools 7.0, before Application Messaging was available).

GL data needs to be sent from Payroll in the HR database to GL in the Financials database. The PeopleSoft delivered mechanism was to use generate and reload an interface file. This customer changed replaced the interface table in HR with a view that referenced a table with the same name and structure on the Financial system via a link. The payroll GL extract process now inserted directly into the table on Financials. I think this is a perfectly reasonable use of a database link. It was simple. It performed better than the interface file, and would certainly have been faster than application messaging.

However, the same customer also used the employee expenses module in Financials. This required employee personal data. So they made the PS_PERSONAL_DATA table in Financials a view of the corresponding table in HR. The result was that every time somebody opened the expenses component in Financials, the application server process referenced the database link to HR. There is a limit on the maximum number of database links that a session can concurrently open (OPEN_LINKS) and that the whole database instance can concurrently open (OPEN_LINKS_PER_INSTANCE). They both default is 4, which gives an indication of how Oracle expect you to use this feature. This system ran out of database links. No Oracle errors were generated, instead sessions wait to be allowed to make the connection to the remote database. There are specific database link wait events that report the amount of time lost. Eventually you reach the point where Tuxedo services timeout and then users receive errors. I think this is an example of the wrong way to use a database link. Instead I think that the data should have been replicated from HR to Financials. In a modern PeopleSoft system this should be done with an application message (on the 7.0 system a Materialised View could have been used).

Not only were database links used to provide data to components, but the same views, that referenced remote objects, were used in queries and reports resulting in complex distributed queries. When multiple remote objects are referenced in a single SQL, I have seen Oracle decide to copy the whole of one or more of these objects to the local temporary tablespace. This does not result in good performance. Remember also, that even query only operations via a database link create a transaction on the remains until a commit or rollback is issued.

I worked on another site where Materialised Views were used to incrementally replicate data from CRM and HR to EPM databases. Processes in the EPM database then referenced the Materialised Views. In this system, there was never any question of EPM processes referencing objects via the links. The data volumes were such that Application Messaging would never have coped. In this case only the Materialised View refresh process references the database links, and so the DBA can manage the usage of links. This is a system where (after upgrade to Oracle 10g) Streams could also have been used to replicate the data.

Conclusion

My view is that database links should not be used to directly support functionality in either on-line components or reporting. When this is done in reports and queries it presents the problem of tuning distributed queries, and having to decide whether local or remote database should drive the query. Instead data should be replicated to the local database, preferably by PeopleSoft messaging, but if necessary by Oracle replication. However, I think that it can be reasonable to use a database link in an occasional batch process that moves data between systems.

Aggregating & Purging Batch Timings

2009-10-11T17:55:00.000+01:00

Application Engine can collect timing information for the programs being execution. These 'batch timings' can be written to log file and/or tables in the database. I always recommend that this is enabled in all environments. The runtime overhead is very low, and this data is extremely valuable to determine the performance of a system over a period of time, and to identify the pieces of SQL or PeopleCode code that account for the most time. The timing data collected for individual processes can be viewed directly within the Process Monitor component.

The Process Scheduler purge process does not delete batch timings, so this data remains in the database indefinitely, although it can no longer be accessed via the Process Monitor. Over time, on a busy system, a large volume of data can accumulate. In some ways this is a good thing. There are good reasons to purge the Process Scheduler as aggressively as the business will permit. The batch timings can still be analysed by direct SQL query. However, the sheer volume of data is likely to result in queries that can take quite a while to execute. After a while, you are less likely to be interested in the performance of individual processes, but are more likely to want to aggregate the data. So, it makes sense to hold the data at least partly aggregated.

I have produced a very simple Application Engine program (GFC_TIM_ARCH) to address this problem. This program is available for download from the Go-Faster website as a PeopleTools Application Designer Project. Please note that this process has been written using Oracle RDBMS specific SQL syntax.

Application Engine stores batch timing data in three table PS_BAT_TIMINGS_LOG, PS_BAT_TIMINGS_FN and PS_BAT_TIMINGS_DTL.
I have created three new tables PS_GFC_TIMINGS_LOG, PS_GFC_TIMINGS_FN and PS_GFC_TIMINGS_DTL.
GFC_TIM_ARCH aggregates the data in each of these tables by the day on which the process begin, by the process name, and (where applicable) by the 'detail_id' column. The aggregated data is put into the GFC_TIMINGS% tables, the original data is removed from the BAT_TIMINGS% tables.
It finds the earliest three days for which timing data exists that is older than the longest Process Scheduler retention limit. The idea is that the process should be run daily (the delivered 'Daily Purge' recurrence is suitable), but if it doesn't run for some reason it will catch up the next day.
Only one instance of WMS_TIM_ARCH is permitted to run concurrently.

On one system, where I tested this process, BAT_TIMINGS_DTL was growing by over 1 million rows per day. This became around 5000 rows per day in GFC_TIMINGS_DTL.

If you have been running with batch timings for a while, then when you first introduce this process you will probably have a large backlog of data to be aggregated and purged. The easiest option is to run this process repeatedly until the data has been processed (possibly using the recurrence that causes a program to run every minute). After the backlog has been cleared the BAT_TIMINGS% tables should be rebuilt or shrunk in order to release the space left in the tables by the deleted rows. This will help queries that scan the BAT_TIMINGS_DTL record, otherwise these scans still need to include the empty rows because they are below the tables High Water Mark.

Once the backlog has been cleared, the GFC_TIM_ARCH process can run daily and, not withstanding variations in the load on the system, the rolling volume of data retained in the BAT_TIMINGS% tables should be fairly constant, and it should not be necessary to rebuild them frequently. Space freed by the daily delete should be used by new rows that are inserted into the table as AE processes run.

Identifying Application Engine Source Code

2009-09-10T19:40:00.015+01:00

One of the recurring challenges of performance tuning PeopleSoft systems is that having identified a problem SQL statement with the aid of a database utility, such as Oracle Enterprise Manager or session trace, you often need to identify the source so that you can implement a change.

Stored Outlines and SQL Profiles do not work well with PeopleSoft. Bind variables in Application Engines often become literals before the SQL statement is submitted to the database, unless the ReUse Statement feature has been enabled, which it is not by default.

I wrote about this in PeopleSoft for the Oracle DBA, it contains a script called stmtid.sql (listing 11-23, page 280) that adds an identifying comment to each of the stored statements used by PeopleSoft COBOL programs.

Now, I have produced a similar script for Application Engine programs. The script aeid.sql adds identification comments containing the name of object. It directly modifies the PeopleTools tables for Application Engine steps and PeopleSoft SQL objects. These comments will be seen in database tools and traces. So now when your database tools find a problem statement, it is easy to find the source.

Below, is part of a screen shot from Oracle Enterprise Manager. You can see the identifying comment in the SQL, which was added by this script

The comment identifies the name of the Application Engine program, section, step and step type. In this example, the SQL is from an Application Engine ID GPGB_EDI_PRC, in section called EDI_PRC, in a SQL type step called Step01.

For example, if you need adjust this SQL statement, perhaps add a hint to it, you don't have to spend time working out where it came from. It is not uncommon to find many similar SQL statements in a program. Also, where dynamic code is used to generate the SQL statement, it can be very tricky to find the exact source.

Using aeid.sql

You will need to edit the script each time, to specify which Application Engine programs and SQL objects are to be commented. The script is not perfect. It does not handle some very long statements, so you cannot simply instrument the entire system in one go.
The script is designed to run in SQL*Plus.
It produces a report of statements that it has altered.
The script does not commit. You should either commit or rollback the update depending upon whether you are satisfied with the output.
I suggest that the script only be run in pre-production environments. The comments will of course be migrated along with any other changes.

Automatic Segment Space -v- Freelist Management for PeopleSoft Temporary Records

2009-07-28T06:39:00.008+01:00

Earlier this year, I wrote about some research into the effects of concurrent TRUNCATE TABLE operations in concurrent PeopleSoft Application Engine process. Since then I have been prompted to look at the effect of Automatic Segment Space -v- Freelist (or Manual) Management.

ASSM was introduced in Oracle 9i for Locally Managed Tablespaces, and from Oracle 10g it is default option, It was designed for high concurrency systems, and avoids some of the problems, such as contention on header blocks, that can occur with Freelist Management. It uses tracking blocks to indicate utilisation of data blocks. The ASSM structure is similar to a B-tree index (see Tanel Poder's presentation: Freelists -v- ASSM in Oracle 9i).

Normally, using ASSM is preferable in multi-user OLTP systems. The reduction in contention on the freelists should outweigh the additional I/O for the tracking blocks.

However, non-shared instances of PeopleSoft Temporary Records are only used by a single Application Engine process at any one time. Therefore, there would be no contention on freelists for these objects, and so DML operations on these objects will not derive much benefit from ASSM. In fact, the contrary could easily be true. The additional tracking blocks are also maintained during DML and are cleared out by local writes during a truncate operation. Truncate operations are serialised in Oracle on the RO enqueue. If you run multiple concurrent Application Engine programs you can get contention between the truncate operations. The additional I/O on the ASSM tracking blocks causes the truncate to take longer and in extreme cases can aggravate truncate contention.

The local write operations that occur during truncate operations cannot be deferred. Significant truncate contention can be a symptom of disk contention. However, switching back to Freelist Management saves the I/O to the tracking blocks, and so improves the performance of truncate.

I ran a test where I truncated a table with 5000 rows. I examined an Oracle trace with waits of the truncate. I tested it in an ASSM tablespace, and Freelist Managed tablespace. I tested truncating just the table on its own, and sometimes with an index.

Number of Wait EventsTable only, without any indexes
With a single primary key index
Tablespace Typedb file sequential readenq: RO - fast object reuselocal writesdb file sequential readenq: RO - fast object reuselocal writes
Automatic Segment Space Management8
1
4
16
2
9
Freelist Management3
1
1
6
2
3

Number of Wait Events	Table only, without any indexes	With a single primary key index
Tablespace Type	db file sequential read	enq: RO - fast object reuse	local writes	db file sequential read	enq: RO - fast object reuse	local writes
Automatic Segment Space Management	8	1	4	16	2	9
Freelist Management	3	1	1	6	2	3

You can see from this table that the truncates in the ASSM tablespace required more single block reads (db_file_sequential_read) and more local writes.

Recommendation

In addition to my previous recommendation to move the all (non-shared instances) of temporary working storage tables, and their indexes, to tablespaces with a larger block size, I also recommend that those tablespaces should be specified with MANUAL segment space management.

PeopleSoft and the Oracle Recycle Bin

2009-07-23T19:41:00.006+01:00

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;
>select * from user_recyclebin

OBJECT_NAME                    ORIGINAL_NAME                    OPERATION TYPE
------------------------------ -------------------------------- --------- -------------------------
TS_NAME                        CREATETIME          DROPTIME               DROPSCN
------------------------------ ------------------- ------------------- ----------
PARTITION_NAME                   CAN CAN    RELATED BASE_OBJECT PURGE_OBJECT      SPACE
-------------------------------- --- --- ---------- ----------- ------------ ----------
BIN$ZCLja8iAA9LgRAAOf+3h5A==$0 T                                DROP      TABLE
PSDEFAULT                      2009-03-02:13:35:12 2009-03-02:13:35:14 9.7561E+12
                          YES YES     776642      776642       776642          4

If you don't want a table to go into the recycle bin when you drop it, you can code DROP TABLE ... PURGE - but PeopleSoft doesn't do this.

PeopleSoft alter scripts usually drop and recreate tables. In a production system this is often the best option, otherwise you run the risk of causing rows to migrate to other blocks when you add new columns. In one system, I found 17000 objects in the recycle bin, occupying 1.3Gb.

My Opinion

Personally, I would disable the recycle bin by setting the initialisation parameter RECYCLEBIN = OFF in all PeopleSoft environments, with the possible exception of the development environment.

The RECYCLEBIN parameter can also be set dynamically at session or system level. You could perhaps turn it on prior to upgrade/data migration procedures, and then when satisfied turn it off again and manually purge the recycle bin.

I think Oracle features should be used knowingly. It doesn't matter whether you decide to use a feature or not. It is important that in making that decision you have thought about how to deal with the implications, rather than be surprised later.

Oracle Terminated Connection Timeout

2009-07-14T10:18:00.006+01:00

I have recently come across situations on two different PeopleSoft sites where ad-hoc queries continue to run on the Oracle database server long after the Application Server process, which is the Oracle session client, has terminated. Often, queries perform poorly because they are poorly coded, but that is another story. To help guard against this situation Oracle has mechanism called Terminated Connection Timeout (also known as Dead Connection Detection (DCD) when it was introduced in Net8).

Metalink Note 615782.1 explains the mechanism. "DCD is initiated on the server when a connection is established. At this time SQL*Net reads the SQL*Net parameter files and sets a timer to generate an alarm. The timer interval is set by providing a non-zero value in minutes for the SQLNET.EXPIRE_TIME parameter in the sqlnet.ora file on the database server side. When the timer expires, SQL*Net on the server sends a 'probe' packet, essentially an empty SQL*Net packet, to the client. If the client end of the connection is still active, the probe is discarded, and the timer mechanism is reset. If the client has terminated abnormally, the server will receive an error from the send call issued for the probe, and SQL*Net on the server will signal the operating system to release the connection's resources."

Thus, if a PeopleSoft operator initiates an ad-hoc query that runs on the PSQRYSRV server for longer than the ICQuery service time-out (default 1200 seconds), then Tuxedo will terminate and restart the busy server process. However, the query will continue run on the database server until the current fetch operation returns. In the case of a query performing a large sort or hash operation, it might be a long time before the first row is returned. All the while, the query is continuing to consume resources on the database.

PeopleTools 8.44, also introduced the ability to kill a query via the Query Monitor that had reached a maximum run time. This is one of the functions of the PSMONITORSRV server process (see Metalink Note 624339.1. The maximum run time is specified in a permission list (see Security Administration PeopleBook Permission List Query Profile) and then the ability to kill queries that have timed out can be enabled or disabled system-wide (see PeopleSoft Query PeopleBook Query Administration. It will kill the application server process that submitted the query, but for the same reasons, the query may continue to run on the database.

Thus, setting Terminated Connection Timeout is not merely a good idea for a PeopleSoft system running on Oracle, it is effectively mandatory. Otherwise. some PeopleSoft functionality simply won't work as intended.

What is an appropriate value for SQLNET.EXPIRE_TIME?

The value for this parameter is the time between successive SQL*Net probes sent by the Oracle shadow server process to the client. Setting it is a balance between the maximum time that a query can be left to consume resources after a client process terminates, against the additional overhead of every client process sending a probe every few minutes.

The SQL*Net documents often talk about additional network traffic generated by DCD. This was a consideration in the past on client-server applications that ran across a wide area network. However, it is rarely a consideration in relatively modern systems such as PeopleTools 8, the database connections are made by Application Server and Process Scheduler, which are usually physically close to the database server.

The time-out can be set independently of any of the other time-outs for the Application Server and Web Server. Documents on Metalink often suggest 5 or 10 minutes, and I don't think that is unreasonable.

My thanks to Colin Kilpatrick who prompted me to look at this again.

Dynamically Switching PeopleSoft Temporary Records between Global Temporary and Normal Tables during Application Engine Programs

2009-07-06T22:48:00.010+01:00

I am working on a Time & Labour system. We run the main T&L process in different modes. There is a company wide overnight batch process, but individual parts of the company can run the process for their section during the day to process exceptions and to generate schedules. This is done with a custom AE program that calls the delivered TL_TIMEADMIN.

Running on Oracle 10gR2, we have faced performance problems caused by contention between concurrent truncate operations in these processes (see Factors Affecting Performance of Concurrent Truncate of Working Storage Tables).

One way to evade this problem is to convert the working storage tables to be Global Temporary Tables (GTTs). The contention problem caused by serialisation of truncate operations does not occur with GTTs. Last year I wrote a blog entry (Global Temporary Tables and PeopleSoft Temporary Records) that discussed when it was possible to use GTTs in Application Engine (AE) programs. Essentially, you can only use GTTs when restart is disabled for all AE programs that reference a temporary records.

So we could consider running the small but frequent daytime processes with restart disabled. Then we can make the non-shared instances of the temporary records into Global Temporary tables for the non-restartable daytime processes. However, we want to continue to run the overnight processes with restart enabled so that we have the option to restart a process that fails overnight from where it crashed, rather than going back to the beginning. The same non-shared instances may need to be Global Temporary during the day but normal recoverable tables by night.

Previously, I have only converted tables to Global Temporary where they are not referenced by a restartable AE program. I have now devised a way of switching a table to being a GTT if it is allocated to a process for which restart is disabled, and switching it back to a normal table if not. This is the best of both worlds.

When a non-shared instance of a temporary record is allocated to a particular process instance, a row is inserted into PS_AETEMPTBLMGR. The value of the restart disable flag is also stored in PS_AETEMPTBLMGR. I have created a trigger that switches a non-shared instance of a temporary record from a normal table to a GTT if restart is disabled, or switches it back to a normal table if restart is enabled. The trigger will create the GTT and any indexes on it if necessary, and will rename the tables as necessary so that the correct version has the default name expected by the process.

So if, for example, I have instance number 11 of a record called WRK_SHIFT_TAO, then the table will be called PS_WRK_SHIFT_TAO11. If that is allocated to a non-restartable AE program, the trigger will check for a table called GT_WRK_SHIFT_TAO11. If it doesn't exist the trigger will create it, dynamically generating the DDL with the dbms_metadata package. Thus, the structure of the GTT will be identical to PS_WRK_SHIFT_TAO11, the leading PS will also be changed to GT. The same indexes will also be created. The original normal table PS_WRK_SHIFT_TAO11 will be renamed to XX_WRK_SHIFT_TAO11, and then the GTT, at that point called GT_WRK_SHIFT_TAO11 will be renamed to PS_WRK_SHIFT_TAO11. The indexes are not renamed. If later the same instance is allocated to a restartable process, the change will be reversed by renaming again. The GTT will not be dropped so that it does not need to be recreated again the next time the non-restartable program uses it.

All DDL generated and executed by the trigger is written to the Message Log for that process (click on the screen shot to enlarge).

The shared instance of a record (the one without a instance number suffix) is never rebuilt as a Global Temporary table because it is possible that a restartable and non-restartable process might both use the shared instance at the same time.

One complication is how to handle changes to the temporary records. Application Designer will only create normal tables. So, if the table is to be rebuilt, them it needs to be switched back to a normal table, and the corresponding GTT created by the trigger should then be dropped. The Application Designer can recreate the table in the usual way. Next time the non-restartable AE runs, it will recreate the GTT with the new structure.

An updated version of T_LOCK is available which handles PeopleSoft temporary records and prevent DDL on PS_ tables with corresponding GT_ tables. The commands which swap these tables back and forth are explicitly permitted by the new version of this trigger.

Controlling How %UpdateStats Collects Optimizer Statistics

2009-06-25T20:30:00.005+01:00

I have written a number of entries on this blog about updating database statistics on tables during Application Engine processes.

Statistics Management for PeopleSoft Temporary Records in Application Engine Programs, discussed management of statistics on GTTs, and the use of Optimizer Dynamic sampling.
Statistics Management for Partitioned Objects in PeopleSoft discussed an enhancement to the wrapper so that it only gathered stale statistics on partitioned objects.
%UpdateStats() -v- Optimizer Dynamic Sampling noted that I had found some situations where Oracle's Optimizer Dynamic Sampling, even at the highest level, does not produce the best execution plan and there is no alternative but to collect statistics on temporary tables.

I proposed a PL/SQL package (wrapper.sql) called from the DDL model to intercept the call from the %UpdateStats macro in Application Engine. By default it

Gathers statistics on regular tables.
Refreshed only stale statistics on partitioned tables.
Does not gather statistics on Global Temporary Records.

I have now published an enhanced version: wrapper848meta.sql.

A table PS_GFC_STATS_OVRD holds meta-data to override the default behaviour of the script for certain records. The meta-data can also specify the size of the sample, and the options to control the collection of histograms.
If a private instance of a Temporary Records is a Global Temporary Tables, the wrapper may still collect statistics (normally this would be suppressed because of the risk of one session using the statistics collected by another session, but this will not happen for these tables).

Now, it is possible to specify the few tables where statistics must still be explicitly gathered, or whether to do this only if the current statistics on the table are stale. The DBA is probably the person best placed to decide whether and how to collect statistics on which tables, and these decisions can be implemented with the meta-data, but without code change.

Thus, it is possible to

Reduce the number of calls to dbms_stats,
to reduce the overhead of the remaining calls
and at least preserve, if not improve performance of batch processes without making any code changes.

Oracle 10g Statistics History Retention in PeopleSoft

2009-06-25T20:23:00.007+01:00

I have been working on a system where many Application Engine programs are running throughout the day, and are frequently collecting Optimizer statistics with the %UpdateStats macro on many working storage tables. Concurrent calls to dbms_stats are typical.

There are two new behaviours in Oracle 10g RDBMS that can in extreme cases, in combination with a system that calls dbms_stats very frequently, create a significant performance overhead.

From Oracle 10g, histograms may, by default, be collected automatically. That means that rows are concurrently deleted from and inserted into histgrm$ and hist_head$, leading to contention and consistent read.

Also from Oracle 10g, every time you collect statistics on a table the old statistics are retained in the SYS.WRI$_OPTSTAT%HISTORY tables. If histograms have previously been collected, these are also copied. DBMS_STATS has the additional overhead of writing this history. I found in excess of 10,000 versions of previous statistics for some tables, because the batch processes have updated statistics on working storage tables that many times.

dbms_stats also appears to be responsible for purging history older than the retention limit. The default retention period is 31 days. I have seen concurrent calls to dbms_stats blocked on row level locks on the statistics history tables. For me, this occurred 31 days after the system went live on a significantly increased volume.

Statistics history was designed to work in conjunction with schema wide statistics jobs that only refreshed stale statistics. There is an option on gather_schema_stats to collect only statistics on tables where the current statistics are stale. However, there is no such option on gather_table_stats. If you have decided to call this procedure for a particular table, then it is assumed you know you need to refresh the statistics. However, by calling dbms_stats from a batch program you can end up calling it much more frequently than is really necessary.

Recommendations

Disable statistics history by setting the retention period to zero. Unfortunately this can only be set at database level. The statistics history is there in case you want to revert to a previous version of the statistics should a new set of statistics produce a problem, but it is only used rarely, and I think this is a necessary sacrafice.

Use Oracle Optimizer Dynamic Sampling. However, I suggest increasing the level from the default of 2 to 4 to increase the situations in which it is used.

Introduce the new version of the PL/SQL wrapper package for dbms_stats so that you can specify the records for which statistics will be explicitly collected, and whether histograms are to be collect. Thus you can reduce the number of calls to dbms_stats.

Manually Booting Tuxedo Application Server Processes in Parallel

2009-05-19T11:30:00.009+01:00

Normally when an Application Server is booted, initialisation of each process completes before the next one is started. The ability to boot Application Server processes in parallel was added to the psadmin utility in PeopleTools 8.48. However, psadmin is merely a wrapper for the BEA Tuxedo tmadmin command line utility, and it has always been possible to do this manually in previous versions of PeopleTools via the tmadmin utility as follows.

1. Boot the Tuxedo Bulletin Board Liaison process.

#boot the Tuxedo administrative processes
boot -A

2. Boot the PeopleSoft Application Server processes and but specify the -w parameter so that they don't wait as they start

boot -g APPSRV -w

If you are running PUBSUB or other servers in other groups then you would also boot them here.

3. Boot the JREPSRV process (which maps Java Classes to Tuxedo Services).

boot -g JREPGRP

4. List the servers with print server so you know that the PeopleSoft servers are booted.

psr

5. When all the other processes have booted, boot the WSL and JSL processes.

boot -g BASE
boot -g JSLGRP

UKOUG PeopleSoft Conference 2009 Presentations

2009-05-13T07:00:00.006+01:00

The 2009 conference is past, but the agenda is still available on the UKOUG webiste, and if you are a UKOUG member or attended the conference, you can download the presentations.

I will be gave two new presentations that are on my website.

Reducing Unnecessary Instances of Temporary Records

2009-04-22T09:08:00.013+01:00

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.

AE_APPLID    TEMPTBLINSTANCES
------------ ----------------
TL_PUB_TM1                  5
TL_PUB_TM_AE                5

They share 8 temporary records in common.

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.

Statistics Management for Partitioned Objects in PeopleSoft

2009-04-06T15:23:00.005+01:00

I have implemented partitioned objects in a number of PeopleSoft systems on Oracle. Recently, I was working on a system where a table was partitioned into weekly range partitions, and I encountered a performance problem when Oracle's automatic maintenance window job to collect statistics did not run between populating the new partition for the first time, and running a batch process that referenced that partition. Oracle, understandably produced a execution plan for a statement that assumed the partition was empty, but as the partition actually had quite a lot of data, the statement ran for a long time.

The solution was to tell Oracle the truth by gathering statistics for that partition. However, I didn't want to refresh the statistics for the whole table. There were many partitions with historical data that has not changed, so I don't need to refresh those partitions. I only need to refresh just the stale partitions, and here is the problem. Unfortunately, dbms_stats package will let you gather stale and missing statistics for all tables in a given schema, or the whole database, but not for a named table. It is not completely unreasonable, if you are targeting a single table then you ought to know what needs to be refreshed.

I have written a PL/SQL procedure to flush the table monitoring statistics to the data dictionary and determine whether the statistics on the table, any of its partitions and sub-partitions are stale or missing, and if so gather statistics on those segments. It uses (I believe) the same criteria as dbms_stats to determine stale objects: 10% change relative to last gathered statistics, or if the segment has been truncated. I have incorporated the new refresh_stats procedure into my PL/SQL packaged procedure wrapper which can be called by the %UpdateStats PeopleCode macro via a customised DDL model. The new procedure is only called for partitioned tables.

All that is necessary it to use the %UpdateStats macro in an Application Engine program.

This is all still work-in-progress, but so far, the results are encouraging.

Statistics Management for PeopleSoft Temporary Records in Application Engine Programs

2009-04-06T14:39:00.009+01:00

Last year, I wrote about Oracle Optimizer Statistics and Optimizer Dynamic Sampling with PeopleSoft Temprorary Records. Earlier this year, I encountered a situation where Optimizer Dynamic Sampling was not sufficient, and I did need properly gathered statistics on an object. I modified my PL/SQL packaged procedure wrapper which can be called by the %UpdateStats PeopleCode macro via a customised DDL model to collects statistics.

I still recommend locking statistics on PeopleSoft Temporary Record, so that table is omitted from schema or database wide operations to refresh statistics. If the statistics on a table are locked, and it is not a Global Temporary Table, then the wrapper package will force collection and update of statistics on the table (previously it suppressed gathering of statistics on tables with locked statistics).

However, when the Application Engine program completes, any statistics collected on those temporary tables are no longer needed. Worse, the statistics refer to data that will be deleted and replaced by some future program, and if the table were not reanalysed, the statistics would be misleading, and could cause the database to produce an inappropriate execution plan. Some temporary records are shared by multiple programs, so you cannot guarantee that statistics will always be refreshed when the table is next used.

When an Application Engine program completes successfully, or when the process request is cancelled, specific instances of temporary records that were allocated when the program began are deallocated by deleting the row from PS_AETEMPTBLMGR. Therefore, I propose the following trigger that will delete the statistics for that record when that row is deleted.

CREATE OR REPLACE TRIGGER gfc_deletetemptablestats
AFTER DELETE ON sysadm.ps_aetemptblmgr
FOR EACH ROW
WHEN (old.curtempinstance > 0)
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
l_table_name VARCHAR2(30) := '';
BEGIN
SELECT DECODE(r.sqltablename,' ','PS_'||r.recname,r.sqltablename)||:old.curtempinstance tabname
INTO   l_table_name
FROM   psrecdefn r
WHERE  r.recname = :old.recname;

dbms_stats.delete_table_stats(ownname=>'SYSADM',tabname=>l_table_name,force=>TRUE);

EXCEPTION
WHEN no_data_found THEN NULL;
END;
/
show errors

NB: The trigger must use an autonomous transaction because dbms_stats also commits its updates.

You can test the trigger like this: First I will populate the control table with a dummy record, and collect statistics

INSERT INTO ps_aetemptblmgr
(PROCESS_INSTANCE, RECNAME, CURTEMPINSTANCE, OPRID, RUN_CNTL_ID, AE_APPLID
,RUN_DTTM, AE_DISABLE_RESTART, AE_DEDICATED, AE_TRUNCATED)
VALUES
(0,'TL_EXCEPT_WRK',24,'PS','Wibble','TL_TIMEADMIN',sysdate,' ', 1,0)
/
execute dbms_stats.gather_table_stats(ownname=>'SYSADM',tabname=>'PS_TL_EXCEPT_WRK24',force=>TRUE);

column table_name format a18
SELECT table_name, num_rows, last_analyzed
FROM user_tables where table_name = 'PS_TL_EXCEPT_WRK24'
/

TABLE_NAME           NUM_ROWS LAST_ANALYZED
------------------ ---------- -------------------
PS_TL_EXCEPT_WRK24          0 14:36:12 06/04/2009

Now I will delete the row, and the trigger will delete the statistics for me.

DELETE FROM ps_aetemptblmgr
WHERE process_instance = 0
and curtempinstance = 24
and recname = 'TL_EXCEPT_WRK'
/

SELECT table_name, num_rows, last_analyzed
FROM user_tables where table_name = 'PS_TL_EXCEPT_WRK24'
/

TABLE_NAME           NUM_ROWS LAST_ANALYZED
------------------ ---------- -------------------
PS_TL_EXCEPT_WRK24

Automatically Granting Privileges on Newly Created Tables (continued)

2009-04-02T20:19:00.003+01:00

Following this posting it was put to me that you could get Application Designer to build scripts with the commands to add the privilege by adding a second command to the create table DDL model, like this:

CREATE TABLE [TBNAME] ([TBCOLLIST]) TABLESPACE [TBSPCNAME] STORAGE (INITIAL **INIT** NEXT **NEXT** MAXEXTENTS **MAXEXT** PCTINCREASE **PCT**) PCTFREE **PCTFREE** PCTUSED **PCTUSED**;
GRANT SELECT ON [TBNAME] TO psreadall;

Yes, this does work when creating the table. The additional command is put into the create table script generated by Application Designer

DROP TABLE PS_PERSON
/
CREATE TABLE PS_PERSON (EMPLID VARCHAR2(11) NOT NULL,
BIRTHDATE DATE,
BIRTHPLACE VARCHAR2(30) NOT NULL,
BIRTHCOUNTRY VARCHAR2(3) NOT NULL,
BIRTHSTATE VARCHAR2(6) NOT NULL,
DT_OF_DEATH DATE,
LAST_CHILD_UPDDTM DATE) TABLESPACE HRLARGE STORAGE (INITIAL 40000
NEXT 100000 MAXEXTENTS UNLIMITED PCTINCREASE 0) PCTFREE 10 PCTUSED 80
/

GRANT SELECT ON PS_PERSON TO PSREADALL
/

However, the second command does not appear in the alter script.

CREATE TABLE PSYPERSON (EMPLID VARCHAR2(11) NOT NULL,
BIRTHDATE DATE,
BIRTHPLACE VARCHAR2(30) NOT NULL,
BIRTHCOUNTRY VARCHAR2(3) NOT NULL,
BIRTHSTATE VARCHAR2(6) NOT NULL,
DT_OF_DEATH DATE,
LAST_CHILD_UPDDTM DATE) TABLESPACE HRLARGE STORAGE (INITIAL 40000
NEXT 100000 MAXEXTENTS UNLIMITED PCTINCREASE 0) PCTFREE 10 PCTUSED 80
/
INSERT INTO PSYPERSON (
     EMPLID,
 BIRTHDATE,
 BIRTHPLACE,
 BIRTHCOUNTRY,
 BIRTHSTATE,
 DT_OF_DEATH,
 LAST_CHILD_UPDDTM)
SELECT
     EMPLID,
 BIRTHDATE,
 BIRTHPLACE,
 BIRTHCOUNTRY,
 BIRTHSTATE,
 DT_OF_DEATH,
 LAST_CHILD_UPDDTM
FROM PS_PERSON
/
DROP TABLE PS_PERSON
/
RENAME PSYPERSON TO PS_PERSON
/

So if you alter a table by create, rename and drop, you will lose the granted privileges.

Using Oracle Enterprise Manager (Grid Control) with PeopleSoft

2009-03-13T11:17:00.020Z

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.

...
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'
  AND    x.effdt = (
   SELECT MAX(x1.effdt)
   FROM   psxlatitem x1
   WHERE  x1.fieldname = x.fieldname
   AND    x1.fieldvalue = x.fieldvalue
   AND    x1.effdt <= SYSDATE);
 EXCEPTION 
  WHEN no_data_found THEN l_runstatus := 'Status:'||p_runstatus;       
 END;    
 sys.dbms_application_info.set_action(
  action_name => SUBSTR('PI='||p_prcsinstance||':'||l_runstatus,1,32) );
END set_action;
...

This procedure can be called from a trigger thus:

CREATE OR REPLACE TRIGGER sysadm.psftapi_store_prcsinstance
BEFORE UPDATE OF runstatus ON sysadm.psprcsrqst
FOR EACH ROW
WHEN ((new.runstatus IN('3','7','8','9','10') OR
     old.runstatus IN('7','8')) AND new.prcstype != 'PSJob')
BEGIN
 IF :new.runstatus = '7' THEN
  psftapi.set_prcsinstance(p_prcsinstance => :new.prcsinstance);
  psftapi.set_action(p_prcsinstance=>:new.prcsinstance
                    ,p_runstatus=>:new.runstatus
                    ,p_prcsname=>:new.prcsname);
 ELSIF psftapi.get_prcsinstance() = :new.prcsinstance THEN
  psftapi.set_action(p_prcsinstance=>:new.prcsinstance
                    ,p_runstatus=>:new.runstatus);
 END IF;
EXCEPTION WHEN OTHERS THEN NULL; --exception deliberately coded to suppress all exceptions
END;
/

What is the benefit? The MODULE and ACTION show up in Grid Control. So now you can immediately identify the name and Process Instance of those expensive processes.

Unfortunately, it is not possible to do anything similar for sessions created by the Application Server. So all you know is what session belongs to what kind of server process. The Client Information is set at the top of each service, so you know the PeopleSoft Operator ID, but that is all.

It would be useful nice if perhaps the Component name and PeopleCode context was written to MODULE and ACTION. But it isn’t.

Minimum Number of Application Server Processes

2009-03-12T19:41:00.001Z

I have had two conversations recently about what happens if you have only a single PSAPPSRV process in a domain. One of which was on the DBA Forum.

Basically, you should always have at least two instances of any server process that has a non-zero recycle count.

It is rare to see only one PSAPPSRV process in Application Server domains that support the PIA, but customers who use the Integration Broker often have separate Application Server domains for the publication and subscription servers. These domains are often not heavily used, in which case they have been configured with just one of each server process.

This advice applies to the PSAPPSRV, PSQRYSRV, PSBRKHND, PSSUBHND, PSANALYTICSRV servers

The exceptions are

PSSAMSRV is only used by Windows clients in 3-tier mode (nVision and PS/Query)
PSMSGDSP, only a single process can be configured
PSAESRV, because in the Process Scheduler each PSAESRV has its own queue.

The problem occurs when the server process recycles. This occurs when the number of services handled reaches the recycle count. When the only remaining server process on a shared queue shuts down the queue is also deleted, and the advertised services are removed from the Tuxedo Bulletin Board. If a service requests arrives in the application server domain before the new server process has started, and updated the bulletin board with advertised processes, the Jolt handler (JSH) will determine that the service request is not advertised and will raise an error.

It is quite simple to demonstrate this in PeopleSoft. In my demo system, I set the recycle count on PSAPPSRV to just 10 and the minimum number of servers to 1.

[PSAPPSRV]
;=========================================================================
; Settings for PSAPPSRV
;=========================================================================

;-------------------------------------------------------------------------
; UBBGEN settings
Min Instances=1
Max Instances=2
Service Timeout=300

;-------------------------------------------------------------------------
; Number of services after which PSAPPSRV will automatically restart.
; If the recycle count is set to zero, PSAPPSRV will never be recycled.
; The default value is 5000.
; Dynamic change allowed for Recycle Count
Recycle Count=10

It is not long until the PSAPPSRV process recycles, and you get this message in the application server log.

PSAPPSRV.2140 (10) [03/11/09 06:55:15 PTWEBSERVER@GO-FASTER-4](0) Recycling server after 10 services

You can also see in the shutdown message in the TUXLOG file.

The last line is the error message from the JSH process that cannot enqueue the service request because the Application Server is down. If you suspect that you have been getting this problem look for that error message.

065655.GO-FASTER-4!BBL.2444.1760.0: LIBTUX_CAT:541: WARN: Server APPSRV/1 terminated
065655.GO-FASTER-4!BBL.2444.1760.0: LIBTUX_CAT:550: WARN: Cleaning up restartable server APPSRV/1
065655.GO-FASTER-4!cleanupsrv.1756.1204.-2: 03-11-2009: Tuxedo Version 8.1, 32-bit
065655.GO-FASTER-4!cleanupsrv.1756.1204.-2: CMDTUX_CAT:542: ERROR: Cannot find service to which to forward request
065655.GO-FASTER-4!cleanupsrv.1756.1204.-2: server APPSRV/1: CMDTUX_CAT:551: INFO: server removed
065655.GO-FASTER-4!JSH.2192.4860.-2: JOLT_CAT:1043: "ERROR: tpcall() call failed, tperrno = 6"

Hence, you should always have at least two PSAPPSRVs process, so that queue is not removed, and the other server(s) can handle requests. Of course there is a small chance that two servers could recycle at the same time, but that is very unlikely.

Automatically Granting Privileges on Newly Created Tables

2009-03-05T19:28:00.009Z

I saw an interesting question on the Oracle-L forum: We have Peoplesoft applications that create tables on the fly. Developers want access to those tables that will be created on the fly, in case the process that creates it ends abnormally. I looked into granting via DDL triggers, it seemed like granting access via them is a problem. Is there a way to grant access other than doing 'grant select any'.

I am finding it increasingly common for developers and support staff not to have direct access to the PeopleSoft OwnerID schema (SYSADM) in even development environments, but using personal database logins. They need to have SELECT privilege on tables. The problem described above also occurs when table is rebuilt by Application Designer. When it is dropped the granted privileges disappear with the table.

It is certainly true that you cannot issue DDL in DDL trigger on the same object that caused the trigger to fire. You will get an error caused by a deadlock in the recursive SQL.

CREATE OR REPLACE TRIGGER gfc_grant AFTER CREATE ON sysadm.schema
DECLARE
l_cmd VARCHAR2(1000 CHAR);
BEGIN
IF ora_dict_obj_type = 'TABLE' THEN
l_cmd := 'GRANT SELECT ON '||ora_dict_obj_owner||'.'||ora_dict_obj_name||' TO gofaster';
dbms_output.put_line('DDL:'||l_cmd);
EXECUTE IMMEDIATE l_cmd;
END IF;
END;
/
show errors
set serveroutput on
DDL:GRANT SELECT ON SYSADM.T TO gofaster
CREATE TABLE t (a NUMBER)
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-30511: invalid DDL operation in system triggers
ORA-06512: at line 9

Some sites have regular maintenance jobs that recreate any missing privileges.

However, there is a way to have the privileges automatically recreated soon after the table is built. You could use a DDL trigger to submit a job to the Oracle job scheduler to grant the privileges. You can't submit DDL directly via the job scheduler, so you need a procedure to which you can pass the DDL as a string parameter, and then execute it as dynamic SQL in the procedure.

CREATE OR REPLACE PROCEDURE myddl
(p_ddl IN VARCHAR2) IS
BEGIN
EXECUTE IMMEDIATE p_ddl;
END;
/

Then the DDL trigger can submit a job to call this procedure with the GRANT command in the parameter.

CREATE OR REPLACE TRIGGER gfc_grant
AFTER CREATE ON sysadm.schema
DECLARE
l_jobno NUMBER;
BEGIN
IF ora_dict_obj_type = 'TABLE' THEN
dbms_job.submit(l_jobno,'myddl(''GRANT SELECT ON '||ora_dict_obj_owner||'.'||ora_dict_obj_name||' TO gofaster'');');
END IF;
END;
/

If I create this table

CREATE TABLE t (a NUMBER);

I get this job

SELECT * FROM dba_jobs
/

 JOB LOG_USER
---------- ------------------------------------------------------------------------------------------
PRIV_USER
------------------------------------------------------------------------------------------
SCHEMA_USER                                                                                LAST_DATE
------------------------------------------------------------------------------------------ -------------------
LAST_SEC                 THIS_DATE           THIS_SEC                 NEXT_DATE
------------------------ ------------------- ------------------------ -------------------
NEXT_SEC                 TOTAL_TIME BRO
------------------------ ---------- ---
INTERVAL
---------------------------------------------------------------------------------------------------------------
FAILURES
----------
WHAT
---------------------------------------------------------------------------------------------------------------
  60 SYSADM
SYSADM
SYSADM
                                                                19:04:52 05/03/2009
19:04:52                          0 N
null

myddl('GRANT SELECT ON SYSADM.T TO gofaster');

After the job has run, which should normally only be a few seconds, I get these privileges

SELECT * FROM user_tab_privs WHERE table_name = 'T';

GRANTEE
------------------------------------------------------------------------------------------
OWNER
------------------------------------------------------------------------------------------
TABLE_NAME
------------------------------------------------------------------------------------------
GRANTOR
------------------------------------------------------------------------------------------
PRIVILEGE
---------------------------------------------------------------------------------------------------------------
GOFASTER
SYSADM
T
SYSADM
SELECT

Added 2.4.2009: Following this posting it was put to me that you could get Application Designer to build scripts with the commands to add the privilege by adding a second command to the create table DDL model. See part 2.

Performance Benefits of ReUse Statement Flag in Application Engine

2009-02-27T16:01:00.008Z

I have achieved some significant performance improvements in some Application Engine programs by just enabling the ReUse Statement flag on certain steps. I thought I would share a recent example of how effective this can be.

I don't think I can improve on the description of this feature in PeopleBooks:
"One of the key performance features of PeopleSoft Application Engine is the ability to reuse SQL statements by dedicating a persistent cursor to that statement.

Unless you select the ReUse property for a SQL action, %BIND fields are substituted with literal values in the SQL statement. The database has to recompile the statement every time it is executed.

However, selecting ReUse converts any %BIND fields into real bind variables (:1, :2, and so on), enabling PeopleSoft Application Engine to compile the statement once, dedicate a cursor, and re-execute it with new data multiple times. This reduction in compile time can result in dramatic improvements to performance.

In addition, some databases have SQL statement caching. Every time they receive SQL, they compare it against their cache of previously executed statements to see if they have seen it before. If so, they can reuse the old query plan. This works only if the SQL text matches exactly. This is unlikely with literals instead of bind variables."

In fact most databases do this, and Oracle certainly does.

On Oracle, you could enable CURSOR_SHARING. Then Oracle effectively replaces the literals with bind variables at parse time. However, I certainly would not recommend doing this database-wide. Whenever I have tried this on a PeopleSoft system, it has had severe negative effects elsewhere. I have enabled cursor sharing at session level for specific batch programs (using a trigger), but even then it is not always beneficial.

Instead, I do recommend using the ReUse Statement flag wherever possible. It cannot just be turned on indiscriminately, the same section in PeopleBooks goes on to describe some limitations (which is probably why the default value for the flag is false).

To illustrate the kind of improvement you can obtain, here is a real-life example. This is an extract from the batch timings report at the end of the Application Engine trace file. We are interested in statements with the high compile count.

ReUse Statement is not enabled on these 4 steps. They account for more that 50% of the overall execution time.

                          PeopleSoft Application Engine Timings
                              (All timings in seconds)

        C o m p i l e    E x e c u t e    F e t c h        Total
SQL Statement                  Count   Time     Count   Time     Count   Time     Time
------------------------------ ------- -------- ------- -------- ------- -------- --------
99XxxXxx.Step02.S                 8453      2.8    8453    685.6       0      0.0    688.4
99XxxXxx.Step03.S                 8453      5.0    8453   2718.8       0      0.0   2723.8
99XxxXxx.Step05.S                 8453      0.9    8453    888.4       0      0.0    889.3
99XxxXxx.Step06.S                 8453      0.4    8453     17.4       0      0.0     17.8

------------------------------------------------------------------------------------------
Total run time                :     8416.4
Total time in application SQL :     8195.0   Percent time in application SQL :       97.4%
Total time in PeopleCode      :      192.7   Percent time in PeopleCode      :        2.3%
Total time in cache           :        8.7   Number of calls to cache        :       8542
------------------------------------------------------------------------------------------

Now, I have enabled ReUse Statement on these steps. I have not changed anything else.

                         C o m p i l e    E x e c u t e    F e t c h        Total
SQL Statement                  Count   Time     Count   Time     Count   Time     Time
------------------------------ ------- -------- ------- -------- ------- -------- --------
99XxxXxx.Step02.S                    1      0.0    8453    342.3       0      0.0    342.3
99XxxXxx.Step03.S                    1      0.0    8453     83.3       0      0.0     83.3
99XxxXxx.Step05.S                    1      0.0    8453      8.7       0      0.0      8.7
99XxxXxx.Step06.S                    1      0.0    8453      7.6       0      0.0      7.6
------------------------------------------------------------------------------------------
Total run time                :     5534.1
Total time in application SQL :     5341.7   Percent time in application SQL :       96.5%
Total time in PeopleCode      :      190.8   Percent time in PeopleCode      :        3.4%
Total time in cache           :        1.1   Number of calls to cache        :         90
------------------------------------------------------------------------------------------

Notice that:

The number of compilations for each step has gone down to 1, though the number of executions remains the same
The execution time for the first three statements has fallen by nearly 90%.
The improvement in the 4th statement is quite modest because it did not contain any bind variables, but clearly some of the time reported as execution time by Application Engine is associated with the preparation of a new SQL statement.

To emphasise the point, lets look at the effect on the database. The following are extracts from the TKPROF output for Oracle SQL trace files for these processes.

First the TKPROF without ReUse Statement:

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse   101063   2600.60    2602.83       6197     661559          4           0
Execute 101232   1817.96    3787.17    1572333   73729207   10617830     4770112
Fetch    96186    385.41    1101.47     374425   25986600          0       96285
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total   298481   4803.97    7491.48    1952955  100377366   10617834     4866397

Misses in library cache during parse: 25498
Misses in library cache during execute: 90

Elapsed times include waiting on following events:
Event waited on                             Times   Max. Wait  Total Waited
----------------------------------------   Waited  ----------  ------------
db file sequential read                   1199472        0.36       2601.83
SQL*Net message from client                130345        1.57        296.50
db file scattered read                       8816        0.39        171.47

OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse   100002     13.51      13.57         17        820         94           0
Execute 131495     30.00      31.31       7025      29277      21164       74315
Fetch   141837    218.77     295.49     159969    3039304         12      519406
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total   373334    262.28     340.38     167011    3069401      21270      593721

160446  user  SQL statements in session.
70478  internal SQL statements in session.
230924  SQL statements in session.

And now with ReUse Statement set on only those four steps

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse    67238     10.24      10.75         47       4415          9           0
Execute 101160   1650.25    4040.88    1766325  129765633   11160830     4781797
Fetch    96123    385.50    1024.50     372737   26097251          0      103844
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total   264521   2045.99    5076.14    2139109  155867299   11160839     4885641

Misses in library cache during parse: 73
Misses in library cache during execute: 21

Elapsed times include waiting on following events:
Event waited on                             Times   Max. Wait  Total Waited
----------------------------------------   Waited  ----------  ------------
db file sequential read                   1506834        0.61       2839.19
SQL*Net message from client                130312        1.53        258.81
db file scattered read                       8782        0.37        147.01

OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse     1331      0.46       0.46          0        173         16           0
Execute   4044      2.72       5.82      12923      33374      24353      113323
Fetch     5697      8.38      13.43      15550      55431         12       13449
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    11072     11.56      19.72      28473      88978      24381      126772

67425  user  SQL statements in session.
3154  internal SQL statements in session.
70579  SQL statements in session.

Nearly all the saving is in parse time of non-recursive statements, the rest is the reduction of recursive SQL because there is less parsing.
There is less parsing, because there are fewer different SQL statements submitted by Application Engine. The number of user statements has fallen from 160446 to 67425.
The number of misses on the library cache has fallen from 25498 to just 73.
There has been a reduction in SQL*Net message from client (database idle time) from 296 seconds to 253 because the Application Engine program spends less time compiling SQL statements.

Conclusion

Enabling ReUse Statement can have a very significant effect on the performance of Application Engine batches. It is most effective when SQL statements with %BIND() variables are executed within loops. Otherwise, for each execution of the loop, Application Engine must recompile the SQL statement with different bind variable values, which the database will treat as a new statement that must be parsed.

SQL parsing is CPU intensive. Reducing excessive parse also reduces CPU consumption on the database server. It can also reduce physical I/O to the database catalogue. On PeopleSoft 8.x applications that use Unicode, the overhead of parsing is magnified by the use of length checking constraints on all character columns. This is no longer an issue in version 9 applications which use character semantics.

If you use Oracle's Automatic Memory Management, excessive parsing can cause the database to allocate more memory to the Shared Pool at the expense of the Block Buffer Cache. This in turn can increase physical I/O and can degrade query performance.

Bind Variables are a good thing. You should use them. Therefore, ReUse Statement is also a good thing. You should use that too!

Do You Need More Temporary Table Instances?

2009-02-26T22:48:00.006Z

When an Application Engine loads a program prior to execution, it attempts to allocate an instance of each temporary record specified in the program to itself. If the allocation of a table fails because there are no available instances, Application Engine is will use the shared instance (unless the program is configured to abort if non-shared tables cannot be assigned). In this case it will write an entry to the message log to warn that the shared instance of the record has been used.

When processes use the shared tables performance is likely to be degraded by contention on the table. The %TruncateTable() PeopleCode macro generates a DELETE by process instance on the shared table instead of a TRUNCATE command.

The problem is that unless you look in the message log, you will not know that this is happening. However, it easy to write a query that will look at the message log table and report whenever this has occurred.

select  p.message_parm recname, r.prcsname
,  count(*) occurances
,  max(l.dttm_stamp_sec) last_occurance
,  max(p.process_instance) process_instance
from  ps_message_log l
,  ps_message_logparm p
 left outer join psprcsrqst r
 on r.prcsinstance = p.process_instance
where  l.message_set_nbr = 108
and    l.message_nbr = 544
and    p.process_instance = l.process_instance
and    p.message_seq = l.message_seq
and    l.dttm_stamp_sec >= sysdate - 7
group by p.message_parm, r.prcsname
order by 1,2
/

This report tells you which programs failed to allocated instances of which record, how many times that has happened within the last 7 days.

            Processes Unable to Allocate Non-Shared Temporary Record

                                                                  Last
Record          Process                  Last                   Process
Name            Name         Occurrences Occurrence            Instance
--------------- ------------ ----------- ------------------- ----------
TL_ABS_WRK      TL_TIMEADMIN           4 08:24:39 01/01/2009      12345
TL_ATTND_HST1   TL_TIMEADMIN          10 08:23:40 01/01/2009      12345
TL_COMP_BAL     TL_TIMEADMIN          11 08:23:40 01/01/2009      12345
...

NB: Just because an Application Engine could not allocate a non-shared table, does not automatically imply that you need more instances of that record. It could be that other processes had failed, but the temporary tables are still allocated to the process until the process is either restarted and completes successfully, or the process is deleted or cancelled.

You might choose to create some spare instances of records to allow for failed processes, but if you do not clear failed processes you will eventually run out of instances.

UKOUG PeopleSoft Conference 2009 - Call for Papers

2009-02-11T23:22:00.004Z

Building on the success of the inaugural UKOUG PeopleSoft Conference & Exhibition in 2008, this Conference, presented by UKOUG and GPUG, will provide a forum for the presentation and exchange of ideas and practical experiences within the areas of PeopleSoft Financials, HCM/HR and Technology. The multi-stream agenda will feature keynote presentations, technical and non-technical sessions, roundtables, panel discussions and more.
If you are interested in sharing your experience of using PeopleSoft technology and applications, here is your chance as the call for papers is now open.

Deadlines are tight. Submit your abstracts now at: www.oug.org/peoplesoft

Closing date for submissions: Friday 27th February.

The review panel, comprised of PeopleSoft community members, will evaluate all abstracts submitted by the closing date. The authors of accepted abstracts will receive confirmation at the beginning of March.

Managing Changes to the Number of Instances of Temporary Tables used in Application Engine Programs

2009-01-30T12:18:00.022Z

When you run multiple copies of either the same Application Engine program, or different Application Engines that happen to use the same temporary work records, you need to worry about how many instances of the program or programs are likely to run concurrently, and hence how many instances of the temporary table to build.

But how do you manage these tables when you change the number of instances in the Application Engine properties?

How many tables are built for each Temporary Record?

The number of instances of each Application Engine program is set in the properties for that program (and stored on the PeopleTools table PSAEAPPLDEFN in the column TEMPTBLINSTANCES).

This screenshot is the Application Engine Program Properties for TL_TIMEADMIN. It is delivered configured for up to 10 concurrent instances.

The number of tables that are built for each Temporary Record is the sum of the instances in all the Application Engine programs in which the record is used, plus the number of Global Instances (in set up on the PeopleTools Options page.

Let’s take the record TL_PROF_LIST as an example. It is defined as a Temporary Record in 8 different Application Engine programs (in my HCM8.9 demo system).

SELECT a.recname, a.ae_applid, b.temptblinstances
FROM   psaeappltemptbl a, psaeappldefn b
WHERE  a.ae_applid = b.ae_applid
AND    a.recname = 'TL_PROF_LIST'
ORDER BY a.recname
/

RECNAME         AE_APPLID    TEMPTBLINSTANCES
--------------- ------------ ----------------
TL_PROF_LIST    TL_AGG_SECTN                1
TL_PROF_LIST    TL_OUTTCD                   5
TL_PROF_LIST    TL_PUB_TM1                  5
TL_PROF_LIST    TL_SCHHRSRPT                5
TL_PROF_LIST    TL_SCHRES_AE                9
TL_PROF_LIST    TL_ST_LIB                   5
TL_PROF_LIST    TL_TIMEADMIN               10
TL_PROF_LIST    TL_TRPROFILE               10

So, across all the programs 50 temporary tables are required.

SELECT * FROM pstemptblcntvw 
WHERE  recname = 'TL_PROF_LIST'
/

RECNAME         TEMPTBLINSTANCES
--------------- ----------------
TL_PROF_LIST                  50

The system has three global instances.

SELECT temptblinstances
FROM   psoptions
/

TEMPTBLINSTANCES
----------------
               3

So, Application Designer will build 54 tables based on this record.

3 ‘global’ instances for Application Engines without a Process Instance number. The table names will be suffixed with numbers 1 to 3.
50 ‘private’ instances for the number of requested instances of the Application Engines. The table names will be suffixed with numbers 4 to 53
1 ‘shared’ version without a suffix (in other words the usual name) which is used if there is no instance no already allocated to a process instance.

And this is the top and bottom of the script to build the tables only generated by Application Designer.

CREATE TABLE PS_TL_PROF_LIST (PROCESS_INSTANCE DECIMAL(10) NOT NULL,
EMPLID VARCHAR2(11) NOT NULL,
EMPL_RCD SMALLINT NOT NULL,
START_DT DATE,
END_DT DATE) TABLESPACE TLWORK STORAGE (INITIAL 40000 NEXT 100000
MAXEXTENTS UNLIMITED PCTINCREASE 0) PCTFREE 10 PCTUSED 80
/
…
CREATE TABLE PS_TL_PROF_LIST53 (PROCESS_INSTANCE DECIMAL(10) NOT NULL,
EMPLID VARCHAR2(11) NOT NULL,
EMPL_RCD SMALLINT NOT NULL,
START_DT DATE,
END_DT DATE) TABLESPACE TLWORK STORAGE (INITIAL 40000 NEXT 100000
MAXEXTENTS UNLIMITED PCTINCREASE 0) PCTFREE 10 PCTUSED 80
/

Are there any temporary tables that have not been built but that should be built?

If you increase the number of instances of temporary tables on an Application Engine program then you may need to build the extra temporary tables. This query reports the missing tables.

SELECT r.recname
,      n.n instance
,      c.temptblinstances
     + o.temptblinstances temptblinstances
FROM   pstemptblcntvw c
,      psrecdefn r
,      (SELECT rownum-1 n FROM psrecdefn
        WHERE rownum <= 100) n
,      psoptions o
WHERE  r.recname = c.recname
AND    n.n <= c.temptblinstances+o.temptblinstances
AND NOT EXISTS(
       SELECT 'x'
       FROM   user_tables t
       WHERE  t.table_name = 
            DECODE(r.sqltablename, ' ', 'PS_'||r.recname,
                   r.sqltablename) ||DECODE(n.n,0,'',n.n)
        )
ORDER BY 1,2
/

I dropped PS_TL_PROF_LIST42, but I also increased the number of instance of TL_TIMEADMIN from 10 to 11. So the query reports that instances 42 and 54 of this table are missing, and all the other temporary tables for TL_TIMEADMIN also report a missing instance.

RECNAME           INSTANCE TEMPTBLINSTANCES
--------------- ---------- ----------------
…
TL_PMTCH_TMP1           19               19
TL_PMTCH_TMP2           19               19
TL_PROF_LIST            42               54
TL_PROF_LIST            54               54
TL_PROF_WRK             38               38
TL_PT_FINAL             29               29
…

The remedy is very simple. Application Designer will build a script for just the missing tables.

CREATE TABLE PS_TL_PROF_LIST42 (PROCESS_INSTANCE DECIMAL(10) NOT NULL,
EMPLID VARCHAR2(11) NOT NULL,
EMPL_RCD SMALLINT NOT NULL,
START_DT DATE,
END_DT DATE) TABLESPACE TLWORK STORAGE (INITIAL 40000 NEXT 100000
MAXEXTENTS UNLIMITED PCTINCREASE 0) PCTFREE 10 PCTUSED 80
/

Are there any temporary tables built that should not be built?

If you have reduced the number of temporary tables, then you may need to drop the excess tables.

This query reports tables that are beyond the number required.

SELECT r.recname
,      n.n instance
,      c.temptblinstances
     + o.temptblinstances temptblinstances
,      t.table_name
FROM   pstemptblcntvw c
,      psrecdefn r
,      (SELECT rownum-1 n FROM psrecfield
        WHERE rownum <= 100
       ) n
,      user_tables t
,      psoptions o
WHERE  r.recname = c.recname
AND    t.table_name = 
          DECODE(r.sqltablename, ' ', 'PS_'||r.recname,
                 r.sqltablename) ||DECODE(n.n,0,'',n.n)
AND    n.n > c.temptblinstances+o.temptblinstances
ORDER BY r.recname, n.n
/

I built the missing tables for the previous example, but then I reduced the number of instances on TL_TIMEADMIN back to 10. Now, the query reports that there is an extra table for each record beyond the number defined.

RECNAME         INSTANCE TEMPTBLINSTANCES TABLE_NAME
------------- -------- ---------------- ------------------
…
TL_PMTCH_TMP1       19               18 PS_TL_PMTCH_TMP119
TL_PMTCH_TMP2       19               18 PS_TL_PMTCH_TMP219
TL_PROF_LIST        54               53 PS_TL_PROF_LIST54
TL_PROF_WRK         38               37 PS_TL_PROF_WRK38
TL_PT_FINAL         29               28 PS_TL_PT_FINAL29
…

The problem is that Application Designer will not generate the DDL to drop any of these tables. If you reduce the number of temporary table instances, you will have to drop these tables yourself. However, now, it is easy to adjust the above query to produce the DDL to drop the tables.

SELECT 'DROP TABLE '||t.table_name||' PURGE;' cmd
FROM   …

The query then produces these commands.

…
DROP TABLE PS_TL_PMTCH_TMP119 PURGE;
DROP TABLE PS_TL_PMTCH_TMP219 PURGE;
DROP TABLE PS_TL_PROF_LIST54 PURGE;
DROP TABLE PS_TL_PROF_WRK38 PURGE;
DROP TABLE PS_TL_PT_FINAL29 PURGE;
…

The scripts in this posting can be downloaded from my website.

Factors Affecting Performance of Concurrent Truncate of Working Storage Tables

2009-01-26T09:24:00.009Z

A longer version of this posting, with detailed experimental results, is available on my website.

Over the past year, I have seen problems with Local Write Wait in the Oracle database on two different Oracle systems. One occasion was in a PeopleSoft Time and Labour batch process, the other was in a custom PL/SQL process in non-PeopleSoft system.

In both cases, normal tables in the databases were being used for temporary working storage before that data was then written to another table. The content of the working storage tables was then cleared out by periodically truncating them. In order to increase overall batch throughput, several instances of the program were run in parallel. The resulting concurrent truncate operations contended with each other, and the processes did not scale well.

I have written about this subject previously. These problems have prompted me to do some research and testing, I am now able to make definite recommendations.

Oracle Note 334822.1 (which I have also quoted before) provides a good technical description of the database’s internal behaviour. Put simply; truncate (and drop) operations serialise. Only one process can truncate at any one time. If you have multiple concurrent processes all trying to truncate their own working storage tables, you could experience performance problems. Such processes not scale well as the number of concurrent processes increases.

Real Problems

In the case of the non-PeopleSoft PL/SQL process, I was able to recreate the working storage tables as Global Temporary Tables (GTTs) that deleted the rows on commit because the process committed only when each transaction was complete. Local write wait totally disappeared in this case. Temporary objects do not need to be recovered, so this mechanism does not apply to them.

The PeopleSoft scenario involved one of the ‘Time & Labor’ batch processes, TL_TIMEADMIN. However, GTTs cannot easily be introduced into the T&L batches because there are ‘restartable’. Therefore, the contents of temporary working storage tables need to be preserved after the process and its session terminates. This precludes the use of GTTs.

The combination of Local Write Wait and enq: RO - fast object reuse accounted for 31% of the total response time. This is a significant proportion of the total response time.

local write wait occurs, as the name suggests, when the session is waiting for its own write operations. The RO enqueue is used to protect the buffer cache chain while it is scanned for dirty blocks in an object for the database writer to then write to the data files.

enq: RO - fast object reuse occurs when a process waits to acquire the RO enqueue, in other words, while somebody else is truncating or dropping an object.

Two factors affect the time for which the RO enqueue is held:

The time taken to write the blocks to disk. Processes that are frequently truncating temporary working storage are also doing a lot of DML operations to populate the working storage and other tables. The disks under the data files are going to be busy. If the disk becomes a bottleneck, the duration of the local write wait will certainly increase.
The time taken to scan the buffer cache for dirty blocks to be written to disk and flushed from cache. The larger the buffer cache, the longer it will take to find these blocks.

The Metalink note also suggests using a different block size, saying that "a separate buffer pool for temporary tables will also reduce RO enqueue". It is not clear whether it is more important to have a different block size or a separate buffer pool. I wanted to find out which factor was more important.

Tests

I created a simple test to model the behaviour of T&L. I created pairs of simple tables, populated one of each pair, and then repeatedly copied the data back and forth between them, truncating the source after the copy. The test script has evolved into a PL/SQL package procedure, mainly so that the tests could be submitted to and run concurrently by the Oracle job scheduler. There are also procedures to create, populate, and drop the pairs of working storage tables. The scripts can be downloaded from my website.

I was able to run the same controlled test in a variety of scenarios. I have run the tests on Oracle 10.2.0.3 on various platforms with similar results. A detailed set of results are available in the longer version of this document on my website.

General Recommendations

If you have to store temporarily working data in a database table, it is much better to use a Global Temporary Table, although the design of the application may preclude this. It is not possible to do this with data used by restartable Application Engine processes, because the contents of the GTT would be lost when the process terminates.

The Metalink note references unpublished bug 414780 in which a PeopleSoft customer reported this problem, but “they seemed to fix it by changing some PeopleSoft code to implement delete rather than truncate on small temporary tables”. However, my tests show that this probably degraded performance further. The individual delete statements take longer than the truncate operations, and the overall test times increased. Although the truncate operations serialise on the RO enqueue and wait for local writes, this is still better than deleting the data and waiting for the undo information to be written to the redo log. Furthermore, although the truncate operations did not scale well, the delete operations exhibited negative scalability for the same volumes and concurrency. They became bottlenecked on redo log.

Using a recycle pool of the same block size as the rest of the database was not effective; possibly because these pools use the same LRU latches.
Using a larger non-default block size improved performance of truncate. The performance with 32Kb blocks was better than with 16Kb.
Using a larger uniform extent size also improved performance for just the truncate operations and the test as a whole. Fewer, larger extents were involved, and hence less time was spent on CPU and row cache lock. The overall throughput truncate operations degraded as the number of processes increased, although, the throughput of the test as whole did scale.
The presence or absence of indexes did not have a significant effect on the relative test timings, and does not alter my advice.
The effect of truncating with the REUSE STORAGE option is less clear cut. There are no waits on row cache lock because the blocks do not have to be cleared out of the buffer cache, but on the other hand more time is spent on local write wait because all the dirty blocks have to be written to disk, hence the RO enqueue is held for longer and more time is spent on enq: RO - fast object reuse. If you are using an AUTOALLOCATE tablespace then you would be better to use REUSE STORAGE option, but generally you would be slightly better to use a larger uniform extent size and not to use the REUSE STORAGE option.

PeopleSoft Recommendations

Over time, PeopleSoft batch processing has moved slightly away from SQR and COBOL. These types of process cannot be restarted, and so tables used for temporary working storage within the process can usually be recreated as Global Temporary Tables. This will produce better performance and scalability that any option that involves retaining the permanent table.

However, we are seeing more processing in PeopleSoft applications done with Application Engine. If restart has been disabled for an Application Engine program, then temporary records can also be rebuilt as Global Temporary Tables, because their contents does not need to be preserved for another session to pick up.

Otherwise, move the temporary records and their indexes to tablespace with a 32Kb block size. The change of assigned tablespace can be managed within Application Designer, and released like any other patch or customisation. A 32Kb buffer cache must be created in the database instance. Sizing this is going to be a trade-off between how much memory can be taken from other activities to cache just working storage tables, and how much physical I/O you are going to have to wait for. Oracle’s Automatic Shared Memory Management is of no assistance here (until Oracle 11g), the KEEP, RECYCLE, and other block size buffer caches must be sized manually (see Oracle Reference Manual for SGA_TARGET).

No change to the application code is required. There is no performance improvement to be obtained by customising the application code, either to add the REUSE STORAGE option to the TRUNCATE TABLE commands, nor to use DELETE commands instead.

Added 4.7.2009:Tests on Oracle 10.2.0.3 have shown that there is an advantage to putting the working storage tables into a Non-ASSM tablespace. ASSM introduces additional blocks to map the storage in a segment. These are also maintained during a truncate. No having to maintain these blocks saved me 25% on the elapsed run time on some T&L batch processes.

Oracle Bug 4224840/4260477

Added 2.4.2009:Unfortunately, nothing is quite as simple as it seems. If you have a transaction that locks more than 4095 rows in a 32Kb block you can encounter block corruption (bug 4224840). The fix/workaround in Oracle 10g (bug 4260477) is that a transaction will fail with this message before the corruption occurs.

There is an excellent explanation of this problem, and a test script to reproduce it, on Hermant's Oracle DBA Blog

ORA-08007: Further changes to this block by this transaction not allowed

This error will not be resolved until Oracle 11g. However, it does not occur with smaller block sizes. The workaround is either to commit more frequently, or to move the table concerned back to a tablespace with a smaller block size. I have run into this with Time & Labor in a particular scenario.

PeopleSoft's PSADMIN Role

2009-01-06T19:15:00.006Z

Recently, I have had a number of conversations about the privileges that are granted to the PeopleSoft OWNERID account (usually SYSADM) via the PSADMIN role.

A posting on the PeopleSoft DBA Forum, referenced Tanel Poder's blog entry: Oracle Security: All your DBAs are SYSDBAs and can have full OS access. Essentially, there is a security bug in Oracle where users with IMP_FULL_DATABASE and BECOME_USER can gain SYSDBA access. This hole has been closed up a patch delivered in the July 2008 Critical Patch Update.

The PSADMIN role has both of these privileges, as well as another 22 that are not required for normal operation of a PeopleSoft system.

There are two morals to this story:

There are good reasons to keep up to date with Oracle's CPUs.
PSADMIN has privileges that if given to application accounts could compromise database security.

I think that PSADMIN could be stripped down to just the following 8 privileges:

ANALYZE ANY
ALTER SESSION
CREATE SESSION
CREATE TABLE
CREATE TRIGGER
CREATE_VIEW
SELECT_CATALOG_ROLE
ALTER USER

You only need CREATE USER when creating the PeopleSoft database with the Data Mover import. SELECT_CATALOG_ROLE is not in the delivered PSADMIN, but is required if you remove IMP_FULL_DATABASE and EXP_FULL_DATABASE.

Each privilege is discussed in Chapter 3 of PeopleSoft for the Oracle DBA.