G.6.1 Database-Level Tuning

Database Level performance tuning describes a group of activities used to optimize and standardize the performance of a database. This can be improved using the following options:

  • Simple View versus Materialized View
  • Cursor Sharing
  • Indexes Optimization

Simple View versus Materialized View

In case ALM processes a huge volume of source records; one may observe a significant number of hits to the views created on REV registration tables (for example, REV_TAB_COLUMNS_V) which result in generating a lot of I/O and cluster waits, in turn spending most of the process execution time spent querying these views only. In such a case, users can change the view to a materialized view. However, for any future upgrade/install customers need to take care of maintaining the changes post-installation.

Cursor Sharing

Cursor handling within the ALM Engine can be implemented using the CursorSharingMode entry in the ofsrm.ini file as mentioned below. It is observed that setting this value to "FORCE" results in the best performance.

  1. Update the CursorSharingMode entry in the ofsrm.ini file (File Location: $FIC_DB_HOME/conf) with cursor sharing parameter (EXACT, FORCE, SIMILAR).
  2. Engine reads the value against CursorSharingMode and sets CURSOR_SHARE at DB Session level accordingly.
  3. The cursor level setting is applicable only for the process or session hence the Engine executes the below statement if a value is set against the CursorSharingMode. ALTER SESSION SET CURSOR_SHARING = <Input Value in upper case>
    <Input Values> can be: EXACT, FORCE, SIMILAR
  4. If no value is supplied, then the Engine will not alter cursor sharing for that session or process.

Process Cash flows Indexes Optimization

While persisting cash flows, the existing unique index on FSI_O_PROCESS_CASH_FLOWS can be dropped and an index on RESULT_SYS_ID must be created before running an ALM process. Dropping the index on FSI_O_PROCESS_CASH_FLOWS is recommended when outputting cash flows for a large number of instrument records. After the process completes, the index must then be added back.