Help - Search - Members - Calendar
Full Version: Query tuning question in 10g
Oracle DBA Forums > Oracle > Oracle Forum
tuseau
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!
burleson
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
tuseau
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


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.
Invision Power Board © 2001-2014 Invision Power Services, Inc.