Re: Oracle 3.3.4 a performance dog?

From: Peter Sharman <psharman_at_us.oracle.com>
Date: Mon, 01 Mar 1999 10:00:09 -0800
Message-ID: <36DAD5A9.DE6F4C9C@us.oracle.com>

Jonathon

Sorry for the delay in responding, I've been offline for a week, and boy has the ,ail accumulated!

I'm going from memory here, since the work I was doing with this issue was over a year ago. In-list processing normally does perform conversion to multiple OR statements. I believe this can be overcome if wanted by a hint (NO_EXPAND), but that may be version specific.

If you want more details on how the optimizer actually changed, you'll probably find it either in the release notes (which I don't have), or by calling support.

HTH. Pete

Jonathan Lewis wrote:

> Peter,
>
> Could you expand on this briefly - Were IN lists converted
> to concatenated OR's or was it some other strategy that
> caused the problem.
>
> Jonathan Lewis
> Yet another Oracle-related web site: www.jlcomp.demon.co.uk
>
> > There were changes in the optimizer between these two releases
> >that made selects that did a lot of in-list processing (select ... where
> abc
> >in (.....)) perform slower. The upside was that most sites noticed a
> >performance improvement.
> >

Regards

Pete

Peter Sharman                              Email: psharman_at_us.oracle.com
WISE Course Development Manager            Phone: +1.650.607.0109 (int'l)
Worldwide Internal Services Education               (650)607 0109 (local)

San Francisco

"Controlling application developers is like herding cats." Kevin Loney, ORACLE DBA Handbook
"Oh no it's not! It's much harder than that!" Bruce Pihlamae, long term ORACLE DBA

Received on Mon Mar 01 1999 - 12:00:09 CST