H.2 Profitability Management Parallel Execution
Profitability Management implementations frequently employ dozens, hundreds, or even thousands of allocation rules. Frequently, many of these rules must be executed sequentially and in larger implementations, end-to-end execution times may exceed allowable thresholds. One way to reduce end-to-end runtimes is to execute as many processes as possible in parallel. For example, running Funds Transfer Pricing against multiple instrument tables simultaneously. You may also find that you can run some allocation processes concurrently. Other ways to reduce your end-to-end runtimes include tuning your database, employing hints on your slowest running allocations (see SQL Hints), or upgrading your hardware (more CPUs, more memory, faster I/O subsystems, and so on). Having a more powerful database server does not improve your performance if the hardware resources are idle. When executing allocation rules serially, you will generally utilize only a single CPU on your database server.
Oracle Financial Services Profitability Management allows you to exploit Oracle Parallel Execution. To capture what might otherwise be idle system resources, Oracle Parallel Execution subdivides SQL statements into multiple independent units of work each of which can run in parallel.
The Oracle database supports parallel execution of SQL statements with the use of:
- The PARALLEL clause for selected tables and indexes
- Parallel hints
- Alter Session statements to enable subsequent SQL operations to run in parallel
Working with your DBA, you may choose to establish specific degrees of parallelism for selected tables and indexes utilizing the PARALLEL clause. You may also elect to employ parallel SQL Hints on selected allocation rules (see SQL Hints). Either of these approaches may be employed to encourage the database the run SELECT statements in parallel (Parallel Query), but the third mechanism is required if you want to encourage the database to run DML statements in parallel (UPDATE, INSERT, DELETE, MERGE, and so on). Employing a Parallel Query can help, but the most allocation execution time is consumed by DML queries where you are modifying the database. To exploit Parallel DML, the session from which a SQL query is issued must be specifically enabled for Parallel DML.
Each time an allocation rule is executed, a new database session is established. Each allocation starts by issuing a series of Alter Session statements based on parameters that you define in the FSI_PARALLEL_PARAMETERS table. This parameter table contains four parallel parameters as follows:
- Parallel Query
- Parallel DML
- Parallel Degree
- Parallel Degree Policy
Table H-1 FSI_PARALLEL_PARAMETERS Table after Seeded Values
| Column Name | Valid Values | Seeded Value |
|---|---|---|
| PROCESS_ENGINE_CD | 0 for Profitability Management | 0 |
| PARALLEL_QUERY | Disable, Enable, or Force | Disable |
| PARALLEL_DML | Disable, Enable, or Force | Disable |
| PARALLEL_DEGREE | Integers between 0 and 99 | Null |
| PARALLEL_DEGREE_POLICY | Manual, Limited, or Auto | Manual |
The parameter values found in FSI_PARALLE_PARAMETERS apply to all allocation rules. Currently, Oracle Parallel Execution is only supported for Profitability Management. OFSAA Funds Transfer Pricing and OFSAA Asset Liability Management employ a different technique for parallelism: OFSAA Application Multiprocessing. For more details on OFSAA Multiprocessing, see the OFS Advanced Analytical Applications Infrastructure Installation and Configuration Guide).
UPDATE FSI_PARALLEL_PARAMETERS SET
PARALLEL_QUERY = 'ENABLE',
PARALLEL_DML = 'FORCE',
PARALLEL_DEGREE = NULL,
PARALLEL_DEGREE_POLICY = 'AUTO';The specific Alter Session statements generated by the allocation engine for the different possible parallel parameter values are discussed as follows.