OraFAQ Forum: Performance Tuning » Force the optimizer to consider all join permutations

Home » RDBMS Server » Performance Tuning » Force the optimizer to consider all join permutations (11.2.0.4 RHEL)

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

Force the optimizer to consider all join permutations [message #598374]

Mon, 14 October 2013 07:21

Roachcoach
Messages: 1576
Registered: May 2010
Location: UK

Senior Member

Hi all,

I'm looking to see if there is a way (fully expecting it to be an underscore, or two...) to force the optimizer to keep churning until all permutations are exhausted.

I'm aware that it, to paraphrase, cuts out when it's spent more time parsing than it would just running it based on it's estimates.

I've got some irritating problems with xml rewrite, xml indexes and access paths/cardinalities etc and I'm really needing the entire thing considered as a one off for debugging this. I've already cranked up the maximum permutations to the max but it's not enough, it shorts out after 5041 permutations (I'd set that to 80000 max).

I know you'd not want to do this in the real world but I cant get the damned thing to run the plan I want in a 10053 so I can see the values it has there. I know I can hint it, but I'm trying to ascertain why it's not even considering it in a "normal" parse. The whole plan is crap, actually, but with it being an xml index I'm limited on working out just why/what she's up to or why it is going wrong.

Report message to a moderator

Re: Force the optimizer to consider all join permutations [message #598380 is a reply to message #598374]

Mon, 14 October 2013 08:33

Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

There is "_optimizer_max_permutations", default 2000 per query block.

Report message to a moderator

Re: Force the optimizer to consider all join permutations [message #598384 is a reply to message #598380]

Mon, 14 October 2013 08:48

Roachcoach
Messages: 1576
Registered: May 2010
Location: UK

Senior Member

Yeah, I tried that up to 80000, it didnt give the desired result. It tried 5041 permutations then quit, presumably because the time it had spent was approaching the query execution time it estimated.

Report message to a moderator

Re: Force the optimizer to consider all join permutations [message #598387 is a reply to message #598384]

Mon, 14 October 2013 09:04

Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

And if you put the resource manager off, is it going further?

Report message to a moderator

Re: Force the optimizer to consider all join permutations [message #598389 is a reply to message #598387]

Mon, 14 October 2013 09:06

Roachcoach
Messages: 1576
Registered: May 2010
Location: UK

Senior Member

Its off

15:01:47 sql> sho parameter resource

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
resource_limit                       boolean     FALSE
resource_manager_cpu_allocation      integer     24
resource_manager_plan                string

Report message to a moderator

Re: Force the optimizer to consider all join permutations [message #598400 is a reply to message #598389]

Mon, 14 October 2013 09:45

Roachcoach
Messages: 1576
Registered: May 2010
Location: UK

Senior Member

Unfortunately even with _optimizer_search_limit set to 255, it still caps out at 5041.

I realise it's a bit off piste, but debugging xml indexes being unable to query the path table to ratify the values and its estimations it is using leaves me little recourse but massive 10053 traces.

Unless you folks know of a better way Smile

Report message to a moderator

Previous Topic:	Please help me to tune this procedure
Next Topic:	database links question

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Jan 30 12:14:47 CST 2025