Oracle 9i : PB7 : Optimizer_mode

[Luis Cabral]

Hi

I have a datawindow with a very complex query, that blocked the computer
when run.

After some research, I found out that that query only works when
optimizer_mode = rule. As this parameter was set to CHOOSE, I modified the
POST_LOGON to issue an 'alter session set optimizer_mode=rule'. So far so
good.

[I know it must have a better solution, but I'll let the DBA do it...]

If I preview this DW in painter, it works great. If I execute its SQL in
sql*plus, ok also. But when I run the app, the DW still blocks! As if, in
runtime, for some reason, OPTIMIZER_MODE was set back to CHOOSE...

[Of course, I used the same user/database in the 3 situations.]

There isn't a single explicit reference to OPTIMIZER_MODE in the whole app.

What could be happening? Some configuration ou connection parameter? What
difference could exist between DW painter and runtime, in relation to SQL
execution?

Thanks!
Luis

[Luis Cabral]

I found what the problem was.

In fact, the query performed identically in the 3 situations.

What caused the strange behaviour is that the query should return about 1700
records, but it returns only 1600 and then blocks... I've never seen nothing
like this.

In DW preview, I was hitting the stop button before the DW retrieved all the
rows... The same in Sql*Plus... So it seemed to me that the query was ok.

Anyway, if someone could help in this new problem...

Thanks!

"Luis Cabral" <luisc...@nospam-ig.com.br> escreveu na mensagem
news:#zHNdIgUDHA.349@forums-2-dub...

[Jerry Siegel]

You might try converting the DW to syntax and trying a /*+ RULE */ hint. If
you run that from PB DBA it will probably treat it as a comment and ignore
it. I'm not sure about DW preview.
My wild guess on the blocking is that it might be searching for more
temporary space for intermediate results - your DBA may be able to monitor
that.

"Luis Cabral" <luisc...@nospam-ig.com.br> wrote in message
news:eSHjGOgUDHA.349@forums-2-dub...