Oracle Exadata

 

Hardware Architecture:-

Oracle Exadata Database Machine employs a modern scale-out architecture encompassing database compute, storage, network, and software. Uniquely designed to be the ideal platform to run Oracle Database, Oracle Exadata enables enterprises to add capacity and increase performance as workloads scale. Oracle Exadata utilizes state-of-the-art hardware and purpose-built software to drive the highest database performance. All database and storage servers are interconnected with each other, communicating over an intelligent, high-speed, low-latency network fabric.

Remote Direct Memory Access (RDMA) is an integral architectural component of Exadata. It is enhanced in every generation of hardware, Oracle Database, and Exadata System Software release. RDMA enables one server to interact with memory structures on another server without interacting with the OS and network stack on either machine. Each Database and Storage Server is equipped with RDMA capable network cards that directly read from and write to remote memory with no extra copying or buffering. RDMA is also used by Oracle Database when running on Exadata to optimize communication between Real Application Clusters (RAC) instances.

Fundamental to the architecture of Exadata is modular, shared, smart storage. Each Storage Server is capable of interpreting and processing data independently. The CPUs in the storage servers do not replace database CPUs. Instead, they accelerate database-intensive workloads similar to how graphics cards accelerate image-intensive workloads. Exadata System Software enables Exadata Storage Server's unparalleled performance without any of the bottlenecks of traditional storage. Automatic caching of data from disk into Exadata Smart Flash Cache and Exadata RDMA Memory (XRMEM) (a multi-level tiering of data into high-speed, low-latency storage media) enables staggering performance. Every storage server is accessible from every database server. As a result, flash and XRMEM can be shared effectively across multiple database workloads. By automatically caching data from disk into flash and XRMEM, the benefits of these technologies are available and realized by all workloads..

Smart Scan:-

Oracle Exadata is comprised of Database Servers, Storage Servers, high-speed, low-latency network fabric, Oracle Database Software, and Exadata System Software. Together, these five components address the need for Oracle Databases shared-everything architecture of Real Application Clusters (RAC).

Starting with Exadata System Software release 24.1, Oracle Exadata Exascale transforms Exadata storage management by decoupling Oracle Database and GI clusters from the underlying Exadata storage servers. For new and existing deployments, you can deploy a configuration that uses Exascale, ASM, or both. This option enables new and existing customers to implement Exascale while keeping selected databases on the time-tested storage management infrastructure provided by ASM.

Oracle Database, running on the Exadata Database Servers, leverages a purpose-built protocol - iDB - and Remote Direct Memory Access (RDMA) over Converged Ethernet (RoCE) to communicate with the Exadata Storage.

iDB is used to direct Smart I/O operations on the Storage Servers. Smart I/O includes Smart Scan (SQL Offload), Fast File Initialization, and RMAN Incremental Backup Offload. Exadata essentially can "function ship" or push processing from the database servers to the storage servers rather than move potentially hundreds to thousands of terabytes from disk to database server. When the storage server receives a request to perform a Smart I/O, Exadata System Software unpacks the iDB message and executes and processes I/O requests locally, including executing portions of Oracle Database code on the storage servers, before sending only the data that satisfies the request back to the database servers for final processing (such as aggregation).

Where iDB is used to offload I/O intensive processing, such as backups and Analytic queries, to the storage servers, Exadata also leverages the high-speed RoCE network fabric for OLTP workloads. High Capacity Exadata Storage Servers are equipped with Exadata RDMA Memory (XRMEM), flash drives, and traditional hard drives. The Exadata System Software automatically caches data into flash and XRMEM based on incoming workload. This automatically managed caching strategy ensures that the most appropriate storage tier is used to service I/O requests.

For example, Incremental Fast Backups are offloaded to the Exadata Storage Server. Since Exadata knows that blocks read during a backup are likely of little use for satisfying I/O beyond the backup itself, this data is not cached into either flash or XRMEM. At the same time, the Storage Server will cache blocks in both flash and XRMEM to satisfy read and write database requests for OLTP workloads.

Automatically caching data in this manner enables all workloads to run faster and resources to be used more efficiently for individual databases. It also enables high degrees of database consolidation.

Oracle Database and Exadata are engineered together. Oracle Database leverages the hardware and software Exadata provides. Exadata Storage Servers include XRMEM and are accessible over the RoCE network fabric. As OLTP workloads typically access and modify very small amounts of data - one or very few rows - caching this data on a fast device, such as XRMEM ensures the quickest read and write speeds for such data. As Exadata employs scale-out hardware and network architecture, placing the XRMEM in the storage servers ensures all database instances benefit from this cutting-edge technology.

Exadata Database Server:-

Oracle Exadata System Software runs on all database and storage servers to enable Oracle Database to leverage every aspect of Exadata's hardware and network capabilities.

On each Database Server, in addition to the Oracle Linux Operating System, Oracle Grid Infrastructure, Automatic Storage Management (ASM), and Oracle Database software and instances, you will find the following processes and library:

• MS – Exadata System Software Management Server – the primary interface for managing the Exadata Database Server, including hardware and software monitoring, metric collection, alerting, and a command-line interface (DBMCLI).
• RS – Exadata System Software Restart Server – monitors and restarts the MS process if needed.
• libcell – Exadata-specific library linked into the Oracle Database and Grid Infrastructure that allows Oracle to communicate with the Exadata Storage Servers using iDB commands and RDMA.
• EGSB/EDSB – Exascale Global Services – per database instance services that maintain metadata about the Exascale cluster and vaults.
• EGS – Exascale Global Services – provides the core foundation for the Exascale system. EGS instances will run on database servers when the Exadata configuration has fewer than five storage servers.
• ESNP – Exascale Node Proxy – maintains information about the current state of the Exascale cluster, which it provides to local Oracle Grid Infrastructure and Oracle Database processes.
• EDV – Exascale Direct Volume – exposes Exascale block storage as raw block devices on Exadata compute nodes.
• BSW – Block Store Worker – primarily services requests from block store clients and performs the resulting storage server I/O.

Exadata Storage Server:-

Oracle Exadata System Software runs on all database and storage servers to enable Oracle Database to leverage every aspect of Exadata's hardware and network capabilities.

On each Extreme Flash and High Capacity Storage Server, in addition to the Oracle Linux Operating System, you will find the following processes and library:

• MS – Exadata System Software Management Server – the primary interface for managing the Exadata Storage Server, including hardware and software monitoring, metric collection, alerting, and a command-line interface (CELLCLI).
• RS – Exadata System Software Restart Server– monitors and restarts the MS process if needed.
• CELLSRV – Exadata Cell Server – The core Exadata System Software component responsibile for the majority of the services provided by the Storage Servers, including SQL Offload, I/O Resource Management (IORM), Exadata RDMA Memory (XRMEM) and Flash Cache tiering, and Storage Index creation and maintenance.
• IORM – I/O Resource Manager – A sub-process of the CELLSRV process. Responsible for ensuring multiple databases can share the same shared I/O resources, ensuring databases on the same infrastructure do not monopolize I/O bandwidth and degrade perfomance of other databases.
• CELLOFLSRVn – Cell Offload Server – A sub-process of the Cell Server processes that provides version-specific Storage Offload functions. Each version of Oracle Database uses an associated version-specific CELLOFLSRV process.
• EGS – Exascale Global Services – provides the core foundation for the Exascale system. Every Exascale cluster contains five EGS instances, each running on a storage server.
• ERS – Exascale RESTful Services – provides a management endpoint for Exascale management operations.
• EDS – Exascale Data Services – the collective name for the Exascale software services that manage file and vault metadata.
  • SYSEDS – System Vault Manager Service – serves and manages the metadata for Exascale vaults.
  • USREDS – User Vault Manager Service – serves and manages the metadata for files inside the Exascale vaults.
• BSM – Block Store Manager – serves and manages the metadata for Exascale block storage.
• BSW – Block Store Worker – primarily services requests from block store clients and performs the resulting storage server I/O.
• IFD – Instant Failure Detection – a dedicated lightweight service that quickly detects and responds to any storage server failure.

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.