Striving for Optimal Performance
  • Blog
    • Archive
    • Categories
    • Search
  • Troubleshooting Oracle Performance
    • Description
    • Structure
    • Table of Contents
    • Forewords
    • Reviews
    • Downloadable Files
    • Addenda and Errata
    • First Edition
  • Public Appearances
    • Past Public Appearances
  • Contact
  • About

Exadata Storage Server and the Query Optimizer – Part 2

3 May 2010 5 Comments Written by Christian Antognini

In the first post of this series I shared with you some basics about smart scan and gave some details about projection. The aim of this post is to cover the second basic technique: restriction. Simply put, the aim of this technique is to offload to a cell the processing of predicates found in the WHERE clause. And, as a result, to reduce the amount of data that a cell has to send back to a database instance.

To check whether offloading is used for a given execution plan, you can have a look to the predicate information section of the DBMS_XPLAN output. If offloading takes place, a restriction based on a STORAGE predicate is displayed. Here an example (in this case the restriction “N=1” is offloaded to a cell):

------------------------------------------
| Id  | Operation                 | Name |
------------------------------------------
|   0 | SELECT STATEMENT          |      |
|*  1 |  TABLE ACCESS STORAGE FULL| T    |
------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - storage("N"=1)
       filter("N"=1)

As you can see in the previous example, it is interesting to notice that the STORAGE predicate is used along with a FILTER predicate based on the same restriction. Honestly, I don’t know why this is necessary. And, I guess, it’s not because of false positives. In fact the cell should be able to correctly apply this kind of restrictions.

It is essential to point out that offloading is only applied to predicates that in a non-Exadata system would be processed as FILTER predicates. In fact, when an ACCESS predicate has to be applied (e.g. an index look-up is performed), smart scan cannot be used.

Now that we have seen how a simple predicate is offloaded to a cell, it is interesting to ask ourselves the following questions: Which predicates are supported by a cell? What happens when predicates contain functions or expressions?

To answer this question I tested about a hundred single-row functions to find out whether all of them can be offloaded. Here is a summary of what I observed:

  • Numeric functions: they can all be offloaded with a single exception, the WIDTH_BUCKET function. For example the predicate “width_bucket(n,1,10,100) = 1” is not offloaded.
  • Character functions returning character values: they can all be offloaded.
  • Character functions returning number values: they can all be offloaded.
  • Datetime functions: the offloading of these functions is, in my opinion, not very consistent. On the one hand, only the DATE datatype seems to be supported. In fact, when a TIMESTAMP datatype is involved, offloading almost never happens. And, be careful, this is also true for simple predicates like “t = systimestamp” (note that “t” is a column of TIMESTAMP datatype). On the other hand, while predicates like “d = sysdate” (note that “d” is a column of DATE datatype) and “add_months(d,1) = to_date(’01-01-2010′,’DD-MM-YYYY’)” can be offloaded, something like “add_months(d,1) = sysdate” cannot. What I observed is that basically every datetime function can be offloaded provided that it is not used along with SYSDATE or CURRENT_DATE.
  • NULL-related functions: they can all be offloaded.

According to my tests, except for datetime functions, offloading can take place most of the time. This is a good thing. However, the limitations related to datetime functions are tough and, therefore, I expect that Oracle addresses this issue very soon.

Addenda 2010-08-09: have a look to this post for further discussion of the problem related to datetime functions.

11gR1, 11gR2, Exadata, Query Optimizer
Bind Variable Peeking: Bane or Boon?
Native Full Outer Join Officially Available in 10.2.0.5

5 Comments

1 Ping/Trackback

  1. SaiGanesh SaiGanesh
    9 August 2010    

    When we migrate from Oracle 10g to Exadata one of the key challenges I heard is to rewrite Oracle Cusors completely in Exadata as it doesn’t support cursors. Have you come across this and insights from you would be great.

    Reply
    • Christian Antognini Christian Antognini
      9 August 2010    

      Hi

      In my opinion somebody provided you wrong information. In fact, cursors are supported in an Exadata environment as in any other standalone or RAC environment. It’s just Oracle Database with some additional features.

      Cheers,
      Chris

      Reply
  2. David Aldridge David Aldridge
    17 August 2010    

    Hi Christian,

    Just noted this post because it is referenced in a Netezza white paper on Exadata here: http://www.netezza.com/eBooks/exadata-twinfin-compared.pdf

    Anyway, do you suppose that the restriction that “every datetime function can be offloaded provided that it is not used along with SYSDATE or CURRENT_DATE” might be related to something as mundane as the synchronisation of system clocks between the storage and database layers? If so then it could be solved by the rewrite of sysdate to a date literal at the database layer. Otherwise, I’m struggling to think why this is not rather easily implementable.

    Reply
    • Christian Antognini Christian Antognini
      17 August 2010    

      Hi David

      First of all, did you read the post I referenced in the last line of the post?

      Second, IMO, there is no synchronization between the db layer and the storage layer for the purpose of offloading datetime functions. Whatever they would do, it is simply impossible to have exactly synchronized timers. Hence, inconsistent results might be generated. Therefore, IMO, the date/time for stuff like SYSDATE must be provided by the instance where a SQL statement is started.

      Third, since predicates like “d=sysdate” are already offloaded, IMO a replacement like you suggested already takes place.

      Cheers,
      Chris

      Reply
  3. Oracle Exadata and Netezza TwinFin Compared – An Engineer’s Analysis | Structured Data Oracle Exadata and Netezza TwinFin Compared – An Engineer’s Analysis | Structured Data
    18 August 2010    

    […] […]

  1. Oracle Exadata and Netezza TwinFin Compared – An Engineer’s Analysis | Structured Data on 18 August 2010 at 21:39

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.