BO Universe Hint is one of the option to improve your Report / Query performance by forcing to optimize your Database servers in different ways. If you have complex report query which has more tables and complex joins, BO Universe level Hint would help to reduce the report refresh time at Query level.

Will discuss how Hints are helping to improve the report performance in ORACLE database.

• Hints can be created for any report by forcing to do some activity for a particular table in the report.

• Developer has to create Hints based on the report requirement at universe level.

• Mostly hints would help in Standard reports (reason, Ad-hoc reports developer does not know what are all the objects will be used and hint should be based on the object used in the report).

• Hints details are updated in some common object of that report and that hint object should be placed in first object in the Report Query panel.

• Hints can be used in universe connection level to force to do some additional activities whenever the report runs from that particular connection. Drawback of this kind of hint is, if many user runs report using that particular connection your database server might be overloaded. So these kinds of hints can be useful for standard scheduled report universes.

Oracle Hint Function in BO Universe Object

Below are some common / import hints for BO universe object to improve the report performance. For more hint option please refer to End of this Post.

•    Fact(table)
•    Parallel(table,2)
•    Parallel(table,4)
•    Parallel(table,8)
•    Full(table)
•    Use_hash(table1 Table2 Table3 …etc)
•    Index(table “ForeignKey column of that table”)
•    No_index(table, “Index column of that table”)

Above Hint function can be used for any of your objects depends on the complexity and that should be placed first object in your report query panel.

Create / Define Hint Object

1.    Create / update object by adding the hint within “/* +…..*/”. Example Branch Name object created with hint.

2.    Add the Branch Name object into your report query panel as a first object, and then only the Hint will take into an action. Otherwise this object would act as normal object.

3.    SQL query for the above report would be as shown below.

If you add more objects into that report as mentioned below, but if the Hint Object will have only 2 tables defined for optimization. It’s not necessary all the tables should be added into the Hint for the report. It’s depends on volume of data in the table. Your Hint should be based on, how the table is loaded and volume.

If you feel Your Product Table also should be forced in the optimization, add that table also in the Hint.

Forcing the table for optimization in the report should be decided after based on the report refresh performance. Same table hint function might be different for reports and that’s can be defined after checking the performance of your report.

Example -1

/*+ parallel(BRANCH_DIM, 2)
parallel(SALES_FACT, 2)
Parallel(PRODUCT_DIM,2)
*/

Note: Good to have a same numbers for Parallel process in a single object (parallel 2 in the above Example).

Example – 2

/*+ Full(BRANCH_DIM ) parallel(BRANCH_DIM, 2)
Full(SALES_FACT ) parallel(SALES_FACT, 2)
Full(PRODUCT_DIM ) Parallel(PRODUCT_DIM,2)
*/

Example – 3

/*+ Full(BRANCH_DIM )
Full(SALES_FACT )
Full(PRODUCT_DIM )
*/

Example – 4

/*+ Fact(SALES_FACT )
*/

Example – 5

/*+ Use_hash (BRANCH_DIM SALES_FACT PRODUCT_DIM)
*/

Oracle Hint Function in BO Universe connection

•    ALTER SESSION FORCE PARALLEL QUERY PARALLEL 2
•    ALTER SESSION FORCE PARALLEL QUERY PARALLEL 4
•    ALTER SESSION FORCE PARALLEL QUERY PARALLEL 8
•    ALTER SESSION FORCE PARALLEL QUERY

These connections should be placed in the universe connection parameters. In the universe connection parameters, we have option for to enter the “ConnectInit” in Custom parameters.

Hint – apply the optimization / Hint for particular tables.

Example:

• ALTER TABLE COUNTRY_DIM PARALLEL 2;
• ALTER TABLE ADDRES_DIM NOPARALLEL;

ConnectInit – Apply the Hint for entire schema.

This Universe Object level Hint & Universe Connection level Hint might show huge impact on your reports and this is one of the best ways to improve your report performance without changing the report and universe joins.

Note: If you use parallel process 8, means it will refresh the report query in 8 consecutive process and it might hit back your Database server badly, so better to avoid parallel process 8 in your hint.

For more Details about Oracle Hint functions, please refer to http://docs.oracle.com/cd/E11882_01/server.112/e16638/hintsref.htm#PFGRF94936