H.1 SQL Hints

Oracle Financial Services Profitability Management allows users to exploit SQL Hints to tune the performance of allocation rules. SQL Hints provide a mechanism to instruct the optimizer to choose a desired query execution plan based on specified hint criteria. The Profitability Management allocation engine reads user-defined hints that are stored in the FSI_SQL_HINTS_OPTIONS table.

Profitability Management supports the following types of SQL hint:

• DML_HINT
• PERCENT_DIST_HINT
• TREE_FILTER_HINT
• FILTER_ON_HINT

Note the following:

• Hints must be defined by users; there are no default hints.
• There is currently no user interface for the definition of hints. Your DBA can assist you in defining, testing, and optimizing SQL Hints. SQL Hints need to be manually inserted into the FSI_SQL_HINTS_OPTIONS table.
• The FSI_SQL_HINTS_OPTIONS table is indexed by the following:

  The system identifier (SYS_ID_NUM) of the rule to which the hint applies.

  By the allocation engine's PROCESS_ENGINE_CD (zero).

  Currently, only the Profitability Management engine supports SQL Hints; additional analytical application engines may support SQL Hints in future releases.

• You may determine an allocation's SYS_ID_NUM by performing a mouse-over on the allocation rule's name in the Allocation Specification summary screen.
• Hints are limited to 250 characters.
• DML_HINT and FILTER_ON_HINT apply to all types of allocations.
• TREE_FILTER_HINT applies only to allocation rules that include a Hierarchy Filter.
• PERCENT_DIST_HINT applies only to Dynamic Driver allocations.