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.
- Query is executed by the client.
- Smart Scan constructed and Query is sent from database server to the Storage Server.
- Smart scan identifies rows and columns withing terabyte data that match request.
- Only related data returned to Storage server.
- Consolidated result set built from all exadata cells.
- 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.
But When I execute any query with Smart Scan feature like following, it takes about 1 second.
Do you want to learn Oracle Database for Beginners, then read the following articles.
https://ittutorial.org/oracle-database-19c-tutorials-for-beginners/