You know how you sometimes have one of those problems that just won’t let you sleep? Here’s one:
I’m updating the classware for our publicly available training classes.
I created an AGO measure. Works great.
I created a TODATE measure. Works great.
I displayed the AGO and TODATE measures side-by-side in BI Answers. No problem.
I then added a third column, just a simple base measure no less, and … it failed. The query returned an error ORA-00942 – table or view does not exist.
I spent several hours trying to figure out what was wrong. Checked out the sesion log. Ran the query in little pieces. Added code, deleted code, trying to find the answer.
So…. buried in the OBIEE Release Notes, version 10.1.3.2, is this little tasty tidbit:
If you use a version of Oracle Database 10g Release 2 prior to 10.2.0.3, you might encounter an issue with a Full Outer Join that causes severe database performance issues… (The VMware image that I’m working with uses 10.2.0.2.)
To work around this issue when using Oracle Database 10g Release 1 or 2, upgrade to Oracle Database 10g Release 2 Patch Set 2 (for 10.2.0.3). For performance reasons, this is the preferred workaround.
To work around this issue without installing Patch Set 2, modify the Database Features table using the Administration Tool. This configuration prevents the Oracle BI Server from sending SQL constructs that expose the issue in an unpatched Oracle Database 10g.
To modify the Database Features table:
1. In the Administration Tool, open the properties of the Physical Database metadata object.
2. Navigate to the Features tab.
3. Ensure that the values for
– PERF_PREFER_MINIMAL_WITH_USAGE, and
– PERF_PREFER_INTERNAL_STITCH_JOIN
are selected, that is, set to True.
My thanks to Duc Huynh for his assistance on this one!
Thank you for shopping a Woolworths.
