How to set _optimizer_search_limit and _optimizer_max_permutations in Oracle10g

oracleoracle10g

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.

Best Answer

Never heard of a query taking 15 minutes to optimize.

First, I'd be checking the alert log. There may be some block corruption on a system table that is causing an issue.

Second, do you have stats gathered for all the tables being queried. 10g introduced dynamic sampling, by which the database will go look at the table for some stats if there are none in the data dictionary. If you have lots of very large tables, then gathering stats dynamically could be slow.

You can see if you've got (recent) stats with this

select table_name, last_analyzed from user_tables

If you are dealing with complex queries on large tables, then you should take some time to plan a stats gathering strategy.

Related Topic