H.1.2 How to use SQL Hints

To enable one or more SQL Hints for an allocation rule, you must generate appropriate entries into the FSI_SQL_HINTS_OPTIONS table for the allocation rule to which you want your hints to apply. For general guidance on SQL Hints, see the Oracle Database Performance Tuning Guide.

Following is a sample query to insert a FILTER ON HINT and a TREE FILTER HINT into FSI_SQL_HINTS_OPTIONS:

INSERT INTO FSI_SQL_HINTS_OPTIONS
  (SYS_ID_NUM,
   PROCESS_ENGINE_CD,
   FILTER_ON_HINT,
   DML_HINT,
   TREE_FILTER_HINT,
   PERCENT_DIST_HINT)
VALUES
  (201230,
   0,
   '/*+ parallel(fsi_d_management_ledger, 8)*/',
   ,
   '/*+ parallel(ofsa_idt_rollup, 2)*/'
   ,
   )

In this example:

• SYS_ID_NUM is the system identifier for the allocation rule to which the hint is applicable. You may determine the system identifier for an allocation rule by performing a mouse-over on the rule's name in the Allocation Specification summary screen.
• PROCESS_ENGINE_CD is set to a value of zero for the Profitability Management engine. Other Oracle Financial Services Analytical Applications (OFSAA) engines may support SQL Hints in future releases. In designing hints to enhance the performance of slow-running rules, your DBA will typically begin by capturing the slow-running SQL. You may capture the SQL generated by an allocation rule from the OFSAA log files (first set Profitability Management -> Application Preferences -> Debugging Output Level to Show All SQL) or through generic database tracing (For more information about using Application Tracing Tools, see Oracle Database Performance Tuning Guide ). Once your DBA has captured the slow-running SQL and its execution plan, he or she can experiment offline to determine what form of hint will provide the best performance.