In the cloud (or any virtualised environment), performance is instrumented as cost. This is also true in any other on-premises environment, but it takes a lot longer to feedback!
- If you never run out of CPU, then you have probably bought/rented/allocated/licensed too many CPUs.
- If you do run out of CPU, then you should use the database resource manager to prioritise the processes that are most important to the business.
- If you don't enable the resource manager, you will have less visibility of when you do run out of CPU.
At the very least, you can use one of the sample resource manager plans installed in the Oracle database by default.
This article proposes a resource plan for PeopleSoft systems. It can be used as a starting point before enhancing it with your own specific requirements.
- See also Go-Faster Blog: More Bang for your Buck in the Cloud with Resource Manager
Resource Plan Design Goals
The purpose of a database resource plan is to prioritise important/urgent processes over less important/less urgent processes by allocating CPU, to the higher priority processes, and by restricting CPU, other resources, and the degree of parallelism for lower priority processes.
The design of a resource plan should reflect what the business defines as important.
Consumer Groups
A resource plan consists of several resource groups with different priorities, and resource allocations. Each priority level defined by the business becomes a consumer group in the resource plan. A consumer group can be allocated to one of 8 priority levels in a resource plan. Multiple consumer groups can exist at the same priority level with different CPU guarantees (adding up to not more than 100%) and can include other limits.
I have made some assumptions about process priorities in a typical PeopleSoft system, and have grouped and ranked them in the table below starting with the highest priority. Not all customers run all these processes. Consumer groups and mappings that are not needed can be omitted. There are gaps in the priority levels to allow for other definitions to be introduced.
| Priority Level | Consumer Group | %CPU Guarantee | Comment |
|---|---|---|---|
| 1 | SYS _GROUP | 100% | Oracle system processes. Defined automatically. |
| 2 | PSFT _GROUP | 100% |
Any process that connects to the database as either SYSADM (the default
PeopleSoft owner ID) or PS has higher priority than other processes unless other rules apply. The online application (other than ad hoc query) falls into this category so that the online user experience is safeguarded before other PeopleSoft processes. This includes remote call Cobol processes, but not remote call Application Engine that should be run in the component processor. |
| 4 | BATCH _GROUP | 100% | Process scheduler processes, and processes run by the process schedulers |
| 5 | NVISION _GROUP | 100% | nVision (NVSRUN) and nVision report book (RPTBOOK) processes |
| 6 | PSQUERY _ONLINE _GROUP | 90% | Ad hoc queries are allocated to one of three consumer groups with the same priority, but different CPU guarantees, comprising:
|
| PSQUERY _BATCH _GROUP | 9% |
| |
| NVSRUN _GROUP | 1% |
| |
| 8 | LOW _GROUP | 1% | Other low-priority processes |
| LOW _LIMITED _GROUP | 1% | Other low-priority processes, but whose maximum query time is limited. | |
| OTHER _GROUPS | 1% | All other processes. Defined automatically. |
Consumer Group Mapping Priority
Sessions are allocated to the consumer groups. They can be allocated explicitly, or via mapping rules that use various session attributes. As the attributes are set or changed, the consumer group will be set according to the matching rules.
I have set the following attributes to be mapped in the following order of precedence. The more specific mappings take precedence over the more generic ones.
| Priority | Mapping Attribute | Comment |
|---|---|---|
| 2 | Module, Action | The PIA instrumentation sets attributes MODULE to the component name and ACTION to the page name. Specific component pages are allocated to specific consumer groups |
| 3 | Module | Specific scheduled processes are allocated by name to specific consumer groups. PeopleSoft instrumentation puts this name in the MODULE attribute. |
| 4 | Client Program | Batch and query processes are identified by program name and allocated to certain consumer groups. |
| 5 | Oracle User | Anything that connects to the database as either SYSADM or PS is allocated to the PSFT_GROUP. So other mapping rules must take precedence over this mapping. |
Required PeopleSoft Configuration
The PSFT_PLAN sample resource manager plan relies on MODULE and ACTION being set by the PeopleSoft Application. Therefore, the following additional configuration is required.
- Enable PeopleSoft instrumentation: Set EnableAEMonitoring=1 in ALL PeopleSoft application server and process scheduler domains so that PeopleSoft processes set MODULE and ACTION information in the session attributes (using DBMS_APPLICATION_INFO).
See also:
- PeopleTools PeopleBooks: Administration Tools -> Data Management -> Administering PeopleSoft Databases on Oracle -> Monitoring PeopleSoft MODULE and ACTION Information, Press Enter to collapse
- PeopleSoft DBA Blog: Undocumented (until PeopleTools 8.55) Application Engine Parameter: EnableAEMonitoring
- PeopleSoft DBA Blog: PeopleTools 8.50 uses DBMS_APPLICATION_INFO to Identify Database Sessions
- Go-Faster Blog: One of my Favourite Database Things: DBMS_APPLICATION_INFO
- Install instrumentation trigger for PeopleSoft (psftapi.sql). Not all PeopleSoft processes are instrumented. COBOL, SQR, and nVision do not set MODULE or ACTION. When a PeopleSoft process is started by the process scheduler, the first thing it does is set its own status to 7, meaning that it is processing. This script creates a database trigger that fires on that DML and sets the session attributes MODULE to the name of the process and ACTION to the process instance number. Application Engine processes may then subsequently update these values again.
Consumer Group Mappings
Consumer groups are matched to session attributes. The highest priority matching mapping is applied. Mappings can be matched to literal values, or with LIKE or REGEXP_LIKE operations.
| Mapping Priority | Attribute | Value | Consumer Group Priority |
Consumer Group |
|---|---|---|---|---|
| 2 | MODULE_ACTION | QUERY_MANAGER.QUERY_VIEWER | 6 | PSQUERY_ONLINE_GROUP |
| 3 | MODULE | RPTBOOK NVSRUN | 5 | NVISION_GROUP |
| PSQRYSRV% | 6 | PSQUERY_ONLINE_GROUP | ||
| PSAE.PSQUERY.% | 6 | PSQUERY_BATCH_GROUP | ||
| 4 | CLIENT_PROGRAM | PSRUNRMT | 2 | PSFT_GROUP |
| psae% PSAESRV% PSDSTSRV% PSMSTPRC% PSRUN@% PSSQR% pssqr% sqr% | 4 | BATCH_GROUP | ||
| PSQRYSRV% | 6 | PSQUERY_ONLINE_GROUP | ||
| PSNVSSRV% | 6 | NVSRUN_GROUP | ||
| SQL Developer sqlplus% Toad% | 8 | LOW_GROUP / LOW_LIMITED_GROUP | ||
| 5 | ORACLE_USER | PS SYSADM | 2 | PSFT_GROUP |
Resource Plan Script
Two SQL scripts are available on GitHub
- psft_resource_plan_simple.sql creates the resource plan. This is intended to be a starting point to which either unwanted parts can be removed, or additional requirements can be added
- resource_plan_report.sql reports on all the resource plan metadata.
Other Options
There are other resource manager options that are either not illustrated in the sample plan, or that are commented out. They may be worth considering in some situations.
- PeopleSoft does not use parallel query by default, but if you do use it, you may well want to limit which processes use how much parallelism. Consumer groups can specify a limit to the parallel query degree.
- If you use the resource plan to restrict the degree of parallelism, and you also plan to vary the number of CPUs in a cloud environment, then I suggest creating a resource plan for each number of CPUs and switch between the plans by changing the setting of the RESOURCE_MANAGER_PLAN parameter.
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
'PSFT_PLAN', 'NVISION_GROUP', 'nVision Reports.'
,mgmt_p5 => 100
,parallel_degree_limit_p1=>2
);
- A parallel query may queue waiting to obtain sufficient parallel query server processes. A timeout can be specified to limit that wait and to determine the behaviour when the timeout is reached. The query can either be cancelled raising error ORA-07454, or run at a reduced parallelism).
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
'PSFT_PLAN', 'PSQUERY_ONLINE_GROUP'
,mgmt_p6 => 90
,parallel_queue_timeout=>900
,pq_timeout_action=>'RUN'
);
- A consumer group can restrict queries that run for a long time, or that are expected to run for a long time based on their optimizer cost. They can be switched to the CANCEL_SQL group after a number of seconds and they will terminate with ORA-00040: active time limit exceeded - call aborted:. This has only specified for the LOW_LIMITED_GROUP, and the PSQUERY_BATCH_GROUP for scheduled queries because the message is captured by the process scheduler and logged. It has not been specified for PSQUERY_ONLINE_GROUP because this error is not handled well by the online application. Just the Oracle error message will be displayed to the user without further explanation, which is neither friendly nor helpful. Instead, there are PeopleSoft configuration options to limit query runtime.
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
'PSFT_PLAN', 'PSQUERY_BATCH_GROUP'
,mgmt_p6 => 1
,switch_group => 'CANCEL_SQL'
,switch_time => 14400
,switch_estimate => TRUE
,switch_for_call => TRUE
);
- Sometimes customers may have different priorities and different priorities at different times that cannot be satisfied by a single resource plan. In which case, different resource plans can be activated at different times by different scheduler windows.
Other Online Resources
- Oracle White Paper: Using Oracle Database ResourceManager
- Documentation: Oracle Resource Manager Enhancements in Oracle Database 10g
- Documentation: Oracle Resource Manager Enhancements in Oracle Database 12c Release 1 (12.1)
Posts
