UKOUG PeopleSoft Roadshow 2015

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?

Sunday, February 22, 2015

PeopleTools 8.54: %SelectDummyTable Meta-SQL

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.
  
PeopleTools simply evaluates this meta-SQL as 'DUAL' on Oracle.

In Oracle, DUAL is just a convenience table.  You don't need to use it, you can use anything you want. PeopleSoft applications often use PS_INSTALLATION when they needs a single row source in a query.  This was another example of platform agnosticism.  PS_INSTALLATION is available on every platform and every PeopleSoft installation, DUAL is not.

Instead of coding this (the example is taken from ESPP_REF_REVMAIN.PSHUP.Do When)
%Select(IF_FLAG) 
 SELECT 'X' 
  FROM PS_INSTALLATION 
 WHERE %Bind(ST_SEND_SRC) = 'N'
You can now code this instead:
%Select(IF_FLAG) 
 SELECT 'X' 
  FROM %SelectDummyTable
 WHERE %Bind(ST_SEND_SRC) = 'N'
Which resolves to:
%Select(IF_FLAG)  
 SELECT 'X'  
  FROM DUAL  
 WHERE %Bind(ST_SEND_SRC) = 'N'
There are two advantages to using DUAL.
  • In the database, the Oracle optimizer knows that DUAL is a special one row, one column table.  When you use it in queries, it uses this knowledge when generating the execution plan.
  • If you used a real table, there was a risk that it could have no rows, or more than one row.  Either could cause application problems.  In previous versions of Oracle, when it was a real physical table, that problem could also occur with DUAL.  However, since Oracle 10g it is just a memory structure in the database, and accessing it involves neither a logical nor a physical read. 

Conclusion

PeopleSoft have created a Meta-SQL that evaluates as DUAL so that this Oracle optimization is implemented in a PeopleSoft platform generic manner. 
I would not bother to go back and change existing code to use this, unless perhaps I was visiting the code anyway, but I would certainly recommend its use going forward.