Re: Deadly OPTIMIZER_SECURE_VIEW_MERGING=TRUE

Noons <wizofoz2k_at_yahoo.com.au> wrote:

>> Are there any other deadly preset parameters I should be aware of
>> after Upgrade to Oracle 10g, according to your experience?
>
>_unnest_subquery=true (default)

>even in 10.2.0.3 patched, I'm still getting
>a lot of complex Peoplesoft queries with
>problems with this one, particularly with the
>ubiquitous (select max(date_field) ...)
>subqueries of this product. Set it to false
>and problems vanish.

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?).

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.

>
>> Do you really recommend to let Oracle do things automatically? (SGA,
>> PGA, db_file_multibloc_read_count, etc.)
>
>if you don't have multiple schemas then it's fine.
>If you do, then watch out for tables with the
>same name in different schemas and auto sga:
>deadly combination!

It looks like a bug, I wouldn't otherwise understand. Would you confirm please?
How does the performance issue manifest in this case? Is it related to the tables with the same names only? Does SGA distribution get screwed having a general impact?

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.

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

Bye

Rick Denoire Received on Sun Dec 02 2007 - 13:41:43 CST