Deadly OPTIMIZER_SECURE_VIEW_MERGING=TRUE

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

Thanks a lot

Rick Denoire Received on Sat Dec 01 2007 - 19:30:36 CST