Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

share|improve this question
1  
explain plan for your-precious-sql-statement; see if running explian takes several minutes –  Geraint Jones Aug 30 '10 at 19:32
    
Thanks. I just tried this on a query that returns in a few seconds when using MSSQL and takes 2 hours with oracle. The CPU on the server shoots to 100%, and it's been 15 minutes now with no execution plan returned. –  user52856 Aug 30 '10 at 20:23
add comment

1 Answer 1

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.