Re: Deadly OPTIMIZER_SECURE_VIEW_MERGING=TRUE

On Dec 3, 6:41 am, Rick Denoire <educacion.super..._at_online.de> wrote:

> All right, I will pay sharp attention to that.
> (By the way: You can't change such an underscore parameter using the
> spfile, right? And then, one has to restart the DB, right?).

alter system set "_unnest_subquery"=false scope=both;

it's dynamic as well. the secret is in the quotes! ;-)

> I will try it in the test instance first, the main problem being
> collecting queries generated by the client in the production instance
> in order to build a kind of benchmark to be transferred and run in the
> test environment.

hang on a tick! Don't go about changing undoc parameters just because! Do it only if you see performance with complex subqueries going crazy. Undocumented parameters should not be used as a matter of fact, only when needed.

>
> It looks like a bug, I wouldn't otherwise understand.
> Would you confirm please?

Yes, it is a bug. Been there since 9i...

> How does the performance issue manifest in this case?

It doesn't. You just get updates on the wrong table.

> Is it related to the tables with the same names only?

Yes, absolutely.

> Does SGA distribution get screwed having a general impact?

Nope.

> Your assumption about having views directly hits my problem.
> This application does not allow direct user access to tables, but
> access goes through a schema having lots of views based on the tables.

That's fine. Shouldn't be a problem, other than the parameter you already found out about.

> Nice to know that the problem can be rationalized in some extent.

;-) Received on Sun Dec 02 2007 - 18:05:13 CST