Oracle Exadata Smart Scan

Smart Scan is a feature in Oracle’s Exadata platform that allows certain types of operations to be offloaded from the database server to the Exadata storage servers, dramatically improving performance by reducing the amount of data that needs to be transferred between storage and the database server.

Key aspects of Smart Scan include:

1. Predicate Filtering: Instead of transferring entire data blocks from the storage to the database, Smart Scan enables Exadata storage servers to apply filtering operations directly. This means that only the relevant rows of data are sent back, reducing data transfer and improving performance.
2. Column Projection: Only the necessary columns from a table, based on the query, are returned from storage, further reducing the amount of data transferred.
3. Decompression: For data stored in Oracle’s Hybrid Columnar Compression (HCC) format, Smart Scan allows the storage servers to decompress only the columns and rows of interest, rather than decompressing entire data blocks, making the data retrieval process faster and more efficient.
4. Direct Path Reads: Smart Scan relies on Oracle’s direct path read mechanism, which bypasses the database buffer cache and reads data directly into the process’s Program Global Area (PGA), eliminating the overhead of cache management.
5. Full Scans Requirement: Smart Scan is only triggered during full table scans or fast full index scans. Queries that access data through index range scans or other methods will not invoke Smart Scan.
6. Exadata Storage Requirement: Smart Scans are only possible for data stored on Exadata storage, as they depend on Exadata’s specialized storage software.

By reducing the amount of data transferred and the work performed by the database servers, Smart Scan significantly speeds up data-intensive operations, especially in analytical workloads that involve large table scans.

However, there are conditions that can disable Smart Scan, such as the use of certain data types (e.g., LOBs), features like Flashback Query, or when querying data from non-Exadata storage.

For more detailed verification of whether a Smart Scan has occurred, tracing methods and monitoring tools are often employed to analyze the performance metrics and execution plans .

1. How to use Exadata Smart Scan

To use Smart Scan in Oracle Exadata, there are certain prerequisites and configurations that must be met.

Smart Scan is automatically invoked by Oracle Exadata when specific types of queries and conditions are satisfied, typically during full table scans.

Here is how you can ensure that Smart Scan is being utilized effectively:

1.1 Ensure Full Table Scans or Index Fast Full Scans

Smart Scan is triggered primarily during:

• Full Table Scans: When Oracle reads the entire table.
• Fast Full Index Scans: When Oracle retrieves index data in bulk.

How to Trigger:

• Use queries that perform full scans (e.g., SELECT * without an indexed WHERE clause).
• Avoid row-by-row lookups or queries that rely heavily on indexing mechanisms like index range scans.

Example:

SELECT * FROM large_table WHERE column > 100;

1.2 Use Direct Path Reads

Smart Scan relies on direct path reads, which bypass the database buffer cache and read data directly into the PGA. Direct path reads are typically used during full table scans or parallel query execution.

How to Trigger:

• Use PARALLEL hint or configure parallelism to ensure direct path reads.

For example:

SELECT /*+ PARALLEL(large_table, 8) */ * FROM large_table WHERE column > 100;

1.3 Store Data on Exadata Storage

Smart Scan only works with data stored on Exadata storage cells.

Make sure the data being accessed is on Exadata, as data stored on non-Exadata storage (like NFS, SAN) won’t benefit from Smart Scan.

How to Ensure:

• Use Automatic Storage Management (ASM) on Exadata for your database storage.

1.4 Use Large Tables or Large Data Sets

Smart Scan is designed to optimize performance for large data sets.

It won’t provide noticeable benefits for small tables or queries that retrieve a small number of rows.

How to Ensure:

• Target large tables for Smart Scan usage, such as those used in Data Warehousing environments.

1.5 Ensure the Use of Supported Features

Smart Scan is available for a variety of operations such as:

• Predicate Filtering: Ensures that only the rows satisfying the WHERE clause are returned.
• Column Projection: Only the required columns are returned to the database server.

Example:

SELECT column1, column2 FROM large_table WHERE column3 > 100;

1.6. Avoid Features That Disable Smart Scan

Certain Oracle features can prevent Smart Scan from being invoked.

Avoid the following when trying to use Smart Scan:

• Index Range Scans
• Row Locking
• Flashback Query
• Using unsupported data types like LOBs

1.7 Monitor and Verify Smart Scan Usage

You can verify whether Smart Scan was used by monitoring session statistics or execution plans. Use Oracle tools like:

• SQL Monitoring (DBMS_SQLTUNE.REPORT_SQL_MONITOR)
• Look for statistics like cell smart table scan or cell smart index scan.

Example:

SELECT * FROM v$session_event WHERE event LIKE 'cell%';

2. Should you use indexes with Exadata

The use of indexes in Oracle Exadata depends on the workload and query patterns.

While Exadata’s Smart Scan and offloading capabilities can greatly reduce the reliance on indexes for certain types of workloads, indexes are still very much relevant for others.

Here’s a detailed look at when and why you might still need indexes in Exadata:

2.1 When Indexes Are Less Important in Exadata

Exadata is designed to excel at scanning large datasets efficiently, often reducing or eliminating the need for indexes in scenarios like:

1. Data Warehousing and Analytics:
   • Queries that involve full table scans or process large volumes of data benefit from Smart Scan.
   • Predicate filtering and column projection reduce the amount of data returned, making scans faster.
2. Hybrid Columnar Compression (HCC):
   • Exadata’s Hybrid Columnar Compression can make full table scans more efficient by significantly reducing the data size.
3. OLAP (Online Analytical Processing):
   • Analytical queries that summarize or aggregate data across large tables often rely on full scans, where Smart Scan provides optimal performance.
4. Parallel Query Execution:
   • Exadata supports high levels of parallelism, allowing it to process large scans efficiently.

In these cases, Exadata’s storage offloading can replace the need for certain indexes.

2.2 When Indexes Are Still Essential

While Exadata reduces reliance on indexes, there are still scenarios where they are highly beneficial:

1. OLTP (Online Transaction Processing):
   • OLTP workloads often involve small, selective queries (e.g., fetching a single row or small range of rows). Indexes are critical for performance in such cases, as they avoid full scans by directly pointing to the relevant rows.
   
   Example:
   SELECT customer_name FROM customers WHERE customer_id = 101;
   
2. Highly Selective Queries:
   • If a query returns only a few rows from a large dataset, an index can outperform a full table scan, even with Smart Scan.
3. Frequent DML Operations:
   • Indexes can improve the performance of queries on frequently updated tables, especially when queries target specific rows based on unique or primary keys.
4. Join Operations:
   • Indexes on join columns can significantly improve join performance in OLTP systems.
5. Unique Constraints:
   • Indexes are still required to enforce unique constraints or primary keys.
6. Queries Not Supported by Smart Scan:
   • Smart Scan does not work with:
     • Index range scans
     • Small tables that don’t trigger full scans
     • Certain data types like LOBs
     • Features like Flashback Query or block storage

2.3 Exadata Best Practices: Balancing Index Use

To optimize performance in Exadata, balance the use of indexes and Smart Scan by following these guidelines:

1. Focus on Critical Queries:
   • Analyze the workload to identify queries that need fast, point-based access to data. Use indexes for those queries.
2. Use Storage Indexes:
   • Exadata automatically creates storage indexes at the storage level, which help eliminate unnecessary I/O for large scans. These do not require manual intervention.
3. Limit Over-Indexing:
   • Avoid creating too many indexes, especially on large tables used for analytics, as maintaining them during DML operations can be costly.
4. Test and Monitor:
   • Use SQL execution plans and tools like SQL Monitoring to assess whether indexes are helping or hurting performance.

Example

EXPLAIN PLAN FOR SELECT * FROM orders WHERE order_date > SYSDATE - 30; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());

2.4 Key Takeaway

• For analytics and large-scale reporting, Exadata’s Smart Scan can reduce the need for traditional indexes.
• For OLTP or selective queries, indexes remain crucial.
• The optimal strategy is to use indexes judiciously, focusing on query patterns and performance needs.

Indexes and Smart Scan are complementary, and understanding your workload is key to deciding when to use one or the other.

3. Summary Checklist for Smart Scan

• Use full table scans or index fast full scans.
• Store data on Exadata storage.
• Ensure the query uses direct path reads.
• Use large tables and data sets.
• Avoid features that disable Smart Scan (e.g., Flashback, LOBs).
• Verify with performance statistics and monitoring tools.

By following these steps, you can maximize your chances of leveraging Smart Scan to improve the performance of your queries in Exadata.