Home » SQL & PL/SQL » SQL & PL/SQL » Optimizer Choosing a Path with higher cost
Optimizer Choosing a Path with higher cost [message #213660] Thu, 11 January 2007 14:26 Go to next message
annagel
Messages: 220
Registered: April 2006
Senior Member
I have been working on a data-dump process which essentially loads data from all areas of our system based on the restrictions the user passes into it. This process gets pretty complex if you use parameters because the different conditions that could be used by the user exist at all different points in the system. Under normal use the user won't be putting in all their conditions at once, just a few so I re-wrote the entire report as a set of 4 dynamic queries which hard-code the parameters passed into the procedure into the SQL text. The idea being that I could get the CBO to do a lot of the work for me. This has actually worked out pretty well so far, but in testing I ran into a bit of an oddity.

One of my four data queries was taking a very long time compared to the others. And after all this time it just correctly returned nothing. This is by far the most complex of the queries, but I figured I would look at the query just to see if there was anything that could be done. I ran the explain plan on the query and found that the query started at a table different from what I would have chosen. I figured maybe my reasons for choosing my starting table were unknown to the CBO and so it was making an assumption about cost that was untrue.

So I added a leading hint to get the optimizer to listen to me and start were I wanted to start. I re-ran the explain plan and found that my plan had a lower cost that the one chosen by the CBO. And not just slightly smaller significantly so. We are talking 80000 for the CBO and 20000 for my route. This also carried forward into execution as well. My path took about 45 seconds to arrive at the conclusion that there was no data to return, the CBO's path took 15 minutes.

Now I understand that the CBO is not always going to arrive at the best actual cost for running a query, but I had always assumed it would take the path that had the lowest statistical cost. Is this assumption incorrect? Or are their certain situations under which it does not apply?

I am not overly concerned about it because realistically I am dealing with a very small percentage of the times this request will be run, but the oddness of the thing stood out to me and I just wanted to throw it out there to see if anyone had any idea why it might happen.

Thanks for any ideas or nods in the right direction,
Andrew
Re: Optimizer Choosing a Path with higher cost [message #213663 is a reply to message #213660] Thu, 11 January 2007 14:51 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
For further analysis, I would enable 10053 trace event for the said query.
Re: Optimizer Choosing a Path with higher cost [message #213664 is a reply to message #213663] Thu, 11 January 2007 14:54 Go to previous messageGo to next message
annagel
Messages: 220
Registered: April 2006
Senior Member
You mean for running it? I know why my method works better when running, but why would it pick another method when as far as the cost based analysis it knows this as well?
Re: Optimizer Choosing a Path with higher cost [message #213665 is a reply to message #213664] Thu, 11 January 2007 15:05 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>> why would it pick another method
As far as i know, only 10053 event could answer that.
Regards
Re: Optimizer Choosing a Path with higher cost [message #213681 is a reply to message #213665] Thu, 11 January 2007 19:04 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
5-Table join problem

Ross Leishman
Re: Optimizer Choosing a Path with higher cost [message #213821 is a reply to message #213681] Fri, 12 January 2007 07:49 Go to previous messageGo to next message
annagel
Messages: 220
Registered: April 2006
Senior Member
Thank you for the help, took a look at the article and that makes sense, this query was far and away the most complex of the 4 and so it would make sense that this would cause the most problems.
Re: Optimizer Choosing a Path with higher cost [message #215123 is a reply to message #213665] Fri, 19 January 2007 07:04 Go to previous message
William Robertson
Messages: 1640
Registered: August 2003
Location: London, UK
Senior Member
Mahesh Rajendran wrote on Thu, 11 January 2007 15:05
>> why would it pick another method
As far as i know, only 10053 event could answer that.
Regards

I agree, it would be interesting to analyze the 10053 (CBO) trace to see what join orders it considered and why your one was missed. Possibly there are some missing or incomplete statistics somewhere.
Previous Topic: Changing datatype for the existing column
Next Topic: Erratic Procedure behaviour
Goto Forum:
  


Current Time: Thu Dec 08 02:15:50 CST 2016

Total time taken to generate the page: 0.21529 seconds