BusinessObjects Board

Using optmizer hint objects

building semantic layer
#1

Hi All,
To improve the performance of some queries, I thought of including an object that has optimizer hint text in it like /*+ CHOOSE */

This object, whenh selected, appears fine in select clause. But when measure objects are selected, it also appears in Group by clause causing the query to fail. How do I prevent this from appearing in group by clause and make it appear only in select clause.

Also when the query runs, report should not have a column of its own for this hint object. Is that possible?

Anyone who has done this, can you share your experiences.

Thanks much

bofan (BOB member since 2003-05-27)

How to make BO use of an index object
data provider query
#2

One of the best things about BOB is we’ve discussed many topics over the years. Things like:

Oracle Hints In Object Defintions
Adding hints to queries

Have a look at those topics and see if they help. 8)

Dave Rathbun :us: (BOB member since 2002-06-06)

#3

A) Why do you need the hint in the first place? Cost based optimization performs overall so much better without having to worry about hints.

B) Do you want this hint to be used for all queries in your universe? If so modify your universe connection to include this hint.

C) Change your universe “hint” object to

/*+ CHOOSE */ ' '

and see how it works with measure objects

Finally, try a search on BOB, keyword: hint.

Andreas :de: (BOB member since 2002-06-20)

#4

In other words, remember that here on BOB:
Search is Your Friend™

Anita Craig :us: (BOB member since 2002-06-17)

#5

Thanks for kind answers. It resolved my issue. We use Rule based database :frowning: . In some cases like Oracle Order Entry universe, we have tremendous performance issues. We can not use hint connection definition level, only when users have issues in running a report due to performance issues, we want to suggest them to use this hint object.

Thanks again.

bofan (BOB member since 2003-05-27)

#6

You probably already know this, but just in case. If you are using the Oracle rules based optimizer, make sure you have the number of rows for each table in the universe and have the correct value for the Table_Weight setting in the *.prm file. (One version of BusObj 5.0.? had it wrong by default). This can be a tremendous performance issue.

Lee Drake :us: (BOB member since 2002-08-15)

#7

make sure you have the number of rows for each table in the universe and have the correct value for the Table_Weight setting in the *.prm file

What should be the correct value for this Table_Weight setting?

bofan (BOB member since 2003-05-27)

#8

I fought this problem for a long while, but it was a long time ago.

Try this topic Table Weighting for Table Order

If you do a search on Table Weight, you will get more results. The oldest 4 or 5 are from when I was working on it.

Lee Drake :us: (BOB member since 2002-08-15)