Home » RDBMS Server » Performance Tuning » Changing the defaut number of Plans checked by optimzer (Aix)
Changing the defaut number of Plans checked by optimzer [message #602321] Tue, 03 December 2013 03:42 Go to next message
deadpoet69
Messages: 3
Registered: August 2006
Junior Member
Dear,

I'm looking for a command to change the default number of plans , the optimizer checked before choosing the better cost.

In the courses , we have seen , that the optimizer got a little time to get the first 500 plans and then via the cost base , choose the better one.

I need to increase this number of plans, 1000 , 2000 ... just to see if it helps us on a special case.

Anyone remember a command fot that ?

thanks
Re: Changing the defaut number of Plans checked by optimzer [message #602384 is a reply to message #602321] Tue, 03 December 2013 21:55 Go to previous messageGo to next message
Kevin Meade
Messages: 1907
Registered: December 1999
Location: Connecticut USA
Senior Member
I seem to recall in Oracle 8 there was a database parameter that controled the number of plan permutations. Least I think that is waht it did. Let me see if I can find it. I want to say though I think it was deprecated or obsoleted. But lets see what we find.

Here is it. Is this what you were looking for?

optimizer_max_permutations

OPTIMIZER_MAX_PERMUTATIONS and OPTIMIZER_FEATURES_ENABLE
Starting with Oracle Database 10g, the OPTIMIZER_MAX_PERMUTATIONS initialization parameter has been made obsolete. If you are upgrading from Oracle9i and have OPTIMIZER_FEATURES_ENABLE set to 8.1.7 or lower and OPTIMIZER_MAX_PERMUTATIONS explicitly set to 2000 in the parameter file, then the release 8.1.7 default of 80000 is used when you start up the Oracle Database 11g Release 1 (11.1) database.

Setting OPTIMIZER_FEATURES_ENABLE to 9.0.0 or higher sets the default to 2000.

Not sure what all the above is saying but I don't see this for 11g. There was also this one for limiting number of join combinations but it too has gone the way of dead ideas.

optimizer_search_limit

good luck. Kevin




Re: Changing the defaut number of Plans checked by optimzer [message #602403 is a reply to message #602384] Wed, 04 December 2013 02:26 Go to previous messageGo to next message
deadpoet69
Messages: 3
Registered: August 2006
Junior Member
Correct,

I've found it back just after the post yesterday.
"permutation" was the word I was seeking Smile

Thank you
Re: Changing the defaut number of Plans checked by optimzer [message #602412 is a reply to message #602403] Wed, 04 December 2013 04:07 Go to previous message
Roachcoach
Messages: 1125
Registered: May 2010
Location: UK
Senior Member
I had trouble with this - it still "cuts the corner" if it believes the query is going to be fast.

From my experiences there is an internal algorithm which prevents the optimizer spending "too long" on optimization relative to query time.

To use ridiculous figures for the sakes of example what I mean is that it'll not spend 10 seconds forming plans when every plan it has run through on the way is coming to a 0.5 seconds estimate, it just stops trying and runs it - no point spending significantly longer than the query will take trying to think how to do it.

Basically it's done to avoid "overthinking" and to date I've not been able to disable that in a way that would still leave a "real" plan coming out.
Previous Topic: Need help in tuning query
Next Topic: FTS on table
Goto Forum:
  


Current Time: Wed Apr 16 09:06:39 CDT 2014

Total time taken to generate the page: 0.10091 seconds