Understanding Oracle Exadata Smart Scan

Expert Shyam Varan Nath explains what Oracle Exadata Smart Scan does and how it can improve query performance on Exadata.

More on Oracle Exadata

Ask your own Oracle Exadata question

Can the Oracle DBA manage Exadata?

Read our Exadata server guide

What is Oracle Exadata Smart Scan and how does it work? Are there any best practices around them?

Over the years, the efficiency of storage technology and computational power of CPUs have increased exponentially. However, in any larger database system, the interconnect between the two acts as the limiting factor. Hence, if the amount of data needed by the compute node or the database engine is reduced, the system can perform better. When database users want, say, all the high value customers based on sales, we may write a SQL query like this:

SELECT customer_name, sales_amt FROM customer_sales WHERE sales_amt >500;

In the traditional Oracle database, this will result in a full scan of the table customer_sales. All the data blocks will be sent via the interconnect to the database compute node, where the predicate filtering will take place.  When Smart Scan is used in the Oracle Exadata System, the query predicate is pushed down to the storage sub-system by adding intelligence to that layer. Using the intelligent Database (iDB) protocol, the database node sends the query details to the storage sub-system, which only  sends relevant pieces of information to the interconnect. In our example, only the customer_name and sales_amt columns for customers where the sales_amt exceeded $500, is sent via the interconnect. This is an example of a Smart Scan. It can reduce the need for data transfer by many times.

The best practices around Smart Scan may involve not using indexes on the tables and allowing the cost-based SQL optimizer (CBO) to make the decision, depending upon the nature of the user application.  Hence, it is advisable to remove any hints from the SQL statements.

Dig Deeper on Oracle database performance problems and tuning