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:

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:

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:

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:

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.

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.

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:

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.

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 :-)

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.

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;