Accenture Enkitec Group E4 Webinar

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

No comments :