Home » RDBMS Server » Performance Tuning » Force the optimizer to consider all join permutations (11.2.0.4 RHEL)
Force the optimizer to consider all join permutations [message #598374] Mon, 14 October 2013 07:21 Go to next message
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.
Re: Force the optimizer to consider all join permutations [message #598380 is a reply to message #598374] Mon, 14 October 2013 08:33 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

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

Re: Force the optimizer to consider all join permutations [message #598384 is a reply to message #598380] Mon, 14 October 2013 08:48 Go to previous messageGo to next message
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.
Re: Force the optimizer to consider all join permutations [message #598387 is a reply to message #598384] Mon, 14 October 2013 09:04 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

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

Re: Force the optimizer to consider all join permutations [message #598389 is a reply to message #598387] Mon, 14 October 2013 09:06 Go to previous messageGo to next message
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
Re: Force the optimizer to consider all join permutations [message #598400 is a reply to message #598389] Mon, 14 October 2013 09:45 Go to previous message
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
Previous Topic: Please help me to tune this procedure
Next Topic: database links question
Goto Forum:
  


Current Time: Fri Mar 29 02:25:08 CDT 2024