Exadata Survival Guide

Performance is the reason companies choose Exadata over other platforms, as it can provide avenues of improvement commodity hardware can’t match.  One of those avenues is the Smart Scan, a mechanism where the database servers and the storage cells join forces to return the requested results.  Let’s see what triggers a Smart Scan and discover how it reduces the processing time for qualifying queries.

Smart Scans are remarkable; they take a seemingly common query and divide the labor between the database servers and the storage cells to return results in a fraction of the time required from non-Exadata hardware.  There are several conditions, which must be met before a Smart Scan can be executed; I’ve mentioned these before but I will provide them again.  To qualify for a Smart Scan a query must:

Use full table scans and/or full index scans coupled with direct path reads, have at least one predicate and use one or more of the following simple comparison operators

        =,<,>,>=,=<,BETWEEN,IN,IS NULL,IS NOT NULL

– or –

Use parallel query, again with at least one predicate and using the above mentioned simple comparison operators.

Once the criterion is met the ‘magic’ begins.  Oracle will pass the query to the storage cells in a manner similar to that used by Parallel Query (but, remember, Parallel Query does not need to be in use to get a Smart Scan to run).  Each storage cell then processes the query/where clause, builds or updates the various storage indexes and returns only the requested data to the database servers eliminating the need for these servers to actually scan/read entire data blocks.  Since the end result of such pre-processing is much smaller, the database servers can more quickly process the data, returning the final results to the user very quickly.

Knowing if a Smart Scan was used is fairly simple, but does require a bit of work.  Execution plans can show Smart Scans were executed but those plans need to be generated by the optimizer at run time.  Explain Plan won’t be a reliable indicator of Smart Scan usage as a different mechanism is used to generate the anticipated plan.  Note that I said ‘anticipated plan’ as that is exactly what Explain Plan provides, a guess at what Oracle will actually do to return the data.  Valid methods for gathering usable plans are:

  • Using autotrace on
  • Querying either V$SQL_PLAN or DBA_HIST_SQL_PLAN for the actual plan
  • Tracing the session with event 10046 and processing the trace file with tkprof

[There are a number of options to autotrace, including explain, but the explain option is no better than using Explain Plan.]

There are also two counters available in the V$SQL and GV$SQL views, which report on Smart Scan activity; these are IO_CELL_OFFLOAD_ELIGIBLE_BYTES and IO_CELL_OFFLOAD_RETURNED_BYTES.  Let’s look at a query that uses a Smart Scan and query V$SQL for the resulting I/O savings:

SQL> select *
  2  from emp
  3  where empid = 7934;

     EMPID EMPNAME                                      DEPTNO
---------- ---------------------------------------- ----------
      7934 Smorthorper7934                                  15

Elapsed: 00:00:00.02

Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932

----------------------------------------------------------------------------------
| Id  | Operation                 | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |      |     1 |    26 |  1167   (1)| 00:00:01 |
|*  1 |  TABLE ACCESS STORAGE FULL| EMP  |     1 |    26 |  1167   (1)| 00:00:01 |
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - storage("EMPID"=7934)
       filter("EMPID"=7934)


Statistics
----------------------------------------------------------
          1  recursive calls
          1  db block gets
       4349  consistent gets
       4276  physical reads
          0  redo size
        680  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>
SQL> set autotrace off timing off
SQL>
SQL> select       sql_id,
  2          io_cell_offload_eligible_bytes qualifying,
  3          io_cell_offload_returned_bytes actual,
  4          round(((io_cell_offload_eligible_bytes - io_cell_offload_returned_bytes)/io_cell_offload_eligible_bytes)*100, 2) io_saved_pct,
  5          sql_text
  6  from v$sql
  7  where io_cell_offload_returned_bytes > 0
  8  and instr(sql_text, 'emp') > 0
  9  and parsing_schema_name = 'BING';

SQL_ID        QUALIFYING     ACTUAL IO_SAVED_PCT SQL_TEXT
------------- ---------- ---------- ------------ --------------------------------------
gfjb8dpxvpuv6   35028992       6872        99.98 select * from emp where empid = 7934

SQL>
SQL> create index empid_idx on emp(empid);

Index created.

SQL>
SQL> set autotrace on timing on
SQL>
SQL> select *
  2  from emp
  3  where empid = 7934;

     EMPID EMPNAME                                      DEPTNO
---------- ---------------------------------------- ----------
      7934 Smorthorper7934                                  15

Elapsed: 00:00:01.00

Execution Plan
----------------------------------------------------------
Plan hash value: 1109982043

-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |     1 |    26 |     4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP       |     1 |    26 |     4   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | EMPID_IDX |     1 |       |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("EMPID"=7934)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          5  consistent gets
          2  physical reads
        148  redo size
        684  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>
SQL> set autotrace off timing off
SQL>
SQL> select       sql_id,
  2          io_cell_offload_eligible_bytes qualifying,
  3          io_cell_offload_returned_bytes actual,
  4          round(((io_cell_offload_eligible_bytes - io_cell_offload_returned_bytes)/io_cell_offload_eligible_bytes)*100, 2) io_saved_pct,
  5          sql_text
  6  from v$sql
  7  where io_cell_offload_returned_bytes > 0
  8  and instr(sql_text, 'emp') > 0
  9  and parsing_schema_name = 'BING';

no rows selected

SQL>

The execution plan reports that a Smart Scan was used — the TABLE SCAN STORAGE FULL entry and the storage(“EMPID”=7934) predicate information indicate this.  To back up that assertion the query from V$SQL shows that there were qualifying and returned offload bytes and computes the percentage of I/O saved because of the Smart Scan.  Following traditional practices, an index was created to eliminate the full table scan; notice that the presence of the index also disabled the Smart Scan and increased the response time for the query.  Granted, had it not been reported by SQL*Plus, the difference would not have been noticeable but it is interesting to see the elapsed times for both queries.

The I/O savings with Smart Scans can be dramatic; in some cases a Smart Scan can outperform an index scan on the same data (proven by the example shown), which is why it may be beneficial to remove some indexes from tables migrated to Exadata.

Smart Scans may not be the greatest thing since sliced bread but they do fulfill the promise of improved performance offered by Exadata.  The nice thing about these is you don’t have to set any obscure parameter to get them to work, which goes to show that simpler can indeed, be better.

See all articles by David Fitzjarrell

David Fitzjarrell
David Fitzjarrell
David Fitzjarrell has more than 20 years of administration experience with various releases of the Oracle DBMS. He has installed the Oracle software on many platforms, including UNIX, Windows and Linux, and monitored and tuned performance in those environments. He is knowledgeable in the traditional tools for performance tuning – the Oracle Wait Interface, Statspack, event 10046 and 10053 traces, tkprof, explain plan and autotrace – and has used these to great advantage at the U.S. Postal Service, American Airlines/SABRE, ConocoPhilips and SiriusXM Radio, among others, to increase throughput and improve the quality of the production system. He has also set up scripts to regularly monitor available space and set thresholds to notify DBAs of impending space shortages before they affect the production environment. These scripts generate data which can also used to trend database growth over time, aiding in capacity planning. He has used RMAN, Streams, RAC and Data Guard in Oracle installations to ensure full recoverability and failover capabilities as well as high availability, and has configured a 'cascading' set of DR databases using the primary DR databases as the source, managing the archivelog transfers manually and montoring, through scripts, the health of these secondary DR databases. He has also used ASM, ASMM and ASSM to improve performance and manage storage and shared memory.

Latest Articles