C H A P T E R  2

Offloading / Smart Scan

Offloading is the secret sauce of Exadata. It’s what makes Exadata different from every other platform that Oracle runs on. Offloading refers to the concept of moving processing from the database servers to the storage layer. It is also the key paradigm shift provided by the Exadata platform. But it’s more than just moving work in terms of CPU usage. The primary benefit of Offloading is the reduction in the volume of data that must be returned to the database server. This is one of the major bottlenecks of most large databases.

The terms Offloading and Smart Scan are used somewhat interchangeably. Offloading is a better description in our opinion, as it refers to the fact that part of the traditional SQL processing done by the database can be “offloaded” from the database layer to the storage layer. It is a rather generic term, though, and is used to refer to many optimizations that are not even related to SQL processing including improving backup and restore operations.

Smart Scan, on the other hand, is a more focused term, in that it refers only to Exadata’s optimization of SQL statements. These optimizations come into play for scan operations (typically Full Table Scans). A more specific definition of a Smart Scan would be any section of the Oracle kernel code that is covered by the Smart Scan wait events. There are actually two wait events that include the term “Smart Scan” in their names, Cell Smart Table Scan and Cell Smart Index Scan. We’ll discuss both of these wait events in detail a bit later, in Chapter 10. While it’s true that “Smart Scan” has a bit of a marketing flavor, it does have specific context when referring to the code covered by these wait events. At any rate, while the terms are somewhat interchangeable, keep in mind that Offloading can refer to more than just speeding up SQL statement execution.

In this chapter we will focus on Smart Scan optimizations. We’ll cover the various optimizations that can come into play with Smart Scans, the mechanics of how they work, and the requirements that must be met for Smart Scans to occur. We’ll also cover some techniques that can be used to help you determine whether Smart Scans have occurred for a given SQL statement. The other offloading optimizations will only be mentioned briefly as they are covered elsewhere in the book.

Why Offloading Is Important

We can’t emphasize enough how important this concept is. The idea of moving database processing to the storage tier is a giant leap forward. The concept has been around for some time. In fact, rumor has it that Oracle approached at least one of the large SAN manufacturers several years ago with the idea. The manufacturer was apparently not interested at the time and Oracle decided to pursue the idea on its own. Oracle subsequently partnered with HP to build the original Exadata V1, which incorporated the Offloading concept. Fast-forward a couple of years, and you have Oracle’s acquisition of Sun Microsystems. This put the company in a position to offer an integrated stack of hardware and software and gives it complete control over which features to incorporate into the product.

Offloading is important because one of the major bottlenecks on large databases is the time it takes to transfer the large volumes of data necessary to satisfy DW-type queries between the disk systems and the database servers (that is, because of bandwidth). This is partly a hardware architecture issue, but the bigger issue is the sheer volume of data that is moved by traditional Oracle databases. The Oracle database is very fast and very clever about how it processes data, but for queries that access a large amount of data, getting the data to the database can still take a long time. So as any good performance analyst would do, Oracle focused on reducing the time spent on the thing that accounted for the majority of the elapsed time. During the analysis, the team realized that every query that required disk access was very inefficient in terms of how much data had to be returned to and processed by the database servers. Oracle has made a living by developing the best cache-management software available, but for really large data sets, it is just not practical to keep everything in memory on the database servers.

Imagine the fastest query you can think of: a single column from a single row from a single table where you actually know where the row is stored (rowid). On a traditional Oracle database, at least one block of data has to be read into memory (typically 8K) to get the one column. Let’s assume your table stores an average of 50 rows per block. You’ve just transferred 49 extra rows to the database server that are simply overhead for this query. Multiply that by a billion and you start to get an idea of the magnitude of the problem in a large data warehouse. Eliminating the time spent on transferring completely unnecessary data between the storage and the database tier is the main problem that Exadata was designed to solve.

Offloading is the approach that was used to solve the problem of excessive time spent moving irrelevant data between the tiers. Offloading has three design goals, although the primary goal far outweighs the others in importance:

• Reduce the volume of data transferred from disk systems to the database servers.
• Reduce CPU usage on database servers.
• Reduce disk access times at the storage layer.

Reducing the volume was the main focus and primary goal. The majority of the optimizations introduced by Offloading contribute to this goal. Reducing CPU load is important as well, but is not the primary benefit provided by Exadata and therefore takes a back seat to reducing the volume of data transferred. (As you’ll see, however, decompression is a notable exception to that generalization, as it is performed on the storage servers.) Several optimizations to reduce disk access time were also introduced, and while some of the results can be quite stunning, we don’t consider them to be the bread-and-butter optimizations of Exadata.

Exadata is an integrated 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. Here is an example:

This example shows the performance of a scan against a single table with 384 million rows. We ran it once with Offloading disabled, effectively using all the hardware benefits of Exadata and none of the software benefits. You’ll notice that even on Exadata hardware, this query took almost a minute. Keep in mind that this was only spread across three storage servers on our V2 quarter rack and did not utilize the flash cache at all. We then re-enabled Offloading, and the query completed in substantially less than a second. Obviously the hardware in play was the same in both executions. The point is that it’s the software’s ability via Offloading that made the difference.

A GENERIC VERSION OF EXADATA?

What Offloading Includes

There are many optimizations that can be lumped under the Offloading banner. This chapter focuses on SQL statement optimizations that are implemented via Smart Scans. The big three Smart Scan optimizations are Column Projection, Predicate Filtering, and Storage Indexes. The primary goal of most of the Smart Scan optimizations is to reduce the amount of data that needs to be transmitted back to the database servers during scan execution. However, some of the optimizations also attempt to offload CPU-intensive operations, decompression for example. We won’t have much to say about optimizations that are not related to SQL statement processing in this chapter, such as Smart File Creation and RMAN-related optimizations. Those topics will be covered in more detail elsewhere in the book.

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 or 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. Here is an example:

This example deserves a little discussion. First we used a trick to force direct path reads with the _SERIAL_DIRECT_READ parameter (more on that later). Next we disabled Smart Scans by setting CELL_OFFLOAD_PROCESSING to FALSE. You can see that our test query doesn’t have a WHERE clause. This means that Predicate Filtering and Storage Indexes cannot be used to cut down the volume of data that must be transferred from the storage tier, because those two optimizations can only be done when there is a WHERE clause (we’ll discuss those optimizations shortly). That leaves Column Projection as the only optimization in play. Are you surprised that Column Projection alone could cut a query’s response time in half? We were, the first time we saw it, but it makes sense if you think about it. You should be aware that columns in the select list are not the only columns that must be returned to the database server. This is a very common misconception. Join columns in the WHERE clause must also be returned. As a matter of fact, in early versions of Exadata, the Column Projection feature was not as effective as it could have been and actually returned all the columns included in the WHERE clause, which in many cases included some unnecessary columns.

The DBMS_XPLAN package can display information about column projection, although by default it does not. The projection data is stored in the PROJECTION column in the V$SQL_PLAN view as well. Here is an example:

So as you can see, the plan output shows the projection information, but only if you use the +PROJECTION argument in the call to the DBMS_XPLAN package. Note also that the PK_COL columns from both tables were listed in the PROJECTION section, but that not all columns in the WHERE clause are included. Only those columns that need to be returned to the database (join columns) should be listed. Note also that the projection information is not unique to Exadata but is a generic part of the database code.

The V$SQL family of views contain columns that define the volume of data that may be saved by Offloading (IO_CELL_OFFLOAD_ELIGIBLE_BYTES) and the volume of data that was actually returned by the storage servers (IO_INTERCONNECT_BYTES). Note that these columns are cumulative for all the executions of the statement. We’ll be using these two columns throughout the book because they are key indicators of offload processing. Here’s a quick demonstration to show that projection does affect the amount of data returned to the database servers and that selecting fewer columns results in less data transferred:

Note that the extra column resulted in a great deal of extra time required to complete the query and that the columns in V$SQL verified the increased volume of data that had to be transferred. We’ve also shown the output of a modified version of the fsx.sql script, which we’ll discuss in more detail later in this chapter. For now, please just accept that it shows us whether a statement was offloaded or not.

Predicate Filtering

The second of the big three Smart Scan optimizations is Predicate Filtering. This term refers to Exadata’s ability to return only rows of interest to the database tier. Since iDB includes the predicate information in its requests, this is accomplished by performing the standard filtering operations at the storage cells before returning the data. On databases using non-Exadata storage, filtering is done on the database servers. This generally means that a large number of records that will eventually be discarded will be returned 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 data transfer.

Here is an example:

First we completely disabled Offloading using the CELL_OFFLOAD_PROCESSING parameter and ran a query without a WHERE clause. Without the benefit of Offloading this query took about 48 seconds. We then enabled Offloading and re-ran the query. This time the query took only about 27 seconds. The savings of approximately 21 seconds was due strictly to Column Projection (because without a WHERE clause for filtering, there were no other optimizations that could come into play). We then used a trick to disable storage indexes by setting the hidden parameter, _KCFIS_STORAGEIDX_DISABLED, to TRUE (we’ll discuss that more in the next section) and added a WHERE clause, which reduced the execution time to about 9 seconds. This reduction of an additional 18 seconds or so was thanks to Predicate Filtering. Note that we had to disable storage indexes to be sure that we weren’t getting any benefit from that optimization and that all the improvement was due to Predicate Filtering, which brings us to the next topic.

Storage Indexes

Storage Indexes provide the third level of optimization for Smart Scans. Storage Indexes are in-memory structures on the storage cells that maintain a maximum and minimum value for each 1MB disk storage unit, for up to eight columns of a table. Storage Indexes are a little different than most Smart Scan optimizations. The goal of Storage Indexes is not to reduce the amount of data being transferred back to the database tier. In fact, whether they are used on a given query or not, the amount of data returned to the database tier remains constant. On the contrary, Storage Indexes are designed to eliminate time spent reading data from disk on the storage servers themselves. Think of this feature as a pre-filter. Since Smart Scans pass the query predicates to the storage servers, and Storage Indexes contain a map of values in each 1MB storage region, any region that can’t possibly contain a matching row can be eliminated without ever being read. You can also think of Storage Indexes as an alternate partitioning mechanism. Disk I/O is eliminated in analogous fashion to partition elimination. If a partition can’t contain any records of interest, the partition’s blocks will not be read. Similarly, if a storage region cannot contain any records of interest, that storage region need not be read.

Storage Indexes cannot be used in all cases, and there is little that can be done to affect when or how they are used. But in the right situations, the results from this optimization technique can be astounding.

Here is an example:

In this example we disabled storage indexes (using the _KCFIS_STORAGEIDX_DISABLED parameter) to remind you of the elapsed time required to read through 384 million rows using Column Projection and Predicate Filtering. Remember that even though the amount of data returned to the database tier is extremely small in this case, the storage servers still had to read through every block containing data for the SKEW3 table and then had to check each row to see if it matched the WHERE clause. This is where the majority of the 8 seconds was spent. We then re-enabled storage indexes and reran the query, which reduced the execution time to about .08 seconds. This reduction in elapsed time is a result of storage indexes being used to avoid virtually all of the disk I/O and the time spent filtering through those records.

Just to reiterate, Column Projection and Predicate Filtering (and most other Smart Scan optimizations) improve performance by reducing the volume of data being transferred back to the database servers (and thus the amount of time to transfer the data). Storage Indexes improve performance by eliminating time spent reading data from disk on the storage servers and filtering that data. Storage Indexes are covered in much more detail in Chapter 4.

Simple Joins (Bloom Filters)

In some cases, join processing can be offloaded to the storage tier as well. Offloaded joins are accomplished by creating what is called a bloom filter. Bloom filters have been around for a long time and have been used by Oracle since Oracle Database Version 10g Release 2. So they are not specific to Exadata. One of the main ways Oracle uses them is to reduce traffic between parallel query slaves. They have the advantage of being very small relative to the data set that they represent. However, this comes at a price— they can return false positives. That is, rows that should not be included in the desired result set can occasionally pass a bloom filter. For that reason, an additional filter must be applied after the bloom filter to ensure that any false positives are eliminated. The interesting thing about bloom filters from an Exadata perspective is that they may be passed to the storage servers and evaluated there. This technique can result in a large decrease in the volume of data that must be transmitted back to database servers.

Here’s an example:

In this listing we used a hidden parameter, _BLOOM_PREDICATE_PUSHDOWN_TO_STORAGE, to disable this feature for comparison purposes. Notice that our test query ran in about 2 minutes with Offloading and 11.5 minutes without. If you look closely at the Predicate Information of the plans, you will see that the SYS_OP_BLOOM_FILTER(:BF0000,"A"."PK_COL") predicate was run on the storage servers for the second run. The offloaded version ran faster because the storage servers were able to pre-join the tables, which eliminated a large amount of data that would otherwise have been transferred back to the database servers.

Function Offloading

Oracle’s implementation of SQL includes many built-in SQL functions. These functions can be used directly in SQL statements. They may be divided into two main groups: single-row functions and multi-row functions. Single-row functions return a single result row for every row of a queried table. These single row functions can be further subdivided into the following general categories:

• Numeric functions (SIN, COS, FLOOR, MOD, LOG, …)
• Character functions (CHR, LPAD, REPLACE, TRIM, UPPER, LENGTH, …)
• Datetime functions (ADD_MONTHS, TO_CHAR, TRUNC, …)
• Conversion functions (CAST, HEXTORAW, TO_CHAR, TO_DATE, …)

Virtually all of these single-row functions can be offloaded to Exadata storage. The second major group of SQL functions operate on a set of rows. There are two subgroups in this multi-row function category:

• Aggregate functions (AVG, COUNT, SUM, …)
• Analytic functions (AVG, COUNT, DENSE_RANK, LAG, …)

These functions return either a single row (aggregate functions) or multiple rows (analytic functions). Note that some of the functions are overloaded and belong to both groups. None of these functions can be offloaded to Exadata. Which makes sense, because many of these functions require access to the entire set of rows, which individual storage cells do not have.

There are some additional functions that don’t fall neatly into any of the previously described groupings. These functions are a mixed bag in terms of Offloading. For example, DECODE and NVL are offloadable, but the XML functions are not. Some of the Data Mining functions are offloadable and some are not. Also keep in mind that the list of offloadable functions may change as newer versions are released. The definitive list of which functions are offloadable for your particular version is contained in V$SQLFN_METADATA.

Offloading functions does allow the storage cells to do some of the work that would normally be done by the CPUs on the database servers. However, the saving in CPU usage is generally a relatively minor enhancement. The big gain usually comes from limiting the amount of data transferred back to the database servers. Being able to evaluate functions contained in WHERE clauses allows storage cells to send only rows of interest back to the database tier. So as with most Offloading, the primary goal of this optimization is to reduce the amount of traffic between the storage and database tiers.

Compression/Decompression

One Exadata feature that has received quite a bit of attention is Hybrid Columnar Compression (HCC). Exadata offloads the decompression of data stored in HCC format during Smart Scan operations. That is, columns of interest are decompressed on the storage cells when the compressed data is accessed via Smart Scans. This decompression is not necessary for filtering, so only the data that will be returned to the database tier will be decompressed. Note that all compression is currently done at the database tier, however. Decompression may also be done at the database tier when data is not accessed via a Smart Scan. So to make it simple, Table 2-1 shows where the work is done.

Decompressing data at the storage tier runs counter to the theme of most of the other Smart Scan optimizations. Most of them are geared to reducing the volume of data to be transported back to the database servers. Because decompression is such a CPU-intensive task, particularly with the higher levels of compression, the decision was made to do the decompression on the storage servers. This decision is not be locked in stone, however, as in some situations there may be ample CPU resources available to make decompressing data on the database servers an attractive option (that is, in some situations the reduction in data to be shipped may outweigh the reduction in database server CPU consumption). In fact, as of cellsrv version 11.2.2.3.1, Exadata does have the ability to return compressed data to the database servers when the storage cells are busy.

By the way, there is a hidden parameter that controls whether decompression will be offloaded at all. Unfortunately, it doesn’t just move the decompression back and forth between the storage and database tiers. If the _CELL_OFFLOAD_HYBRIDCOLUMNAR parameter is set to a value of FALSE, Smart Scans will be completely disabled on HCC data.

Encryption/Decryption

Encryption and decryption are handled in a manner very similar to compression and decompression of HCC data. Encryption is always done at the database tier, while decryption can be done by the storage servers or by the database servers. When encrypted data is accessed via Smart Scan, it is decrypted on the storage servers. Otherwise, it is decrypted on the database servers. Note that the X2-2 and x2-8 platforms both use Intel Xeon Westmere chips in the storage servers (X2-2 uses the same chips in the database servers, by the way). These chips contain a special instruction set (Intel AES-NI) that effectively adds a hardware boost to processes doing encryption or decryption. Note that Oracle Database Release 11.2.0.2 is necessary to take advantage of the new instruction set.

Encryption and HCC compression work well together. Since compression is done first, there is less work needed for processes doing encryption and decryption on HCC data. Note that the CELL_OFFLOAD_DECRYPTION parameter controls this behavior, and that as it does with the hidden parameter _CELL_OFFLOAD_HYBRIDCOLUMNAR, setting the parameter to a value of FALSE completely disables Smart Scans on encrypted data, which also disables decryption at the storage layer.

Virtual Columns

Virtual columns provide the ability to define pseudo-columns that can be calculated from other columns in a table, without actually storing the calculated value. Virtual columns may be used as partition keys, used in constraints, or indexed. Column level statistics can also be gathered on them. Since the values of virtual columns are not actually stored, they must be calculated on the fly when they are accessed. These calculations can be offloaded when access is via Smart Scans.

This example shows that virtual column evaluation can be offloaded. It also shows that the optimization can be controlled using the _CELL_OFFLOAD_VIRTUAL_COLUMNS parameter. Note that storage indexes are not built on virtual columns. As with Function Offloading, the real advantage of offloading virtual column calculations has more to do with reducing the volume of data returned to the database servers than with reducing CPU usage on the database tier.

Data Mining Model Scoring

Some of the data model scoring functions can be offloaded. Generally speaking this optimization is aimed at reducing the amount of data transferred to the database tier as opposed to pure CPU offloading. As with other function Offloading, you can verify which data mining functions can be offloaded by querying V$SQLFN_METADATA. The output looks like this:

As you can see, some of the functions are offloadable and some are not. The ones that are offloadable can be used by the storage cells for Predicate Filtering. Here’s an example query that should only return records that meet the scoring requirement specified in the WHERE clause:

This optimization is designed to offload CPU usage as well as reduce the volume of data transferred. However, it is most beneficial in situations where it can reduce the data returned to the database tier, such as in the previous example.

Non-Smart Scan Offloading

There are a few optimizations that are not related to query processing. As these are not the focus of this chapter we will only touch on them briefly.

Smart File Creation

This optimization has a somewhat misleading name. It really is an optimization designed to speed up block initialization. Whenever blocks are allocated, the database must initialize them. This activity happens when tablespaces are created, but it also occurs when files are extended for any number of other reasons. On non-Exadata storage, these situations require the database server to format each block and then write them back to disk. All that reading and writing causes a lot of traffic between the database servers and the storage cells. As you are now aware, eliminating traffic between the layers is a primary goal of Exadata. So as you might imagine, this totally unnecessary traffic has been eliminated. Blocks are formatted by the storage cells themselves without having to send them to the database servers. Time spent waiting on this activity is recorded by the Smart File Creation wait event. This wait event and the operations that invoke it are covered in more detail in Chapter 10.

RMAN Incremental Backups

Exadata speeds up incremental backups by increasing the granularity of block change tracking. On non-Exadata platforms, block changes are tracked for groups of blocks; on Exadata, changes are tracked for individual blocks. This can significantly decrease the number of blocks that must be backed up, resulting in smaller backup sizes, less I/O bandwidth, and reduced time to complete incremental backups. This feature can be disabled by setting the _DISABLE_CELL_OPTIMIZED_BACKUPS parameter to a value of TRUE. This optimization is covered in Chapter 9.

RMAN Restores

This optimization speeds up the file initialization portion when restoring from backup on a cell. Although restoring databases from backups is rare, this optimization can also help speed up cloning of environments. The optimization reduces CPU usage on the database servers and reduces traffic between the two tiers. If the _CELL_FAST_FILE_RESTORE parameter is set to a value of FALSE, this behavior will be disabled. This optimization is also covered in Chapter 9.

Smart Scan Prerequisites

Smart Scans do not occur for every query run on Exadata. There are three basic requirements that must be met for Smart Scans to occur:

• There must be a full scan of an object.
• The scan must use Oracle’s Direct Path Read mechanism.
• The object must be stored on Exadata storage.

There is a simple explanation as to why these requirements exist. Oracle is a C program. The function that performs Smart Scans (kcfis_read) is called by the direct path read function (kcbldrget), which is called by one of the full scan functions. It’s that simple. You can’t get to the kcfis_read function without traversing the code path from full scan to direct read. And of course, the storage will have to be running Oracle’s software in order to process Smart Scans.

We’ll discuss each of these requirements in turn.

Full Scans

In order for queries to take advantage of Exadata’s Offloading capabilities, the optimizer must decide to execute a statement with a Full Table Scan or a Fast Full Index Scan. Note that I am using these terms somewhat generically. Generally speaking, these terms correspond to TABLE ACCESS FULL and INDEX FAST FULL SCAN operations of an execution plan. With Exadata, these familiar operations have been renamed slightly to show that they are accessing Exadata storage. The new operation names are TABLE ACCESS STORAGE FULL and INDEX STORAGE FAST FULL SCAN. Note that there are also some minor variations of these operations, such as MAT_VIEW ACCESS STORAGE FULL, that also qualify for Smart Scans. You should, however, be aware that the fact that your execution plan shows a TABLE ACCESS STORAGE FULL operation does not mean that your query was performed with a Smart Scan. It merely means that this prerequisite has been satisfied. We’ll discuss how to verify whether a statement was actually Offloaded via a Smart Scan a little later in this chapter.

Direct Path Reads

In addition to requiring Full Scan operations, Smart Scans also require that the read operations be executed via Oracle’s Direct Path Read mechanism. Direct Path Reads have been around for a long time. Traditionally, this read mechanism has been used by slave processes, which service parallel queries. Because parallel queries were originally expected to be used for accessing very large amounts of data (typically much too large to fit in the Oracle buffer cache), it was decided that the parallel slaves should read data directly into their own memory (also known as the program global area or PGA). The Direct Path Read mechanism completely bypasses the standard Oracle caching mechanism of placing blocks in the buffer cache. This was a very good thing for very large data sets, as it eliminated extra work that was expected to not be helpful (caching full table scan data that would probably not be reused) and kept them from flushing other data out of the cache. And as we previously mentioned, the kcfis (kernel file intelligent storage) functions are buried under the kcbldrget (kernel block direct read get) function. Therefore, Smart Scans can only be performed if the direct path read mechanism is being used.

In addition to parallel slaves, direct path reads are possible for non-parallel SQL statements, when conditions are right. There is a hidden parameter, _SERIAL_DIRECT_READ, which controls this feature. When this parameter is set to its default value (AUTO), Oracle automatically determines whether to use direct path reads for non-parallel scans. The calculation is based on several factors including the size of the object, the size of the buffer cache and how many of the objects blocks are already in the buffer cache. There is also a hidden parameter (_SMALL_TABLE_THRESHOLD) which plays a role in determining how big a table must be before it will be considered for serial direct path reads. The algorithm for determining whether to use the direct path read mechanism on non-parallel scans is not published. While the ability to do serial direct path reads has been around for some time, it has only recently become a relatively common occurrence. Oracle Database 11gR2 has a modified version of the calculations used to determine whether to use direct path reads for non-parallel scans. The new modifications to the algorithm make the direct path read mechanism much more likely to occur than it was in previous versions. This was probably done as a result of Exadata’s Smart Scan optimizations and the desire for them to be triggered whenever possible. The algorithm may be somewhat overly aggressive on non-Exadata platforms.

Exadata Storage

Of course the data being scanned must be stored on Exadata storage in order for Smart Scans to occur. It is possible to create ASM disk groups that access non-Exadata storage on Exadata database servers. And of course it makes sense that any SQL statements accessing objects defined using these non-Exadata diskgroups will not be eligible for Offloading. While it is unusual, it is also possible to create ASM diskgroups using a combination of Exadata and non-Exadata storage. This might be done to facilitate a migration via an ASM rebalance, for example. Queries against objects whose segments reside on these mixed storage diskgroups are also not eligible for Offloading. There is actually an attribute assigned to ASM disk groups (cell.smart_scan_capable) that specifies whether a disk group is capable of processing Smart Scans. This attribute must be set to FALSE before non-Exadata storage can be assigned to an ASM disk group. Here’s a listing showing the process of creating a mixed storage disk group and the effects on queries against a table with segments stored in that disk group. Note that we are jumping ahead in this example, so some of the commands may not make a lot of sense yet. Don’t worry; we’ll cover the details in due time. For now we just want you to see that you can access non-Exadata storage, but that it will disable the Exadata storage-based optimizations.

We started out with a diskgroup on Exadata storage that contained a table compressed with HCC. When we initially tried to add the non-Exadata storage to that disk group, we got an error saying we had violated the cell.smart_scan_capable disk group attribute. Once we changed the attribute, setting it to FALSE, we were able to add the non-Exadata storage (of course, changing this setting disables Smart Scans on any object with segments stored in this disk group). We then logged onto the database and tried to access our compressed table:

So adding the non-Exadata storage also disabled HCC. We had to decompress the table simply to access it. Once this was done we executed a parallel query on the table and, as you can see, the query was not Offloaded. So all that was to show that you need Exadata storage to do Smart Scans.

Smart Scan Disablers

There are situations where Smart Scans are effectively disabled. The simple case is where they have not been enabled in the code yet, and so Smart Scans don’t happen at all. There are other cases where Oracle starts down the Smart Scan path but the storage software either decides, or is forced, to revert to block shipping mode. Generally this decision is made on a block-by-block basis.

Simply Unavailable

During the discussion of Smart Scan optimizations we have covered the prerequisites that must be met to enable Smart Scans. However, even when those conditions are met, there are circumstances that prevent Smart Scans. Here are a few other situations that are not related to specific optimizations, but where Smart Scans simply cannot be used (at least as of cellsrv version 11.2.2.2.0).

• Smart Scans cannot be used on clustered tables.
• Smart Scans cannot be used on Index Organized Tables (IOTs).
• Smart Scans cannot be used on tables with ROWDEPENDENCIES enabled.

Reverting to Block Shipping

There are situations where Smart Scans are used, but for various reasons cellsrv reverts to block shipping mode. This is a very complex topic, and we struggled with whether to include it in an introductory chapter on offloading. But it is a fundamental concept and so in the end we decided to discuss it here.

We’ve described how Smart Scans avoid transferring large amounts of data to the database layer by returning prefiltered data directly to the PGA. The key concept to understand here is that Smart Scans can choose (or be forced) to return complete blocks to the SGA. Basically any situation that would cause Oracle to have to read another block to complete a record will cause this to happen. A chained row is perhaps the simplest example. When Oracle encounters a chained row, the head piece will contain a pointer to the block containing the second row piece. Since the storage cells do not communicate directly with each other, and it is unlikely that the chained block resides on the same storage cell, cellsrv simply ships the entire block and allows the database layer to deal with it.

So in this very simple case, the Smart Scan is paused momentarily, and a single block read is effectively performed, which motivates another single block read to get the additional row piece. Keep in mind that this is a very simple case. This same behavior comes into play when Oracle must deal with read consistency issues. For example, if Oracle notices that a block is “newer” than the current query, the process of finding an age-appropriate version of the block is left for the database layer to deal with. This effectively pauses the Smart Scan processing while the database does its traditional read consistency processing.

So is this really important, and why should you care? The answer, of course, is that it depends. In most cases you probably won’t care. Oracle guarantees that reads will be consistent, even when doing Smart Scans. This is an important point, by the way. The fact that Oracle behaves exactly the same from the application standpoint, regardless of whether Smart Scans are used or not is a big deal. The fact that Oracle may do some single block reads along with its Smart Scan is of little concern if the results are correct and the performance is not severely impacted, and in most cases it won’t be. There are cases, though, where choosing to do a Smart Scan and then reverting to block shipping mode can be painful from a performance standpoint. These are the cases where it’s important to understand what’s going on under the covers. You’ll find more information on this issue in Chapter 16.

Skipping Some Offloading

Another very complex behavior that we will only mention briefly is the ability of cellsrv to refuse to do some of the normal offload processing. This can be done to avoid overloading the CPU resources on the storage cells for example. A good example of this behavior occurs when decompressing HCC data. Decompression is an extremely CPU intensive task, especially for the higher levels of compression. In later versions of the storage software (11.2.2.3.0 and later), cellsrv can choose to skip the decompression step on some portion of the data when the CPUs on the storage cells are very busy and the database host’s CPU are not very busy. This effectively moves some of the workload back to the database tier by forcing the database hosts to do the decompression. In this case, some steps such as projection may still be done on the storage cells, despite the fact that the decompression step is skipped. As Kevin has already pointed out, this selective determination of which offload processing to do can make the boundaries between the tiers very fluid and difficult to monitor. Chapter 12 covers some techniques for monitoring this behavior.

How to Verify That Smart Scan is Happening

One of the most important things you can learn about Exadata is how to identify whether a query has been able to take advantage of Smart Scans. This is not as easy as it sounds. Unfortunately, the normal execution plan output produced by the DBMS_XPLAN package will not show you whether a Smart Scan was used or not. Here’s an example:

Notice that the optimizer chose a TABLE ACCESS STORAGE FULL operation and that the predicate section shows a storage() predicate associated with step 2 of the plan. Both of these characteristics indicate that a Smart Scan was possible, but neither provides a definitive verification. In fact, the statement in this listing was not executed with a Smart Scan.

The fact that execution plans do not show whether a Smart Scan was performed is a bit frustrating. However, there are several techniques that we can use to work around this issue. We’ll cover a few options in the next several sections.

10046 Trace

One of the most straightforward ways to determine whether a Smart Scan was used is to enable a 10046 trace on the statement in question. Unfortunately, this is a bit cumbersome and doesn’t allow you to do any investigation into what has happened with past executions. Nevertheless, tracing is a fairly foolproof way to verify whether a Smart Scan was used or not. If Smart Scan was used, there will be CELL SMART TABLE SCAN or CELL SMART INDEX SCAN events in the trace file. Here is an excerpt from the trace file collected for the previous statement:

Notice that there are no Smart Scan wait events in the trace file output. For comparison, here is a brief excerpt from a statement using Smart Scan:

As you can see, this trace file contains several CELL SMART TABLE SCAN wait events. There is no doubt that this statement was offloaded. We cover Exadata-specific and -related wait events in detail in Chapter 10.

Performance Statistics (v$sessstat)

Of course, we can also look at some of the performance views such as V$SESSSTAT and V$ACTIVE_SESSION_HISTORY. Tanel Poder’s Snapper script provides a great way to see what wait events are being generated while a statement is running; but again, you must catch it in the act to verify what’s happening. Active Session History (ASH) is good as well, but since the data is sampled, there is no guarantee that it will catch the wait event you are looking for. Nevertheless, performance statistics provide a reliable source of data as long as you can access the system during the execution of the statement you are investigating. Here’s an example using V$MYSTATS, which is simply a version of V$SESSSTAT that limits data to your current session. For this example we’ll look at the cell scans statistic, which is incremented when a Smart Table Scan occurs:

So as you can see, the first time the statement was executed it was offloaded, and the cell scans statistic was incremented from 833 to 834. We then turned off Smart Scans and ran the statement again. This time the statistic was not incremented. So this approach works well as long as we can catch the statements of interest in action. Note that Oracle performance statistics are complementary to the Oracle wait event interface and provide information that is just not available elsewhere. We cover the Exadata-related statistics in detail in Chapter 11.

Offload Eligible Bytes

There is another clue to whether a statement used a Smart Scan or not. As we’ve already mentioned, the V$SQL family of views contain a column called IO_CELL_OFFLOAD_ELIGIBLE_BYTES, which shows the number of bytes that are eligible for Offloading. This column can be used as an indicator of whether a statement used a Smart Scan. It appears that this column is set to a value greater than 0 only when a Smart Scan is used. We can make use of this observation to write a little script (fsx.sql) that returns a value of YES or NO depending on whether that column in V$SQL has a value greater than 0. The output of the script is a little too wide to fit in a book format, so we’ve used a couple of cut-down versions in our examples. And of course, all of the versions will be available in the online code repository. You’ve already seen the script in action in several of the previous sections. Here’s what’s inside the script and an example of its use:

In the fsx script you can see that the OFFLOAD column is just a DECODE that checks to see if the IO_CELL_OFFLOAD_ELIGIBLE_BYTES column is equal to 0 or not. The IO_SAVED_% column is calculated using the IO_INTERCONNECT_BYTES field, and it attempts to show how much data was returned to the database servers. The example shows the same statement run once with Smart Scan enabled and once with Smart Scan disabled. We used the CELL_OFFLOAD_PROCESSING parameter to turn Smart Scans on and off for this purpose. Changing this parameter caused the original cursor to be invalidated because of an optimizer mismatch. This resulted in two child cursors for the statement. In our example, the output of the script shows that one version of the statement was offloaded using Smart Scans and took less than a second. The second execution of the statement though was not Offloaded and took almost a minute.

The technique used by the fsx script seems to work pretty well most of the time. However, in situations where a single child cursor is used repeatedly, it is possible that some executions may be executed using Smart Scans while others are not. While this is an unusual situation, it can cause confusion because the IO_CELL_OFFLOAD_ELIGIBLE_BYTES column contains a cumulative value for all executions. That is to say that each execution adds its eligible bytes count to the running total. When some executions use Smart Scans and some don’t, the IO_CELL_OFFLOAD_ELIGIBLE_BYTES column will be greater than 0. This is a fairly rare occurrence and you may never run into it. Nevertheless, here is an example:

In this example we used the _SERIAL_DIRECT_READ parameter to disable Smart Scans. This parameter does not invalidate cursors, so the same cursor was used for all three executions of the statement. If you look at the second execution, you can probably guess that it didn’t use a Smart Scan, as it was slower than the previous execution. You can verify that assumption by noting that the Eligible Byte Count did not increase. However, the fsx script simply checks to see if the value of the IO_CELL_OFFLOAD_ELIGIBLE_BYTES column is greater than 0. As you can see, the value of the column remained the same between executions causing the fsx script to report that the statement was offloaded, even though the second execution was not offloaded. So keep in mind that our indicator is a cumulative value for all the executions of this cursor. Note that in the previous example we set up a rather unusual situation where a single cursor is offloaded on one execution and not on another. This rarely happens in real life.

The technique demonstrated by the fsx script provides a very useful alternative to tracing or using session statistics to verify whether Smart Scans are being performed. The biggest advantage is that you don’t have to try to catch Smart Scan in real time. The IO_CELL_OFFLOAD_ELIGIBLE_BYTES column is stored in V$SQL and related views, which means it’s also captured by AWR. This provides us with a historical view into how statements have been processed in the past. It is one of the main tools that we use for a quick-and-dirty verification of whether a Smart Scan has been used or not.

SQL Monitoring

There is one other tool that is very useful for determining whether a SQL statement was Offloaded. The REPORT_SQL_MONITOR procedure is part of the new Real Time SQL Monitoring functionality that was added with 11g. It is built into the DBMS_SQLTUNE package and provides a great deal of information, not only on whether a statement was offloaded, but also on which steps in a plan were offloaded. Here’s an example (first of a statement that was not offloaded and then of the same statement when it was offloaded):

Note that we cut out a number of columns from the report because it is very wide and doesn’t fit nicely in book format. Nevertheless, you can see that the report shows which steps were Offloaded (Cell Offload), where the statement spent its time (Activity %), and what it spent time doing (Activity Detail). This can be extremely useful with more complex statements that have multiple steps eligible for offloading. Also note that monitoring occurs automatically on parallelized statements and on statements that the optimizer anticipates will run for a long time. If Oracle is not automatically choosing to monitor a statement that is of interest, you can use the MONITOR hint to tell Oracle to monitor the statement.

Parameters

There are several parameters that apply to Offloading. The main one is CELL_OFFLOAD_PROCESSING, which turns Offloading on and off. There are several others that are of less importance. Table 2-2 shows a list of the non-hidden parameters that affect Offloading (as of Oracle database version 11.2.0.2). Note that we have also included the hidden parameter, _SERIAL_DIRECT_READ, which controls this very important feature.

In addition to the normal Oracle-approved parameters, there are a number of so-called hidden parameters that affect various aspects of Offloading. The following listing shows all the cell parameters, including the hidden parameters, along with their descriptions:

We’ve used a few of these parameters in the examples in this chapter and we’ll use several in the upcoming chapters. There are several other kcfis (kernel file intelligent storage) parameters as well. Here’s a listing of them with descriptions:

Note that hidden parameters should not be used on production systems without prior discussion with Oracle support. But they do provide valuable clues about how some of the Exadata features work and are controlled.

Summary

Offloading really is the secret sauce of Exadata. While the hardware architecture does a good job of providing more balance between the storage layer’s ability to deliver data and the database layer’s ability to consume it, the bulk of the performance gains are provided by the software. Smart Scans are largely responsible for these gains. The primary focus of most of these optimizations is to reduce the amount of data transferred between the storage tier and the database tier.