Striving for Optimal Performance

In the first and second post of this series I shared with you some basics about smart scan and gave some details about projection and restriction. The aim of this post is to cover the third basic technique: join filtering.

Join filtering is not something specific to the Exadata Storage Server. In fact, it is an Enterprise Edition feature available since Oracle Database 10g Release 2. Simply put, it is used to reduce data communication between slave processes in parallel joins. For more information about it I suggest you to read a paper I published in June 2008 entitled Bloom Filters. In it I describe not only what bloom filters are, but also how Oracle Database uses them. And, one of the use cases is join filtering.

What I want to show here is how Exadata Storage Server is able to take advantage of join filtering. For that purpose let’s have a look to the following execution plan:

-----------------------------------------------------
| Id  | Operation                        | Name     |
-----------------------------------------------------
|   0 | SELECT STATEMENT                 |          |
|   1 |  PX COORDINATOR                  |          |
|   2 |   PX SEND QC (RANDOM)            | :TQ10002 |
|*  3 |    HASH JOIN BUFFERED            |          |
|   4 |     JOIN FILTER CREATE           | :BF0000  |
|   5 |      PX RECEIVE                  |          |
|   6 |       PX SEND HASH               | :TQ10000 |
|   7 |        PX BLOCK ITERATOR         |          |
|*  8 |         TABLE ACCESS STORAGE FULL| T1       |
|   9 |     PX RECEIVE                   |          |
|  10 |      PX SEND HASH                | :TQ10001 |
|  11 |       JOIN FILTER USE            | :BF0000  |
|  12 |        PX BLOCK ITERATOR         |          |
|* 13 |         TABLE ACCESS STORAGE FULL| T2       |
-----------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("T1"."ID"="T2"."ID")
   8 - storage("T1"."MOD"=42)
       filter("T1"."MOD"=42)
  13 - storage(SYS_OP_BLOOM_FILTER(:BF0000,"T2"."ID"))
       filter(SYS_OP_BLOOM_FILTER(:BF0000,"T2"."ID"))

As you can see, join filtering is used. In fact, the operation 4 (JOIN FILTER CREATE) builds a bloom filter that, later on, is used by operation 11 (JOIN FILTER USE) to filter out the data that does not fulfill the join condition. However, the most important thing to notice in this execution plan is the STORAGE predicate applied by the operation 13. According to it the bloom filter is applied not only by the operation 11, but also by the operation 13. And, since the operation 13 is a smart scan operation, the STORAGE predicate is evaluated by the cells. This means that the reduction of data communication does not only take place between slave processes, but also between the cells and the database instances. Remarkable!

Today I installed for the first time the patchset 10.2.0.5. While reading the README file, I noticed the following piece of information.

To enable a new native full outer join implementation in the database, a user has to set the following underscore parameter:

_optimizer_native_full_outer_join =force

You can set this parameter for the system or for a specific session.

Besides dramatically improving the performance of a full outer join, the new implementation fixes a variety of issues, for examples a variety of ORA-942 (table or view doesn’t exists) and ORA-4331 (unable to allocate string bytes of shared memory) errors.

This issue is tracked with Oracle bug 6322672.

Great! At last we can officially take advantage of native full outer join also in 10.2 (the feature was officially introduced in 11.1, but was already “available” in 10.2.0.3).

Here is an example:

• By default native full outer joins are disabled (notice the implementation with the UNION ALL operation):

SQL> SELECT * FROM emp FULL OUTER JOIN dept USING (deptno);

Execution Plan
----------------------------------------------------------
Plan hash value: 2291915024

---------------------------------------------
| Id  | Operation            | Name         |
---------------------------------------------
|   0 | SELECT STATEMENT     |              |
|   1 |  VIEW                |              |
|   2 |   UNION-ALL          |              |
|*  3 |    HASH JOIN OUTER   |              |
|   4 |     TABLE ACCESS FULL| EMP          |
|   5 |     TABLE ACCESS FULL| DEPT         |
|   6 |    NESTED LOOPS ANTI |              |
|   7 |     TABLE ACCESS FULL| DEPT         |
|*  8 |     INDEX RANGE SCAN | EMP_DEPTNO_I |
---------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("EMP"."DEPTNO"="DEPT"."DEPTNO"(+))
   8 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")

• As suggested by the README file, the feature can be enabled at the session level:

SQL> ALTER SESSION SET "_optimizer_native_full_outer_join" = force;

SQL> SELECT * FROM emp FULL OUTER JOIN dept USING (deptno);

Execution Plan
----------------------------------------------------------
Plan hash value: 51889263

------------------------------------------
| Id  | Operation             | Name     |
------------------------------------------
|   0 | SELECT STATEMENT      |          |
|   1 |  VIEW                 | VW_FOJ_0 |
|*  2 |   HASH JOIN FULL OUTER|          |
|   3 |    TABLE ACCESS FULL  | DEPT     |
|   4 |    TABLE ACCESS FULL  | EMP      |
------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")

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.

Almost one year ago Iggy Fernandez asked me to write a short text for the Ask the Oracles column of the NoCOUG Journal. The topic was “Bind Variable Peeking: Bane or Boon?”. My text along with the ones of Wolfgang Breitling, Dan Tow and Jonathan Lewis were published in the August issue. For some (unknown) reasons I never published that text on this site. Today, I correct that oversight. The text can be downloaded from this page.

Even though the utilization of an Exadata Storage Server should be transparent for the query optimizer, when you look at execution plans generated in an environment using it you might notice slight differences. The purpose of this series of post is to summarize the differences I was able to observe.

Disclaimer: I do not have access to an Exadata Storage Server (cell, from now on…). All tests I did, i.e. generation of execution plans, were performed with an 11gR2 database using “regular” storage. All I did is to set the CELL_OFFLOAD_PLAN_DISPLAY initialization parameter to ALWAYS. As a result, this post contains untested assumptions based on what I was able to observe in such an environment. It goes without saying that any correction is highly welcome!

Smart scan is one of the key performance features available only when a cell is used. Its purpose is to offload to the storage layer part of the work that would usually be performed by a database instance. As a result, not only the amount of work performed by a database instance might be (much) lower, but also the amount of data transferred from a cell to a database instance might be strongly reduced. When smart scan is used a cell, instead of sending back to the database engine “regular” database blocks (as a “regular” storage layer would do), sends back packets of data containing only the relevant information (I guess they are similar to the messages exchanged during PX operations).

Three are the basic smart scan techniques: projection, restriction and join filtering. As of Oracle Database 11g Release 2 three additional improvements are available: storage indexing, hybrid columnar compression and encryption. But, honestly, I consider them “simple” enhancements (that might have a major impact, though) of the previous ones. In other words, they add nothing to the basic concepts.

In this first post I cover projection. In the next posts I will cover the other two.

Whenever a query does not reference all columns belonging to the tables referenced in the FROM clause AND smart scan is used, it makes no sense that a cell sends back to a database instance the data belonging to the unreferenced columns. Since it makes no sense, it’s not done. Instead, a cell is able to extract only the data of the referenced columns. In this way, especially when queries reference few columns from wide tables, the amount of data received by a database instance is much smaller. As far as I know this technique is not explicitly externalized in execution plans (I mean, you don’t see which columns are actually sent back to the database instance). But, I guess, if you see one of the new operations added to support Exadata (they all contains the keyword “STORAGE” in them), you take advantage of this technique. Here is an example with a FTS (notice the keyword “STORAGE”):

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

The full list of new “operations” that I was able to observe is the following:

• STORAGE FULL
• STORAGE SAMPLE
• STORAGE SAMPLE BY ROWID RANGE
• STORAGE BY ROWID RANGE
• STORAGE SAMPLE FAST FULL SCAN
• STORAGE FAST FULL SCAN
• STORAGE FULL SCAN
• STORAGE RANGE SCAN
• STORAGE FULL SCAN (MIN/MAX)

So, it seems that for the most common access paths there is a “smart scan version”.