{"id":7885,"date":"2019-05-03T13:29:06","date_gmt":"2019-05-03T13:29:06","guid":{"rendered":"https:\/\/rax-stg-wp-aws.com\/blogs\/oracle-exadata-smart-scan-feature\/"},"modified":"2026-11-03T19:29:07","modified_gmt":"2026-11-03T19:29:07","slug":"oracle-exadata-smart-scan-feature","status":"publish","type":"blogs","link":"https:\/\/www.rackspace.com\/en-gb\/blogs\/oracle-exadata-smart-scan-feature\/","title":{"rendered":"Oracle Exadata Smart Scan feature"},"content":{"rendered":"

Introduction<\/strong><\/p>\n

This blog delves into the details of the Oracle\u00a9 Exadata&\u00a9 Smart Scan feature, including an explanation of how SQL processing differs with Exadata and usage examples.<\/p>\n

Oracle designed and introduced Exadata in 2008 as one of the first in the family of engineered systems. Unlike traditional database systems, which are configured, installed, tuned, and managed by a user, an engineered system has all the necessary components like OS, database, memory, and storage preinstalled, configured, tuned, and optimized by the solution provider, such as Oracle.<\/p>\n

There are various components of an Exadata machine.  The following components
\nare the primary ones:<\/p>\n

Database servers<\/strong>: Enterprise-class servers that run database software,
\n  Automatic Storage Management (ASM), or the Oracle Grid Infrastructure.<\/p>\n

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

InfiniBand network<\/strong>: The internal Exadata network is built on InfiniBand,
\n  which is primarily designed for high-performance computing environments.<\/p>\n

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
\nwith database servers for any storage-related requests. <\/p>\n

Key features of ESS<\/strong><\/p>\n

Following are the key features of ESS:<\/p>\n

Flash cache<\/strong>:  The Flash cache is a high-performance cache on cell servers
\n  that caches recently accessed objects.<\/p>\n

Flash log<\/strong>: The Flash log provides a high-performance, low-latency, and
\n  reliable temporary store for redo-log writes.<\/p>\n

EHCC<\/strong>: Exadata Hybrid Columnar Compression (EHCC) enables the highest
\n  levels of data compression. EHCC organizes data into sets of rows called
\n  compression units<\/em>. Within a compression unit, EHCC organizes the data by
\n  column and then compresses the data.<\/p>\n

 Storage indexes<\/strong>: Storage indexes are in-memory structures on the storage
\n  servers that are designed to reduce the time spent reading data from a disk
\n  on the storage servers.<\/p>\n

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

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

SQL processing<\/strong><\/p>\n

Traditionally, SQL processing happens on database servers only. In Exadata, SQL
\nprocessing is divided and also takes place on the storage tier.<\/p>\n

Tradition SQL processing<\/strong><\/p>\n

The following steps comprise traditional SQL processing, as shown in the
\nfollowing diagram:<\/p>\n

\"exadata<\/p>\n

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

    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.
    \n <\/p>\n

    Exadata server SQL processing<\/strong><\/p>\n

    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<\/p>\n

    \"exadata<\/p>\n

      \n
    1. A client submits a query.<\/li>\n
    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.<\/li>\n
    3. The cellsrv<\/strong> component of the ESS scans the data blocks to identify the matching rows and columns that satisfy the request.<\/li>\n
    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.<\/li>\n
    5. The database consolidates the result and returns the rows to the client. <\/li>\n<\/ol>\n

      Smart Scan requirements<\/strong><\/p>\n

      Following are the requirements to use Smart Scan:<\/p>\n

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

          Smart Scan examples<\/strong><\/p>\n

          Execute the following steps, first with and then without Smart Scan enabled, to measure the Smart Scan effect on the statistics.<\/p>\n

          Step 1: Connect to database<\/strong><\/p>\n

          Run the following command to connect to your database with SQL*Plus:<\/p>\n

              [oracle@nd01db01 ~]$ sqlplus nd\/nd
          \n    SQL*Plus: Release 11.2.0.3.0 Production…<\/em><\/p>\n

          Step 2: Verify statistics<\/strong><\/p>\n

          Run the following query to verify that the statistics are at or near zero:<\/p>\n

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

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

              10 rows selected.
          \n <\/p>\n

          Step 3: Execute a select query with Smart Scan disabled<\/strong><\/p>\n

          Run the following select query with the optimizer hint that disables Smart Scan:<\/strong><\/p>\n

              SQL> select \/*+ OPT_PARAM(‘cell_offload_processing’ ‘false’) *\/ count(*)
          \n    from sales where time_id between ’01-JUN-2017′ and ’30-OCT-2017′
          \n    and amount_sold = 1;<\/em><\/p>\n

              COUNT(*)
          \n    ———-
          \n    134055<\/em><\/p>\n

          Step 4: Verify statistics again<\/strong><\/p>\n

          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).<\/p>\n

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

              NAME                                                               MB
          \n    ————————————————————— ———–
          \n    physical read total bytes                                           759.429688
          \n    physical write total bytes                                        0
          \n    cell physical IO interconnect bytes                               759.429688
          \n    cell physical IO bytes saved during optimized file creation       0
          \n    cell physical IO bytes saved during optimized RMAN file restore    0
          \n    cell physical IO bytes eligible for predicate offload             0
          \n    cell physical IO bytes saved by storage index                      0
          \n    cell physical IO bytes sent directly to DB node to balance CPU    0
          \n    cell physical IO interconnect bytes returned by smart scan          0
          \n    cell IO uncompressed bytes                                        0<\/p>\n

              10 rows selected.<\/p>\n

          \nStep 5: Reset the statistics<\/strong><\/p>\n

          Run the following command to reconnect the session to the database to reset the session-level statistics:<\/p>\n

              [oracle@nd01db01 ~]$ sqlplus nd\/nd
          \n    SQL*Plus: Release 11.2.0.3.0 Production…<\/em><\/p>\n

          Step 6: Verify statistics again<\/strong><\/p>\n

          Run the following query to verify that the statistics are at or near zero:<\/p>\n

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

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

              10 rows selected.<\/p>\n

          Step 7: Execute a select query without Smart Scan disabled<\/strong><\/p>\n

          Run the following select query without the optimizer hint that disables Smart Scan:<\/p>\n

              SQL> select count(*) from sales where time_id between ’01-JUN-2017′
          \n    and ’30-OCT-2017’and amount_sold = 1;<\/em><\/p>\n

              COUNT(*)
          \n    ——-
          \n    134055<\/em><\/p>\n

          Step 8: Verify statistics<\/strong><\/p>\n

          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
          \nIO 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.<\/p>\n

              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%’)<\/em><\/p>\n

            NAME                                                             MB
          \n    —————————————————————-    ———-
          \n    physical read total bytes                                           759.429688
          \n    physical write total bytes                                        0
          \n    cell physical IO interconnect bytes                               1.71562805
          \n    cell physical IO bytes saved during optimized file creation       0
          \n    cell physical IO bytes saved during optimized RMAN file restore    0
          \n    cell physical IO bytes eligible for predicate offload             759.429688
          \n    cell physical IO bytes saved by storage index                      0
          \n    cell physical IO bytes sent directly to DB node to balance CPU    0
          \n    cell physical IO interconnect bytes returned by smart scan          1.71562805
          \n    cell IO uncompressed bytes                                        759.429688<\/em><\/p>\n

              10 rows selected.<\/em><\/p>\n

          Conclusion<\/strong><\/p>\n

          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.<\/p>\n","protected":false},"excerpt":{"rendered":"

          Oracle Exadata Smart Scan feature<\/p>\n","protected":false},"author":2,"featured_media":7887,"menu_order":0,"comment_status":"open","ping_status":"open","template":"","format":"standard","meta":{"content-type":"","nid":"26541","content_type":"blog","created_by":"10130","authored_on":"2024-01-25T14:29:06+00:00","field_article_industries":"","tag_category":"Technical","field_blog_author_text":"by Rackspace Technology Staff","seo_description":"Explore Oracle Exadata's Smart Scan feature in-depth, understand its functionality and applications, and get familiar with Oracle's engineered systems.\r\n","field_featured_image":"","insights_image":"\/wp-content\/uploads\/2026\/blog_technical_images\/26541-insights_image-oracle-technical-blog-1-insights.jpg","field_partners":"","rxt_platforms":"Oracle","product":"","publisheddate":"2019-05-03T13:29:06","rxt_solutions":"Cloud Optimization, Data, Databases","opengraph_tag":"","x_tag":"","teaser_flag":"Technical Blog","seo_titile":"Unlock Oracle Exadata: Deep Dive Into Smart Scan Feature | Rackspace Technology","field_topics":"","field_slug":"oracle-exadata-smart-scan-feature","footnotes":""},"categories":[178],"tags":[204],"class_list":["post-7885","blogs","type-blogs","status-publish","format-standard","has-post-thumbnail","hentry","category-blog","tag-technical"],"_links":{"self":[{"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/blogs\/7885","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/blogs"}],"about":[{"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/types\/blogs"}],"author":[{"embeddable":true,"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/comments?post=7885"}],"version-history":[{"count":3,"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/blogs\/7885\/revisions"}],"predecessor-version":[{"id":9732,"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/blogs\/7885\/revisions\/9732"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/media\/7887"}],"wp:attachment":[{"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/media?parent=7885"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/categories?post=7885"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/tags?post=7885"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}