Answer

Understanding Oracle Exadata Smart Scan

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?

    Requires Free Membership to View

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.

This was first published in February 2012

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: