BusinessObjects Board

Passing Set Parameters and Hints to an Oracle Database

building semantic layer
#1

Earlier this month the DBA applied the following to the Oracle database (which resides on HP-Unix server):
Alter session set star_transformation_enabled=true;

This paramater has increased our query performance (against a star schema data mart) but we are experiencing long running queries against our “typical” relational data mart.

When I tried to reset the session, in either query panel ( I selected “Do Not Generate SQL before running”) or free-hand sql, to:
Alter session set star_transformation_enabled=false;
I get the following error message:
The only authorized SQL statement is “Select”. (QPF0001)

Can I create an object, in Designer, to apply this parameter on demand or do I need to modify my log-in process?

Business Objects version 5.12
Oracle version 8.1
HP-Unix version 11

Thanks for any help you provide.

Robb (BOB member since 2002-08-23)

#2

Create an object in your universe - call it Hint. Include it as First object in your Data provider in BO reporter.

For syntax see http://otn.oracle.com/docs/products/oracle9i/doc_library/901_doc/server.901/a87503/hintsref.htm#4894 - you will need an Oracle Technet ID, which is free - just sign up for Oracle Technet.

Example


SELECT /*+NO_MERGE(dallasdept)*/ e1.ename, dallasdept.dname 
FROM emp e1, 
  (SELECT deptno, dname 
   FROM dept 
   WHERE loc = 'DALLAS') dallasdept 
WHERE e1.deptno = dallasdept.deptno;

I am not sure if you can use a hint to prevent the use of star transformation though.

Alos, I encourage you to use the search feature here for “Hints”: https://bobj-board.org/t/15442

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

Oracle Hints In Object Defintions
#3

You cannot use ALTER SESSION commands in Free Hand Query SQL. All you can do is to have a stored procedure based query and put that ALTER SESSION command in that…

Anjan Roy (BOB member since 2002-07-10)

#4

There is no direct hint to disable star transformations, but if you could use different business objects connection for accessing the star schema and the “regular” schema, you could have the DBA turn off star transformations at the database level and include a star transformation hint on the star schema connection to turn it back on only where you need it.

slimdave :uk: (BOB member since 2002-09-10)