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
- A client submits a query.
- This action triggers a full table scan.
- The database maps the request to the file and corresponding extents, which contain the table being scanned.
- Similar to a full scan, the database issues the I/O operations to read all the blocks.
- All the queried blocks of the table are read into memory.
- The process searches the blocks for the rows that satisfy the predicate.
- 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
- A client submits a query.
- 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.
- The cellsrv component of the ESS scans the data blocks to identify the matching rows and columns that satisfy the request.
- 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.
- 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.
