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 4

9 August 2010 2 Comments Written by Christian Antognini

When I started writing the series of posts about Exadata Storage Server and the query optimizer, I didn’t expect to write more than three posts (part 1, part 2, part 3). Of course, things change. Hence, here is part 4 to cover a couple of things that I learned in the last couple of months.

In part 2 I pointed out that Oracle Database is not able to offload the processing of all datetime functions. This fact, to my surprise, was also referenced by Netezza in a recent paper entitled Oracle Exadata and Netezza TwinFin Compared. The essential thing to understand is that this limitation is due to bug 9682721. The fix is expected to be part of 11.2.0.2. According to my test cases (that Greg Rahn was so kind to execute against an early release of 11.2.0.2), offloading works correctly for all datetime functions but for the following three predicates.

  • months_between(d,sysdate) = 0
  • months_between(d,current_date) = 0
  • months_between(d,to_date(’01-01-2010′,’DD-MM-YYYY’)) = 0

Note that the MONTHS_BETWEEN function can basically be offloaded. The problem in these cases is that the offloading does not work when, for example, SYSDATE is used as parameter.

To have a full list of the functions supporting offloading, the “official reference” is available through the V$SQLFN_METADATA view. Here is a simple query to summarize the current situation.

SQL> SELECT offloadable, count(DISTINCT name)
  2  FROM v$sqlfn_metadata
  3  GROUP BY offloadable;

OFF COUNT(DISTINCTNAME)
--- -------------------
NO                  511
YES                 319

Another thing I would like to point out about offloading is that the feature can be controlled through the CELL_OFFLOAD_PROCESSING initialization parameter. By default it is set to TRUE and, therefore, offloading is used whenever possible. It goes without saying that offloading is disabled when it is set to FALSE. Note that it can not only be set at the instance and session level, but also at the statement level. The following example illustrate this (notice that only the first query uses offloading).

SQL> ALTER SESSION SET cell_offload_plan_display = always;

SQL> ALTER SESSION SET cell_offload_processing = true;

SQL> EXPLAIN PLAN FOR SELECT * FROM t WHERE id = 101;

SQL> SELECT * FROM table(dbms_xplan.display(format=>'basic +predicate'));

PLAN_TABLE_OUTPUT
---------------------------------------------------
Plan hash value: 3557914527

-------------------------------------------
| Id  | Operation                  | Name |
-------------------------------------------
|   0 | SELECT STATEMENT           |      |
|   1 |  PARTITION RANGE ALL       |      |
|*  2 |   TABLE ACCESS STORAGE FULL| T    |
-------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - storage("ID"=101)
       filter("ID"=101)

SQL> EXPLAIN PLAN FOR SELECT /*+ opt_param('cell_offload_processing' 'false') */  * FROM t WHERE id = 101;

SQL> SELECT * FROM table(dbms_xplan.display(format=>'basic +predicate'));

PLAN_TABLE_OUTPUT
---------------------------------------------------
Plan hash value: 3557914527

-------------------------------------------
| Id  | Operation                  | Name |
-------------------------------------------
|   0 | SELECT STATEMENT           |      |
|   1 |  PARTITION RANGE ALL       |      |
|*  2 |   TABLE ACCESS STORAGE FULL| T    |
-------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("ID"=101)

Another initialization parameter that controls offloading is CELL_OFFLOAD_DECRYPTION. This parameter is relevant for encrypted tablespaces only. With it you can specify whether the keys necessary to decrypt the data can be shipped to the cells. By default it is set to TRUE and, therefore, the keys are shipped. For security reasons you might want to set it to FALSE and disable offloading for encrypted tablespaces. Note that this parameter can only be changed at the instance level.

11gR2, Bug, Exadata, Query Optimizer
Partially Index a Table
SIOUG Conference in Portoroz

2 Comments

1 Ping/Trackback

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

    […] […]

  2. Bertrand Bertrand
    30 November 2012    

    Hello,

    I just want to let you know that I developed a script to extract exadata real-time metric information based on cumulative metrics.

    The main idea is that cumulative, instantaneous, rates and transition exadata metrics are not enough to answer all the basic questions.

    That’s why the script has been created as it provides a better understanding of what’s is going on on the cells right now.

    More details (on how and why) here : http://bdrouvot.wordpress.com/2012/11/27/exadata-real-time-metrics-extracted-from-cumulative-metrics/

    Please don’t hesitate to give your opinion and report any issue you may found with it.

    Thx
    Bertrand

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

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.