14.1 Parallelize Executions using the FSI_OPTIMIZATION_SETTINGS Table

A typical Balance Computation implementation can involve a large number of computation instances given the number of Data Origins, GL Types and currencies. Frequently, many of these rules must be executed sequentially; and in larger implementations, end-to-end execution times can exceed allowable thresholds. One way to reduce end-to-end runtime is to execute as many processes as possible in parallel. Other ways to reduce your end-to-end runtime include tuning your database, employing hints on your slowest running computations (see SQL Hints), or upgrading your hardware (more CPUs, more memory, faster Input/Output subsystems, and so on). Having a more powerful database server will not improve your performance if hardware resources are left idle. When executing computation rules sequentially, you will utilize only a single CPU on your database server.

OFSBCE allows you to utilize Oracle Parallel Execution. To capture the idle system resources, Oracle Parallel Execution subdivides the SQL statements into multiple independent units of work each of which can run in parallel.

Note:

In OFSBCE, you can add the alter table partition for all the processing tables for each posting date or effective date of the journal entries.

The Oracle database supports parallel execution of SQL statements through the use of the following:

• The PARALLEL clause for selected tables and indexes
• Parallel SQL Hints
• Alter Session statements to enable subsequent SQL operations to run in parallel

Working with your DBA, you can choose to establish specific degrees of parallelism for selected tables and indexes utilizing the PARALLEL clause. You can also elect to employ parallel SQL Hints on selected computation rules (see SQL Hints). Either of these approaches can 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 Parallel Query can help, however, most of the computation execution time is consumed by the DML queries where you are modifying the database. To utilize Parallel DML, the session from which a SQL query is issued must be specifically enabled for Parallel DML.

Each time a computation rule is executed, a new database session is established. Each computation starts by issuing a series of Alter Session statements based on parameters that you define in the FSI_OPTIMIZATION_SETTINGS table. This parameter table contains four parallel parameters as follows:

• Parallel Query
• Parallel DML
• Parallel Degree of Parallelism
• Parallel Degree Policy

There must be only one row in the FSI_OPTIMIZATION_SETTINGS table. Upon installation, this one row is seeded with the following values:

Table 14-1 Seeded values in the FSI_OPTIMIZATION_SETTINGS table row

Column Name	Seeded Value in a Row
PROCESS_TYPE	2
PARALLEL_QUERY	1
PARALLEL_DML	1
PARALLEL_DOP
PARALLEL_DEGREE_POLICY	3

The data seeded into the FSI_OPTIMIZATION_SETTINGS table OOTB is shown in the following image.

Note:

There must be only one row of data for each PROCESS_TYPE and V_EXECUTION_IDENTIFIER combination. If there are more than one rows for a given combination, the engines refer to the row for which the rownum is returned as 1 for the given combination of values.

The columns in the FSI_OPTIMIZATION_SETTINGS table and possible values are as follows:

• The PROCESS_TYPE column: For the Process Type, 2, 4, 6, and 12 are passed from the PMF process when setting up the Service Tasks. The Process Types 8 and 10 are read by the system during the backdated daily and average balance processes. The possible values for this column are as follows:

  Table 14-2 The PROCESS_TYPE column details

  PROCESS_TYPE column	Process Name to which the Process Type is applicable
  2	Management Ledger Daily Movement Computation
  4	Management Ledger Average Balance Computation
  6	Management Ledger Revaluation Computation
  8	Management Ledger Backdated Daily Movement Computation
  10	Management Ledger Backdated Average Balance Computation
  12	Management Ledger Backdated Data Identification Process

• The PARALLEL_DML column: The default value is 1 - Enable. The possible values for this column are as follows:

  Table 14-3 The PARALLEL_DML column details

  PARALLEL_DML column	Associated Action
  1	Enable
  2	Disable
  3	Force

• •The PARALLEL_QUERY column: The default value is 1 – Enable. The possible values for this column are as follows:

  Table 14-4 The PARALLEL_QUERY column details

  PARALLEL_QUERY column	Associated Action
  1	Enable
  2	Disable
  3	Force

• The PARALLEL_DEGREE_POLICY column: The default value is 3 – Auto. The possible values for this column are as follows:

  Table 14-5 The PARALLEL_DEGREE_POLICY column details

  PARALLEL_QUERY column	Associated Action
  1	Manual
  2	Limited
  3	Auto

• The PARALLEL_DOP column: Use this column to mention the number of parallel threads required in an execution process. The possible values are integers between 0 and 99.
• The SOURCE_HINT column: This column contains database hints that are used during the select operations performed during processing. See the SQL Hints section for more information.
• The TARGET_HINT column: This column contains database hints that are used during the insert operations performed during processing. See the SQL Hints section for more information.
• The V_EXECUTION_IDENTIFIER column: This column is used to identify the optimization settings to a specific branch within the PMF process. This can be helpful in the following illustrated scenario.

  Scenario: When the volume distribution by the General Ledger types is such that the Assets contains a significantly higher volume when compared to the Revenue accounts. For performance reasons, the user may want to the process the journals specific to Assets at a parallelism degree of 16 v/s revenue accounts at a parallelism degree of 4. The value in this column helps the process to identify the row of optimization settings to be used for a specific branch. This column allows an input of type VARCHAR2 with a maximum length of 50 characters. This value is passed to the system from within the PMF process when you double-click the required Service Task. This illustration is shown in the following image (click the ).

  • Navigate to the Process Modeller page and select the Balance Computation Management Ledger process. In the Process Flow page, double-click any Service task, then select .

    Figure 14-1 Rule Implementation in the Service Task

    
    

    

    
    
  In the preceding illustration, the EXEC_ID_ASST Value is AST, which is same as the V_EXECUTION_IDENTIFIER column (see the next illustration) of the FSI_OPTIMIZATION_SETTINGS table for a process.

  Figure 14-2 V_EXECUTION_IDENTIFIER column value in the FSI_OPTIMIZATION_SETTINGS table

  
  

  

  
  

  Note:

  Ensure that the Rule engine Execution Identifier Value (for example, AST) is same as the V_EXECUTION_IDENTIFIER column value in the FSI_OPTIMIZATION_SETTINGS table for a PMF process.

The specific Alter Session statements generated by the computation engine for the different possible parallel parameter values are mentioned in the following sections.