Oracle Optimizer and Designer

building semantic layer
1 
 Steve,

 We jumped directly from 7.3.2.3 to 8.05, both running cost based
 optimization, and saw no problems of this sort.  However, that is not
 to say that such a problem might not exist in 7.3.4.

 I would suggest trying to run the sql that generates the tables/column
 list directly in a sql editor (sql programmer, DBartisan, etc), and
 check out the explain plan in prod v. dev.  The sql you need to run
 would be found in the \\businessobjects\oracle\stora7en.txt file.
 What you do based on the results of that test is up to you, but at
 least you'll be able to quantify the difference.

 Jason Beard
 -------------------------------------------------------------------

I’m using BO4.1 with an Oracle 7.3.4 backend on Unix.
Following a recent Oracle upgrade from 7.1 whenever I try to insert a new

table into a universe, it takes over an hour to respond with a table list
in the browser
window. When I do this in development in runs fine, but in production it
take forever
and slows down the server.
Production runs with Cost based optimization, development runs with rule
based.

Listserv Archives (BOB member since 2002-06-25)

2

STEVE MORTON schrieb:

BUSOB-L@LISTSERV.AOL.COM

Hi,

I’m using BO4.1 with an Oracle 7.3.4 backend on Unix.
Following a recent Oracle upgrade from 7.1 whenever I try to insert a new

table into a universe, it takes over an hour to respond with a table list
in the browser
window. When I do this in development in runs fine, but in production it
take forever
and slows down the server.
Production runs with Cost based optimization, development runs with rule
based.

Anyone experienced this?
Is it possible to manage the session optimizer mode from the client?

If you use external strategies, RE-ORDER the tyble in the FROM clause of the
strategy and add the RULE optimizer hint after the select clause, so that it looks
like (here as an example):

[STRATEGY]
TYPE=JOIN
NAME= External Strategy: Constraints

[SQL]
SQL=
SELECT /*+ RULE */
INSTANCE1.TABLE_NAME,‘|’,
INSTANCE2.TABLE_NAME,‘|’,
INSTANCE1.TABLE_NAME || ‘.’ || INSTANCE1.COLUMN_NAME
|| ’ = ’ ||
INSTANCE2.TABLE_NAME || ‘.’ || INSTANCE2.COLUMN_NAME, ‘|’,
’ ‘,’|’
FROM
USER_CONS_COLUMNS INSTANCE1,
USER_CONS_COLUMNS INSTANCE2,
USER_CONSTRAINTS
WHERE
USER_CONSTRAINTS.OWNER = USER
AND USER_CONSTRAINTS.CONSTRAINT_NAME = INSTANCE1.CONSTRAINT_NAME
AND USER_CONSTRAINTS.R_CONSTRAINT_NAME = INSTANCE2.CONSTRAINT_NAME
AND USER_CONSTRAINTS.CONSTRAINT_TYPE = ‘R’
AND INSTANCE1.POSITION = INSTANCE2.POSITION
;

This speeds up the query significantly…

hope this helps
Walter

DI Walter Muellner
Delphi Software GmbH, Vivenotgasse 48, A-1120 Vienna / Austria
Tel: +43-1-8151456-12, Fax: +43-1-8151456-21
e-mail: w.muellner@delphi.at, WEB: http://www.delphi.at

Listserv Archives (BOB member since 2002-06-25)