This is an Exadata wait event that occurs when fast full index scans are offloaded to the storage cells. This event is related to cell smart table scan except that the object is an index.

This event does not show up very frequently in Exadata probably because it is very good at performing full table scans and index reads may actually be less efficient. Also, only full fast scans (not range or full scans) of indexes are eligible for smart scans, which reduces the likelihood of seeing this event.

Solutions

Offloading the index scans to the storage cells within an Exadata machine is probably a good thing, so there may be no tuning required. However, if you have a query that waits extensively on this wait event, it may mean that query tuning is required so it utilizes indexes more effectively. Also review the Objects tab in Ignite to understand which is causing the most wait time.

This is an Exadata wait event that occurs when doing incremental level 1 backups via RMAN. Exadata automatically offloads RMAN backup activities to the storage cells.

Solutions

Offloading the RMAN backup activity to the storage cells within an Exadata machine is probably a good thing, so there may no tuning required. If RMAN backups are affecting performance, scheduling them at different times may be appropriate.

Sessions wait on this event in Exadata when creating tablespaces, adding files to an existing tablespace or automatic expansion a data file because of DML operations, e.g. data is being loaded into a table. One of the performance gains of Exadata when creating of modifying files is that activity is offloaded to the storage cells rather than performed by the database server. This can make the process much faster and less impactful to the database server. To make sure this option is enabled by default, check the parameter “_cell_fast_file_create”=TRUE.

Solutions

This event should not be a major contributor to wait times. If there are excessive waits on this event, it indicates that file creation or extension is taking a long time or being done too often. Check the code that is creating datafiles and ensure file sizes and numbers of files are appropriate. If this is related to DML activity, check the autogrowth options on the tablespace to make sure it is sized large enough to prevent many small extensions.

This is an Exadata wait event similar to db file sequential read and normally occurs during index lookups when the block is not in memory and must be read from the Exadata storage cell disks. These are generally considered a ‘good’ read unless the index being used is not very efficient. In this case the query will read more blocks than necessary and possibly age out other good blocks from the Exadata smart cache.

Solutions

Review the ‘Cell Single Block Physical Read Latency’ metric (in Ignite Resources view on the Exadata tab) to determine latencies. If the time to read data is above 2ms, that could indicate configuration issues with the Exadata storage cell.

If there are excessive waits on this event for a specific SQL statement, tune the statement so that it reads fewer blocks. If the top objects listed in the Ignite Object tab are indexes, determine if there is a more efficient index that could be used. If the top objects are tables, Oracle is going back to the table to get more data after the index lookup completes. That may indicate criteria in the WHERE clause that is not using a column in this index. Adding that column to the index could help performance.

DML statements like INSERT, UPDATE, DELETE, etc can also wait on this event because they are being forced to update inefficient indexes. Review the Ignite Object tab to determine which indexes are being waited for. If they are inefficient, Oracle is most likely not utilizing them in other SQL statements, so consider dropping them.

This is an Exadata wait event similar to ‘db file scattered read’ and typically indicates the statement is performing a full table scan or an index fast full scan. This wait event is not seen nearly as much as ‘db file scattered read’ is seen on non-Exadata platforms because many full scans are offloaded to the storage cell via smart scans. This wait event is more prevalent against tables that are smaller and below the threshold for serial direct scan, a requirement for smart scans.

Solutions

Review the ‘Cell Multiblock Physical Read Latency’ metric (in Ignite Resources view on the Exadata tab) to determine latencies. If the time to read data is above 2ms, that could indicate configuration issues with the Exadata storage cell.

Tune the SQL statement so that it uses an index rather than a full table scan if warranted. If the table is small or the query reads more than 7-10% of the rows in the table, a full table scan could be more efficient than using an index so test the differences. This is especially true in an Exadata system because of the storage cell efficiencies for reading large quantities of data. Use the Ignite Objects tab to determine the most costly full table scan if there are more than one table in the query.

Update table and index statistics if they are stale so that Oracle understands the benfits of existing indexes.

This is an Exadata wait event that occurs when a storage cell is starting and opening a cell for use.

Solutions

This is a normal part of starting up the storage cell in an Exadata system. If there are queries waiting on this, it means the storage cell has not yet started.

This is an Exadata wait event that occurs when a storage cell is starting and discovering disks.

Solutions

This is a normal part of starting up the storage cell in an Exadata system. If there are queries waiting on this, it means the storage cell has not yet started.

This is an Exadata wait event that occurs during the shutdown of a storage cell. This is normal activity associated with shutdown processing.

Solutions

If there are queries waiting on this event, it may indicate that your storage cell is in progress of shutting down and the query is forced to wait.

This is an Exadata wait event similar to ‘db file parallel read’ and normally occurs during multi-block reads of non-contiguous blocks. The most common operations associated with this wait event are index range scans, index unique scans and lookups by rowid.

Solutions

Review solutions for db file parallel read for more information.

One of the main new features associated with Exadata systems is that I/O can be offloaded to storage servers rather than be done on the database server. Each of the storage servers may get a piece of the SQL statement to operate on, so the processing is also parallelized at the same time. This saves valuable database server processing cycles for other non-I/O related activities and can dramatically reduce response times. Smart Scan is another term that essentially means the same thing.

This feature is arguably the single most important part of the Exadata architecture and significantly reduces I/O bottlenecks. This is especially true in data warehouse or other larger database applications where significant amounts of data need to be moved from disk subsystems into the database server. Also, this I/O is offloaded to a storage server which is designed for this purpose.

There are several primary Smart Scan optimizations in Exadata for SQL statement processing:

1. Column Projection – only return the data for columns that are contained in the SELECT list or required for joins.
2. Predicate Filtering – return only the rows of interest to the database server. Since predicate information is sent to the Storage Server, it can filter the result sets before sending the data back to the database server. For example, in a standard database server, a query like “select count(1) from table1” will return all rows to the database server. In an offloading scenario, only the row count will be returned, thus saving extreme amounts of processing time for the database server, not to mention much less strain on the buffer cache.
3. Storage Indexes – in-memory structures on the Storage Cell servers that holds min and max values for each MB of disk storage which limits the physical I/O that must be done. This is essentially a filtering process. By reading through these memory structures, the storage cell will understand which disk regions will or will not contain the data being requested. Think of this as something akin to partitioning for a table in the database server. For example, if the query “select count(1) from table1 where col1 > 0”, the storage cell would use the storage indexes to know which portions of the disk it needs to read to satisfy the “col1 > 0” criteria. Also, and probably more importantly, the storage cell knows which portions of the disk it DOES NOT need to read.
4. Function Offloading – SQL functions can be broken up into two main categories, single row and multi-row. Examples of single row functions include: SIN, COS, REPLACE, TRIM, TO_CHAR, TO_DATE, etc. Most of these functions can be offloaded to the storage cells. Examples of multi-row functions include AVG, COUNT, SUM, etc. None of the multi-row functions can be offloaded to the storage cells because they work on the entire result set which no one storage cell has. A view named V$SQLFN_METADATA includes more information about the specific functions that can/cannot be offloaded.

There are other features that help in the offloading portion but the above list seems to be used by almost all databases types Those other features include, joins or bloom filters, hybrid columnar compression (HCC), encryption and decryption and virtual columns.