How to set _optimizer_search_limit and _optimizer_max_permutations in Oracle10g.

Posted by user52856 on Server Fault See other posts from Server Fault or by user52856
Published on 2010-08-30T19:08:00Z Indexed on 2011/02/06 15:27 UTC
Read the original article Hit count: 641

Filed under:
|

I am working on a product that must support both MSSQL and Oracle (10g and 11g). I have some very complex queries that seem to run without issue on MSSQL 2005/2008, but very, very slow with Oracle. The CPU on the oracle server skyrockets for long periods of time, and it seems like the optimizer may be trying to find the best execution plan for the very complex query. I did some Googling to figure out how to limit the amount of time the optimizer spends on this, and came up with _optimizer_search_limit and _optimizer_max_permutations. Both of these parameters are hidden in Oracle 10g, and setting them in init.ora doesn't seem to make any difference.

How do I set these parameters in Oracle.

Or am I just totally barking up the wrong tree with the assumption that the optimizer is spending several minutes finding an execution plan?

Thanks.

© Server Fault or respective owner

Related posts about Oracle

Related posts about oracle10g