OraFAQ Forum: Performance Tuning » Changing the defaut number of Plans checked by optimzer

Home » RDBMS Server » Performance Tuning » Changing the defaut number of Plans checked by optimzer (Aix)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Changing the defaut number of Plans checked by optimzer [message #602321]

Tue, 03 December 2013 03:42

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

Report message to a moderator

Re: Changing the defaut number of Plans checked by optimzer [message #602384 is a reply to message #602321]

Tue, 03 December 2013 21:55

Kevin Meade
Messages: 2103
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

Report message to a moderator

Re: Changing the defaut number of Plans checked by optimzer [message #602403 is a reply to message #602384]

Wed, 04 December 2013 02:26

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

Report message to a moderator

Re: Changing the defaut number of Plans checked by optimzer [message #602412 is a reply to message #602403]

Wed, 04 December 2013 04:07

Roachcoach
Messages: 1576
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.

Report message to a moderator

Previous Topic:	Need help in tuning query
Next Topic:	FTS on table

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Dec 17 22:22:37 CST 2024