1565805 - Business Objects XI sending /*+ RULE */ to Oracle database with queries in SQL.

Symptom

• There is a query that comes from BO XI environment to Oracle that contains /*+ RULE */. This is not a report, but something BOXI is doing to look at Oracle metadata.
• In Oracle 10g, this appears to be causing an issue. It causes 450k buffer gets per execution.
• This results in latch waits and decreased performance time.
• Without the /*RULE*/ hint is much better. Execution time goes from 2-4 seconds to .4 seconds, the buffer will be greatly reduced.
• Where does this query come from and how /*RULE*/ hint can be removed from the query? This should not really be used for a cost based optimizer.

Environment

• BusinessObjects Enterprise XI 3.1.
• Windows Server 2003.
• Oracle 10G (10.2.3 or above)
• Middleware native client driver (Oracle 10.2.0.1).

Reproducing the Issue

Run or schedule any report based on Oracle (10.2.0.3/4) database using Oracle ODBC driver (10.2.0.1).

Cause

In newer versions of Oracle 10G, /*RULE*/ based optimization has been deprecated and older version of drivers still force and over-ride it over cost-based optimization.

Resolution

1. As for the ODBC driver, these are Oracle delivered drivers that can be downloaded from their site.
2. In the 10.2.0.3 driver there is a way to disable the RULE hint that does not exist in the 10.2.0.1 driver.
3. So even though the 10.2.0.1 driver will work with the 10.2.0.3 database, the newer driver corrects this problem.
4. Anyone using Oracle 10g should be sure to use the 10.2.0.3 driver so they can disable RULE hints.

See Also

A Hint is commented information that is embedded in a query, and used by the Oracle optimizer to choose an execution plan. You should consult your Oracle documentation for full information on the Hints that can be used, and how they can be used to optimize queries.
You enter the value for a Hint as a comment. A plus sign is used at the beginning, immediately after the comment delimiter, with no space between the comment delimiter and the plus sign:
/*+ <hint> */
Some useful Hints that you can use are as follows. Consult your Oracle documentation for a full list:
FIRST_ROWS: Optimizes response time.
RULE: Use rule-based optimization and not cost.
FULL: Does a full table scan on the table.
ROWID: Scans table by rowid.
INDEX_FFS Perform a fast full scan on the index rather than on the table.
FIRST_ROWS
On the Custom page, click Hints and type /* FIRST_ROWS */ in the values box under the parameter list, and click on Set. The SQL is:
SELECT /* FIRST_ROWS */
RESERVATIONS.CUST_ID
FROM RESERVATIONS

Keywords

Hint, Oracle, Slow performance, optimize.

Header Data

Released On	15.03.2011 00:58:38
Release Status	Released to Customer
Component	BI-BIP-ADM BI Servers, security & CrystalReports viewing in BI platform
Priority	Normal
Category	Problem