Some Things To Consider Now ROWIDs Are Updated When Rows Migrate Part I (“More”)

Some Things To Consider Now ROWIDs Are Updated When Rows Migrate Part I (“More”) February 22, 2023

Posted by Richard Foote in 19c, Autonomous Database, Autonomous Transaction Processing, Changing ROWID, Migrated Rows, Oracle, Oracle 21c, Oracle Blog, Oracle Cloud, Oracle General, Oracle Indexes, Oracle19c, Pink Floyd, Richard's Blog.
trackback

In my previous post, I discussed the obvious advantage of ROWIDs now being updated when rows migrate in an Oracle Autonomous Database, that being subsequent accesses to these rows via an index being more efficient.

However, there were likely reasons why Oracle has not historically updated ROWIDs on the fly in the past, so it’s worth exploring some of the possible side-effects of this new behaviour.

The most obvious issue will be for those applications that explicitly currently store ROWIDs, to enable the direct and very fast retrieval of such rows without having to read and access additional index blocks. If the ROWID can now suddenly change when a row is simply migrated, then of course these applications will no longer be guaranteed to be able to access these rows via the stored ROWIDs. Worse, it may now be possible for such applications to unknowingly fetch the wrong row, with the ROWID value now potentially associated with an entirely different row.

If this is a legitimate concern, then the remedy is simply to just NOT assign such tables the ENABLE ROW MOVEMENT attribute (which is disabled by default on a table) and the behaviour of migrated rows in association with ROWIDs will remain unchanged in Oracle Autonomous Databases. The risks here can be clearly and easily limited.

The other obvious disadvantage with ROWIDs being updated on the fly when a row migrates is in the additional costs associated with such Update statements in maintaining all the corresponding indexes.

As I discussed previously, these additional costs can be significant, especially if we have many indexes on a table.

To illustrate these extra costs, a simple example.

I’ll first start by creating and populating a table called BIG_ZIGGY (which at 100,000 rows is actually quite tiny, but it does have a number of columns) that does NOT have ENABLE ROW MOVEMENT set. The PCTFREE is set to 0 is ensure the rows are nicely packed in each block:

As we’ve only inserted rows, there are currently no migrated rows:

I’ll next create a whole bunch of indexes on many of these columns:

I’ll now run an UPDATE statement, that will increase the row size and result in a number of row migrations:

Note that the CPU used by session is 335, the number of db block gets is 544587 and that the raw elapsed time is 00:00:04.532. We’ll shortly compare these values with those of the same demo, but on a table with ENABLE ROW MOVEMENT set.

If we now check for migrated (chained) rows:

We notice we indeed have 28323 migrated rows.

We’ll now repeat the exactly same demo, but this time on the BIG_ZIGGY2 table that has ENABLE ROW MOVEMENT set:

If we now repeat the same UPDATE statement:

We notice that this update consumed more resources than the previous example.

Note that the CPU used by session is now 1310 (previously 335), the number of db block gets is now 2574278 (previously 544587) and that the raw elapsed time has increased to 00:00:17.600 (previously it was 00:00:04.532).

SQLcl doesn’t automatically display redo statistics which is a shame and something I’ve only just noticed, but it will have increased significantly as I discussed previously.

However, if we look at the number of migrated rows on the BIG_ZIGGY2 table:

We notice there are no rows considered chained (migrated), as in this scenario on Oracle Autonomous Databases, all rows that moved to a different block had their associated ROWIDs updated on the fly in all the corresponding indexes and as such there was no need to have the pointer in the original block to denote the row’s new location.

So the choice is entirely yours.

If you have applications that rely on stored ROWIDs not changing in the background when a row happens to migrate OR you have applications in which the performance of the UPDATE DML is absolutely paramount and you wish to avoid the overheads associated with updating ROWIDs on the fly (which in an Exadata environment is less likely to be an issue), then do NOT set ENABLE ROW MOVEMENT on the table.

Generally, the improvements associated with more efficient indexed-based accesses overrides the overheads associated with (usually) one-off and uncommon row migrations (which might be mitigated with more appropriate settings of PCTFREE).

That said, I’ll discuss a few other areas of potential concern associated with this change of behaviour in my next post…