Friday, September 04, 2015

Measuring Tuxedo Queuing in the PeopleSoft Application Server


Why Should I Care About Queuing?

Queuing in the application server is usually an indicator of a performance problem, rather than a problem in its own right.  Requests will back up on the inbound queue because the application server cannot process them as fast as they arrive.  This is usually seen on the APPQ which is serviced by the PSAPPSRV process, but applies to other server processes too.  Common causes include (but are not limited to):
  • Poor performance of either SQL on the database or PeopleCode executed within the application server is extending service duration
  • The application server domain is undersized for the load.  Increasing the number of application server domains or application server process may be appropriate.  However, before increasing the number of server process it is necessary to ensure that the physical server has sufficient memory and CPU to support the domain (if the application server CPU is overloaded then requests move from the Tuxedo queues to the operating system run queue).
  • The application server has too many server processes per queue causing contention in the systems calls that enqueue and dequeue requests to and from IPC queue structure.  A queue with more than 8-10 application server processes can exhibit this contention.  There will be a queue of inbound requests, but not all the server processes will be non-idle.
When user service requests spend time queuing in the application server, that time is part of the users' response time.  Application server queuing is generally to be avoided (although it may be the least worst alternative). 
What you do about queuing depends on the circumstances, but it is something that you do want to know about.

3 Ways to Measure Application Server Queuing

There are a number of ways to detect queuing in Tuxedo
  • Direct measurement of the Tuxedo domain using the tmadmin command-line interface.  A long time ago I wrote a shell script tuxmon.sh.  It periodically runs the printqueue and printserver commands on an application server and extracts comma separated data to a flat that can then be loaded into a database.  It would have to be configured for each domain in a system.
  • Direct Measurement with PeopleSoft Performance Monitor (PPM).  Events 301 and 302 simulate the printqueue and printserver commands.  However, event 301 only works from PT8.54 (and at the time of writing I am working on a PT8.53 system).  Even then, the measurements would only be taken once per event cycle, which defaults to every 5 minutes.  I wouldn't recommend increasing the sample frequency, so this will only ever be quite a coarse measurement.
  • Indirect Measurement from sampled PPM transactions.  Although includes time spent on the return queue and to unpack the Tuxedo message.  This technique is what the rest of this article is about.

Indirectly Measuring Application Server Queuing from Transactional Data

Every PIA and Portal request includes a Jolt call made by the PeopleSoft servlet to the domain.  The Jolt call is instrumented in PPM as transaction 115.  Various layers in the application server are instrumented in PPM, and the highest point is transaction 400 which where the service enters the PeopleSoft application server code.  Transaction 400 is always the immediate child of transaction 115.  The difference in the duration of these transactions is the duration of the following operations:
  • Transmit the message across the network from the web server to the JSH.  There is a persistent TCP socket connection.
  • To enqueue the message on the APPQ queue (including writing the message to disk if it cannot fit on the queue).
  •  Time spent in the queue
  • To dequeue the message from the queue (including reading the message back from disk it was written there).
  • To unpack the Tuxedo message and pass the information to the service function
  • And then repeat the process for the return message back to the web server via the JSH queue (which is not shown  in tmadmin)
I am going make an assumption that the majority of the time is spent by message waiting in the inbound queue and that time spent on the other activities is negligible.  This is not strictly true, but is good enough for practical purposes.  Any error means that I will tend to overestimate queuing.
Some simple arithmetic can convert this duration into an average queue length. A queue length of n means that n requests are waiting in the queue.  Each second there are n seconds of queue time.  So the number of seconds per second of queue time is the same as the queue length. 
I can take all the sampled transactions in a given time period and aggregate the time spent between transactions 115 and 400.  I must multiply it by the sampling ratio, and then divide it by the duration of the time period for which I am aggregating it.  That gives me the average queue length for that period.
This query aggregates queue time across all application server domains in each system.  It would be easy to examine a specific application server, web server or time period.
WITH c AS (
SELECT B.DBNAME, b.pm_sampling_rate
,      TRUNC(c115.pm_agent_Strt_dttm,'mi') pm_agent_dttm
,      A115.PM_DOMAIN_NAME web_domain_name
,      SUBSTR(A400.PM_HOST_PORT,1,INSTR(A400.PM_HOST_PORT,':')-1) PM_tux_HOST
,      SUBSTR(A400.PM_HOST_PORT,INSTR(A400.PM_HOST_PORT,':')+1) PM_tux_PORT
,      A400.PM_DOMAIN_NAME tux_domain_name
,      (C115.pm_trans_duration-C400.pm_trans_duration)/1000 qtime
FROM   PSPMAGENT A115 /*Web server details*/
,      PSPMAGENT A400 /*Application server details*/
,      PSPMSYSDEFN B
,      PSPMTRANSHIST C115 /*Jolt transaction*/
,      PSPMTRANSHIST C400 /*Tuxedo transaction*/
WHERE  A115.PM_SYSTEMID = B.PM_SYSTEMID 
AND    A115.PM_AGENT_INACTIVE = 'N'
AND    C115.PM_AGENTID = A115.PM_AGENTID
AND    C115.PM_TRANS_DEFN_SET=1
AND    C115.PM_TRANS_DEFN_ID=115
AND    C115.pm_trans_status = '1' /*valid transaction only*/
--
AND    A400.PM_SYSTEMID = B.PM_SYSTEMID 
AND    A400.PM_AGENT_INACTIVE = 'N'
AND    C400.PM_AGENTID = A400.PM_AGENTID
AND    C400.PM_TRANS_DEFN_SET=1
AND    C400.PM_TRANS_DEFN_ID=400
AND    C400.pm_trans_status = '1' /*valid transaction only*/
--
AND    C115.PM_INSTANCE_ID = C400.PM_PARENT_INST_ID /*parent-child relationship*/
AND    C115.pm_trans_duration >= C400.pm_trans_duration
), x as (
SELECT dbname, pm_agent_dttm
,      AVG(qtime) avg_qtime
,      MAX(qtime) max_qtime
,      c.pm_sampling_rate*sum(qtime)/60 avg_qlen
,      c.pm_sampling_rate*count(*) num_services
GROUP BY dbname, pm_agent_dttm, pm_sampling_rate
)
SELECT * FROM x
ORDER BY dbname, pm_agent_dttm
  • Transactions are aggregated per minute, so the queue time is divided by 60 at the end of the calculation because we are measuring time in seconds.
Then the results from the query can be charted in excel (see http://www.go-faster.co.uk/scripts.htm#awr_wait.xls). This chart was taken from a real system undergoing a performance load test, and we could see


Is this calculation and assumption reasonable?

The best way to validate this approach would be to measure queuing directly using tmadmin.  I could also try this on a PT8.54 system where event 301 will report the queuing.  This will have to wait for a future opportunity.
However, I can compare queuing with the number of busy application servers at reported by PPM event 302 for the CRM database.  Around 16:28 queuing all but disappears.  We can see that there were a few idle application servers which is consistent with the queue being cleared.  Later the queuing comes back, and most of the application servers are busy again.  So it looks reasonable.
Application Server Activity

Tuesday, March 10, 2015

PeopleTools 8.54: Performance Performance Monitor Enhancements

This is part of a series of articles about new features and differences in PeopleTools 8.54 that will be of interest to the Oracle DBA.

Transaction History Search Component

There are a number of changes:
  • You can specify multiple system identifiers.  For example, you might be monitoring Portal, HR and CRM.  Now you can search across all of them in a single search.
    • It has always been the case that when you drill into the Performance Monitoring Unit (PMU), by clicking on the tree icon, you would see the whole of a PMU that invoked services from different systems.
  • You can also specify multiple transaction types, rather than have to search each transaction type individually.
This is a useful enhancement when searching for a specific or a small number of transaction.  However, I do not think it will save you from having to query the underlying transactions table.

PPM Archive Process

The PPM archive process (PSPM_ARCHIVE) has been significantly rewritten in PeopleTools 8.54.  In many places, it still uses this expression to identify rows to be archived or purged:
%DateTimeDiff(X.PM_MON_STRT_DTTM, %CurrentDateTimeIn) >= (PM_MAX_HIST_AGE * 24 * 60)
This expands to
ROUND((CAST(( CAST(SYSTIMESTAMP AS TIMESTAMP)) AS DATE) - CAST((X.PM_MON_STRT_DTTM) AS DATE)) * 1440, 0)
   >= (PM_MAX_HIST_AGE * 24 *  60)
which has no chance of using an index.  This used to cause performance problems when the archive process had not been run for a while and the high water marks on the history tables had built up.

Now, the archive process now works hour by hour, and this will use the index on the timestamp column.
"... AND X.PM_MON_STRT_DTTM <= SYSDATE - PM_MAX_HIST_AGE 
and (PM_MON_STRT_DTTM) >= %Datetimein('" | DateTimeValue(&StTime) | "')
and (PM_MON_STRT_DTTM) <= %DateTimeIn('" | DateTimeValue(&EndTime) | "')"

Tuxedo Queuing

Since Performance Monitor was first introduced, event 301 has never reported the length of the inbound message queues in Tuxedo.  The reported queue length was always zero.  This may have been fixed in PeopleTools 8.53, but I have only just noticed it

Java Management Extensions (JMX) Support

There have been some additions to Performance Monitor that suggest that it will be possible to extract performance metrics using JMX.  The implication is that the Oracle Enterprise Manager Application Management Pack of PeopleSoft will be able to do this.  However, so far I haven't found any documentation. The new component is not mentioned in the PeopleTools 8.54: Performance Monitor documentation.
  • New Table
    • PS_PTPMJMXUSER - keyed on PM_AGENTID
  • New Columns
    • PSPMSYSDEFAULTS - PTPHONYKEY.  So far I have only seen it set to 0.
    • PSPMAGENT - PM_JMX_RMI_PORT.  So far only seen it set to 1
  • New Component

Wednesday, March 04, 2015

Undocumented Application Engine Parameter: EnableAEMonitoring

This is part of a series of articles about new features and differences in PeopleTools 8.54 that will be of interest to the Oracle DBA.

Oracle Support Document 1640355.1: E-AE: Performance Degradation When Using Do Loop Action to Process Large Amount of Data on PT 8.52 & PT 8.53 describes a performance problem in Application Engine when a small but frequently executed loop.  Application Engine calls DBMS_APPLICATION_INFO to set MODULE and ACTION on v$session each time it goes round the loop.

However, Oracle Bug 10130415  Latch contention on "resmgr group change latch" acknowledges a problem in some versions of Oracle, but it is resolved in 11.2.0.3 and 12c.
Updated 8 May 2015: Oracle support do not know of any explicit link to this database bug.

A new parameter was introduced in PT8.54 and backported to PT8.52.23 and PT8.53.13 in the Database Options section of the process scheduler configuration file (psprcs.cfg).  EnableAEMonitor controls whether Application Engine calls DBMS_APPLICATION_INFO.  The default value for this parameter is 0.

The PeopleSoft support document does not reference the database bug report, but it seems reasonable to infer that the new parameter was introduced to work around the database bug.

This new parameter is not described in PeopleBooks.  It does appear in the delivered configuration files on at least 8.53.13.  However, it is not present in the delivered 8.54.05 configuration file (bug 21055140). Therefore, by default, Application Engine will not set the module and Action unless you add it to the configuration file.

[Database Options]
;=========================================================================
; Database-specific configuration options
;=========================================================================
…
;DMK - added to enable DBMS_APPLICATION_INFO instrumentation
EnableAEMonitoring=1
Then the behaviour is then as it has been since 8.52, described in PeopleTools 8.52 Application Engine sets MODULE and ACTION.

My Recommendation 

I certainly think that you should add this parameter to all process scheduler configuration files at relevant PeopleTools version.  Unless you specifically have the problem described in the support note, I recommend that you also set the parameter to 1 as shown above. I have never seen the problem in affected database versions, and it is fixed in the terminal release of 11g.

Without setting the parameter, you will loose the ability to relate Enterprise Manager and ASH data to specific application engine steps.  If you need to make a code change to achieve a performance improvement you will have to go through the manual process of finding the SQL in an application engine trace.

Wednesday, February 25, 2015

PeopleTools 8.54: Oracle Resource Manager

This is part of a series of articles about new features and differences in PeopleTools 8.54 that will be of interest to the Oracle DBA.

Oracle Resource manager is about prioritising one database session over another, or about restricting the overhead of one session for the good of the other database users.  A resource plan is a set of rules that are applied to some or all database sessions for some or all of the time.  Those rules may be simple or complex, but they need to reflect the business's view of what is most important. Either way Oracle resource manager requires careful design.
I am not going to attempt to further explain here how the Oracle feature works, I want to concentrate on how PeopleSoft interfaces with it.

PeopleTools Feature

This feature effectively maps Oracle resource plans to PeopleSoft executables.  The resource plan will then manage the database resource consumption of that PeopleSoft process.  There is a new component that maps PeopleSoft resource names to Oracle consumer groups.  For this example I have chosen some of the delivered plans in the MIXED_WORKLOAD_GROUP that is delivered with Oracle 11g.

  • The Oracle Consumer Group field is validated against the name of the Oracle consumer groups defined in the database, using view     .
SELECT DISTINCT group_or_subplan, type
FROM dba_rsrc_plan_directives
WHERE plan = 'MIXED_WORKLOAD_PLAN'
ORDER BY 2 DESC,1
/

GROUP_OR_SUBPLAN               TYPE
------------------------------ --------------
ORA$AUTOTASK_SUB_PLAN          PLAN
BATCH_GROUP                    CONSUMER_GROUP
INTERACTIVE_GROUP              CONSUMER_GROUP
ORA$DIAGNOSTICS                CONSUMER_GROUP
OTHER_GROUPS                   CONSUMER_GROUP
SYS_GROUP                      CONSUMER_GROUP
If you use Oracle SQL Trace on a PeopleSoft process (in this case PSAPPSRV) you find the following query.  It returns the name of the Oracle consumer group that the session should use.The entries in the component shown above are stored in PS_PT_ORA_RESOURCE
  • PS_PTEXEC2RESOURCE is another new table that maps PeopleSoft executable name to resource name.
SELECT PT_ORA_CONSUMR_GRP 
FROM   PS_PT_ORA_RESOURCE
,      PS_PTEXEC2RESOURCE 
WHERE  PT_EXECUTABLE_NAME = 'PSAPPSRV' 
AND    PT_ORA_CONSUMR_GRP <> ' ' 
AND    PS_PT_ORA_RESOURCE.PT_RESOURCE_NAME = PS_PTEXEC2RESOURCE.PT_RESOURCE_NAME

PT_ORA_CONSUMR_GRP
------------------------
INTERACTIVE_GROUP

And then the PeopleSoft process explicitly switches its group, thus:
DECLARE 
  old_group varchar2(30); 
BEGIN 
  DBMS_SESSION.SWITCH_CURRENT_CONSUMER_GROUP('INTERACTIVE_GROUP', old_group, FALSE); 
END;
Unfortunately, the consequence of this explicit switch is that it overrides any consumer group mapping rules, as I demonstrate below.

Setup

The PeopleSoft owner ID needs some additional privileges if it is to be able to switch to the consumer groups.
BEGIN
  DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SYSTEM_PRIVILEGE
  ('SYSADM', 'ADMINISTER_RESOURCE_MANAGER',FALSE);
END;

BEGIN
  FOR i IN(
    SELECT DISTINCT r.pt_ora_consumr_grp
    FROM   sysadm.ps_pt_ora_resource r
    WHERE  r.pt_ora_consumr_grp != ' '
    AND    r.pt_ora_consumr_grp != 'OTHER_GROUPS'
  ) LOOP
    dbms_output.put_line('Grant '||i.pt_ora_consumr_grp);
    DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP 
    (GRANTEE_NAME   => 'SYSADM'
    ,CONSUMER_GROUP => i.pt_ora_consumr_grp
    ,GRANT_OPTION   => FALSE);
  END LOOP;
END;
/

The RESOURCE_MANAGER_PLAN initialisation parameters should be set to the name of the plan which contains the directives.
NAME                                 TYPE        VALUE
------------------------------------ ----------- ----------------------
resource_manager_plan                string      MIXED_WORKLOAD_PLAN

I question one or two of the mappings on PS_PTEXEC2RESOURCE.
SELECT * FROM PS_PTEXEC2RESOURCE …

PT_EXECUTABLE_NAME               PT_RESOURCE_NAME
-------------------------------- -----------------
…
PSAPPSRV                         APPLICATION SERVE
PSQED                            MISCELLANEOUS
PSQRYSRV                         QUERY SERVER
…
  • PSNVS is the nVision Windows executable.  It is in PeopleTools resource MISCELLANEOUS.  This is nVision running in 2-tier mode.  I think I would put nVision into the same consumer group as query.  I can't see why it wouldn't be possible to create new PeopleSoft consumer groups and map them to certain executables.  nVision would be a candidate for a separate group. 
    • For example, one might want to take a different approach to parallelism in GL reporting having partitioned the LEDGER tables by FISCAL_YEAR and ACCOUNTING_PERIOD
  • PSQED is also in MISCELLANEOUS.  Some customers use it to run PS/Query in 2-tier mode, and allow some developers to use it to run queries.  Perhaps it should also be in the QUERY SERVER group.

Cannot Mix PeopleSoft Consumer Groups Settings with Oracle Consumer Group Mappings

I would like to be able to blend the PeopleSoft configuration with the ability to automatically associate Oracle consumer groups with specific values of MODULE and ACTION.  Purely as an example, I am trying to move the Process Monitor component into the SYS_GROUP consumer group.
BEGIN
  DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
 
  DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING
  (attribute      => 'MODULE_NAME'
  ,value          => 'PROCESSMONITOR'
  ,consumer_group => 'SYS_GROUP'); 
 DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
END;
/

However, it doesn't work because the explicit settings overrides any rules, and you cannot prioritise other rules above explicit settings
exec dbms_application_info.set_module('PROCESSMONITOR','PMN_PRCSLIST');
SELECT REGEXP_SUBSTR(program,'[^.@]+',1,1) program
, module, action, resource_consumer_group
FROM v$session
WHERE module IN('PROCESSMONITOR','WIBBLE')
ORDER BY program, module, action
/

So I have created a new SQL*Plus session and set the module/action and it has automatically mover into the SYS_GROUP.  Meanwhile, I have been into the Process Monitor in the PIA and the module and action of the PSAPPSRV session has been set, but they remain in the interactive group.
PROGRAM          MODULE           ACTION           RESOURCE_CONSUMER_GROUP
---------------- ---------------- ---------------- ------------------------
PSAPPSRV         PROCESSMONITOR   PMN_PRCSLIST     INTERACTIVE_GROUP
PSAPPSRV         PROCESSMONITOR   PMN_SRVRLIST     INTERACTIVE_GROUP
sqlplus          PROCESSMONITOR   PMN_PRCSLIST     SYS_GROUP

If I set the module to something that doesn't match a rule, the consumer group goes back to OTHER_GROUPS which is the default. 
exec dbms_application_info.set_module('WIBBLE','PMN_PRCSLIST');

PROGRAM          MODULE           ACTION           RESOURCE_CONSUMER_GROUP
---------------- ---------------- ---------------- ------------------------
PSAPPSRV         PROCESSMONITOR   PMN_PRCSLIST     INTERACTIVE_GROUP
PSAPPSRV         PROCESSMONITOR   PMN_SRVRLIST     INTERACTIVE_GROUP
sqlplus          WIBBLE           PMN_PRCSLIST     OTHER_GROUPS

Now, if I explicitly set the consumer group exactly as PeopleSoft does my session automatically moves into the INTERACTIVE_GROUP.
DECLARE 
  old_group varchar2(30); 
BEGIN 
  DBMS_SESSION.SWITCH_CURRENT_CONSUMER_GROUP('INTERACTIVE_GROUP', old_group, FALSE); 
END;
/

PROGRAM          MODULE           ACTION           RESOURCE_CONSUMER_GROUP
---------------- ---------------- ---------------- ------------------------
PSAPPSRV         PROCESSMONITOR   PMN_PRCSLIST     INTERACTIVE_GROUP
PSAPPSRV         PROCESSMONITOR   PMN_SRVRLIST     INTERACTIVE_GROUP
sqlplus          WIBBLE           PMN_PRCSLIST     INTERACTIVE_GROUP

Next, I will set the module back to match the rule, but the consumer group doesn't change because the explicit setting takes priority over the rules.
PROGRAM          MODULE           ACTION           RESOURCE_CONSUMER_GROUP
---------------- ---------------- ---------------- ------------------------
PSAPPSRV         PROCESSMONITOR   PMN_PRCSLIST     INTERACTIVE_GROUP
PSAPPSRV         PROCESSMONITOR   PMN_SRVRLIST     INTERACTIVE_GROUP
sqlplus          PROCESSMONITOR   PMN_PRCSLIST     INTERACTIVE_GROUP
You can rearrange the priority of the other rule settings, but explicit must have the highest priority (if you try will get ORA-56704). So, continuing with this example, I cannot assign a specific component to a different resource group unless I don't use the PeopleSoft configuration for PSAPPSRV.
Instead, I could create a rule to assign a resource group to PSAPPSRV via the program name, and have a higher priority rule to override that when the module and/or action is set to a specific value.  However, first I have to disengage the explicit consumer group change for PSAPPSRV by removing the row from PTEXEC2RESOURCE.
UPDATE ps_ptexec2resource 
SET    pt_resource_name = 'DO_NOT_USE' 
WHERE  pt_executable_name = 'PSAPPSRV'
AND    pt_resource_name = 'APPLICATION SERVER'
/
COMMIT
/
BEGIN
  DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
END;
/
BEGIN
  DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING
  (attribute      => 'CLIENT_PROGRAM'
  ,value          => 'PSAPPSRV'
  ,consumer_group => 'INTERACTIVE_GROUP'); 

  DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING
  (attribute      => 'MODULE_NAME'
  ,value          => 'PROCESSMONITOR'
  ,consumer_group => 'SYS_GROUP'); 

  DBMS_RESOURCE_MANAGER.set_consumer_group_mapping_pri(
    explicit              => 1,
    oracle_user           => 2,
    service_name          => 3,
    module_name_action    => 4, --note higher than just module
    module_name           => 5, --note higher than program
    service_module        => 6,
    service_module_action => 7,
    client_os_user        => 8,
    client_program        => 9, --note lower than module
    client_machine        => 10
  );
  DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
END;
/
So, you would have to choose between using either the PeopleSoft configuration or the Oracle Resource Manager configuration.  It depends on your requirements.  This is going to be a decision you will have to take when you design your resource management.  Of course, you can always use just the mapping approach in versions of PeopleTools prior to 8.54.

Conclusion

I have never seen Oracle Resource Manager used with PeopleSoft.  Probably because setting it up is not trivial, and then it is difficult to test the resource plan.  I think this enhancement is a great start, that makes it very much easier to implement Oracle Resource Manager on PeopleSoft.  However, I think we need more granularity.
  • I would like to be able to put specific process run on the process scheduler by name into specific consumer groups.  For now, you could do this with a trigger on PSPRCSRQST that fires on process start-up that makes an explicit consumer group change (and puts it back again for Application Engine on completion). 
  • I would like the ability to set different resource groups for the same process name in different application server domains.  For example,
    • I might want to distinguish between PSQRYSRV processes used for ad-hoc PS/Queries on certain domains from PSQRYSRVs used to support nVision running in 3-tier mode on other domains.
    • I might have different PIAs for backup-office and self-service users going to different applications servers.  I might want to prioritise back-office users over self-service users.
Nonetheless, I warmly welcome the new support for Oracle Resource Manager in PeopleTools.  It is going to be very useful for RAC implementations, I think it will be essential for multi-tenant implementations where different PeopleSoft product databases are plugged into the same container database overrides any rules

Monday, February 23, 2015

PeopleTools 8.54: Multiple Query Security Records

This is part of a series of articles about new features and differences in PeopleTools 8.54 that will be of interest to the Oracle DBA.

This post is not about a database feature newly supported in PeopleTools, but PeopleTools is capable of doing something new that could negatively impact the database.  When I saw the following warning in the PeopleTools 8.54 release notes, I thought I should look into it.
"PeopleTools has added an Advanced Query Security option to Application Designer. This feature allows up to five Query Security Records to be associated with a single record, including the ability to associate security with non-key fields. While powerful, this feature should be used sparingly because multiple additional joins will affect query performance."

The PeopleTools documentation shows how to add multiple query security records in Application Designer, but doesn't really explain what effect it will have on queries on that record.
PeopleTools has always allowed a query security record to be defined on a record.  This is the record properties for JOB.


I am going to create a simple example query that joins PS_JOB and PS_NAMES.  These records have different query security records, so both query security records appear in the PS/Query.
SELECT B.EMPLID, B.DEPTID
  FROM PS_JOB B, PS_EMPLMT_SRCH_QRY B1, PS_NAMES A, PS_PERALL_SEC_QRY A1
  WHERE ( B.EMPLID = B1.EMPLID
    AND B.EMPL_RCD = B1.EMPL_RCD
    AND B1.OPRID = 'PS'
    AND A.EMPLID = A1.EMPLID
    AND A1.OPRID = 'PS'
    AND ( B.EFFDT =
        (SELECT MAX(B_ED.EFFDT) FROM PS_JOB B_ED
        WHERE B.EMPLID = B_ED.EMPLID
          AND B.EMPL_RCD = B_ED.EMPL_RCD
          AND B_ED.EFFDT <= SYSDATE)
    AND B.EFFSEQ =
        (SELECT MAX(B_ES.EFFSEQ) FROM PS_JOB B_ES
        WHERE B.EMPLID = B_ES.EMPLID
          AND B.EMPL_RCD = B_ES.EMPL_RCD
          AND B.EFFDT = B_ES.EFFDT)
     AND B.EMPLID = A.EMPLID
     AND A.EFFDT =
        (SELECT MAX(A_ED.EFFDT) FROM PS_NAMES A_ED
        WHERE A.EMPLID = A_ED.EMPLID
          AND A.NAME_TYPE = A_ED.NAME_TYPE
          AND A_ED.EFFDT <= SYSDATE) ))


The new version of the same query, but this time with multiple query security records if force, is below.  Note that:
  • A1, B1 are the query security records defined on the record properties that have always been present in PeopleTools.
  • B4, B5, B6 are the advanced query security records.  Note that EMPLNT_SRCH_QRY has join criteria on the columns specified in the Advanced Query Security Mapping dialogue.
  • EMPLMT_SRCH_QRY gets joined twice to JOB because it is the query security record B1 and an advanced query security record B4, so the advanced settings are in addition to the standard setting.  Be careful not to duplicate records.  The PS/Query Security Join Optimization setting (introduced in PeopleTools 8.52) does not help with this.
  • The standard query security record is stored in PSRECDEFN.QRYSRCRECNAME, while the advanced query security record definitions are stored in a new PeopleTools table PSRECSECFLDMAPS.
SELECT B.EMPLID, B.DEPTID
  FROM PS_JOB B, PS_EMPLMT_SRCH_QRY B1, PS_EMPLMT_SRCH_QRY B4, 
                 PS_PERALL_SEC_QRY B5, PS_PERS_SRCH_QRY B6,
     PS_NAMES A, PS_PERALL_SEC_QRY A1
  WHERE ( B.EMPLID = B1.EMPLID
    AND B.EMPL_RCD = B1.EMPL_RCD
    AND B1.OPRID = 'PS'
    AND B.EMPLID = B4.EMPLID
    AND B.EMPL_RCD = B4.EMPL_RCD
    AND B4.OPRID = 'PS'
    AND B.EMPLID = B5.EMPLID
    AND B5.OPRID = 'PS'
    AND B.EMPLID = B6.EMPLID
    AND B6.OPRID = 'PS'
    AND A.EMPLID = A1.EMPLID
    AND A1.OPRID = 'PS'
    AND ( B.EFFDT =
        (SELECT MAX(B_ED.EFFDT) FROM PS_JOB B_ED
        WHERE B.EMPLID = B_ED.EMPLID
          AND B.EMPL_RCD = B_ED.EMPL_RCD
          AND B_ED.EFFDT <= SYSDATE)
    AND B.EFFSEQ =
        (SELECT MAX(B_ES.EFFSEQ) FROM PS_JOB B_ES
        WHERE B.EMPLID = B_ES.EMPLID
          AND B.EMPL_RCD = B_ES.EMPL_RCD
          AND B.EFFDT = B_ES.EFFDT)
     AND B.EMPLID = A.EMPLID
     AND A.EFFDT =
        (SELECT MAX(A_ED.EFFDT) FROM PS_NAMES A_ED
        WHERE A.EMPLID = A_ED.EMPLID
          AND A.NAME_TYPE = A_ED.NAME_TYPE
          AND A_ED.EFFDT <= SYSDATE) ))

Conclusion

I know from previous experience that having just two different query security records on different base records, as in the first example above can lead to significant performance problems.  This new feature has the potential to add up to five more per record.
I can see that this feature could have occasional application where the additional security is not joined by a key field.  However, I would generally echo the sentiment in the release notes, and use it sparingly.  Instead of two query security records, could you merge them into one security record?