Magic Feature of Oracle Exadata – Smart Scan

Hi,

I will explain Smart Scan which is magic feature of Oracle Exadata in this article.

 

Before this post, If you don’t know What is the Exadata and want to learn, you can read following Exadata Tutorials.

 

 

Exadata Smart Scan feature allows most of the SQL processing to execute in the storage server instead of the database Server, which dramatically improves your query performance. Because Smart Scan reduces the volume of data sent to the database server and reduce CPU usage on database server.

I think that biggest difference of Exadata from non-exadata is Smart Scan, which is called Smart Data Find with Smart Scan feature. The architecture of Exadata Smart Scan can be specified as follows.

 

Queries are executed like above and following steps are performed in the Exadata.

  1. Query is executed by the client.
  2. Smart Scan constructed and Query is sent from database server to the Storage Server.
  3. Smart scan identifies rows and columns withing terabyte data that match request.
  4. Only related data returned to Storage server.
  5. Consolidated result set built from all exadata cells.
  6. Finally, related rows are returned to the client.

 

Smart Scan has the following capabilities.

 

  • Table and index scans are performed in the Cell Server and only related data is sent to database server memory.
  • Filter against comparisons like following.
    SQL> SELECT * FROM emp WHERE sal > 20000;
  • Column Filter
         SQL> SELECT eno,ename,sal,dept_no FROM emp;
  • Join Processing
  • RMAN Incremental Backup: Because it only detects the changing blocks, it allows to take backup at high speed.
  • Fast File Creation: Makes a significant contribution to specific operations such as creating tablespace.

 

Smart Scan does not support the following operations

  • Clustered table, index organized tables
  • FFS compressed ve reverse key indexes
  • CREATE INDEX with NOSORT
  • LONG or LOB column
  • Cache clause queries
  • Query more than 255 column

 

Following features must be set in the available disk groups for Smart Scan to run properly.

  • ‘compatible.rdbms’=’11.2.0.0.0’
  • ‘compatible.asm’=11.2.0.0.0. ‘
  • ‘cell.smart_scan_capable’=’TRUE’

 

In addition, the following parameters have the effect and control on Smart Scan.

  • CELL_OFFLOAD_PROCESSING = TRUE|FALSE
  • CELL_OFFLOAD_PLAN_DISPLAY = NEVER|AUTO|ALWAYS
  • SQL> SELECT /*+ OPT_PARAM(‘cell_offload_processing”true’) */
  • alter session set cell_offload_processing=true;
  • Also many _cell_offload and _kcfis hidden parameters contribute to the behavior

 

You can query If a smart scan is used or not while a query is running as follows.

SQL> EXPLAIN PLAN FOR SELECT * FROM EMP WHERE SAL > 20000;

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

 

When I execute any query without Smart Scan feature like following, it takes about 2:25 minutes.

 

Result1

 

 

But When I execute any query with Smart Scan feature like following, it takes about 1 second.

Result2

 

Mehmet Salih Deveci

I am Founder of IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience.I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource.I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks.I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients.If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress mehmetsalih.deveci@outlook.com.-                                                                                                                                                                                                                                                 -Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  mehmetsalih.deveci@outlook.com a mail atabilirsiniz.

Leave a Reply

Your email address will not be published. Required fields are marked *