Exadata Smart Scan and Parallel Query

As documented, parallel query operations are offload eligible with Exadata Database Machine. Let’s put this to the test.

 

 

We can see that cell offload and Smart Scan worked well and produced a 68% savings in IO. Now let’s do a partition-wise join between SOE.ORDERS and SOE.CUSTOMERS. To demonstrate Smart Scan processing, I’ll make all indices on both tables invisible.

 

 

Notice how even though we joined SOE.ORDERS to SOE.CUSTOMERS, partition-wise joining enabled the optimizer to only do a “TABLE STORAGE ACCESS FULL” on the ORDERS table and cell offload kicked in and did is wonders, returning the result in 2.73 seconds. If we were, however, to add a condition in the SELECT statement to access data from CUSTOMER, it looks like this:

 

 

Still a nice result – 30 seconds to retrieve all that data, saving a great deal of IO. How does it hold up on subsequent runs? Results show similar 30-second response times – I’ve left off details. As one final step though, I’ll make the indexes visible again and see what sort of time and execution plan we get:

 

 

As we can see, this result actually retrieved more data over the cell interconnect (1.25 billion as compared to 684 million) and ran slightly longer. In summary, if this were the only type of query running against this database on Exadata and I wanted predictable, excellent performance, I’d drop the indexes. Of course, previous posts may show that for these OLTP tables, index access may very well still be preferable.

Summary

Smart Scan offload processing for parallel query operations works as advertised, and partition-wise join eligible queries see a benefit on Exadata.