September 25, 2008

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?”

  1. gsalem on September 25th, 2008 6:08 pm

    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.

  2. Daniel Weinreb on September 26th, 2008 6:13 am

    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.)

  3. Curt Monash on September 26th, 2008 7:16 am

    Dan,

    Which product are you referring to?

    CAM

  4. Jay Jakosky on September 26th, 2008 4:21 pm

    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.

  5. Curt Monash on September 26th, 2008 7:09 pm

    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

  6. David Aldridge on September 29th, 2008 4:50 pm

    “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.

  7. Curt Monash on September 29th, 2008 5:12 pm

    I was talking with an Oracle development manager on the DSS side, and he spoke favorably of Oracle’s SQL Tuning chargeable option.

    CAM

  8. Oracle Database Machine and Exadata pricing: Part 2 | DBMS2 -- DataBase Management System Services on September 30th, 2008 3:06 am

    […] 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 […]

  9. Other notes on Oracle data warehousing | DBMS 2 : DataBase Management System Services on April 7th, 2011 11:39 pm

    […] 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 […]

Leave a Reply




Feed: DBMS (database management system), DW (data warehousing), BI (business intelligence), and analytics technology Subscribe to the Monash Research feed via RSS or email:

Login

Search our blogs and white papers

Monash Research blogs

User consulting

Building a short list? Refining your strategic plan? We can help.

Vendor advisory

We tell vendors what's happening -- and, more important, what they should do about it.

Monash Research highlights

Learn about white papers, webcasts, and blog highlights, by RSS or email.