Oracle decided to dismiss the rule-based optimizer from version 10g, leaving the cost-based one as the only choice.
I think that a rule-based optimizer has the unvaluable positive side of being always predictable. I've seen instead Oracle 10g changing execution plans from night to day, leading to turtle-like performances.
Which could be the rationale behind this change?
views:
561answers:
6
+6
A:
Because everything you can do with RBO, can be done with CBO.
The CBO can be rule based too — more than that, you may decide the "rules" yourself.
To create your own "rules", you hint your query or do a CREATE OUTLINE which will hint it for you. As a result, you execution plan is stable.
The outlines are stored in a system schema called OUTLN, they are editable.
As for me, I always supply hints to my queries running in a production database.
Quassnoi
2009-01-29 21:29:12
You can't always add hints to your queries: think of products like Siebel CRM or Hibernate.
friol
2009-01-29 21:33:36
Thanks God I don't have to think of these products :) Even if I did, I would gather their queries and created outlines for them.
Quassnoi
2009-01-29 21:36:50
I've never heard of outlines. Is that a new feature? Do you have any reference for it?
friol
2009-01-29 21:39:43
@friol: Updated my post with a link to the manual
Quassnoi
2009-01-29 21:42:50
Ugh... Every query you hint? Why?
2009-01-29 23:13:10
The optimizer is free to take the hint or not. That is not a deterministic solution.
EvilTeach
2009-01-29 23:32:08
Oracle cannot ignore a hint assuming the hint is properly formed syntactically and that there exists a plan that can use the hinted path.
Justin Cave
2009-01-29 23:59:19
@Mark Brady: for a query to use the best plan, of course.
Quassnoi
2009-01-30 09:57:40
The point of the optimizer is that it makes best possible decisions based on changing data. If your data qualities are not changing, but the plan is, fix how you are collecting your stats.
SquareCog
2009-01-30 16:28:58
@SquareCog: you really think that an algorhitm can make a better decision than a qualified programmer?
Quassnoi
2009-01-30 16:38:05
Yes if you are working in an environment that is changing. Also, "qualified" is not as common as "programmer" ;)
SquareCog
2009-02-02 15:00:03
@SquareCog: Maybe there is a kind of environment that changes so fast and in such an impredictable way that none can even imagine what will happen the next second and has to rely on artificial intelligence to cope with this sutuation. Maybe. But I've never met that in practice, really.
Quassnoi
2009-02-02 17:35:51
Simplest case: dba creates an index. Your hard-coded query forces the optimizer not to use the new index even though it should. More serious: data streams, event databases, federated dbs across WANs (rate of tuple delivery varies with available bandwidth), etc.
SquareCog
2009-02-02 18:25:38
A real-life example from personal experience -- a database for genomic alignments and annotations, that went from a design for a few species and big genomes, to supporting microbial dna -- thousands of species with tiny genomes.
SquareCog
2009-02-02 18:26:25
@SquareCog: What do you mean: DBA creates an index? In my databases, programmer who writes queries create everything that his queries need to run fast: indexes, tablespaces, partitions, whatever. DBA makes backups and rebuilds RAIDS when the disks are out.
Quassnoi
2009-02-02 18:30:19
Sorry for multiple comments, SO limits reply size. Final point -- how many programmers know every kind of join Oracle is capable of? Of those, how many want to select one every time they write a query? This is what declarativity is all about, separation of WHAT from HOW.
SquareCog
2009-02-02 18:32:08
@SquareCog: As for now, this such called "separation of WHAT from HOW" is a myth, there is no AI smart enough to do it in practice, including Oracle's CBO. I don't care how many programmers know that, I just hire those who do.
Quassnoi
2009-02-02 18:35:33
ok, a programmer creates an index. Are you the only programmer touching the codebase? Do you require data analysts to be programmers with detailed knowledge of your data layout and relative sizes? The optimizer is your friend.
SquareCog
2009-02-02 18:35:48
@SquareCog: By the way, this "SQL separation" always reminds me of Joel Spolksy's article about Charles Simonyi, who tried to separate code prototyping from coding itself in Microsoft: http://www.joelonsoftware.com/articles/fog0000000034.html It didn't work in coding, it doesn't work in SQL.
Quassnoi
2009-02-02 18:40:04
@SquareCog: A programmer should know what kind of data he is dealing with, point. Be it C++ programmer, Java programmer or Oracle programmer. I thought it's obvious.
Quassnoi
2009-02-02 18:44:20
Quassnoi, v dannom sluchae "tochka" perevoditsya "period." Look, I wrote a whole conference paper on how optimizers black boxes and programmers want fine-grained control. I hear you. I am just saying the kind of fine control you want is only required in very specific cases, not as a general rule.
SquareCog
2009-02-02 18:56:47
@SquareCog: I can't say to a customer: "you see, there is a CBO, and it decided that you need to SELECT faster and INSERT slower". He's the customer, he decides what he needs, and our task is to translate his decisions to SQL hints.
Quassnoi
2009-02-02 18:57:15
Oh and one of your 6 upvotes is from me, so you don't need to get snarky..
SquareCog
2009-02-02 18:58:39
Not at all, just a friendly talk :)
Quassnoi
2009-02-02 19:03:07
+3
A:
The RBO is often predicatably bad as well as predictably good. It also doesn't support partitioning and some other database features. The CBO is much better, and as Quassnoi says plan stability is a feature of the CBO also.
David Aldridge
2009-01-29 21:49:37
+2
A:
(I am not a DBA.)
My understanding is that Oracle has been moving away from the RBO for a long time in favor of CBO. It seems useful to me to stop supporting a feature that is no longer in active development (given a long enough depreciation period) so that everyone is using the most effective features.
It's interesting that you called predictability an "unvaluable" effect of using the rule-based optimizer. It seems like when the data changes to make an execution plan sub-optimal it would be best to switch to a new one. Only in the case you alluded to where the optimizer flip-flops between two execution plan would there be a problem with picking the best plan for the the data you are actually querying. I'm not sure what advantage predictability is in more normal situation.
Ending support of the out-dated optimizer ought to free up support for the newer optimizer.
Jon Ericson
2009-01-29 22:15:07
+2
A:
The reason they moved to cost-based optimization is that it can perform better since its based on analyzing statistical information that the rule-based optimizer does not have.
To make the CBO work better, its important to understand the role that statistics gathering plays in execution plan changes which directly affect performance. For one thing, running statistics more or less frequently could help you. Here is a good article about the CBO and statistics:
+2
A:
I think you should do rule based programming. Don't think about the situation, follow a list of inviolate rules, no matter what the situation, no matter what you think is the better way, if the rules say use a FOR LOOP in case X then you have to use a loop, even if you know if there will only be 1, loop from 1 to 1.
Stipulate:
Every query has a best plan.
Every query optimizer will determine that plan x% of the time.
The RBO had nowhere else to go, it's percent accuracy is lower than the CBO to be sure, but it was never going to get any better. It was limited like any rule based system.
+3
A:
The RBO has been deprecated for a long time; it was really just retained for backwards compatibility with legacy applications. Oracle have been announcing the demise of the RBO since (IIRC) version 8, which came out about 10 years ago.
The RBO was deterministic, but not all that clever. Oracle was originally designed before cost-based optimisers were even available, let alone a mature technology. The RBO has been frozen for a long time and does not support a lot of features of modern Oracle engines.
Cost-based optimisation is much smarter. However, if you had queries optimised for the RBO, they might not play nicely with the CBO. You will probably have to re-write or hint your queries appropriately to tune them for the CBO. There is also a facility to specify a query plan and override the CBO with that plan. This will give you deterministic query execution with stable plans.
ConcernedOfTunbridgeWells
2009-01-29 23:42:31
This answer touches on what I think is the key point. The RBO cannot support new features (e.g. new access methods). I suspect that trying to invent rules to guide choices between all the access paths and joins now available would become prohibitively difficult.
Dave Costa
2009-02-02 14:58:23