UKOUG PeopleSoft Roadshow 2015 OUG Ireland Conference 2015

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 a change existing code to use this, unless perhaps I was visiting the code anyway, but I would certainly recommend its use going forward.

Saturday, February 21, 2015

PeopleTools 8.54: %SQLHint 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.
 
This new PeopleCode meta-SQL macro performs a search of SQL statement for the nth instance of SQL command keyword and inserts a string after it.
%SqlHint(SQL_cmd, index, hint_text, DB_platform [, {ENABLE | DISABLE}])
It is particularly effective with the %InsertSelect meta-SQL.  Previously the only way to put a hint into the main select was with a variable assignment, but that didn't work if the DISTINCT keyword was used because the hint appeared behind the distinct.
%InsertSelect(DISTINCT, DMK,JOB J, EMPLID= /*+LEADING(J)*/ J.EMPLID) 
  FROM PS_JOB J 
…
which resolves to:
INSERT INTO PS_DMK (EMPLID 
 , EMPL_RCD 
 , EFFDT 
 , EFFSEQ 
 , SETID_DEPT 
 , DEPTID)  
 SELECT  DISTINCT /*+LEADING(J)*/ J.EMPLID 
 , J.EMPL_RCD 
 , J.EFFDT 
 , J.EFFSEQ 
 , J.SETID_DEPT 
 , J.DEPTID  
  FROM PS_JOB J  
…
Here is a deliberately contrived example of how to use the command.
  • I have created a separate SQL object, DMK_CURJOB, to hold effective date/sequence sub-queries which I will reference from an application engine SQL.
%P(2).EFFDT = ( 
 SELECT MAX(%P(3).EFFDT) 
  FROM %Table(%P(1)) %P(3) 
 WHERE %P(3).EMPLID = %P(2).EMPLID 
   AND %P(3).EMPL_RCD = %P(2).EMPL_RCD 
   AND %P(3).EFFDT <= %CurrentDateIn) 
   AND %P(2).EFFSEQ = ( 
 SELECT MAX(%P(4).EFFSEQ) 
  FROM %Table(%P(1)) %P(4) 
 WHERE %P(4).EMPLID = %P(2).EMPLID 
   AND %P(4).EMPL_RCD = %P(2).EMPL_RCD 
   AND %P(4).EFFDT = %P(2).EFFDT)
  • I want my insert statement to run in direct-path mode, so I am putting an APPEND hint into the INSERT statement.
  • I am going to put different hints into each of the different SQL query blocks, including the sub-queries in the SQL object.
%SqlHint(INSERT,1,'/*+APPEND*/',ORACLE,ENABLE)
%SqlHint(INSERT,1,'/*Developer Comment*/',ORACLE,DISABLE)
%SqlHint(SELECT,1,'/*+LEADING(J)*/',ORACLE)
%SqlHint(SELECT,2,'/*+UNNEST(J1)*/',ORACLE)
%SqlHint(SELECT,3,'/*+UNNEST(J2)*/',ORACLE)
%InsertSelect(DISTINCT, DMK,JOB J) 
  FROM PS_JOB J 
 WHERE %Sql(DMK_CURJOB,JOB,J,J1,J2)

Which resolves to:
 INSERT /*+APPEND*/ INTO PS_DMK (EMPLID 
 , EMPL_RCD 
 , EFFDT 
 , EFFSEQ 
 , SETID_DEPT 
 , DEPTID)  
 SELECT /*+LEADING(J)*/  DISTINCT J.EMPLID 
 , J.EMPL_RCD 
 , J.EFFDT 
 , J.EFFSEQ 
 , J.SETID_DEPT 
 , J.DEPTID  
  FROM PS_JOB J  
 WHERE J.EFFDT = (  
 SELECT /*+UNNEST(J1)*/ MAX(J1.EFFDT)  
  FROM PS_JOB J1  
 WHERE J1.EMPLID = J.EMPLID  
   AND J1.EMPL_RCD = J.EMPL_RCD  
   AND J1.EFFDT <= TO_DATE(TO_CHAR(SYSDATE,'YYYY-MM-DD'),'YYYY-MM-DD'))  
   AND J.EFFSEQ = (  
 SELECT /*+UNNEST(J2)*/ MAX(J2.EFFSEQ)  
  FROM PS_JOB J2  
 WHERE J2.EMPLID = J.EMPLID  
   AND J2.EMPL_RCD = J.EMPL_RCD  
   AND J2.EFFDT = J.EFFDT)

The %SQLHint processing appears to be done after all other expansions, so the search and insert can reach into %SQL objects.  Previously we had to put hints into the SQL object.  Although, sometimes, we could avoid that by using query block naming hints.  Now, I can place any hint after any SQL command.  I can choose to apply a hint in just one step that references a SQL object, rather than in the SQL object which affects every step that references it.

If you put multiple substitutions in for the same SQL command, only the last enabled one is processed.

I frequently find that developers love to put comments into SQL which then appears in logs files and Oracle monitoring tool.  I hate that.  Comments in SQL that run on the database is an unnecessary overhead, and it turns up later in SQL monitoring and tuning tools.  It is worth noting that comments that are not hints are stripped out of SQL in PL/SQL procedures.  Perhaps developers should put their comment in a disabled %SQLHint so it will not appear in the final SQL?

Oracle SQL Outlines/Profiles/Patches/Baselines

All this talk of adding hints to source code is going to cause an Oracle DBA to ask why not use the techniques provided by Oracle to control execution plans on application engine SQL.  The problem is that those techniques are frequently thwarted by the dynamic nature of SQL created by PeopleSoft.
  • Bind variables can become literals when the SQL is generated, though profiles and baselines can handle this.
  • Different instances of temporary records are different tables in the database. You would have to handle each table (or every combination of tables if you have multiple temporary records in one statement).
  • Many delivered processes have dynamically generated SQL and you would again have to handle every variation separately.

Conclusion

The %SQLHint meta-SQL brings a huge advantage for Oracle's PeopleSoft developers.  Although it is possible to create platform specific application engine sections, there is huge reluctance to do this in development in Oracle.  This is entirely reasonable as it results in having to develop, test and maintain separate code lines.  Many of the meta-SQL macros are designed precisely to overcome SQL differences between different supported database platforms. Now, using %SQLHint they can now put Oracle specific optimizer hints into platform generic application engine steps, safe in the knowledge that the hints will only affect Oracle platforms.

This is going to be a very useful feature.  Simple.  Effective.  I look forward to hinting the places that other techniques cannot reach!



Friday, February 20, 2015

PeopleTools 8.54: Table/Index Partitioning

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.

  Partitioning in Oracle

Partitioning of table (and index) segments involves breaking them into several smaller segments where certain data values only occur in certain segments.  Thus if a query is looking for a certain data value it may be able to eliminate some partitions without having to scan them because by definition those values cannot occur in those partitions.  Thus saving logical and physical read, and improving performance.  This is called partition elimination or pruning.  It is often the principal reason for partitioning a table.
Physically each partition in a partitioned table is a table, but logically the partitions form a single table.  There should be no need to change application code in order to use partitioning, but the way the code is written will affect Oracle's ability to perform partition elimination.
The following diagram is taken from the Oracle 11g Database VLDB and Partitioning Guide

If a query was only looking data in March, then it could eliminate the January and February partitions without inspecting them.  However, if it was filtering data by another column then it would still have to inspect all three partitions.  Application design will determine whether, and if so how to partition a table.
NB: I can't mention partitioning without also saying that Partitioning Option is a licensed feature of Oracle Database Enterprise Edition.

Partitioning in PeopleTools prior to 8.54

I have to declare an interest.  I have been using partitioning in PeopleSoft since PeopleTools 7.5 when it was introduced in Oracle 8i.  The line from PeopleSoft was that you can introduce partitioning without invalidating your support (the same is not true of E-Business suite).  Application Designer won't generate partition DDL, so you were supposed to give your DDL scripts to the DBA who would add the partition clauses.  So you if wanted to use partitioning, you would be plunged into a hellish world of manual scripting.  One of the key benefits of Application Designer is that it generates the DDL for you.
Since 2001, I have developed a PL/SQL utility that effectively reverse engineers the functionality of Application Designer that builds DDL scripts, but then adds the partitioning clauses.  It also adds partitions, and has been extended to assist with partition-wise data archive/purge.  It is in uses at a number sites using Global Payroll (for which it was originally designed) and Financials (see Managing Oracle Table Partitioning in PeopleSoft Applications with GFC_PSPART Package)
So in investigating the new partitioning feature of PeopleTools 8.54 I was concerned:
  • Is my partitioning utility was now obsolete?  Or should I continue to use it?
  • How would I be able to retrofit existing partitioning into PeopleTools?

Partitioning in PeopleTools 8.54

I am going to illustrate the behaviour of the new partition support with a number of example.

Example 1: Range Partitioning PSWORKLIST

In this example, I will range partition table PSWORKLIST on the INSTSTATUS column. The valid statuses for this column are:

INSTSTATUS Description
0 Available
1 Selected
2 Worked
3 Cancelled
  • the first partition will only contain statuses 0 and 1, which are the open worklist items, 
  • the other partition will contain the other statuses; 2 and 3 which are the closed items. 
The application repeatedly queries this table looking for work lists items to be processed, using the criterion INSTSTATUS < 2.  Over time, unless data is archived, the vast majority of entries are closed.  This partitioning strategy will enable the application to find the open worklist items quickly by eliminating the larger closed partition only querying the smaller open item partition.  As items are worked or cancelled, their statuses are updated to 2 or 3, and they will automatically be moved to the other partition.
This is something that I have actually done on a customer site, and it produced a considerable performance improvement.
PeopleSoft provides a component that allows you to configure the partitioning strategy for a record.  However, I immediately ran into my first problem. 
  • The Partitioning Utility component will only permit me to partition by a PeopleSoft unique key column.  If a query doesn't have a predicate on the partitioning column, then Oracle will certainly not be able to prune any partitions, and the query will perform no better than if the table had not been partitioned.  While a column frequently used in selective criteria is often the subject of an index, and sometimes the unique key, this is not always the case.  It does not make sense to assume this in this utility component.
  • In this case, INSTSTATUS is not part of any delivered index, though I added it to index B.  I have seen that the application frequently queries the PSWORKLIST table by INSTSTATUS, so it does make sense to partition it on that column.
However, I can customise the component to get around this.  The key field is validated by the view PPMU_RECKEYS_VW.
SELECT A.RECNAME 
 ,A.FIELDNAME 
  FROM PSRECFIELDALL A 
 WHERE %DecMult(%Round(%DECDIV(A.USEEDIT,2),0 ) , 2) <> A.USEEDIT
I can change the view as follows:
DROP TABLE PS_ST_RM2_TAO
/
SELECT A.RECNAME 
 ,A.FIELDNAME 
  FROM PSRECFIELDALL A /* WHERE %DecMult(%Round(%DECDIV(A.USEEDIT,2),0 ) , 2) <> A.USEEDIT*/ 
  , PSDBFIELD B 
 WHERE A.FIELDNAME = B.FIELDNAME 
   AND B.FIELDTYPE IN(0,2,3,4,5,6)
So, now I can specify the partitioning for this table in the Partitioning Utility Component
 I notice that can leave tablespace blank in component, but the tablespace keyword is lying around - so I have to put a tablespace in.  It is valid to omit physical attributes at partition level and they will be inherited from table level, and similarly for table level.
  • The component automatically adds a MAXVALUE partition.  This means that is valid to put any value into the partition column, otherwise it can cause an error.  However, it might not be what I want.
  • The component also adds a table storage clause, overriding anything specified in the record, with a fixed PCTFREE 20 which applies to all partitions.  Again this might not be what I want.  The value of PCTFREE depends on whether and how I update data in the table. 
  • There are a number of things that I can't control in this component
    • The name of MAXVALUE partition
    • The tablespace of the MAXVALUE partition, which defaults to be the same tablespace as the last defined partition, which might not be what I want.
    • Any other physical attribute of any of the partitions, for example I might want a different PCTFREE on partitions containing data will not be updated.
  • The component adds clause to enable row movement.  This permits Oracle to move rows between partitions if necessary when the value of the partitioning key column is updated.  In this case it is essential because as worklist items are completed they move from the first partition to the other.  ALTER TABLE ... SHRINK requires row moment, so it is useful to enable it generally.
The partitioning definition can be viewed in Application Designer under Tools -> Data Administration -> Partitioning.


The create table script (PSBUILD.SQL) does not contain any partition DDL.  So first you build the table and then alter it partitioned.  That is not unreasonable as you would often build the table and then decide to partition it.  I do the same in my own utility.
-- Start the Transaction 


-- Create temporary table 

CREATE TABLE PSYPSWORKLIST (BUSPROCNAME VARCHAR2(30)  DEFAULT ' ' NOT
 NULL,
…
   DESCR254_MIXED VARCHAR2(254)  DEFAULT ' ' NOT NULL) PARTITION BY
 RANGE (INSTSTATUS) 
(
 PARTITION OPEN VALUES LESS THAN (2) TABLESPACE PTTBL, 
 PARTITION PE_MAXVALUE VALUES LESS THAN (MAXVALUE) TABLESPACE PTTBL
) 
PCTFREE 20 ENABLE ROW MOVEMENT
/

-- Copy from source to temp table 

INSERT INTO PSYPSWORKLIST (
        BUSPROCNAME,
… 
    DESCR254_MIXED)
  SELECT
        BUSPROCNAME,
…
    DESCR254_MIXED
  FROM PSWORKLIST
/

-- CAUTION: Drop Original Table 

DROP TABLE PSWORKLIST
/

-- Rename Table 

RENAME PSYPSWORKLIST TO PSWORKLIST
/

-- Done 

CREATE UNIQUE  INDEX PS_PSWORKLIST ON PSWORKLIST (BUSPROCNAME,
   ACTIVITYNAME,
   EVENTNAME,
   WORKLISTNAME,
   INSTANCEID) 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 40960 NEXT 106496 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "PSINDEX"
/
ALTER INDEX PS_PSWORKLIST NOPARALLEL LOGGING
/
…
CREATE   INDEX PSBPSWORKLIST ON PSWORKLIST (OPRID,
   INSTSTATUS) 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 40960 NEXT 106496 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "PSINDEX"
/
ALTER INDEX PSBPSWORKLIST NOPARALLEL LOGGING
/
CREATE INDEX PSBWORKLIST ON PSWORKLIST
  ('')  LOCAL TABLESPACE PTTBL
/

The DDL to create partitioned index does not seem to appear properly.  The first CREATE INDEX command was generated by Application Designer extracting it from the catalogue with DBMS_METADATA.  This functionality was introduced in PeopleTools 8.51 to preserve existing configuration.The second create index comes from the partitioning definition.
  • The index column list is missing, it should come from the column list is defined in Application Designer.
  • The locally partitioned index is the same tablespace as the table instead of the tablespace defined on the index. 
    • I would not normally keep indexes in the same tablespace as the table (the rationale is that in the case of having to recover only a tablespace with indexes then I could rebuild it instead of recovering it).
I also note that the table is not altered NOLOGGING.  Yet the indexes are still made NOPARALLEL.  The default degree of parallelism on a partitioned table is equal to the number of partitioned, so it will cause parallel query to be invoked on the table access. 
  • I strongly recommend against generally allowing parallel query in all SQLs that reference a partitioned table in an OLTP system, which is what PeopleSoft is.  There are occasions where parallel query is the right thing to do, and in those cases I would use a hint, or SQL profile or SQL patch.
If I leave the Partitioning Utility component and then go back to a record where I have previously created partition DDL, then the partition DDL is still there, but all the other information that I typed in has disappeared.



If you trace the SQL generated by this component while entering partition details and generating partition DDL, then the only two tables that are updated at all;  PS_PTTBLPARTDDL and PS_PTIDXPARTDDL.  They are both keyed on RECNAME and PLATFORMID and have just one other column, a CLOB to hold the DDL.
  • The partition information disappears because there is nowhere to hold it persistently, and the component cannot extract it from the DDL.  It was being entered into a derived work record.
    • So it is not going to be much help when I want to adjust partitioning in a table that is already partitioned.  For example, over time, I might want to add new partitions, compress static partitions, or purge old ones.
  • It is also clear that there is no intention to support different partitioning strategies for different indexes on the same table.  There are certainly cases where a table will one or more locally partitioned indexes and some global indexes that may or may not be partitioned.
  • Even these two tables are not fully integrated into Application Designer.  There is a throwaway line in Appendix E of the Data Management Guide - Administering Databases on Oracle:"Record and index partitioning is not migrated as part of the IDE project. If you want to migrate the partitioning metadata along with the record, you will need to…" copy it yourself and it goes on to recommend creating a Data Migration Project in the Data Migration Workbench"

Sample 2: Import Existing Partitioning

Sticking with PSWORKLIST, I have partitioned it exactly the way I want.  The partition DDL was generated by my own partitioning utility .  I have added INSTSTATUS to index B.
CREATE TABLE sysadm.psworklist
(busprocname VARCHAR2(30) NOT NULL
…
,descr254_mixed VARCHAR2(254) NOT NULL
)
TABLESPACE PTTBL
PCTFREE 10 PCTUSED 80
PARTITION BY RANGE(INSTSTATUS)
(PARTITION psworklist_select_open VALUES LESS THAN ('2')
,PARTITION psworklist_worked_canc VALUES LESS THAN (MAXVALUE) PCTFREE 1 PCTUSED 90
)
ENABLE ROW MOVEMENT
PARALLEL
NOLOGGING
/
…
ALTER TABLE sysadm.psworklist LOGGING NOPARALLEL MONITORING
/
…
CREATE INDEX sysadm.ps0psworklist ON sysadm.psworklist
(transactionid
,busprocname
,activityname
,eventname
,worklistname
,instanceid
)
TABLESPACE PSINDEX
PCTFREE 10
PARALLEL
NOLOGGING
/
…
CREATE INDEX sysadm.psbpsworklist ON sysadm.psworklist
(oprid
,inststatus
)
LOCAL
(PARTITION psworklistbselect_open
,PARTITION psworklistbworked_canc PCTFREE 1
)
TABLESPACE PSINDEX
PCTFREE 10
PARALLEL
NOLOGGING
/
ALTER INDEX sysadm.psbpsworklist LOGGING
/
ALTER INDEX sysadm.psbpsworklist NOPARALLEL
/

The DDL in the Maintain Partitioning box in Application Designer is extracted from the data dictionary using the Oracle supplied DBMS_METADATA package.  Application Designer has done this since PeopleTools 8.51 for index build scripts, but now you can see the DDL directly in the tool.
When I generate an alter table script I still get two create index command for the partitioned index.  The second one comes from the generated partition DDL and is not correct because it still doesn't have a column list.
CREATE   INDEX PSBPSWORKLIST ON PSWORKLIST (OPRID,
   INSTSTATUS) 
  PCTFREE 10 INITRANS 2 MAXTRANS 255  LOGGING 
  STORAGE(
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "PSINDEX"  LOCAL
 (PARTITION "PSWORKLISTBSELECT_OPEN" 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 LOGGING 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS
 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "PSINDEX" , 
 PARTITION "PSWORKLISTBWORKED_CANC" 
  PCTFREE 1 INITRANS 2 MAXTRANS 255 LOGGING 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS
 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "PSINDEX" )
/
ALTER INDEX PSBPSWORKLIST NOPARALLEL LOGGING
/
CREATE INDEX PSBPSWORKLIST ON PSWORKLIST   ('')  LOCAL TABLESPACE
 PTTBL 
/

Example 3 - GP_RSLT_ACUM

I have now chosen to partition one of the Global Payroll result tables.  This is often the largest table in a payroll system.  I have seen more than 1 billion rows in this table at one customer.  In a Global Payroll system, I usually:
  • range partition payroll tables on EMPLID to match the streamed processing (in GP streaming means concurrently running several Cobol or Application Engine programs to process different ranges of employees).  So there is a 1:1 relationship between payroll processes and physical partitions
  • the largest result tables are sub-partitioned on CAL_RUN_ID so each payroll period is in a separate physical partition.  Later I can archive historical payroll data by partition.
Here, I have swapped the partitioning over.  I have partitioned by CAL_RUN_ID and sub-partitioned by EMPLID.  I explain why below.


And this is Table DDL that the utility generated.
PARTITION BY RANGE (CAL_RUN_ID) 
SUBPARTITION BY RANGE (EMPLID) 
SUBPARTITION TEMPLATE
(
 SUBPARTITION SUB1 VALUES LESS THAN ('K9999999999'), 
 SUBPARTITION SUB2 VALUES LESS THAN ('KE999999999'), 
 SUBPARTITION SUB3 VALUES LESS THAN ('KM999999999'), 
 SUBPARTITION SUB4 VALUES LESS THAN ('KT999999999') , 
 SUBPARTITION PE_MAXVALUE VALUES LESS THAN (MAXVALUE)
) 
(
 PARTITION STRM1 VALUES LESS THAN ('K9999999999') TABLESPACE GPPART1, 
 PARTITION STRM2 VALUES LESS THAN ('KE999999999') TABLESPACE GPPART2, 
 PARTITION STRM3 VALUES LESS THAN ('KM999999999') TABLESPACE GPPART3, 
 PARTITION STRM4 VALUES LESS THAN ('KT999999999') TABLESPACE GPPART4, 
 PARTITION PE_MAXVALUE VALUES LESS THAN (MAXVALUE) TABLESPACE GPPART4
) 
PCTFREE 20 ENABLE ROW MOVEMENT

  • Use of the sub-partition template clause simplifies the SQL.  There is certainly a lot less of it.  However, it means you get all the sub-partitions within in all partitions.  That might not be what you want.  In this demo database both employees and calendars are prefixed by something that corresponds to legislature, so some partitions will be empty.  They won't take up any physical space, due to deferred segment creation, but it would be better not to build them at all.
  • I can specify tablespace on the sub-partitions in the utility component, but there is no tablespace on the sub-partition template in the DDL.  I care more about putting different payroll periods into different tablespaces, than different ranges of employees (so I can compress and purge data later) so I swapped the partition key columns and have range partitioned on CAL_RUN_ID and sub-partitioned on EMPLID.
In Global Payroll, partitioning is required to support streamed processing.  Tables are range partitioned on EMPLID to match the stream definitions.  In UK payroll, there are 45 tables that are updated or heavily referenced by streamed payroll processes that should all have similar range partitioning on EMPLID. 
In PeopleTools 8.54, it is not possible to define a single partitioning strategy and consistently apply it to several tables.  Even if the data entered into the partition utility component was retained, I would have to enter it again for each table.

Conclusion

When I heard that PeopleTools would have native support for partitioning, if only in Oracle, I was hopeful that we would get something that would bring the process of migrating and building partitioned tables in line with normal tables. Unfortunately, I have to say that I don't think the partitioning support that I have seen so far is particularly useful.
  • There is no point typing in a lot of partition data into a utility component that doesn't retain the data.
  • As with materialized views, table partitioning is something on which DBAs will have to advise and will probably implement and maintain.  This component doesn't really help them do anything they already do with a text editor!
  • Even the minimal partition data that the utility component does retain is not migrated between environments by Application Designer when you migrate the record.
Again, I think the problems stem from PeopleTools development trying to minimize the level of alteration to the Application Designer.  The partitioning utility component looks good because it sets out a lot of the partition attributes and metadata that you do need to consider, but there is no data structure behind that to hold it.
I would like to see PeopleTools tables to hold partitioning metadata for tables and indexes, and for Application Designer to build DDL scripts to create and alter partitioned tables, to add partitions to existing tables, and then to migrate those definitions between environments.
One positive that I can take from this is that Oracle has now clearly stated that it is reasonable to introduce partitioning into your PeopleSoft application without invalidating your support. The position hasn't actually changed, but now there is clarity.