Hints in BO Universe to Improve Report Performance

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.

Hint in Bo Universe1

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.

Hint in Bo Universe2

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

Hint in Bo Universe3

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.

Hint in Bo Universe4

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

Hint in Bo Universe5

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 in Bo Universe6

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

Advertisements

5 thoughts on “Hints in BO Universe to Improve Report Performance

  1. hi Madesh,

    Can i have any example for

    1. Complex Universe
    2.Complex webi report
    3.Complex webi report.

    Thanks
    Srinivas

    • Hi Sarinivas, complex universe means I should have more number of joins, context, alias, aggregate aware, hierarchies and objects.

      Complex Webi report will have more tables with break, section and more number of formulas included.

      Thanks MadhesDWBI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s