Exadata Part I: Smart Scan

As many of you will know, Oracle has introduced a new flagship in the core area: The Exadata Database Machine is a combined Hardware & Software offer, consisting of an 8 Node Cluster (each Node with 2 Six-Core CPUs and 72 GB RAM) and 14 Storage Servers (each with 2 Six-Core CPUs and 12 SAS or 12 SATA drives *) in case of a Full Rack, connected (RAC Interconnect & Storage) over Infiniband. Operating System is Oracle Enterprise Linux; Database Version is 11gR2 Enterprise Edition, both mandatory.

I have the privilege to get access to our Oracle University Database Machine – I really do love my job :-) But I am limited to only one Database Node with two Storage Servers – which explains the relatively slow speed during the demonstrations compared to a Full Rack.

The idea of this posting is to show that there is more behind the Exadata Database Machine than sophisticated and well combined Hardware, but that there is actually some kind of (Database) Intelligence built into the Storage Layer that enables it to do column & predicate filtering before it sends results to the Database Layer. This functionality is called Smart Scan:

SQL> create table sales as
select
'Oracle Enterprise Edition' as product,
mod(rownum,5) as channel_id,
mod(rownum,1000) as cust_id ,
5000 as amount_sold,
to_date
('01.' || lpad(to_char(mod(rownum,12)+1),2,'0') || '.2010' ,'dd.mm.yyyy')
as time_id
from dual connect by level<=2e7; 

Table created.
SQL> select bytes/1024/1024/1024 as gb from user_segments;

 GB
----------
 1.0625

Above created a roughly 1 Gig sized demo table that is now blown up to 4 Gig:

SQL> alter table sales nologging;

Table altered.

SQL> insert /*+ append */ into sales select * from sales;

20000000 rows created.

SQL> commit;

Commit complete.

SQL>  insert /*+ append */ into sales select * from sales;

40000000 rows created.

SQL> commit;

Commit complete.

SQL> select bytes/1024/1024/1024 as gb from user_segments;

 GB
----------
4.17822266

SQL> exec dbms_stats.gather_table_stats('ADAM','SALES')

PL/SQL procedure successfully completed.

The playing field is setup. We will look at first after the scan speed without Smart Scan:

SQL> alter session set cell_offload_processing=false;

Session altered.

SQL> set timing on
SQL> select /* NO_SMART_SCAN */ count(*) from sales where channel_id=1;

 COUNT(*)
----------
 16000000

Elapsed: 00:00:22.96
SQL> select /* NO_SMART_SCAN */ count(*) from sales where channel_id=1;

 COUNT(*)
----------
 16000000

Elapsed: 00:00:22.85
SQL> set timing off

Twice the statement to show that caching has not much to say in this situation. We needed about 23 seconds to scan about 4 Gig, with the Storage Servers delivering the full amount of data to the Database Layer. Now the very same statement with the standard functionality Smart Scan:

SQL>  alter session set cell_offload_processing=true;

Session altered.

SQL> set timing on
SQL> select /* WITH_SMART_SCAN */ count(*) from sales where channel_id=1;

 COUNT(*)
----------
 16000000

Elapsed: 00:00:05.58
SQL>  select /* WITH_SMART_SCAN */ count(*) from sales where channel_id=1;

 COUNT(*)
----------
 16000000

Elapsed: 00:00:05.64
SQL> set timing off

Runtime was reduced to less than 6 seconds with Smart Scan. Let’s examine the execution plans for the two statements. I included the comment (it is NOT a hint) to retrieve the statements and their execution plans easier from the Library Cache:

SQL> select sql_id from v$sql where sql_text like '%NO_SMART_SCAN%' and sql_text not like '%like%';

SQL_ID
-------------
81vy8y7fkvzta

SQL> select plan_table_output from table (dbms_xplan.display_cursor('81vy8y7fkvzta'));

PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  81vy8y7fkvzta, child number 0
-------------------------------------
select /* NO_SMART_SCAN */ count(*) from sales where channel_id=1

Plan hash value: 1047182207

------------------------------------------------------------------------------------
| Id  | Operation                  | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |       |       |       |   147K(100)|          |
|   1 |  SORT AGGREGATE            |       |     1 |     3 |            |          |
|*  2 |   TABLE ACCESS STORAGE FULL| SALES |    16M|    46M|   147K  (1)| 00:29:30 |
------------------------------------------------------------------------------------

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

 2 - filter("CHANNEL_ID"=1)

An ordinary full table scan as expected. Now the second trial:

SQL> select sql_id from v$sql where sql_text like '%WITH_SMART_SCAN%' and sql_text not like '%like%';

SQL_ID
-------------
91w7a8mx8c977

SQL>  select plan_table_output from table (dbms_xplan.display_cursor('91w7a8mx8c977'));

PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  91w7a8mx8c977, child number 0
-------------------------------------
 select /* WITH_SMART_SCAN */ count(*) from sales where channel_id=1

Plan hash value: 1047182207

------------------------------------------------------------------------------------
| Id  | Operation                  | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |       |       |       |   147K(100)|          |
|   1 |  SORT AGGREGATE            |       |     1 |     3 |            |          |
|*  2 |   TABLE ACCESS STORAGE FULL| SALES |    16M|    46M|   147K  (1)| 00:29:30 |
------------------------------------------------------------------------------------

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

 2 - storage("CHANNEL_ID"=1)
 filter("CHANNEL_ID"=1)

The Storage Layer did filter on the predicate channel_id=1 before transmitting the result to the Database Layer, which is the reason for the reduced runtime. Apart from Smart Scan, we have some more conventional ways to speed up the statement like doing it in parallel:

SQL> select /*+ parallel(sales) */ count(*) from sales where channel_id=1;

 COUNT(*)
----------
 16000000

Elapsed: 00:00:02.83
SQL> set timing off
SQL> select sql_id from v$sql where sql_text like '% parallel(sales) %' and sql_text not like '%like%';

SQL_ID
-------------
ckq93tnythxa7
QL> select plan_table_output from table (dbms_xplan.display_cursor('ckq93tnythxa7'));

PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  ckq93tnythxa7, child number 0
-------------------------------------
select /*+ parallel(sales) */ count(*) from sales where channel_id=1

Plan hash value: 3130505568

------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |          |       |       |  3409 (100)|          |        |      |            |
|   1 |  SORT AGGREGATE                |          |     1 |     3 |            |          |        |      |            |
|   2 |   PX COORDINATOR               |          |       |       |            |          |        |      |            |
|   3 |    PX SEND QC (RANDOM)         | :TQ10000 |     1 |     3 |            |          |  Q1,00 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE             |          |     1 |     3 |            |          |  Q1,00 | PCWP |            |
|   5 |      PX BLOCK ITERATOR         |          |    16M|    46M|  3409   (1)| 00:00:01 |  Q1,00 | PCWC |            |
|*  6 |       TABLE ACCESS STORAGE FULL| SALES    |    16M|    46M|  3409   (1)| 00:00:01 |  Q1,00 | PCWP |            |
------------------------------------------------------------------------------------------------------------------------

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

 6 - storage(:Z>=:Z AND :Z<=:Z AND "CHANNEL_ID"=1)
 filter("CHANNEL_ID"=1)

Parallel Query combined with Smart Scan was even faster, delivering

SQL> select 4.17/2.83 as GB_PER_SEC from dual;

GB_PER_SEC
----------
1.47349823

a scan rate of about 1.5 Gigabyte per Second. With an Oracle Database at hand, I don’t need a calculator :-)

No idea what the weird storage filter :Z>=:Z and :Z<=:Z means here, to be honest.

Conclusion: Exadata is not only strong hardware but also Database intelligence on the storage layer. Smart Scan means the capability of the Storage Layer to do filtering of columns and predicates before sending the result to the Database Layer.

Addendum:

Using Enterprise Manager (Grid Control or  Database Control flavor even with Exadata), we have an easier way to determine Smart Scans:

* Second Addendum: Meanwhile (May 2011), also the High Capacity Drives are SAS – no more SATA

About these ads

, ,

  1. #1 by Kevin Closson on January 19, 2011 - 18:06

    Hi Uwe,

    Nice post. Can you follow up with some analysis of join queries? Perhaps, 3 or 4 table joins?

  2. #2 by Surachart Opun on January 20, 2011 - 06:33

    Hi Uwe,
    Awesome -) Great Sample for explained ^^

  3. #3 by Uwe Hesse on January 20, 2011 - 09:05

    Hi Kevin,
    thanks for the comment & suggestion! My plan was to cover Storage Index, Compression and Flash Cache with the next postings. Maybe I come back to Smart Scan with Joins later on :-)

    Let me by the way congratulate you to your great Blog!

  4. #4 by Uwe Hesse on January 20, 2011 - 09:06

    Hi Surachart,
    thank you for the nice feedback and for “facebooking” the posting :-)

  5. #5 by Bhavik Desai on January 25, 2011 - 08:25

    No idea what the weird storage filter :Z>=:Z and :Z<=:Z means here, to be honest.

    This is because, you might have taken execution plan when statement was RUNNING (in execution mode) …right? I have observed the same predicates in execution plan output when i took the execution plan from other session when my SQL was running in another.

  6. #6 by Kerry Osborne on January 30, 2011 - 23:32

    Hi Uwe,

    I think the :Z>=:Z and :Z<=:Z stuff has to do with PX queries and the ranges of rowids assigned to the slaves. I'm not sure why it is so cryptically displayed though.

  7. #7 by Uwe Hesse on January 31, 2011 - 15:09

    @Bhavik,
    no, the statement was done already and I retrieved the plan from the library cache afterwards. You should be able to see that from the demonstration – I hoped :-)

    @Kerry,
    yes, it seems to appear only with parallel queries. But the condition doesn’t seem to make much sense, does it? At least not in this case.

  8. #8 by Kerry Osborne on January 31, 2011 - 16:52

    Yes the condition is just weird. You’d think they would use different variable names like
    :Z2>=:Z1 and :Z2<=::Z3 or something. Even on non-Exadata platforms there must be some assignment of rows to slaves, but there is no predicate like this that is displayed. I did do a short post on this over on my blog as well by the way:

    http://kerryosborne.oracle-guy.com/2011/01/storagezz-and-z/

  9. #9 by Kerry Osborne on February 26, 2011 - 22:04

    Hi Uwe,

    I just ran across this post again when I was searching for something else and noticed that I misspoke in my last comment on the (:Z>=:Z AND :Z<=:Z) predicate. It does of course show up on non Exadata platforms since 10.2. It's related strictly to row distribution to PX slaves. Just didn't want to leave that misleading comment hanging out there.

    Kerry

  10. #10 by Uwe Hesse on March 14, 2011 - 17:07

    Hi Kerry,
    thank you for the addition. I liked especially the advice “Don’t worry about it!” from the Oracle Engineer that you mentioned on your Blog :-)

  11. #11 by yasinsaygili on July 1, 2011 - 09:51

    hi Uwe

    Thanks fror Great sharing :) .I just want ask to question If we want use smart scan ,Should we use “cell_offload_processing=true” ? Or another situation what will i do ?

    greeting

    Okcan Yasin Saygili

  12. #12 by Uwe Hesse on July 1, 2011 - 12:42

    You would leave that parameter on the default value TRUE on Exadata, of course, to benefit from the built-in Database Intelligence of the Storage Servers. That’s what you pay the money for in the first place :-)

  13. #13 by kevinclosson on July 1, 2011 - 18:19

    Uwe,

    The X2-8 uses hex-core Xeon 5600. The opening paragraph speaks of quad core (ala V2).

  14. #14 by Uwe Hesse on July 1, 2011 - 20:24

    Thank you, Kevin, for the correction! Indeed, the 8 Database Servers of X2-2 are now with 2 six-core CPUs each, whereas the 2 Database Servers of x2-8 are with 2 eight-core CPUs. The Storage Servers for both flavors are coming with 2 six-core CPUs each. Have updated the posting accordingly.

  15. #15 by ulazmx on August 29, 2011 - 00:35

    Very well explanation.
    Thanks

  16. #16 by Uwe Hesse on September 1, 2011 - 18:34

    Thank YOU for the comment :-)

  17. #17 by Satish Chauhan on January 11, 2012 - 09:15

    Hi Uwe,

    fantabulous job by posting these learnings,in an area which is still in it’s infancy stage…and so much of buzz around of…

  18. #18 by Uwe Hesse on January 12, 2012 - 12:14

    Thank YOU, Satish, for the nice feedback :-)

  19. #19 by saurabh on March 13, 2012 - 13:51

    Thanks Uwe for smarty demonstrating Smart scan performance.

    We have Exadata Quarter Rack…I was trying the same method on our system as you have shown here, but I am unable to find predicate information in the dbms_xplan detail.

    Below is the detail plan:

    PLAN_TABLE_OUTPUT
    ——————————————————————————————————————————————–
    SQL_ID arbf250nbq4s5, child number 0
    ————————————-
    select /* WITH_SMART_SCAN*/count(*) from ROAMING_FACT_BKP_4_FEB_12

    Plan hash value: 1486873337

    ————————————————————————————————
    | Id | Operation | Name | Rows | Cost (%CPU)| Time |
    ————————————————————————————————
    | 0 | SELECT STATEMENT | | | 1372K(100)| |
    | 1 | SORT AGGREGATE | | 1 | | |
    | 2 | TABLE ACCESS STORAGE FULL| ROAMING_FACT_BKP_4_FEB_12 | 242M| 1372K (1)| 05:20:19 |
    ————————————————————————————————

    14 rows selected.

    Elapsed: 00:00:00.02

    Please let me know why it is like this.

    Thanks.

  20. #20 by Uwe Hesse on March 17, 2012 - 11:27

    Suarabh, you have no where condition in your query – therefore no predicate section. It could have been a smart scan in spite of that, because of column filtering. You may spot that with statistics like I demonstrated here:
    http://uhesse.wordpress.com/2011/07/06/important-statistics-wait-events-on-exadata/

  21. #21 by James on October 18, 2012 - 13:16

    Hi Uwe,

    Very Good Presentation, It would be more help full if you can share any material or Masternote for Exadata.

    Regards,
    James

  22. #22 by Uwe Hesse on October 23, 2012 - 16:51

    James, when you look on the right, there is the MOS Exadata Masternote mentioned: https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&doctype=ANNOUNCEMENT&id=1187674.1

  23. #23 by Robin R on February 18, 2013 - 17:35

    Uwe,
    Nice example to explain the concepts. I was able to recreate the steps on X2-2 1/4 rack but interesting both Smart and Non-Smart scan access time were quite the same 4.50 sec. I was able to confirm via dbms_xplan that one out used full access without Smart while the other did. Please see below output – Did I miss something? Thanks, Robin
    SQL> alter session set cell_offload_processing=false;

    Session altered.

    SQL> set timing on
    SQL> select /* NO_SMART_SCAN */ count(*) from sales where channel_id=1;

    COUNT(*)
    ———-
    16000000

    Elapsed: 00:00:04.75
    SQL> select /* NO_SMART_SCAN */ count(*) from sales where channel_id=1;

    COUNT(*)
    ———-
    16000000

    Elapsed: 00:00:04.78
    SQL> alter session set cell_offload_processing=true;

    Session altered.

    Elapsed: 00:00:00.00
    SQL> select /* WITH_SMART_SCAN */ count(*) from sales where channel_id=1;

    COUNT(*)
    ———-
    16000000

    Elapsed: 00:00:04.78
    SQL> select /* WITH_SMART_SCAN */ count(*) from sales where channel_id=1;

    COUNT(*)
    ———-
    16000000

    Elapsed: 00:00:04.52

  24. #24 by Robin R on February 18, 2013 - 17:46

    I had to flush buffer cache and re-execute to see the diff. Thanks. Please ignore previous posting

  1. Exadata « Oracle Scratchpad
  2. test Smart Scan with CELL_OFFLOAD_PROCESSING parameter | Oracle in Thai | Oracle in Thai | Oracle User Group in Thailand |
  3. Important Statistics & Wait Events on Exadata « The Oracle Instructor

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

Follow

Get every new post delivered to your Inbox.

Join 2,429 other followers

%d bloggers like this: