I was teaching the Oracle Database Machine Administration Workshop in London last week, when an interesting discussion arose concerning Exadata Smart Scan processing when the following situations arise:
- Migrated or Chained Rows
- Consistent Read (CR) Requests
The discussion followed on from a situation that a delegate encountered when doing some routine maintenance on tables and indexes in a data warehouse stored in an Exadata based database. Here is the scenario:
- A certain warehouse table had a index dropped, prior to bulk update operations on the table during regular maintenance windows.
- After the maintenance, the index was re-created using CREATE INDEX….
- During the index creation the WAIT EVENT seen by monitoring V$SESSION is mostly for “direct path read”
- During a recent maintenance window, several new columns were added to the table for an application upgrade after dropping the index.
- The table was then bulk updated including values for the new columns.
- When the index was re-created, it took more than 100 times as long to finish as it normally did.
- The WAIT EVENT seen by monitoring V$SESSION during the index creation is mostly for “cell single block physical read“.
The initial questions were:
- Why is this taking so long?
- Why are there so many “cell single block physical read” waits?
- Why not have “cell multiblock physical read” waits.
In order to understand the answer, a few facts should be made clear about how Exadata processes query requests. Much has been written on blogs and in presentations about Exadata “Smart Scan” operations and other “offload” capabilities. But they tend to discuss queries without deep diving into the effects of updates or other less common scenarios, on the operation of the “Smart Scan“.
“Smart Scan” operations only work for DIRECT PATH READs, because they return the results of queries from the Exadata cells and not block images. The decision to do buffered reads or direct path reads, is made at run time, based on the size of the object, the number of blocks already in the buffer cache for that object and the size of the buffer cache to name some. Conventional buffered reads would require blocks to go to the buffer cache, meaning that whole block images would be required. So the index re-creation I mentioned did a “Smart Scan” against the underlying table under normal maintenance conditions. Creating an index accesses all table rows, but only the index key columns are required, so “Column Projection” filtering was done by the cells, which is one of the standard “Smart Scan” capabilities.
When columns were added to the table during the upgrade maintenance window, the ensuing bulk update of the table caused many rows to migrate from their original blocks, to other blocks in the same segment. These blocks could be in any part of the table segment, based on available free space and may be in ASM allocation units in the same cell or in a different cell. One may detect when migrated (or chained) rows are accessed, by examining the “table fetch continued row” value in v$sysstat or in an AWR report. In this case, the value increased by a huge number; in effect nearly every table block had one or more migrated rows.
The huge increase in index build time was caused by the inability of “Smart Scans” to do offload processing for migrated rows. Migrated rows are a special case of chained rows where the entire row is in a different block than where it was originally inserted, but still fits into a single block. The original row is reduced to a 3 byte row header, plus a six byte rowid.
When a “Smart Scan” detects a migrated row, it passes the rowid back to the database session and for all such rows, the database session will access it using a buffered single block read. Since nearly all the table blocks had migrated rows, the database session needed to issue I/O requests for nearly all the blocks one at a time. This was the cause for the huge number of “cell single block physical read” wait events that were seen when monitoring the recreation of the index. This indicates that the presence of many migrated (or chained) rows will degrade performance in Exadata by preventing “Smart Scans” from being so smart. This situation arose on an index creation, but it would also keep occurring until the DBA deleted and reinserted the migrated rows in the usual manner to solve this problem.
Note: There is an Exadata specific situation that causes migrated rows. When a row is updated in a Hybrid Columnar Compressed (HCC) table, then it is migrated to another block in the segment that is managed using “OLTP compression“. Any HCC Compression Unit (CU) containing at least one migrated row, will also cause the block containing that row to be accessed by the server using a “cell single block physical read“.
The discussion then evolved to other situations that might interfere with “Smart Scan” operations on objects for which “Smart Scans” are normally done and which might be caused by updates. Consistent Read requests can force “Smart Scans” to work differently if any of the following is true:
- A block is updated and the update is not yet committed.
- A block is updated and committed, but the query System Change number (SCN) is older than the commit SCN.
- A block is updated and committed but not cleaned out, because it was no longer in the buffer cache at commit time.
In the first two cases, a CR image of the block must be found in the buffer cache of the instance issuing the read, or by RAC cache fusion in the case of RAC databases. If not found, then one must be constructed by accessing the Undo segment for the active transaction and creating an older block image by reference to the Undo blocks. Whichever is done, requires that the CR processing be done in the database instances not the cells. So each cell performing part of the “Smart Scan” operation on part of the table may stop sending results temporarily and ship block images for any such block that it finds, for CR processing in the database instance(s), before resuming smart scan on the next block. Performance will be more affected if there are many such blocks.
In the third case, when a cell sends its first block image to the database instance. the Undo will indicate that the transaction committed prior to the query SCN and the block may be cleaned out. The transaction ID may then be returned to the cell indicating that it was already committed. Any future references by any query to any blocks showing the same transaction to be active, and whose query SCN is older than the commit SCN, may be cleaned out in the cell. This is done by reference to a cached list of committed transaction identifiers maintained in each cell. This means that the overhead of delayed block cleanout is not as serious as the case of an uncommitted transaction or one that committed after a query began, as outlined above.
The lessons learned from this are:
- Migrated rows still affect performance, as they do in conventional storage situations, but with the additional overhead of reducing the beneficial effects of “Smart Scan” in addition to increasing the number of I/Os.
- All queries may be affected by the migrated rows, including queries performed implicitly for DDL.
- Uncommitted bulk updates performed at the same time as queries, may increase overheads due to Consistent Read processing
- Bulk updates committed after a query starts, may cause the same CR processing overheads
DBAs looking after database on a DB machine should monitor for migrated rows and rectify the situation. There are numerous posts outlining the method for doing this, so i wont reproduce them here. Alternatively the Enterprise Manager Reorg Wizard may be used and details may be found in the EM documentation for the tuning pack reorg wizard which is a cost option.
For optimal “Smart Scan” efficiency, it is advisable to avoid warehouse queries done at the same time as bulk updates, as is the case with conventional storage solutions.