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