Exadata Smart Scan

Here, we are discussing smart scan a feature of Exadata database Machine that can be used for data warehouse and OLTP Environment and will compare this with traditional Oracle database management system. Smart Scan feature is introduced for improving query performance. Let's take this example.

Traditional database:














  
1. Client issue a select query "select emp_id from emp where emp_id > 100000". User is looking for specific data only.

2. Database find out file and extents containing this table.

3. Database issue I/O to read all the blocks of the table into buffer cache.

4. All data blocks of table are fetched in to buffer cache.

5. SQL filtering is done to find relevant data.

6. Data is returned to the client.

Though, client only need specific data, but we fetch whole table data into buffer cache and process it and this involves lot of resources.like CPU processing, memory utilization and network bandwidth. This is because all intelligence is with database server only.

Exadata Database Machine: Now, Let see how Exadata (data warehouse and OLTP Environment) process this.

In Exadata intelligence is introduced at storage server level However, in traditional storage system there is no  intelligence at storage level. Or we can say, it's nothing but an intelligent store server.
Queries that perform table scan are processed at Exadata storage level and only relevant data is sent back to the Database server. Filtering rows, columns and join processing are done at Exadata server.











 



1. Client issue a select query "select emp_id from emp where emp_id > 100000". Here, user is looking for specific data only.

2. Database finds the data is stored in Exadata cell so a command is sent to the Exadata cells (A Single Exadata storage server is known as a cell).

3. Exadata data Storage Server scan data blocks to extract relevant rows and columns which satisfy sql criteria.

4. Exadata returns rows and columns to the Database Server but not block to store in buffer cache.

5. Database server collect result from all cell and combine it.

6. Data is returned to the client.

In this approach, we have saved a large amount of resources which were getting used in traditional query processing. The steps of processing data at Exata data cell is known as offloading.


6 comments:

  1. Hi Umesh,that's a good explanation on smartscan, however there is a statement above that you have made
    "Exadata returns rows and columns to the Database Server but not block to store in buffer cache".
    At the same time , in your other post "http://www.dbas-oracle.com/2013/06/What-is-Exadata-Architecture-Components-and-Main-Features-of-Exadata-Storage-Server.html"
    you are saying that
    " In a query with where clause only blocks with satisfy where clause condition will only go to database server not the entire table."
    So please clarify if the blocks are transferd to the DB or only the rows?

    ReplyDelete
    Replies
    1. Yes, you are right it returns only blocks satisfy search criteria not the rows.

      Delete
  2. Excellent post. Only missed to comment about "Columns projection" what makes Oracle Exadata Smart Scan even more powerful and aggressive performance reading/writing data.

    ReplyDelete
  3. Very nicely explained. Thank you for your work!

    ReplyDelete
  4. Appreciate Your Work...Thanks For Sharing

    ReplyDelete