Q

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.

This was last published in February 2012

Dig Deeper on Oracle database performance problems and tuning

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Join the conversation

1 comment

Send me notifications when other members comment.

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

Please create a username to comment.

Getting a Smart Scan on the Exadata requires, minimally, the following two things:

1. A Full Table / Fast Full Index Scan
2. A Direct Path Read

The CBO has nothing to do with the decision to perform a Smart Scan. If you are looking at an execution plan that shows a Full Table Scan but your are not getting a Smart Scan, then you are not getting a Direct Path Read. In this case, the table is probably too small when compared to the "Small Table Threshold" so what you get is a non-Direct Path Read. This means - No Smart Scan.

If you are concerned about currently placed Hints, you can have the optimizer ignore Hints in your session using this statement:

alter session set "_optimizer_ignore_hints" = TRUE ;

At a high level, you should fall back to the two main rules at the top of this post. There are other conditions which will negate Smart Scans, but work on the easy stuff first. Once you satisfy the Smart Scan rules, you still may have to address The Small Table Threshold. If your tables are "too small," they won't get smart scanned. If I had to make a recommendation on managing the database in favor of Smart Scans, I would say you need to keep your SGA moderately sized so you your tables will look bigger. In the end, you need to test,test,test.

Tim Fox
Chief Technical Architect
Enkitec
www.enkitec.com
Cancel

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close