Thursday, December 17, 2009

Hinting Sub-Queries on Oracle

This posting is a purely Oracle RDBMS discussion about how to correctly apply hints to sub-queries. However, it is particularly relevant to PeopleSoft, which it makes extensive use of correlated sub-queries. The point of this story is not the particular hints that I applied, but where I placed the hints, and how I scoped them to the sub-queries.

The following SQL extract is from the delivered Global Payroll GPGB_EDI process (although I have already made some minor changes, and PS_GP_RSLT_PIN is partitioned). Notice that each sub-query joins two tables together. PS_GP_RSLT_PIN is second largest of Global Payroll result tables. PS_GP_PIN is a look-up table, and the criterion on PIN_CODE will only return a single row.

UPDATE Table(GPGB_EDIE_TMP) X
  SET X.GPGB_WK53_IND = ( 
 SELECT %TrimSubstr(%Sql(FUNCLIB_HR_CHAR,A.CALC_RSLT_VAL),1,2) 
  FROM PS_GP_RSLT_PIN A 
  ,PS_GP_PIN B 
 WHERE A.EMPLID = %Table(GPGB_EDIE_TMP).EMPLID 
...
AND A.PIN_NUM = B.PIN_NUM 
   AND B.PIN_CODE = 'TAX VR PERIOD GBR' 
   AND A.SLICE_BGN_DT = ( 
 SELECT MAX(D.SLICE_BGN_DT) 
  FROM PS_GP_RSLT_PIN D 
 WHERE D.EMPLID = %Table(GPGB_EDIE_TMP).EMPLID 
...
   AND D.INSTANCE = A.INSTANCE 
   AND D.PIN_NUM = B.PIN_NUM) 
...
   )
 WHERE EXISTS ( 
...
   )
AND PROCESS_INSTANCE = %Bind(PROCESS_INSTANCE)

This is part of the initial SQL execution plan. The problem is that the sub-query starts by scanning through the PS_GP_RSLT_PIN table, 4 times because there are three correlated sub-queries, and only at the very end does it look up the PIN_CODE by the PIN_NUM.

---------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                     | Name              | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| 
---------------------------------------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT                              |                   |     1 |    65 |   113M (93)| 39:44:51 |       |     
|   1 |  UPDATE                                       | PS_GPGB_EDIE_TMP4 |       |       |            |          |       |     
|*  2 |   FILTER                                      |                   |       |       |            |          |       |     
|*  3 |    TABLE ACCESS FULL                          | PS_GPGB_EDIE_TMP4 |   673K|    41M|  9967   (6)| 00:00:13 |      
|   4 |    NESTED LOOPS                               |                   |     1 |   108 |     4   (0)| 00:00:01 |       |     
|   5 |     PARTITION RANGE SINGLE                    |                   |     1 |    83 |     3   (0)| 00:00:01 |   KEY | 
|   6 |      PARTITION LIST SINGLE                    |                   |     1 |    83 |     3   (0)| 00:00:01 |   KEY | 
|*  7 |       INDEX RANGE SCAN                        | PS_GP_RSLT_PIN    |     1 |    83 |     3   (0)| 00:00:01 |   KEY | 
|   8 |        SORT AGGREGATE                         |                   |     1 |    72 |            |          |       |     
|   9 |         PARTITION RANGE SINGLE                |                   |     1 |    72 |     3   (0)| 00:00:01 |   KEY | 
|  10 |          PARTITION LIST SINGLE                |                   |     1 |    72 |     3   (0)| 00:00:01 |   KEY | 
|* 11 |           INDEX RANGE SCAN                    | PS_GP_RSLT_PIN    |     1 |    72 |     3   (0)| 00:00:01 |   KEY | 
|  12 |            SORT AGGREGATE                     |                   |     1 |    83 |            |          |       |     
|  13 |             PARTITION RANGE SINGLE            |                   |     1 |    83 |     3   (0)| 00:00:01 |   KEY | 
|  14 |              PARTITION LIST SINGLE            |                   |     1 |    83 |     3   (0)| 00:00:01 |   KEY | 
|  15 |               FIRST ROW                       |                   |     1 |    83 |     3   (0)| 00:00:01 |       |     
|* 16 |                INDEX RANGE SCAN (MIN/MAX)     | PS_GP_RSLT_PIN    |     1 |    83 |     3   (0)| 00:00:01 | 
|  17 |                 SORT AGGREGATE                |                   |     1 |    83 |            |          |       |     
|  18 |                  PARTITION RANGE SINGLE       |                   |     1 |    83 |   158  (99)| 00:00:01 |   KEY | 
|  19 |                   PARTITION LIST SINGLE       |                   |     1 |    83 |   158  (99)| 00:00:01 |   KEY | 
|  20 |                    FIRST ROW                  |                   |     1 |    83 |   158  (99)| 00:00:01 |       |     
|* 21 |                     INDEX RANGE SCAN (MIN/MAX)| PS_GP_RSLT_PIN    |     1 |    83 |   158  (99)| 00:00:01 |   KEY |
|* 22 |     INDEX RANGE SCAN                          | PSAGP_PIN         |     1 |    25 |     1   (0)| 00:00:01 |       |     
...
---------------------------------------------------------------------------------------------------------------------------

It would be much better if we started with the PS_GP_PIN table, looked up the PIN_NUM with the PIN_CODE (there is a suitable index on this column) and used the PIN_NUM value as a part of the lookup on PS_GP_RSLT_PIN (again PIN_CODE is in the unique index on that table).

It is tempting to add LEADING hints to the sub-queries, but such a hint does not work because the hint is not scoped to the sub-query and is considered to be invalid because the entire query cannot start at this point.

The only supported place in the query to add a LEADING hint would be the first query, in this case after the UPDATE keyword.

In this case, I have named the query blocks in the sub-queries with the QB_NAME hint. It is valid to put this hint into the sub-query. Then I added LEADING hints for each sub-query after the UPDATE keyword, but I specified their scope using the name of the sub-query specified in the QB_NAME hint. Each sub-query must now start with the PS_GP_PIN table.

UPDATE /*+LEADING(@SUB1 B@SUB1) LEADING(@SUB2 B@SUB2)*/ %Table(GPGB_EDIE_TMP) X
  SET X.GPGB_WK53_IND = ( 
 SELECT /*+QB_NAME(SUB1)*/ %TrimSubstr(%Sql(FUNCLIB_HR_CHAR,A.CALC_RSLT_VAL),1,2) 
  FROM PS_GP_RSLT_PIN A 
  ,PS_GP_PIN B 
 WHERE A.EMPLID = %Table(GPGB_EDIE_TMP).EMPLID 
...
   AND A.PIN_NUM = B.PIN_NUM 
   AND B.PIN_CODE = 'TAX VR PERIOD GBR' 
   AND A.SLICE_BGN_DT = ( 
 SELECT MAX(D.SLICE_BGN_DT) 
  FROM PS_GP_RSLT_PIN D 
 WHERE D.EMPLID = %Table(GPGB_EDIE_TMP).EMPLID 
...
   AND D.INSTANCE = A.INSTANCE 
   AND D.PIN_NUM = B.PIN_NUM) 
...
   )
 WHERE EXISTS ( 
 SELECT /*+QB_NAME(SUB2)*/ 'X' 
  FROM PS_GP_RSLT_PIN A1 
  ,PS_GP_PIN B1 
 WHERE A1.EMPLID = %Table(GPGB_EDIE_TMP).EMPLID 
...
   AND A1.PIN_NUM = B1.PIN_NUM 
   AND B1.PIN_CODE = 'TAX VR PERIOD GBR' 
...
   )
AND PROCESS_INSTANCE = %Bind(PROCESS_INSTANCE)

This is the new execution plan. The sub-query starts with an access of index PSAGP_PIN (which leads on PIN_CODE) on PS_GP_PIN, and then does the lookups on PS_GP_RSLT_PIN. The cost is the same, but the execution time was considerably reduced.

----------------------------------------------------------------------------------------------------
| Id  | Operation                                     | Name              | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| 
----------------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT                              |                   |     1 |    65 |   113M (93)| 39:44:51 |       |     
|   1 |  UPDATE                                       | PS_GPGB_EDIE_TMP4 |       |       |            |          |       |     
|*  2 |   FILTER                                      |                   |       |       |            |          |       |     
|*  3 |    TABLE ACCESS FULL                          | PS_GPGB_EDIE_TMP4 |   673K|    41M|  9967   (6)| 00:00:13 |       |
|   4 |    NESTED LOOPS                               |                   |     1 |   108 |     4   (0)| 00:00:01 |       |     
|*  5 |     INDEX RANGE SCAN                          | PSAGP_PIN         |     1 |    25 |     2   (0)| 00:00:01 |       |     
|   6 |     PARTITION RANGE SINGLE                    |                   |     1 |    83 |     2   (0)| 00:00:01 |   KEY | 
|   7 |      PARTITION LIST SINGLE                    |                   |     1 |    83 |     2   (0)| 00:00:01 |   KEY | 
|*  8 |       INDEX RANGE SCAN                        | PS_GP_RSLT_PIN    |     1 |    83 |     2   (0)| 00:00:01 |   KEY | 
|   9 |        SORT AGGREGATE                         |                   |     1 |    72 |            |          |       |     
|  10 |         PARTITION RANGE SINGLE                |                   |     1 |    72 |     3   (0)| 00:00:01 |   KEY | 
|  11 |          PARTITION LIST SINGLE                |                   |     1 |    72 |     3   (0)| 00:00:01 |   KEY | 
|* 12 |           INDEX RANGE SCAN                    | PS_GP_RSLT_PIN    |     1 |    72 |     3   (0)| 00:00:01 |   KEY | 
|  13 |            SORT AGGREGATE                     |                   |     1 |    83 |            |          |       |     
|  14 |             PARTITION RANGE SINGLE            |                   |     1 |    83 |     3   (0)| 00:00:01 |   KEY | 
|  15 |              PARTITION LIST SINGLE            |                   |     1 |    83 |     3   (0)| 00:00:01 |   KEY | 
|  16 |               FIRST ROW                       |                   |     1 |    83 |     3   (0)| 00:00:01 |       |     
|* 17 |                INDEX RANGE SCAN (MIN/MAX)     | PS_GP_RSLT_PIN    |     1 |    83 |     3   (0)| 00:00:01 |       |
|  18 |                 SORT AGGREGATE                |                   |     1 |    83 |            |          |       |     
|  19 |                  PARTITION RANGE SINGLE       |                   |     1 |    83 |   158  (99)| 00:00:01 |   KEY | 
|  20 |                   PARTITION LIST SINGLE       |                   |     1 |    83 |   158  (99)| 00:00:01 |   KEY | 
|  21 |                    FIRST ROW                  |                   |     1 |    83 |   158  (99)| 00:00:01 |       |     
|* 22 |                     INDEX RANGE SCAN (MIN/MAX)| PS_GP_RSLT_PIN    |     1 |    83 |   158  (99)| 00:00:01 |   KEY |
|  23 |   TABLE ACCESS BY LOCAL INDEX ROWID           | PS_GP_RSLT_PIN    |     1 |    86 |     3   (0)| 00:00:01 |       |
...
---------------------------------------------------------------------------------------------------------------------------

Wednesday, November 25, 2009

Controlling How PeopleSoft Cobol Collects Statistics

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 (Global Temporary Tables Share Statistics Across Sessions) 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 generated a data mover script to replace them with calls to my replacement package using the following SQL.script.

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 cannot use the LIKE operation on the long column.

Wednesday, October 28, 2009

Database Links and PeopleSoft

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.

Sunday, October 11, 2009

Aggregating & Purging Batch Timings

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.

Thursday, September 10, 2009

Identifying Application Engine Source Code

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.
Update 1st December 2014: However, this strategy is pretty much obsolete from PeopleTools 8.52 because Application Engine is instrumented. That is to say that the Application Engine name, section and step are set in the session's Module and Action attributes (see PeopleTools 8.52 Application Engine sets MODULE and ACTION), although there are some problems with DO SELECT and DO WHILE loops.

Tuesday, July 28, 2009

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

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 Events

Table 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 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.

Thursday, July 23, 2009

PeopleSoft and the Oracle Recycle Bin

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.

Tuesday, July 14, 2009

Oracle Terminated Connection Timeout

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).

Oracle Support 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 Oracle Support 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.

Monday, July 06, 2009

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

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.

Thursday, June 25, 2009

Controlling How %UpdateStats Collects Optimizer Statistics

I have written a number of entries on this blog about updating database statistics on tables during Application Engine processes.
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 Statistics History Retention in PeopleSoft

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.
  • SELECT dbms_stats.get_stats_history_retention FROM dual;
    GET_STATS_HISTORY_RETENTION 
    --------------------------- 
                             31
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 using dbms_stats.alter_stats_history_retention to set 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 sacrifice.
  • EXECUTE dbms_stats.alter_stats_history_retention(retention=>0);
  • 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.
If you have allowed the statistics history to grow before you disable history retention, then you might like to read John Hallas' posting on Purging statistics from the SYSAUX tablespace.

Tuesday, May 19, 2009

Manually Booting Tuxedo Application Server Processes in Parallel

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

Wednesday, May 13, 2009

Wednesday, April 22, 2009

Reducing Unnecessary Instances of Temporary Records

In a previous posting, I recommended moving temporary records, that are very frequently truncated 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 calls 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 has 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.

Monday, April 06, 2009

Statistics Management for Partitioned Objects in PeopleSoft

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

(Updated 11.7.2014) Last year, I wrote about Oracle Optimizer Statistics and Optimizer Dynamic Sampling with PeopleSoft Temporary 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-wide 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 sysadm.gfc_deletetemptablestats
AFTER INSERT ON sysadm.ps_aetemptblmgr
FOR EACH ROW
WHEN (new.curtempinstance > 0)
DECLARE
 PRAGMA AUTONOMOUS_TRANSACTION;
 l_table_name VARCHAR2(30) := '';
 l_last_analyzed DATE := '';
 l_stattype_locked VARCHAR2(5) := '';
 table_doesnt_exist EXCEPTION;
 PRAGMA EXCEPTION_INIT(table_doesnt_exist,-20001);
BEGIN
 SELECT r.table_name, t.last_analyzed
 INTO   l_table_name, l_last_analyzed
 FROM ( 
        SELECT r.recname
        ,      DECODE(r.sqltablename,' ','PS_'||r.recname,r.sqltablename)||:new.curtempinstance table_name
        FROM   psrecdefn r
        ) r
        LEFT OUTER JOIN user_tables t
        ON t.table_name = r.table_name
 AND t.temporary = 'N'
 WHERE  r.recname = :new.recname;

 SELECT s.stattype_locked
 INTO   l_stattype_locked
 FROM   user_tab_statistics s
 WHERE  s.table_name = l_table_name
 AND    s.object_type = 'TABLE';

 IF l_last_analyzed IS NOT NULL THEN --only delete statistics if they exist
  dbms_stats.delete_table_stats(ownname=>'SYSADM',tabname=>l_table_name,force=>TRUE);
 END IF;
 IF l_stattype_locked IS NULL THEN --stats need to be locked, 21,11,2009
  dbms_stats.lock_table_stats(ownname=>user,tabname=>l_table_name);
 END IF;
 
EXCEPTION
  WHEN no_data_found THEN NULL;
  WHEN table_doesnt_exist THEN NULL;
END;
/
show errorss

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

Thursday, April 02, 2009

Automatically Granting Privileges on Newly Created Tables (continued)

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.

Friday, March 13, 2009

Using Oracle Enterprise Manager (Grid Control) with PeopleSoft

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 update these values. Oracle’s Applications (that it has developed itself), such as E-Business Suite does 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 procedures 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:
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.
Screenshot from Oracle Enterprise Manager
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 nice if perhaps the Component name and PeopleCode context was written to MODULE and ACTION. But it isn’t.

Updated 9.9.11: PeopleTools 8.50 does exactly this, there is another posting on this subject.

Thursday, March 12, 2009

Minimum Number of Application Server Processes

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.

Thursday, March 05, 2009

Automatically Granting Privileges on Newly Created Tables

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.