PeopleTools 8.50 uses DBMS_APPLICATION_INFO to Identify Database Sessions

I recently worked on a PeopleTools 8.50 system in production for the first time and was able to make use of the new Oracle specific instrumentation in PeopleTools.

PeopleTools now uses the DBMS_APPLICATION_INFO package to set module and action session attributes.  This data is then copied into the Active Session History (ASH).

• Within the PIA, the application server sets module and action to the name of the current component and page within the current components
• For Integration Broker messages they are set to service and queue name.
• For Application Engine processes the module is set to PSAE and the action is set to the program name.

The first two of these three changes are very welcome, but I think the instrumentation of Application Engine is inadequate. I have a better suggestion which can be implemented with a database trigger.

Enterprise Manager
In Oracle Enterprise Manager, I can see the top SQL statements and group them by Module and Action, so I now can see which components are consuming the most time.  I can also produce an AWR for a specific component or page within a component.

OEM Screenshot of a PeopleTools 8.50 system.

I can query the ASH data to profile which pages consume the most time on the database.

MODULE                 ACTION                   ASH_SECS 
---------------------- ---------------------- ---------- 
RECV_PO                PO_PICK_ORDERS                240 
XXX_REQ_INQUIRY        xyzzy                         170 
XXX_REQ_WRKLST         XXX_REQ_WORKLIST              170 
VCHR_EXPRESS           VCHR_LINE_RECV_WRK            170 
XXX_FIN_WORKLIST       XXX_FIN_WORKLIST              160 
VCHR_EXPRESS           VCHR_EXPRESS1                 160 
PURCHASE_ORDER_EXP     PO_EXPRESS                    140 
XXX_HOME_PAGE          XXX_HOME_PAGE                 140 
RECV_PO                RECV_WPO                      130 
VCHR_EXPRESS           xyzzy                         120 
XXX_PUR_WORKLIST       XXX_PUR_WRKLST                120 
CDM_RPT                CDM_RPT_INDEX                 100 
…
----------
sum                                                  2820

(Updated 19.11.2010) For some components the action is set to ‘xyzzy’. This seems to be a default value set when the component is opened, but before any of the pages are processed.  Therefore, it refers to activity in the search dialogue, including processing of :

• look ups to obtain values for search criteria
• SQL issued during SearchSave PeopleCode to validate the search criteria.
• the query on the Component Search record

Batch Processes
Now, I want to look at what happens in batch processes. In previous versions of PeopleTools, the module was set to the same value as the program name, and action was left blank. This is not particularly helpful. In 8.50 module is set to PSAE for Application Engine processes, and action is set to the name of the program.

MODULE                       ACTION                      ASH_SECS
---------------------------- ------------------------- ----------
PSNVS.EXE                                                   10220
PSAE                         AP_PSTPYMNT                     3100
PSAE                         AP_MATCH                        2690
sqrw.exe                                                     1770
PSAE                         PO_RECVACCR                     1390
PSQRYSRV.exe                                                  880
PSAE                         FS_STREAMLN                      870
PSPUBDSP.exe                                                  850
PSBRKDSP.exe                                                  740
PSPRCSRV.exe                                                  690
PSSUBDSP.exe                                                  620

This is certainly better than in previous versions. However, its weakness is that if I have multiple concurrent instances of the same process, although I could tell that the ASH data had come from different sessions, I would not be determine which came from which session. This situation could occur, for example, in Global Payroll if ‘streaming’ was configured, where the payroll calculation can be broken into many processes that run concurrently.

In another blog posting, Using Oracle Enterprise Manager (Grid Control) with PeopleSoft, I proposed a trigger that writes name of the PeopleSoft process name to module, and the Process Instance number into action. Furthermore, this trigger works for all processes scheduled by the Process Scheduler.  I think that that trigger is still useful in PeopleTools 8.50.

Performance Overhead of Multiple SQL calls in SQR

I was asked to look at a fairly simple SQR program that reported on payroll data. It pivoted data for each employee on which it reported. It makes 21 calls to two procedures that each obtain a value by summing data across different sets of data in one of two payroll result tables.

The ASH data shows that most of the database time, 184 out of 192 seconds is spent in the two statements that aggregate that data. These statements are in the procedures that are called within the driving query.

SQL_ID        SQL_PLAN_HASH_VALUE  EXEC_SECS   ASH_SECS
------------- ------------------- ---------- ----------
515d3buvaf8us          1162018321        615        133
55a20fnkwv0ht          3972836246        615         51
...
                                             ----------
sum                                                 192

However, more significantly, only 192 seconds out of a total elapsed run time of 615 seconds is spent in the database. That is just 31%. So most of the time is spent executing code within the SQR program.

We need to look at the code to see exactly what is happening.

This is the driving query. It returns each employee who is paid in a specified payroll, and then for each row the procedure get_gp_acum is used to fetch the sum of certain accumulators for that payroll and employee

Begin-Select
a.emplid
a.cal_run_id
a.cal_id
a.gp_paygroup
a.orig_cal_run_id
a.rslt_seg_num
a.tax_code_uk
a.gpgb_ni_categorye.
n.name

  Let $pin_name = 'GBR AC GRTX SEG'  !Taxable Gross 
  do get_gp_acum
  Let #Taxable_gross = &b.calc_rslt_val
  
  Let $pin_name = 'GBR AC NIBL SEG'  !Nlable Gross
  do get_gp_acum
  Let #Niable_gross1 = &b.calc_rslt_val
  
…
from ps_gpgb_ee_rslt a, ps_person_name n
where a.emplid = n.emplid
and a.cal_run_id = $PNL_CAL_RUN_ID1
and a.empl_rcd = 0
and a.gp_paygroup = $PNL_PYGRP1
and a.cal_id = $PNL_CAL_ID1
and a.orig_cal_run_id = a.cal_run_id
order by a.emplid,a.gp_paygroup
End-Select
End-Procedure

This is one of the two procedures that is called to obtain each value.  It simply sums the data for that employee.

Begin-Procedure get_gp_acum
begin-select
sum(b.calc_rslt_val) &b.calc_rslt_val
from ps_xx_gpacum_rpt1 b, ps_gp_pin c
where b.emplid    = &a.emplid
and b.empl_rcd    = 0
and b.cal_run_id  = &a.cal_run_id
and b.gp_paygroup = &a.gp_paygroup
and b.cal_id      = &a.cal_id
and b.orig_cal_run_id = &a.orig_cal_run_id
and b.rslt_seg_num    = &a.rslt_seg_num  
and b.orig_cal_run_id = b.cal_run_id
and b.pin_num = c.pin_num
and c.pin_nm = $pin_name
end-select
End-Procedure

This code is very clear, well structured, and easy to maintain. The only trouble is that it is slow. Each SQL calls makes SQR do a lot of work, and that takes time.

In this case there was not much procedural code in the SQR and so I was able to coalesce the SQL from the called procedures into the driving query.

If the called procedures had been simple single row look-ups I could have used an outer-join. However, as they are using a group function (sum), I put the query into a scalar query (a query within in the select clause that returns only one row and one column). Each call to a procedure was replaced with a separate scalar query. I ended up with 21 scalar queries.

During this rewrite I encountered an SQR quirk; if the scalar query was placed in the main select clause, SQR produces errors because it is expecting an expression, and it complains that the SELECT keyword is not a variable. I then had to wrap the query in an in-line view. Each scalar query must be given a column alias, and the column alias can be referenced in the SQR select clause.

Begin-Procedure MAIN-REPORT
Begin-Select
a.emplid
a.cal_run_id
a.cal_id
a.gp_paygroup
a.orig_cal_run_id
a.rslt_seg_num
a.tax_code_uk
a.gpgb_ni_category
n.name

a.gbr_ac_grtx_seg
 Let #Taxable_gross = &a.gbr_ac_grtx_seg

a.gbr_ac_nibl_seg
 Let #Niable_gross1 = &a.gbr_ac_nibl_seg

…  
from (
select a.emplid, a.cal_run_id, a.cal_id, a.gp_paygroup, a.orig_cal_run_id, a.rslt_seg_num, a.tax_code_uk, a.gpgb_ni_category
,NVL((SELECT sum(b.calc_rslt_val) from ps_xx_gpacum_rpt1 b, ps_gp_pin c 
      where b.emplid = a.emplid and b.empl_rcd = 0 and b.cal_run_id = a.cal_run_id 
      and b.gp_paygroup = a.gp_paygroup and b.cal_id = a.cal_id 
      and b.orig_cal_run_id = a.orig_cal_run_id and b.rslt_seg_num = a.rslt_seg_num 
      and b.orig_cal_run_id = a.cal_run_id and b.pin_num = c.pin_num 
      and c.pin_nm = 'GBR AC GRTX SEG'),0) gbr_ac_grtx_seg
,NVL((SELECT sum(b.calc_rslt_val) from ps_xx_gpacum_rpt1 b, ps_gp_pin c 
      where b.emplid = a.emplid and b.empl_rcd = 0 and b.cal_run_id = a.cal_run_id 
      and b.gp_paygroup = a.gp_paygroup and b.cal_id = a.cal_id 
      and b.orig_cal_run_id = a.orig_cal_run_id and b.rslt_seg_num = a.rslt_seg_num 
      and b.orig_cal_run_id = a.cal_run_id and b.pin_num = c.pin_num 
      and c.pin_nm = 'GBR AC NIBL SEG'),0) gbr_ac_nibl_seg
…
from ps_gpgb_ee_rslt a
where a.empl_rcd = 0
and a.orig_cal_run_id = a.cal_run_id
) a
,ps_person_name n
where a.cal_run_id = $PNL_CAL_RUN_ID1
and a.gp_paygroup = $PNL_PYGRP1
and a.cal_id = $PNL_CAL_ID1
and a.emplid = n.emplid
order by a.emplid,a.gp_paygroup
End-Select

The SQL looks much more complicated, as does the execution plan.  However, the effect on performance was dramatic.

SQL_ID        SQL_PLAN_HASH_VALUE  EXEC_SECS   ASH_SECS
------------- ------------------- ---------- ----------
f9d03ffbftv81          1694704409        154        114
5d2x9mqvvyrjk           989254841        154          2
3v550ghn6z8jv          1521271881        154          1
                                             ----------
sum                                                 117

The response of just the combined SQL at 117 seconds is better than the separate SQLs at 154 seconds. Much more significantly, the amount of time spent in SQR (rather than the database) has fallen from 432 seconds to just 37. Therefore, 90% of the SQR response time was spent on submitting the SQL calls in the called procedures.

Conclusions

SQL calls in SQR are expensive. The cost of making lots of calls inside a loop or another driving query can add up to a significant amount of time. SQRs that consume time in this way will also be consuming CPU and memory on the server where the Process Scheduler is located. 

In this case, combining SQL statements also improved SQL performance, but that will not always be the case.

There are times when better performance can be achieved at the cost of more convoluted code. In each case there is a judgement to be made as to whether improvement in performance is worth the increase in complexity.

PeopleSoft Run Control Purge Utility

Run Control records are used to pass parameters into processes scheduled processes. These tables tend to grow, and are rarely purged. Generally, once created a run control is not deleted.  When operator accounts are deleted, the Run Controls remain, but are no longer accessible to anyone else.

I have worked on systems where new Run Controls, whose IDs contain either a date or sequence number, are generated for each process. The result is that the Run Control tables, especially child tables, grow quickly and if not regularly managed will become very large. On one system, I found 18 million rows on one table!

RECNAME         FIELDNAME            NUM_ROWS     BLOCKS
--------------- ------------------ ---------- ----------
TL_RUN_CTRL_GRP RUN_CNTL_ID          18424536     126377
AEREQUESTPARM   RUN_CNTL_ID           1742676      19280
AEREQUESTTBL    RUN_CNTL_ID            333579       3271
XPQRYRUNPARM    RUN_CNTL_ID            121337       1630
TL_TA_RUNCTL    RUN_CNTL_ID            112920        622
…

I have written a simple Application Engine process, GFC_RC_ARCH, that purges old Run Controls from these tables.  It can be downloaded from my website.

Run Control records are easily identified. They are characterised by:

• the first column of these tables is always OPRID, and the second is either RUNCNTLID or RUN_CNTL_ID,
• these two columns are also the first two columns of the unique key,
• the Run Control tables appear on pages of components that are declared as the process security component for that process.

I have decided that if the combination of OPRID and RUN_CNTL_ID does not appear in the process scheduler request table, PSPRCSRQST, then the Run Control record should be deleted. Thus, as the delivered Process Scheduler Purge process, PRCSPURGE, deletes rows from the Process Scheduler tables, so my purge process will delete rows from the Run Control tables.

I have chosen to make these two Application Engine processes mutually exclusive, so the Process Scheduler will not run both at the same time, but that configuration cannot be delivered in an Application Designer project.

Announcing the Co-Operative PeopleTools Table Reference

Update 20.6.2019: I have published on Github the code used to generate the PeopleTools Table reference described here.  The element of co-operation is should be easier via updates to the metadata scripts in Github.  See blog post PeopleTools Table Reference Generator.

I have created a reference to the PeopleTools tables and views on my website. 

In the course of my work on PeopleSoft, I spend a lot of time looking at the PeopleTools tables. They contain meta-data about the PeopleSoft application. Much of the application is stored in various tables that are maintained by Application Designer. Some tables provide information about the Data Model. Others contain configuration data that is maintained via PeopleTools components in the PIA.

Many of my utility scripts query information from PeopleTools tables and some also update them. Of course, that is strictly not supported, but if you understand how the tables fit together it can be done relatively safely.  So, it is very helpful to be able to understand what is in these tables.

In PeopleSoft for the Oracle DBA, I discussed some of the PeopleTools tables that are of regular interest. I included the tables that correspond to the database catalogue, and I discussed what happens during the PeopleSoft login procedure, submission of process requests to the Process Scheduler and PS/Query. The tables that are maintained by the process scheduler are valuable because they contain information about who ran what process when, and how long they ran.

I am not the only person to have started to document the PeopleTools tables on their website or blog, most people have picked a few tables that are of particular interest. However, I want to tackle the problem in a slightly different way. There are over 3000 PeopleTools tables and views (as defined by the PeopleTools object security group in PSOBJGROUP). Tackling all of them manually would be a monumental task.

Nevertheless, I do want a complete reference. So, I have written code to dynamically generate a page for each PeopleTools table and view, and I have put as much information about these records as I can find in the PeopleTools tables themselves. Reference to related objects, including objects referenced in the text of views, appear as links to those pages.

I have started to manually add my own annotation to the generated pages.  So far I have only added descriptions to a few tables (marked with an asterisk). However, I would like to make this a collaborative project, and I have already had updates to some pages.

• There is a page for each PeopleTools table and view. If you save that page, add descriptions, and return it to me, I will upload it to the site.
• You can also add links to related websites and blog pages to your entries.
• Please put your name and, if you wish, a link to your website to the bottom of the pages you author.
• Let me know if you think you have found a mistake. 

I hope you find it useful.

Configuring Large PeopleSoft Application Servers

Occasionally, I see very large PeopleSoft systems running on large proprietary Unix servers with many CPUs.  In an extreme case, I needed to configure application server domains with up to 14 PSAPPSRV processes per domain (each domain was on a virtual server with 8 CPU cores, co-resident with the Process Scheduler).

The first and most important point to make is don't have too many server processes.  If you run out of CPU or if you fully utilise all the physical memory and start to page memory from disk, then you have too many server processes.  It is better to queue on a Tuxedo queue rather than the CPU run queue, or disk queue during paging.

Multiple APPQ/PSAPPSRV Queues

A piece of advice that I originally got from BEA (prior to their acquisition by Oracle) was that you should not have more than 10 server processes on a single queue in Tuxedo.  Otherwise, you are likely to suffer from contention on the IPC queue structure because processes must acquire exclusive access to the queue in order to enqueue a service request to the queue or dequeue a request from it.  Instead multiple queues should be configured that are both serviced by the same server processes and so advertise the same services. 

If you look at the 'large' template delivered by PeopleSoft, you will see that it produces a domain that runs between 9 and 15 PSAPPSRV processes.  This does not conform to the advice I received from BEA.  I repeated this advice in PeopleSoft for the Oracle DBA.  Though I cannot now find the source for it, I stand by it.  I have recently been able to conduct some analysis to confirm it on a real production system.  Domains with two queues of 8 PSAPPPSRV server process each out performed domains with only a single queue.

Load Balancing Across Queues

If the same service is advertised on multiple queues, then Tuxedo recommended that you should specify realistic service loads and use Tuxedo load balancing to determine where to enqueue requests.  I want to emphasise that I am talking about load balancing across queues within a Tuxedo domain, and not about load balancing across Tuxedo domains in the web server.

This is what the Tuxedo documentation says about load balancing:

"Load balancing is a technique used by the BEA Tuxedo system for distributing service requests evenly among servers that offer the same service. Load balancing avoids overburdening some servers while leaving others idle or infrequently used. Before sending a request to a service routine, the BEA Tuxedo system identifies all servers capable of handling the request and selects the one most appropriate for maintaining a balanced load across all the servers in the configuration.


You can control whether a load-balancing algorithm is used on the system as a whole. Such as algorithm should be used only when necessary, that is, only when a service is offered by servers that use more than one queue. Services offered by only one server, or by multiple servers in a Multiple Server, Single Queue (MSSQ) do not need load balancing. The LDBAL parameter for these services should be set to N. In other cases, you may want to set LDBAL to Y."

It doesn't state that load balancing is mandatory for multi-queue domains, and only hints that it might improve performance.  If load balancing is not used, the listener process puts the messages on the first empty queue (one where no requests are queued).  If all queues have requests the listener round-robins between the queues.

You could consider giving ICScript, GetCertificate and other services with small service times a higher Tuxedo Service priority.  This means they jump the queue 9 times out of 10.  ICScript is generally used during navigation, GetCertificate is used at log on.  Giving these services higher priority will mean they perform well even when the system is busy.  Users often need to do several mouse clicks to navigate around the system, but these services are usually quick.  This will improve the user experience without changing the overall performance of the system.

Data

I have recently been able to test the performance of a domains with up to 14 PSAPPSRVs on a single IPC queue, versus domains with two queues with up to 7 PSAPPSRVs each, both with and without Tuxedo queue balancing.   These results come from a real production system where the multiple queue configuration was implemented on 2 of the 4 application servers.  The system has a short-lived weekly peak period of on-line processing.  During that time Tuxedo spawns additional PSAPPSRV processes, and so I get different sets of times for different numbers of process. 

The timings are produced from transactions sampled by PeopleSoft Performance Monitor.  I capture the number of spawned processes using the Tuxmon scripts on my website that use tmadmin to collect Tuxedo metrics.

1 Queue			2 Queue
Server Processes per Queue	Number of Services	Mean ICPanel Service Time	Server Processes per Queue	Number of Services	Mean ICPanel Service Time
6	2,616	1.33	3	6945	1.05
7	1,949	0.97
8	1,774	1.06	4	7595	1.16
9	1,713	1.02
10	1,553	1.25	5	4629	1.17
11	1,250	1.30
12	969	1.32	6	3397	1.16
13	427	1.21
14	1,057	1.10	7	3445	1.13
Total	13,308	1.17		26011	1.13

The first thing to acknowledge is that this data is quite noisy because it comes from a real production system, and the effects we are looking for are quite small.

I am satisfied that the domains with two PSAPPSRV queues generally perform better under high load, than those under 1.  Not only does the queue time increase on the single queue domain, the service time also increases.

However, I cannot demonstrate that Tuxedo Load Balancing makes a significant difference in either direction.

My results suggest that domains with multiple queues for requests handled by PSAPPSRV process perform slightly better without load balancing if there is no queue of requests, but perform slightly better if there is a queue of pending requests.  However, the difference is small.  It is not large enough to be statistically significant in my test data.

Conclusion

If you have a busy system with lots of on-line users, and sufficient hardware to resource it, then you might reach a point when you need more than 10 PSAPPSRVs.  In which case, I recommend that you configure multiple Tuxedo queues.

On the whole, I would recommend that Tuxedo Load Balancing should be configured.  I would not expect it to improve performance, but it will not degrade it either.