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
Pickleball spielen 
#1 von Kevin Closson am Januar 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 von Surachart Opun am Januar 20, 2011 - 06:33
Hi Uwe,
Awesome -) Great Sample for explained ^^
#3 von Uwe Hesse am Januar 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 von Uwe Hesse am Januar 20, 2011 - 09:06
Hi Surachart,
thank you for the nice feedback and for „facebooking“ the posting 🙂
#5 von Bhavik Desai am Januar 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 von Kerry Osborne am Januar 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 von Uwe Hesse am Januar 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 von Kerry Osborne am Januar 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 von Kerry Osborne am Februar 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 von Uwe Hesse am März 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 von yasinsaygili am Juli 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 von Uwe Hesse am Juli 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 von kevinclosson am Juli 1, 2011 - 18:19
Uwe,
The X2-8 uses hex-core Xeon 5600. The opening paragraph speaks of quad core (ala V2).
#14 von Uwe Hesse am Juli 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 von ulazmx am August 29, 2011 - 00:35
Very well explanation.
Thanks
#16 von Uwe Hesse am September 1, 2011 - 18:34
Thank YOU for the comment 🙂
#17 von Satish Chauhan am Januar 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 von Uwe Hesse am Januar 12, 2012 - 12:14
Thank YOU, Satish, for the nice feedback 🙂
#19 von saurabh am März 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 von Uwe Hesse am März 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:
https://uhesse.wordpress.com/2011/07/06/important-statistics-wait-events-on-exadata/
#21 von James am Oktober 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 von Uwe Hesse am Oktober 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 von Robin R am Februar 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 von Robin R am Februar 18, 2013 - 17:46
I had to flush buffer cache and re-execute to see the diff. Thanks. Please ignore previous posting
#25 von Bou Kal am November 14, 2015 - 12:55
conventional database indexes is still very important as It’s often stated that in Exadata, you don’t need conventional database indexes anymore as everything runs so damn fast that indexes are simply a waste of time and space. Simply drop all database indexes and things will run just as fast.
see why https://richardfoote.wordpress.com/2012/12/18/storage-indexes-vs-database-indexes-part-i-minmax-maxwells-silver-hammer/
Question : Do I still need Smart Scan if I do have well tune database using Database Indexes ?
See Exadata Smart Scan FAQ (Doc ID 1927934.1)
What are prerequisites for Smart Scan to occur ?
There are 4 basic requirements that must be met for Smart Scans to occur :
1) There must be a full scan of an object; that is, full table scans, fast full index scans and fast full bitmap index scans.
…
…
..
What are the situations that prevent Smart Scan from happening ?
o Scan on a clustered table
o Scan on an index-organized table
o Fast full scan on a compressed index
o Fast full scan on a reverse key indexes
o The table has row-level dependency tracking enabled
o The ORA_ROWSCN pseudocolumn is being fetched
o The optimizer wants the scan to return rows in ROWID order
o The command is CREATE INDEX using NOSORT
o A LOB or LONG column is being selected or queried
o A SELECT .. VERSIONS flashback query is being executed
o To evaluate a predicate based on a virtual column
o More than 255 columns are referenced in the query
o The data is encrypted and cell-based decryption is disabled
o If table has CACHE property
o If _serial_direct_read is turned off (NEVER)
o If the partitioned object’s size is less than _very_large_object_threshold (500MB default)
o Offloading is not done on serial DMLs.
o Serial Direct read is not applied for these cases (and thus no smart scan) :
– sql from a table function
– sql from dbms_sql
– sql from plsql trigger
o Smart scan is not enabled for the sql in plsql when plsql package is called by ‚CALL plsql‘
o Does not work on serial queries issued from shared servers
o The SQL in question must not be quarantined.
#26 von Uwe Hesse am November 16, 2015 - 10:27
Thank you for your contribution 🙂
#27 von Uwe Hermann am Dezember 2, 2016 - 18:49
Excellent Explanation
#28 von Sam am Februar 6, 2017 - 23:24
Smart Scan’s don’t work on CTAS (Create Table as Select) right ? Thanks
#29 von Uwe Hesse am Februar 7, 2017 - 13:23
Sam, the SELECT part of the CTAS may be done as a Smart Scan, the CREATE TABLE with the population of rows not.
#30 von Sam am Februar 7, 2017 - 16:23
Thanks Uwe..
What about ‚GROUP BY‘ statements ? Can Smart Scan work on those ?
#31 von kwan am November 15, 2018 - 23:05
Thanks UWE
I have one issue there with the parallel query with smart scan.
as long as parallel query kicks in, then smart scan is not working
do you know why ?
Thanks
kwan
#32 von Uwe Hesse am November 19, 2018 - 10:01
Hi kwan,
smart scan goes together with parallel query quite well, usually. It is actually more common to see both together than smart scan with serial statements.
One reason why a parallel query doesn’t use smart scan (but the same statement as a serial query does) could be in-memory parallel query. In this case we would expect the in-memory parallel query to be faster than the smart scan, though.