Home » RDBMS Server » Performance Tuning » Execution plan (oracle,11.2.0.2,RHEL)
Execution plan [message #633171] Thu, 12 February 2015 00:42 Go to next message
Prathikesh
Messages: 16
Registered: February 2015
Location: Hyderabad
Junior Member
Hi
I have captured the execution plan of a query using the package "DBMS_XPLAN.DISPLAY". If we reorder the conditions in the WHERE clause, is the predicate information changes ? I think, considering the COST BASED approach, reordering the columns in the WHERE clause will not impact the performance plan. Please suggest. Below are few of the parameters that I have set for my database.
optimizer_mode                       string      ALL_ROWS
optimizer_index_cost_adj             integer     100
optimizer_features_enable            string      11.2.0.2
timed_statistics                     boolean     TRUE
Re: Execution plan [message #633176 is a reply to message #633171] Thu, 12 February 2015 01:32 Go to previous messageGo to next message
Michel Cadot
Messages: 65377
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Generally no, but if the query text is very very complex, yes as optimizer limits its analysis to a certain number of possible execution paths.

Re: Execution plan [message #633196 is a reply to message #633171] Thu, 12 February 2015 02:47 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3123
Registered: May 2013
Location: World Wide on the Web
Senior Member
Prathikesh wrote on Thu, 12 February 2015 12:12
I think, considering the COST BASED approach, reordering the columns in the WHERE clause will not impact the performance plan.


Welcome to the forum!

A quote by T.Kyte on a similar question here -

Quote:
the cost based optimizer is rather insensitive to the ordering of where clauses, it assigns costs in order to determine what to do first and how to do things.

the old unsupported rule based optimizer was sensitive to the ordering, but not so the CBO.

Will I guarantee the SAME EXACT PLAN based on predicates in different order with the CBO? No - ties (operations that are computed to take the same/similar amount of work) could result in different plans given different predicates.

But in short, as a SQL coder - you should put the predicate in an order that makes sense to you, makes the query more readable.
Re: Execution plan [message #633506 is a reply to message #633196] Thu, 19 February 2015 13:04 Go to previous messageGo to next message
Prathikesh
Messages: 16
Registered: February 2015
Location: Hyderabad
Junior Member
Thank you for the information!!
Re: Execution plan [message #633539 is a reply to message #633506] Fri, 20 February 2015 16:31 Go to previous message
Kevin Meade
Messages: 2102
Registered: December 1999
Location: Connecticut USA
Senior Member
11gR2 Oracle automatically reorders predicates using a cost based approach anyway. So unless you have turned this off, your ordering is more or less ignored these days.

I even have an example of this in Chapter #2: Ways to use a Query Execution Plan, in my SQL Tuning book. It was a very interesting example for me as it involved a couple of unexpected co-operating problems that was fun to figure out. But the point is, what used to be true has "as usual" changed between releases.

Was there some specific reason you were asking this question or was it just for general knowledge?

Kevin

[Updated on: Fri, 20 February 2015 16:39]

Report message to a moderator

Previous Topic: Why does monitoring index fix performance?
Next Topic: A oracle query causing 100% cpu utilization and blocking other sessions.
Goto Forum:
  


Current Time: Sun Feb 18 02:01:46 CST 2018

Total time taken to generate the page: 0.30324 seconds