So what’s Oracle’s MPP-aware optimizer and query execution plan story?
Edit: Answers to the title question have now shown up, and so the post below is now superseded by this one.
In most respects — including most data warehousing respects — Oracle’s query optimizer is the most sophisticated on the planet (even ahead of IBM’s, I’d say). But in all the Exadata discussion — and also in a good, comprehensive review of Oracle’s data warehouse technology — I haven’t seen any claims that Oracle has tackled the hard problems of parallel analytics.
Yes, Oracle is now getting data off of multiple disks onto multiple processors at once, without SAN bottlenecks, and doing some local filtering. That’s the heart of the Exadata storage story, and it’s indeed a huge advance over Oracle’s prior technology. But what happens to the data after that? It’s sent over to a RAC cluster. And unless I’m terribly mistaken, any further processing will be done on just a single node in that cluster.
Comments
9 Responses to “So what’s Oracle’s MPP-aware optimizer and query execution plan story?”
Leave a Reply
Curt,
you’re terribly mistaken: If you have RAC, you can make all your nodes work on the query, and all of them profit from exadata: they can scan/join/aggregate data and send it back to the node that your client is connected to.
Pardon me, when you say “Oracle’s query optimizer”, are you referring to the one in Oracle Database, the flagship RDBMS product? If so, I beg to differ. It is very, very hard to write queries for Oracle that will produce good performance. My own experience and that of my DB-savvy friends is that if you want your queries written right, you have to get a super-experienced Oracle wizard. I only know one such person, although I’m sure there are many more. But they cost a lot (as consultants).
Also, there is at least one product you can buy that takes in an Oracle query, and puts out a new Oracle query with the same semantics, but which executes much faster: a third-party query optimizer! (That’s almost as stupid as those third-party uninstall utilities for Windows.)
Dan,
Which product are you referring to?
CAM
I don’t know which one Dan refers to but they’re pretty common. There’s one inside Toad that parses the query, creates candidates and asks the Oracle optimizer to rate those candidates. You can then make a selection and execute the queries to confirm Oracle’s estimates.
I used the product briefly but regularly and it did an excellent job. And this is just an extra-value component to an already popular product. I can only imagine the effectiveness of other solutions.
Fair enough, Jay. I was just curious as to what Dan thought was the one and only such product — but upon rereading, he didn’t assert there was only one. Oops!
Thanks,
CAM
“there is at least one product you can buy that takes in an Oracle query, and puts out a new Oracle query with the same semantics, but which executes much faster” …
That would also be what the Oracle cost-based optimizer does in its query transformation step, wouldn’t it? Star transformations, unnesting subqeries, transitive closure etc?
With regard to getting queries right for Oracle, there may be some truth in Daniel’s statement for complex OLTP queries, but BI/DW queries are generally not rocket science — that is after all practically the raison d’etre for the star schema. So I question the relevance here.
I was talking with an Oracle development manager on the DSS side, and he spoke favorably of Oracle’s SQL Tuning chargeable option.
CAM
[…] look like good things to have no matter what. For example, as per a comment thread last week, what what Oracle packages as its no-added-charge optimizer is somewhat questionable, and many sites need a third-party product and/or Oracle’s extra-charge Tuning Pack to fill […]
[…] the big news this week is Exadata, and its parallelization or lack thereof. But let’s not forget the rest of Oracle’s data warehousing […]