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.
Comment