Oracle Exadata Smart Scan feature

 

There are various components of an Exadata machine.  The following components

are the primary ones:

Database servers: Enterprise-class servers that run database software,
  Automatic Storage Management (ASM), or the Oracle Grid Infrastructure.

Storage servers: Storage servers, or cells, that run Exadata storage server
  software, which manages I/O requests coming from database servers and requests
  for data returned to a user.

InfiniBand network: The internal Exadata network is built on InfiniBand,
  which is primarily designed for high-performance computing environments.

Exadata comes with software known as the Exadata Storage Software (ESS), which enhances the performance of a database. ESS, the storage management server installed on each storage cell, manages the storage servers and communicates
with database servers for any storage-related requests. 

Key features of ESS

Following are the key features of ESS:

Flash cache:  The Flash cache is a high-performance cache on cell servers
  that caches recently accessed objects.

Flash log: The Flash log provides a high-performance, low-latency, and
  reliable temporary store for redo-log writes.

EHCC: Exadata Hybrid Columnar Compression (EHCC) enables the highest
  levels of data compression. EHCC organizes data into sets of rows called
  compression units. Within a compression unit, EHCC organizes the data by
  column and then compresses the data.

 Storage indexes: Storage indexes are in-memory structures on the storage
  servers that are designed to reduce the time spent reading data from a disk
  on the storage servers.

Cell offloading: Unlike traditional storage design, the Exadata storage
  cells are designed to process some workload internally to reduce the overall
  burden on the compute or database nodes. This process is called cell offloading.

Smart Scan: Smart Scan allows most of the SQL processing to happen in
  the storage tier instead of the database tier, which dramatically improves
  query performance. Smart Scan reduces the volume of data sent to the database
  tier thereby reducing CPU usage on database nodes.

SQL processing

Traditionally, SQL processing happens on database servers only. In Exadata, SQL
processing is divided and also takes place on the storage tier.

Tradition SQL processing

1. A client submits a query.
2. This action triggers a full table scan.
3. The database maps the request to the file and corresponding extents, which contain the table being scanned.
4. Similar to a full scan, the database issues the I/O operations to read all the blocks.
5. All the queried blocks of the table are read into memory.
6. The process searches the blocks for the rows that satisfy the predicate.
7. Lastly, the requested rows are returned to the client.

If it is a big table, all the blocks from that table are read, transferred across the storage network, and copied into memory. The process reads many unwanted rows are read into memory to complete the requested SQL operation. The large volume of transmitted data consumes bandwidth, impacts response time, and creates an unnecessary burden on the database tier.
 

Exadata server SQL processing

With Exadata storage, SQL processing is handled much more efficiently because it uses Exadata storage software, which has database logic built into it. The following steps comprise Exadata SQL processing, as shown in the following diagram

1. A client submits a query.
2. The database server constructs an Intelligent Database (iDB) message, which    includes the query criteria. This iDB message goes to all storage servers in a rack.
3. The cellsrv component of the ESS scans the data blocks to identify the matching rows and columns that satisfy the request.
4. Every storage server executes the query criteria in parallel and sends only the relevant rows, or the net result, to the database server by using interconnect.
5. The database consolidates the result and returns the rows to the client. 

Smart Scan requirements

Following are the requirements to use Smart Scan:

• You can use Smart Scan for only full table scans or full index scans.
• Queries must perform direct-path reads.
• You must set the database initialization parameter `CELL_OFFLOAD_PROCESSING  to `TRUE`.
• You must save segments in disk groups that are completely stored on Exadata cells.
• The ASM disk groups that store the segment data must have the following  attribute settings:
  • compatible.rdbms` = 11.2.0.0.0 (or later)
  •  Compatible.asm` = 11.2.0.0.0 (or later)
  •   Cell.smart_scan_capable` = `TRUE`

Smart Scan examples

Execute the following steps, first with and then without Smart Scan enabled, to measure the Smart Scan effect on the statistics.

Step 1: Connect to database

Run the following command to connect to your database with SQL*Plus:

    [oracle@nd01db01 ~]$ sqlplus nd/nd
    SQL*Plus: Release 11.2.0.3.0 Production...

Step 2: Verify statistics

Run the following query to verify that the statistics are at or near zero:

    SQL> select a.name, b.value/1024/1024 MB from v$sysstat a, v$mystat b
    where a.statistic# = b.statistic# and (a.name in ('physical read total bytes',
    'physical write total bytes', 'cell IO uncompressed bytes')
    or a.name like 'cell phy%');
    where a.statistic# = b.statistic# and (a.name in ('physical read total bytes',
    'physical write total bytes', 'cell IO uncompressed bytes')
    or a.name like 'cell phy%');
    NAME                                                               MB
    --------------------------------------------------------------- ---
    physical read total bytes                                           0
    physical write total bytes                                        0
    cell physical IO interconnect bytes                               0
    cell physical IO bytes saved during optimized file creation       0
    cell physical IO bytes saved during optimized RMAN file restore    0
    cell physical IO bytes eligible for predicate offload             0
    cell physical IO bytes saved by storage index                      0
    cell physical IO bytes sent directly to DB node to balance CPU    0
    cell physical IO interconnect bytes returned by smart scan          0
    cell IO uncompressed bytes                                        0
    10 rows selected.

 

Step 3: Execute a select query with Smart Scan disabled

Run the following select query with the optimizer hint that disables Smart Scan:

    SQL> select /*+ OPT_PARAM('cell_offload_processing' 'false') */ count(*)
    from sales where time_id between '01-JUN-2017' and '30-OCT-2017'
    and amount_sold = 1;
    COUNT(*)
    ----------
    134055

Step 4: Verify statistics again

Run the following command to verify statistics again. Note that all the data processed by the query in step 3 (physical read total bytes) is returned to the database server over the storage network (cell physical IO interconnect bytes).

  SQL> select a.name, b.value/1024/1024 MB from v$sysstat a, v$mystat b
    where a.statistic# = b.statistic# and (a.name in ('physical read total bytes',
    'physical write total bytes', 'cell IO uncompressed bytes')
    or a.name like 'cell phy%');
    NAME                                                               MB
    --------------------------------------------------------------- -----------
    physical read total bytes                                           759.429688
    physical write total bytes                                        0
    cell physical IO interconnect bytes                               759.429688
    cell physical IO bytes saved during optimized file creation       0
    cell physical IO bytes saved during optimized RMAN file restore    0
    cell physical IO bytes eligible for predicate offload             0
    cell physical IO bytes saved by storage index                      0
    cell physical IO bytes sent directly to DB node to balance CPU    0
    cell physical IO interconnect bytes returned by smart scan          0
    cell IO uncompressed bytes                                        0
    10 rows selected.

Step 5: Reset the statistics

Run the following command to reconnect the session to the database to reset the session-level statistics:

    [oracle@nd01db01 ~]$ sqlplus nd/nd
    SQL*Plus: Release 11.2.0.3.0 Production...

Step 6: Verify statistics again

Run the following query to verify that the statistics are at or near zero:

 SQL> select a.name, b.value/1024/1024 MB from v$sysstat a, v$mystat b
    where a.statistic# = b.statistic# and (a.name in ('physical read total bytes',
    'physical write total bytes', 'cell IO uncompressed bytes')
    or a.name like 'cell phy%');

   NAME                                                               MB
    --------------------------------------------------------------- ---
    physical read total bytes                                           0
    physical write total bytes                                        0
    cell physical IO interconnect bytes                               0
    cell physical IO bytes saved during optimized file creation       0
    cell physical IO bytes saved during optimized RMAN file restore    0
    cell physical IO bytes eligible for predicate offload             0
    cell physical IO bytes saved by storage index                      0
    cell physical IO bytes sent directly to DB node to balance CPU    0
    cell physical IO interconnect bytes returned by smart scan          0
    cell IO uncompressed bytes                                        0

    10 rows selected.

Step 7: Execute a select query without Smart Scan disabled

Run the following select query without the optimizer hint that disables Smart Scan:

    SQL> select count(*) from sales where time_id between '01-JUN-2017'
    and '30-OCT-2017'and amount_sold = 1;

    COUNT(*)
    -------
    134055

Step 8: Verify statistics

Run the following query to verify the statistics. Note that the query still performs approximately 759 MB of I/O (physical read total bytes) as in step 4. However, this time only 1.7 MB is returned to the DB server (cell physical IO interconnect bytes). This is the result of a Smart Scan in action. In this case, Smart Scan is acting on I/O associated with this query because the cell physical
IO bytes that are eligible for predicate offload equals the physical read total bytes, and the cell physical IO interconnect bytes returned by smart scan equals the cell physical IO interconnect bytes.

    SQL> select a.name, b.value/1024/1024 mb from v$sysstat a, v$mystat b where a.statistic# = b.statistic# and (a.name in ('physical read total bytes', 'physical write total bytes','cell IO uncompressed bytes') or a.name like 'cell phy%')
  NAME                                                             MB
    ----------------------------------------------------------------    ----------
    physical read total bytes                                           759.429688
    physical write total bytes                                        0
    cell physical IO interconnect bytes                               1.71562805
    cell physical IO bytes saved during optimized file creation       0
    cell physical IO bytes saved during optimized RMAN file restore    0
    cell physical IO bytes eligible for predicate offload             759.429688
    cell physical IO bytes saved by storage index                      0
    cell physical IO bytes sent directly to DB node to balance CPU    0
    cell physical IO interconnect bytes returned by smart scan          1.71562805
    cell IO uncompressed bytes                                        759.429688
    10 rows selected.

Conclusion

The Smart Scan feature in Exadata enables SQL processing to happen at the storage tier, instead of the database tier, to improve query performance. 

Smart Scan reduces the volume of data sent to the database tier thereby reducing the CPU usage on database nodes.

Exadata Smart Scan: A Quick Overview

 

-----------------------------------------------------------------------------------------------------------------------------

Simply put, Smart Scan is the capability of an Oracle Database to offload SQL processing to the Exadata Storage Servers. 

It is awesome because the database itself has less data to process once the storage servers process a large amount of data and return just a small portion to the database itself. 

Smart Scan works better with Data Warehouse/DSS databases than with OLTP databases. 

I’m not saying that an OLTP database cannot take advantage of Smart Scan. 

The thing is that OLTP database is normally defined by a database that gets single-row queries and Smart Scan works better with many, many rows. 

We all know that most database workloads are mixed, so we have both DW/DSS and OLTP queries. 

For the Smart Scan to work, the SQL you run must have these requirements:

• The segment you are querying must be stored in an Exadata Database Machine where the disk group with the cell.smart_scan_capable attribute is set to true.
• A Full Table Scan or an Index Fast Full Scan operation must occur.
• The segment must be big enough to fire a direct path read operation.

With all those three requirements met, there will be a Smart Scan operation. Let’s see an example: Here I have my table SALES which has 1.9GB of data and 20 million rows:

SQL> select segment_name, segment_type, bytes/1024/1024 mb from user_segments where segment_name='SALES'
  
 SEGMENT_NAME           SEGMENT_TYPE           MB
 -------------------- ------------------ ----------
 SALES                  TABLE                1984
  
 SQL> select count(*) from sales
  
  COUNT(*)
 ----------
  20000000
  
 Elapsed: 00:00:03.97
 

To control the cell offloading capability, we have the cell_offload_processing parameter which defaults to true, meaning we can use Smart Scan by default if we are running our database in an Exadata:

SQL> show parameter cell_offload_processing
  
 NAME                                 TYPE         VALUE
 ------------------------------------ ----------- ------------------------
 cell_offload_processing              boolean      TRUE
 

I am changing that parameter to false with a hint for the execution:-

SQL> select /*+ OPT_PARAM('cell_offload_processing' 'false') */ max(ORDER_DATE) from SALES;
  
 MAX(ORDER
 ---------
 28-SEP-19
  
 Elapsed: 00:00:16.52
 

We can see above that the execution time was 16.52 seconds without the Smart Scan. We can see below when I query the statistics for the session that the Smart Scan capability was not used. We see 1901MB of physical reads and 1901MB were returned by the interconnect from the cell (storage servers) to the database servers:

SQL> select s.name, m.value/1024/1024 mb from v$mystat m, v$sysstat s where m.statistic#=s.statistic# and (s.name like '%physical IO%' or s.name like '%optimized%' or s.name like 'physical%total bytes');
  
 NAME MB
 ---------------------------------------------------------------- ----------
 physical read requests optimized 7.6294E-06
 physical read total bytes optimized .921875
 physical read total bytes 1901.14063
 physical write requests optimized 0
 physical write total bytes optimized 0
 physical write total bytes 0
 cell physical IO interconnect bytes 1901.14063
 cell physical IO bytes saved during optimized file creation 0
 cell physical IO bytes saved during optimized RMAN file restore 0
 cell physical IO bytes eligible for predicate offload 0
 cell physical IO bytes saved by storage index 0
 cell physical IO bytes sent directly to DB node to balance CPU 0
 cell physical IO interconnect bytes returned by smart scan 0
 cell simulated physical IO bytes eligible for predicate offload 0
 cell simulated physical IO bytes returned by predicate offload 0
  
 15 rows selected.
  
 Elapsed: 00:00:00.01
 

Now I’m disconnecting and reconnecting to reset the session statistics and I’m running the query without any hint so my execution will get the default value of true for the cell_offload_processing parameter:

SQL> select max(ORDER_DATE) from SALES;
  
 MAX(ORDER
 ---------
 28-SEP-19
  
 Elapsed: 00:00:04.36
 

We can see the time dropped by four times. That is because the Smart Scan feature was used. 

We can confirm that by querying the session statistics again:

SQL> select s.name, m.value/1024/1024 mb from v$mystat m, v$sysstat s where m.statistic#=s.statistic# and (s.name like '%physical IO%' or s.name like '%optimized%' or s.name like 'physical%total bytes');
  
 NAME MB
 ---------------------------------------------------------------- ----------
 physical read requests optimized 0
 physical read total bytes optimized 0
 physical read total bytes 1901.14063
 physical write requests optimized 0
 physical write total bytes optimized 0
 physical write total bytes 0
 cell physical IO interconnect bytes 275.122597
 cell physical IO bytes saved during optimized file creation 0
 cell physical IO bytes saved during optimized RMAN file restore 0
 cell physical IO bytes eligible for predicate offload 1901.14063
 cell physical IO bytes saved by storage index 0
 cell physical IO bytes sent directly to DB node to balance CPU 0
 cell physical IO interconnect bytes returned by smart scan 275.122597
 cell simulated physical IO bytes eligible for predicate offload 0
 cell simulated physical IO bytes returned by predicate offload 0
  
 15 rows selected.
  
 Elapsed: 00:00:00.00
 

We see now that the total number of physical reads is the same, but the bytes returned by the interconnect is only 275MB which is also the same amount returned by Smart Scan. 

That is why the query ran faster; the storage servers processed the data and returned only what matters to my query. 

The Smart Scan feature gets the advantage of the Column Projection and Predicate Filtering operations meaning that only the data from the rows and the columns that we are actually querying are returned to the database. 

I mentioned that a Direct Path Read operation must occur for the database to be able to use the Smart Scan feature. 

Let’s see an example when I disable the Direct Path Read by changing the hidden parameter _serial_direct_read to never:

SQL> alter session set "_serial_direct_read" = never;
  
 Session altered.
  
 Elapsed: 00:00:00.00
 SQL> select max(ORDER_DATE) from SALES;
  
 MAX(ORDER
 ---------
 28-SEP-19
  
 Elapsed: 00:00:25.52
 

We can see that not only does it take more time than the first execution, but all the data that was supposed to go directly to the session PGA went to the Buffer Cache:

SQL> select s.name, m.value/1024/1024 mb from v$mystat m, v$sysstat s where m.statistic#=s.statistic# and (s.name like '%physical IO%' or s.name like '%optimized%' or s.name like 'physical%total bytes');
  
 NAME MB
 ---------------------------------------------------------------- ----------
 physical read requests optimized .000020027
 physical read total bytes optimized .9609375
 physical read total bytes 1901.23438
 physical write requests optimized 0
 physical write total bytes optimized 0
 physical write total bytes 0
 cell physical IO interconnect bytes 1901.23438
 cell physical IO bytes saved during optimized file creation 0
 cell physical IO bytes saved during optimized RMAN file restore 0
 cell physical IO bytes eligible for predicate offload 0
 cell physical IO bytes saved by storage index 0
 cell physical IO bytes sent directly to DB node to balance CPU 0
 cell physical IO interconnect bytes returned by smart scan 0
 cell simulated physical IO bytes eligible for predicate offload 0
 cell simulated physical IO bytes returned by predicate offload 0
  
 15 rows selected.
  
 Elapsed: 00:00:00.00
 

Nice, isn’t it? I hope you enjoy Smart Scan as much as I do!

Oracle Exadata — Smart Scan in Nutshell

 

Oracle Exadata — “Smart Scan in Nutshell”

The Oracle Exadata Database Machine is an Engineered System, complete with preoptimized and preconfigured Software, Servers, Storage, configured to current best practices, that provides an optimal solution for all database workloads, ranging from scan-intensive Data Warehouse applications to highly concurrent OLTP applications.

It combines Oracle Exadata Storage Server Software is the secret sauce, Oracle Database software, and hardware components to deliver extreme performance in a highly available and highly secure environment.

Traditional Scan:

1.Client wants to identify customers that who received discount more than $500. 

2. The information about these premium customers occupies 5 MB in a 1 Terabyte table. 

3. With traditional storage, all database intelligence resides in the database hosts. 

4. Database server nodes must identify all table extents that may contain requested data. 

5. Partitioning may help to eliminate some extents. 

6. Database server issues I/O requests for all potentially relevant data.

7. Storage server returns all relevant blocks of data to database server, using more I/O bandwidth. 

8. Database server must discard irrelevant data by checking values against selection criteria. 

9. Results are sent to client. 

10. Large use of resources CPU & memory for mapping extents. I/O bandwidth from disk for data which will be wasted.

## Traditional Scan:

Smart Scan Example:

• Smart Scan completely transparent to applications and users.
• Database server sends database extents and metadata to Exadata Storage Server cells.
• Smart Scan processing on the Exadata Storage cells scans data blocks to identify relevant rows and columns.
• Only relevant rows and columns returned to database server, does not return blocks when Smart Scan used. Will return blocks when appropriate.
• Database server only has to assembled returned relevant data into result set.

## Smart Scan Concept:

Smart Scan

• I/O is a main source of bottleneck in modern computing.

• Smart Scan is designed to reduce the amount of data flowing from Storage Server to Database Server.

• The database nodes sends query details to the storage cells via a protocol known as iDB (intelligent DataBase).

• Using this information, the storage cells filters major portion of the data in query processing. Oracle Exadata Storage cells search storage disks with added intelligence about the query and send only the relevant bytes, not all the database blocks, to the database nodes —> hence the term Smart Scan.

• Only required Data is sent to Database server.

• No I/O bandwidth wasted or Database CPU.

Smart Scan:

Smart Scan Uses direct reads. Direct reads are not new to Exadata. Direct reads involve reading the data into PGA buffers as opposed to the buffer cache used in for caching data blocks. Direct reads make sense when the ratio of cache to the data to be read is very small. If the cache is very small relative to the data to be read, the buffers are going to be evicted anyways and perhaps at a cost of adversely affecting any OLTP type of applications.

Exadata for DW environments involves scanning large volume of data and returning results in formatted data blocks — should not go to buffer cache.

Smart Scan at work 🡪 Query submitted 🡪 Optimizer makes execution plan 🡪 Full scan access 🡪 Smart Scan eligible 🡪 Smart Scan not performed if query columns include LOBs or other conditions 🡪 Smart scan processing.

Smart Scan at work 🡪 Query submitted 🡪 Optimizer makes execution plan 🡪 Full scan access 🡪 Smart Scan 🡪 Smart scan processing 🡪 Select rows and projected columns returned to PGA.

Smart Scan Process:-

---

🔢 Step 01 – Query

SELECT cust_id, name FROM bonus WHERE amount > 500;

---

🔢 Step 02 – Table Extents + Metadata

Table Extents plus meta data sent to Storage Cell.

---

🔢 Step 03 – Smart Scan

Smart Scan identifies rows & columns that matches within terabyte table.

---

🔢 Step 04 – Storage Cell sends required rows

Storage Cell sends only required rows (5MB data) to Database Server, with less I/O as compared to traditional scan.

---

🔢 Step 05 – Consolidated Result Set

Consolidated result set build from all Storage Cell.

---

🔢 Step 06 – Rows returned to Client

Response sent to client.

---

How Smart Scan works?
Access specifies which data blocks need to read.
Filter is applied on those read blocks.
Smart Scan filters query information on the storage level using below
Predicate information to filter.
Access — start and stop conditions of the leaf node traversal.
Index — are applied during the leaf node traversal operation.
Column Projection — “filter” for table operations.

Smart Scan Column projection:
A B C D E A B C D E B D
Smart Scan only returns columns requested by query

• Significant reduction in I/O bandwidth 

• Diagram based on SQL query SELECT B, C FROM table a;

Row Filtering Operators/Functions
Predicates →, <, =, !=, <=, >=, IS [NOT] NULL, LIKE, [NOT] BETWEEN, [NOT] IN, EXISTS, IS OF type, NOT, AND, OR. & most SQL functions.

Use below SQL to find operators & functions that benefitted from 

Smart Scan
select * from v$sqlfn_metadata where offloadable = ‘YES’;


Smart Scan Join filtering:
Join filtering for star schemas • Joins large tables to smaller tables • Uses Bloom filters.
A way to indicate membership in a set in a compact way • Bloom filters are used to reduce potential row candidates for join, reducing the data sent to the database server for join processing.


Smart Scan in action:

• Look for keyword STORAGE in explain plan
• Statistics for individual statements in below views
• V$SQL
• OPTIMIZED_PHY_READ_BYTES — IO_CELL_OFFLOAD_ELIGIBLE_BYTES → Number of eligible bytes for offload processing. If column shows 0 (zero), it means the statement was not off-loadable.
• IO_INTERCONNECT_BYTES → Number of bytes exchanged between compute nodes and Exadata cells
• IO_CELL_UNCOMPRESSED_BYTES → Number of uncompressed bytes that are offloaded to/from Exadata cells
• IO_CELL_OFFLOAD_RETURNED_BYTES → Number of bytes returned by Exadata cells from Smart Scan processing
• DBA_HIST_SQLSTAT
• IO_OFFLOAD_ELIG_BYTES_TOTAL → Total number off offload-eligible bytes for SQL_ID across all samples
• IO_INTERCONNECT_BYTES_TOTAL → Total number off bytes exchanged between compute nodes and Exadata cells across all AWR samples
• IO_OFFLOAD_RETURNED_BYTES_TOTAL → Number of bytes returned by Exadata cells from Smart Scan processing across all samples
• IO_OFFLOAD_RETURNED_BYTES_DELTA → Number of bytes returned by Exadata cells from Smart Scan processing for current sample

Exadata is a combined hardware and software platform that includes scale-out Intel x86–64 compute and storage servers.

Exadata is a combined hardware/software product that depends on both components to provide substantial performance improvement over non-Exadata platforms. However, the performance benefits of the software component dwarf the benefits provided by the hardware.

Smart Scan Example 1 🡪
SQL> alter session set cell_offload_processing=false;
Session altered.
Elapsed: 00:00:00.00
SQL> select /*+ gather_plan_statistics monitor statement001 */
2 count(*) from purchase where amt_poid = 1;
COUNT(*)
— — — — —
4001234
Elapsed: 00:00:36.22
SQL> alter session set cell_offload_processing=true;
Session altered.
SQL> select /*+ gather_plan_statistics monitor statement002 */
2 count(*) from purchase where amt_poid = 1;
COUNT(*)
— — — — —
4001234
Elapsed: 00:00:05.10
This example shows the performance of a scan against a single, partitioned table

Column Projection:
The term column projection refers to Exadata’s ability to limit the volume of data transferred between the storage tier and the database tier by only returning columns of interest. That is, those in the select list are necessary for join operations on the database tier. If your query requests five columns from a 100-column table, Exadata can eliminate most of the data that would be returned to the database servers by non-Exadata storage. This feature is a much bigger deal than you might expect, and it can have a very significant impact on response times. Let us see an example:

Smart Scan Example 2 🡪
SQL> alter session set “_serial_direct_read” = always;
Session altered.
Elapsed: 00:00:00.00
SQL> alter session set cell_offload_processing = false;
Session altered.
Elapsed: 00:00:00.00
SQL> select count(distinct seller) from sales;
COUNT(DISTINCT SELLER)
— — — — — — — — — — -
1000
Elapsed: 00:00:53.55
SQL> alter session set cell_offload_processing = true;
Session altered.
Elapsed: 00:00:00.01
SQL> select count(distinct seller) from sales;
COUNT(DISTINCTSELLER)
— — — — — — — — — — -
1000
Elapsed: 00:00:28.84
This example deserves some discussion. To force direct path reads — a prerequisite for Smart Scans — the session parameter _SERIAL_DIRECT_READ is set to ALWAYS (more on that later).


Predicate Filtering:
Smart Scan optimizations is predicate filtering. This term refers to Exadata’s ability to return only rows of interest to the database tier.
Filtering these rows at the storage layer can provide a very significant decrease in the volume of data that must be transferred to the database tier. While this optimization also results in some savings in CPU usage on the database servers,
the biggest advantage is generally the reduction in time needed for the data transfer.

Smart Scan Example 3 🡪
SQL> alter session set cell_offload_processing=false;
Session altered.
Elapsed: 00:00:00.01
SQL> select count(*) from sales;
COUNT(*)
— — — — —
394575180
Elapsed: 00:00:23.17
SQL> alter session set cell_offload_processing=true;
Session altered.
Elapsed: 00:00:00.00
SQL> select count(*) from sales;
COUNT(*)
— — — — —
294575180
Elapsed: 00:00:05.68
SQL> — disable storage indexes
SQL> alter session set “_kcfis_storageidx_disabled”=true;
System altered.
Elapsed: 00:00:00.12
SQL> select count(*) from sales where quantity_sold = 1;
COUNT(*)
— — — — —
3006298
Elapsed: 00:00:02.78

First, offloading is completely disabled using the CELL_OFFLOAD_PROCESSING parameter followed by an execution of a query without a WHERE clause (“predicate”). Without the benefit of offloading, but with the benefits of reading exclusively from Smart Flash Cache, this query took about 23 seconds.