Dani's IT Blog

…moving from Oracle to Software Development

Oracle Exadata performance revealed – SmartScan – Part III

with one comment

In the first two posts I described what Exadata is and discussed the new concepts introduced with Exadata.

In this post I will show, how the predicate offloading works and how to analyze whether the database used SmartScan (offload processing) or not.

Let us start with a very simple query, which selects all A Class customers from the customers table. There is no Index on the is_a_class column and the table contains approximately 500 million rows.
SELECT * FROM opitz.customers WHERE is_a_class = 'Y';
and have a look at the execution plan

---------------------------------------------------------------------------------------
| Id  | Operation                 | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |           |   500K|    36M|  3100   (1)| 00:00:38 |
|*  1 |  TABLE ACCESS STORAGE FULL| CUSTOMERS |   500K|    36M|  3100   (1)| 00:00:38 |
---------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1 / CUSTOMERS@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("IS_A_CLASS"='Y')
Column Projection Information (identified by operation id):
-----------------------------------------------------------
   1 - "CUSTOMERS"."ID"[NUMBER,22], "CUSTOMERS"."FIRST_NAME"[VARCHAR2,200],
       "CUSTOMERS"."LAST_NAME"[VARCHAR2,200], "CUSTOMERS"."COUNTRY"[VARCHAR2,8],
       "IS_A_CLASS"[VARCHAR2,1]

This execution plan is without the usage of SmartScan, but the data resists in an Exadata Cell. Therefore we can see TABLE ACCESS STORAGE FULL instead of the well known TABLE ACCESS FULL. In this case the word STORAGE indicates that the operation was executed on an Exadata. I have set cell_offload_processing = false to force this execution plan. But what would the execution plan be, with the parameter cell_offload_processing set to the default value, which is true.

---------------------------------------------------------------------------------------
| Id  | Operation                 | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |           |   500K|    36M|  3100   (1)| 00:00:38 |
|*  1 |  TABLE ACCESS STORAGE FULL| CUSTOMERS |   500K|    36M|  3100   (1)| 00:00:38 |
---------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1 / CUSTOMERS@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - storage("IS_A_CLASS"='Y')
       filter("IS_A_CLASS"='Y')
Column Projection Information (identified by operation id):
-----------------------------------------------------------
   1 - "CUSTOMERS"."ID"[NUMBER,22], "CUSTOMERS"."FIRST_NAME"[VARCHAR2,200],
       "CUSTOMERS"."LAST_NAME"[VARCHAR2,200], "CUSTOMERS"."COUNTRY"[VARCHAR2,8],
       "IS_A_CLASS"[VARCHAR2,1]
26 rows selected.

In the Predicate Information the line storage(“IS_A_CLASS”=’Y’) was added. This line indicates that offload processing could be used for this query. It is very important to know, that the decision whether SmartScan should be used or not, is a runtime decision! When the optimizer calculates the best execution plan, it only decides whether SmartScan could be used, not whether it is used! This is very specific to Exadata and often misunderstood.

But how can we know, whether offload processing was used or not?

Oracle added some new wait events, which contain this information. A normal multiblock read from Exadata would look as follows
WAIT #33: nam='cell multiblock physical read' ela= 7552 cellhash#=398250101 diskhash#=786031317 bytes=40960 obj#=16639 tim=1286372833389835

A neat detail. We can not only see what was executed, but also on which disk of what cell, how many bytes where read and which object was retrieved.

When a SmartScan is done, the ‘cell smart table scan’ event will occur.
WAIT #2: nam='cell smart table scan' ela= 154 cellhash#=2520626383 p2=0 p3=0 obj#=15043 tim=1289240139259306

As we have seen the execution plan itself is not enough to know whether a SmartScan happened, only the according Wait events can tell the truth. In the next post I will write about predicate offloading when functions are used to evaluate the data.

Advertisements

Written by danirey

March 7, 2011 at 18:01

One Response

Subscribe to comments with RSS.

  1. […] my last post I was writing about predicate offloading. I showed that it is possible, to offload a filter such as WHERE columnX equals Y. But how useful […]


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: