Hi,
I have a fairly large query involving over 25 joins. It runs fine in 9i, but in 10g it takes a long time (often over 200 seconds). I am running the query on a database with no data in it (zero rows returned). Are there massive difference in the optimizer in 9 and 10 that might be causing this issue? It seems like when I reach a certain number of joins, the 10 instance starts taking a very long time to create the execution plan.
Does anyone have any hints or tips when reading the execution plan that might pinpoint what's wrong? I have books but I'm still kind of new to oracle query tuning.
Platform: Windows 2003 server
Oracle version: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Any help is much appreciated!
Full Version: Query tuning question in 10g
Hi,
>> I have a fairly large query involving over 25 joins. It runs fine in 9i, but in 10g it takes a long time (often over 200 seconds).
That's very common! They re-wrote the whole CBO in 10g!
Check these important 10g upgrade notes, your solution will be there:
http://www.dba-oracle.com/t_slow_performan...ter_upgrade.htm
>> I have a fairly large query involving over 25 joins. It runs fine in 9i, but in 10g it takes a long time (often over 200 seconds).
That's very common! They re-wrote the whole CBO in 10g!
Check these important 10g upgrade notes, your solution will be there:
http://www.dba-oracle.com/t_slow_performan...ter_upgrade.htm
QUOTE (burleson @ Jun 25 2008, 04:11 PM) 
Hi,
>> I have a fairly large query involving over 25 joins. It runs fine in 9i, but in 10g it takes a long time (often over 200 seconds).
That's very common! They re-wrote the whole CBO in 10g!
Check these important 10g upgrade notes, your solution will be there:
http://www.dba-oracle.com/t_slow_performan...ter_upgrade.htm
>> I have a fairly large query involving over 25 joins. It runs fine in 9i, but in 10g it takes a long time (often over 200 seconds).
That's very common! They re-wrote the whole CBO in 10g!
Check these important 10g upgrade notes, your solution will be there:
http://www.dba-oracle.com/t_slow_performan...ter_upgrade.htm
Thanks...I'm having a look through, but when I set the optimizer_features_enable to 9.2 in the 10g instance, it doesn't fix the problem, so does that mean it's not the optimizer?
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.