Re: Deadly OPTIMIZER_SECURE_VIEW_MERGING=TRUE

On 02.12.2007 02:30, Rick Denoire wrote:
> Hello
>
> After Upgrading a commercial application from 9.2.0.5 to 10.2.0.3 we
> got a disastrous performance, even though the new server was at least
> 10 times faster (in terms of CPU). The application manager did not
> follow test procedures prior to the upgrade, which lead us to a
> crisis.
>
> After some investigation I found that just setting the following
> parameter resulted in a huge (about 100-fold) performance increase:
> OPTIMIZER_SECURE_VIEW_MERGING=FALSE
>
> See for example:
> http://www.freelists.org/archives/oracle-l/07-2006/msg00260.html
>
> I did not dare to set any hidden parameters yet; perhaps those would
> have boosted performance even more.
>
> Can you confirm that reverting mentioned parameter to the non-default
> value (FALSE) improves performance so dramatically?
>
> What is the rationale of such detrimental default value of the
> initalization parameter that effectively renders the database
> "misconfigured"?
>
> Are there any other deadly preset parameters I should be aware of
> after Upgrade to Oracle 10g, according to your experience?
>
> Do you really recommend to let Oracle do things automatically? (SGA,
> PGA, db_file_multibloc_read_count, etc.)

I guess with this information given you cannot expect profound help. What did you do to pinpoint the source of the slowness? I mean, do you *know* where time is spent? If not, I'd investigate this first before doing any changes to the system.

Kind regards

        robert Received on Sun Dec 02 2007 - 05:44:32 CST