This discussion is archived
3 Replies Latest reply: Jun 24, 2013 6:47 AM by 968673 RSS

SQL Modification for Exadata

968673 Newbie
Currently Being Moderated

Hello, We took delivery of our X3 1/4 rack server last week, will be setting it up later this month.

 

In a presentation on the web, someone listed comparison test results of 15 SQL's running on Exadata vs normal RAC. What I found interesting the results were all over the place, some SQL's showed x10 improvement, while others x25 improvement, however there were some that only showed x2 and x4 improvement. The author didn't elaborate on this variance.


So the question is about SQL's that show very little improvementwhen run on a Exa Machine.I would like to get a better understanding of the 'types' of SQL's that donot run well on a Exa server and why?

 

It appears this class of SQL's, are not taking advantage of the smart scan orflash cache. What sort of changes would need to made to them to get them to x10 performance improvement on a exa? removal of indexes etc?

 

We have a mixed workload of data warehouse and OLTP databases that we are planning to migrate.

 

Thank you.

  • 1. Re: SQL Modification for Exadata
    UweHesse Expert
    Currently Being Moderated

    The truth is that not each and every statement can see a (10X) improvement on Exadata.

     

    On a high level view, you'll likely see improvements in terms of higher IO per second rates for OLTP migrations because of the Smart Flash Cache and improvements in terms of higher throughput (MB per second) for Data Warehouse migrations because of Smart Scans.

     

    Sometimes an index may indeed prevent a Full Table Scan with Direct Read that could be processed as a Smart Scan, but you can not generalize that as an advice to remove all indexes on Exadata. Indexes may still be useful also on Exadata. That is especially true for OLTP, while chances are that at least some indexes are no longer required for Data Warehouse.

    Reduced runtime of statements may also come along together with the compression via HCC for Data Warehouse systems.

     

    Kind regards

    Uwe Hesse

     

    "Don't believe it, test it!"

    http://uhesse.com

  • 2. Re: SQL Modification for Exadata
    Alex Fatkulin Explorer
    Currently Being Moderated

    Realize that, in a grand scheme of things, off-loading is a ratio of how much you asked for versus how much you really needed. If you ask the database to scan 100GB of data and you only need 1GB back you'll get 99% off-load ratio. This is a simplification since other stuff like columns projection affects this too but it works good enough to demonstrate the point. So the more you ask for and the less you need the more you will be able to off-load. These types of queries will generally benefit the most from the Exadata since they don't have to ship all the stuff back to be dealt with on the database nodes.

     

    Another way to think about the above situation is you need 100GB of input to get 1GB of output. Remember that a ratio of output to input is also called efficiency. From that perspective off-load ratio is nothing else but "query inefficiency ratio" and I think it would be much better if people thought about it that way and stop blindly pursuing higher off-load ratios at the expense of everything else.

     

    Let's say somebody figures out that you can partition the above table so instead of scanning 100GB of data you only need to scan 2GB. So now your off-load ratio becomes 50% but only because it's actually a much better query. These type of queries will generally benefit (a lot) less on the Exadata. Problem is most people will look at 50% off-load figure and decide that it's somehow not a good thing.

  • 3. Re: SQL Modification for Exadata
    968673 Newbie
    Currently Being Moderated

    Thank you both for your replies. It has helped me clarify my thinking.


    I like this comment, a good way of putting things

    "Another way to think about the above situation is you need 100GB of input to get 1GB of output. Remember that a ratio of output to input is also called efficiency. From that perspective off-load ratio is nothing else but "query inefficiency ratio"  "

     

    So essentially, if a query is not taking advantage of the four(4) main features of the Exa machine, then no performance imporvement

     

    1) flash cache

    2) offloading

    3) storage indexes

    4) columnar compression

     

    Thanks


Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points