jump to navigation

Some Things To Consider Now ROWIDs Are Updated When Rows Migrate Part II (“Look Back In Anger”) February 24, 2023

Posted by Richard Foote in 19c, Autonomous Database, Autonomous Transaction Processing, BLEVEL, Changing ROWID, Index Internals, Leaf Blocks, Migrated Rows, Oracle, Oracle Blog, Oracle Cloud, Oracle General, Oracle Indexes, Oracle19c, Richard's Blog, ROWID.
3 comments

Some weekend reading…

In my previous post, I discussed a couple of potential areas of concern with the ROWIDs of migrated rows now being updated on the fly in Oracle Autonomous Databases, namely that it can cause issues with applications that reply on stored ROWIDs not changing and that there are additional resources required to maintain such ROWIDs in corresponding indexes, especially if there are many indexes on a table.

In this post, I’ll discuss another issue to just bear in mind with this change in behaviour.

To illustrate, I’ll run a demo similar to the previous post, first creating and populating a table with no ENABLE ROW MOVEMENT set:

SQL> create table ziggy(id number, code1 number, code2 number, code3 number, code4 number, code5 number, code6 number, code7 number, code8 number, code9 number, code10 number, code11 number, code12 number, code13 number, code14 number, code15 number, code16 number, code17 number, code18 number, code19 number, code20 number, name varchar2(142)) PCTFREE 0;

Table ZIGGY created.

SQL> insert into ziggy SELECT rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, 'BOWIE' FROM dual CONNECT BY LEVEL <= 200000;

200,000 rows inserted.

SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'ZIGGY');

PL/SQL procedure successfully completed.

SQL> analyze table ziggy compute statistics;

Table ZIGGY analyzed.

I’ll next create a bunch of indexes on the table:

SQL> create index ziggy_id_i on ziggy(id);

Index ZIGGY_ID_I created.

SQL> create index ziggy_code1_i on ziggy(code1);

Index ZIGGY_CODE1_I created.

SQL> create index ziggy_code2_i on ziggy(code2);

Index ZIGGY_CODE2_I created.

SQL> create index ziggy_code3_i on ziggy(code3);

Index ZIGGY_CODE3_I created.

SQL> create index ziggy_code4_i on ziggy(code4);

Index ZIGGY_CODE4_I created.

SQL> create index ziggy_code5_i on ziggy(code5);

Index ZIGGY_CODE5_I created.

SQL> create index ziggy_code6_i on ziggy(code6);

Index ZIGGY_CODE6_I created.

SQL> create index ziggy_code7_i on ziggy(code7);

Index ZIGGY_CODE7_I created.

SQL> create index ziggy_code8_i on ziggy(code8);

Index ZIGGY_CODE8_I created.

SQL> create index ziggy_code9_i on ziggy(code9);

Index ZIGGY_CODE9_I created.

SQL> create index ziggy_code10_i on ziggy(code10);

Index ZIGGY_CODE10_I created.

SQL> create index ziggy_code11_i on ziggy(code11);

Index ZIGGY_CODE11_I created.

SQL> create index ziggy_code12_i on ziggy(code12);

Index ZIGGY_CODE12_I created.

SQL> create index ziggy_code13_i on ziggy(code13);

Index ZIGGY_CODE13_I created.

SQL> create index ziggy_code14_i on ziggy(code14);

Index ZIGGY_CODE14_I created.

SQL> create index ziggy_code15_i on ziggy(code15);

Index ZIGGY_CODE15_I created.

SQL> create index ziggy_code16_i on ziggy(code16);

Index ZIGGY_CODE16_I created.

SQL> create index ziggy_code17_i on ziggy(code17);

Index ZIGGY_CODE17_I created.

SQL> create index ziggy_code18_i on ziggy(code18);

Index ZIGGY_CODE18_I created.

SQL> create index ziggy_code19_i on ziggy(code19);

Index ZIGGY_CODE19_I created.

SQL> create index ziggy_code20_i on ziggy(code20);

Index ZIGGY_CODE20_I created.

Let’s take note of the size of the table and its associated indexes:

SQL> select table_name, num_rows, blocks, empty_blocks, avg_space, avg_row_len, chain_cnt
from user_tables where table_name='ZIGGY';

   TABLE_NAME    NUM_ROWS    BLOCKS    EMPTY_BLOCKS    AVG_SPACE    AVG_ROW_LEN    CHAIN_CNT
_____________ ___________ _________ _______________ ____________ ______________ ____________
ZIGGY              200000      3268              60          857            113            0

SQL> select index_name, blevel, leaf_blocks, clustering_factor from user_indexes 
where table_name='ZIGGY';

       INDEX_NAME    BLEVEL    LEAF_BLOCKS    CLUSTERING_FACTOR
_________________ _________ ______________ ____________________
ZIGGY_CODE20_I            1            473                 3250
ZIGGY_ID_I                1            473                 3250
ZIGGY_CODE1_I             1            473                 3250
ZIGGY_CODE2_I             1            473                 3250
ZIGGY_CODE3_I             1            473                 3250
ZIGGY_CODE4_I             1            473                 3250
ZIGGY_CODE5_I             1            473                 3250
ZIGGY_CODE6_I             1            473                 3250
ZIGGY_CODE7_I             1            473                 3250
ZIGGY_CODE8_I             1            473                 3250
ZIGGY_CODE9_I             1            473                 3250
ZIGGY_CODE10_I            1            473                 3250
ZIGGY_CODE11_I            1            473                 3250
ZIGGY_CODE12_I            1            473                 3250
ZIGGY_CODE13_I            1            473                 3250
ZIGGY_CODE14_I            1            473                 3250
ZIGGY_CODE15_I            1            473                 3250
ZIGGY_CODE16_I            1            473                 3250
ZIGGY_CODE17_I            1            473                 3250
ZIGGY_CODE18_I            1            473                 3250
ZIGGY_CODE19_I            1            473                 3250

We next perform an update on the table that will increase the row size sufficiently to result in a bunch of migrated rows:

SQL> update ziggy set name='THE RISE AND FALL OF ZIGGY STARDUST AND THE SPIDERS FROM MARS';

200,000 rows updated.
Elapsed: 00:00:07.716

I’ll then perform a COMMIT and look at differences in the table statistics:

SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'ZIGGY');

PL/SQL procedure successfully completed.

SQL> analyze table ziggy compute statistics;

Table ZIGGY analyzed.

SQL> select table_name, num_rows, blocks, empty_blocks, avg_space, avg_row_len, chain_cnt 
from user_tables where table_name='ZIGGY';

   TABLE_NAME   NUM_ROWS     BLOCKS    EMPTY_BLOCKS    AVG_SPACE    AVG_ROW_LEN    CHAIN_CNT
_____________ ___________ _________ _______________ ____________ ______________ ____________
ZIGGY              200000      4906              86          415            170        56186

 

We can see that there are indeed a bunch of migrated/chained rows, some 56186 of them.

We also notice that the size of the table has increased to 4906 blocks (previously is was 3268). This increase is in large part due to the increased size of the NAME column value, but also partly due to the storage allocated to pointers that are stored in the original block to denote the new location of the migrated rows (as discussed previously here).

If we look at the current state of the indexes:

SQL> select index_name, blevel, leaf_blocks, clustering_factor 
from user_indexes where table_name='ZIGGY';

       INDEX_NAME    BLEVEL    LEAF_BLOCKS    CLUSTERING_FACTOR
_________________ _________ ______________ ____________________
ZIGGY_CODE7_I             1            473                 3250
ZIGGY_CODE8_I             1            473                 3250
ZIGGY_CODE9_I             1            473                 3250
ZIGGY_CODE10_I            1            473                 3250
ZIGGY_CODE11_I            1            473                 3250
ZIGGY_CODE12_I            1            473                 3250
ZIGGY_CODE13_I            1            473                 3250
ZIGGY_CODE14_I            1            473                 3250
ZIGGY_CODE15_I            1            473                 3250
ZIGGY_CODE16_I            1            473                 3250
ZIGGY_CODE17_I            1            473                 3250
ZIGGY_CODE18_I            1            473                 3250
ZIGGY_CODE19_I            1            473                 3250
ZIGGY_CODE20_I            1            473                 3250
ZIGGY_ID_I                1            473                 3250
ZIGGY_CODE1_I             1            473                 3250
ZIGGY_CODE2_I             1            473                 3250
ZIGGY_CODE3_I             1            473                 3250
ZIGGY_CODE4_I             1            473                 3250
ZIGGY_CODE5_I             1            473                 3250
ZIGGY_CODE6_I             1            473                 3250

We notice that the indexes remain unchanged. As the table does NOT have ENABLE ROW MOVEMENT set, the indexes are NOT updated at all as part of the migrated row process (thus the same behaviour as non-autonomous database environments).

However, if I perform the same demo but instead perform a ROLLBACK of the transaction rather than the commit:

SQL> rollback;

Rollback complete.

Elapsed: 00:00:06.919

Note that the rollback takes 00:00:06.919 to complete.

If we now look at the size of the table and corresponding indexes:

SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'ZIGGY');

PL/SQL procedure successfully completed.

SQL> analyze table ziggy compute statistics;

Table ZIGGY analyzed.

SQL> select table_name, num_rows, blocks, empty_blocks, avg_space, avg_row_len, chain_cnt 
from user_tables where table_name='ZIGGY';

   TABLE_NAME    NUM_ROWS    BLOCKS    EMPTY_BLOCKS    AVG_SPACE    AVG_ROW_LEN    CHAIN_CNT
_____________ ___________ _________ _______________ ____________ ______________ ____________
ZIGGY              200000      4906              86         2899            113            0

We notice that the size of the table has increased to be the same 4906 blocks as when we performed the commit. The extra storage remains allocated even after the rollback operation.

That’s because Oracle does NOT deallocate any additional storage that might have been consumed during the original transaction. We notice that the AVG_SPACE has increased substantially as a result (now 2899, previously it was just 857).

If we look at the current state of the indexes after the rollback:

SQL> select index_name, blevel, leaf_blocks, clustering_factor 
from user_indexes where table_name='ZIGGY';

       INDEX_NAME    BLEVEL    LEAF_BLOCKS    CLUSTERING_FACTOR
_________________ _________ ______________ ____________________
ZIGGY_CODE20_I            1            473                 3250
ZIGGY_ID_I                1            473                 3250
ZIGGY_CODE1_I             1            473                 3250
ZIGGY_CODE2_I             1            473                 3250
ZIGGY_CODE3_I             1            473                 3250
ZIGGY_CODE4_I             1            473                 3250
ZIGGY_CODE5_I             1            473                 3250
ZIGGY_CODE6_I             1            473                 3250
ZIGGY_CODE7_I             1            473                 3250
ZIGGY_CODE8_I             1            473                 3250
ZIGGY_CODE9_I             1            473                 3250
ZIGGY_CODE10_I            1            473                 3250
ZIGGY_CODE11_I            1            473                 3250
ZIGGY_CODE12_I            1            473                 3250
ZIGGY_CODE13_I            1            473                 3250
ZIGGY_CODE14_I            1            473                 3250
ZIGGY_CODE15_I            1            473                 3250
ZIGGY_CODE16_I            1            473                 3250
ZIGGY_CODE17_I            1            473                 3250
ZIGGY_CODE18_I            1            473                 3250
ZIGGY_CODE19_I            1            473                 3250

We notice that all the indexes again remain unchanged. As the indexes are not updated during the transaction, this is of course to be expected.

 

Let’s now repeat the same demo, but this time on a table with ENABLE ROW MOVEMENT set:

SQL> create table ziggy2(id number, code1 number, code2 number, code3 number, code4 number, code5 number, code6 number, code7 number, code8 number, code9 number, code10 number, code11 number, code12 number, code13 number, code14 number, code15 number, code16 number, code17 number, code18 number, code19 number, code20 number, name varchar2(142)) PCTFREE 0 ENABLE ROW MOVEMENT;

Table ZIGGY2 created.

SQL> insert into ziggy2 SELECT rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, 'BOWIE' FROM dual CONNECT BY LEVEL <= 200000;

200,000 rows inserted.

SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'ZIGGY2');

PL/SQL procedure successfully completed.

SQL> analyze table ziggy2 compute statistics;

Table ZIGGY2 analyzed.

SQL> create index ziggy2_id_i on ziggy2(id);

Index ZIGGY2_ID_I created.

SQL> create index ziggy2_code1_i on ziggy2(code1);

Index ZIGGY2_CODE1_I created.

SQL> create index ziggy2_code2_i on ziggy2(code2);

Index ZIGGY2_CODE2_I created.

SQL> create index ziggy2_code3_i on ziggy2(code3);

Index ZIGGY2_CODE3_I created.

SQL> create index ziggy2_code4_i on ziggy2(code4);

Index ZIGGY2_CODE4_I created.

SQL> create index ziggy2_code5_i on ziggy2(code5);

Index ZIGGY2_CODE5_I created.

SQL> create index ziggy2_code6_i on ziggy2(code6);

Index ZIGGY2_CODE6_I created.

SQL> create index ziggy2_code7_i on ziggy2(code7);

Index ZIGGY2_CODE7_I created.

SQL> create index ziggy2_code8_i on ziggy2(code8);

Index ZIGGY2_CODE8_I created.

SQL> create index ziggy2_code9_i on ziggy2(code9);

Index ZIGGY2_CODE9_I created.

SQL> create index ziggy2_code10_i on ziggy2(code10);

Index ZIGGY2_CODE10_I created.

SQL> create index ziggy2_code11_i on ziggy2(code11);

Index ZIGGY2_CODE11_I created.

SQL> create index ziggy2_code12_i on ziggy2(code12);

Index ZIGGY2_CODE12_I created.

SQL> create index ziggy2_code13_i on ziggy2(code13);

Index ZIGGY2_CODE13_I created.

SQL> create index ziggy2_code14_i on ziggy2(code14);

Index ZIGGY2_CODE14_I created.

SQL> create index ziggy2_code15_i on ziggy2(code15);

Index ZIGGY2_CODE15_I created.

SQL> create index ziggy2_code16_i on ziggy2(code16);

Index ZIGGY2_CODE16_I created.

SQL> create index ziggy2_code17_i on ziggy2(code17);

Index ZIGGY2_CODE17_I created.

SQL> create index ziggy2_code18_i on ziggy2(code18);

Index ZIGGY2_CODE18_I created.

SQL> create index ziggy2_code19_i on ziggy2(code19);

Index ZIGGY2_CODE19_I created.

SQL> create index ziggy2_code20_i on ziggy2(code20);

Index ZIGGY2_CODE20_I created.

 

The table and indexes all have the same initial size as the previous example:

SQL> select table_name, num_rows, blocks, empty_blocks, avg_space, avg_row_len, chain_cnt
from user_tables where table_name='ZIGGY2';

   TABLE_NAME    NUM_ROWS    BLOCKS    EMPTY_BLOCKS    AVG_SPACE    AVG_ROW_LEN    CHAIN_CNT
_____________ ___________ _________ _______________ ____________ ______________ ____________
ZIGGY2             200000      3268              60          857            113            0

SQL> select index_name, blevel, leaf_blocks, clustering_factor from user_indexes 
where table_name='ZIGGY2';

        INDEX_NAME    BLEVEL    LEAF_BLOCKS    CLUSTERING_FACTOR
__________________ _________ ______________ ____________________
ZIGGY2_ID_I                1            473                 3250
ZIGGY2_CODE1_I             1            473                 3250
ZIGGY2_CODE2_I             1            473                 3250
ZIGGY2_CODE3_I             1            473                 3250
ZIGGY2_CODE4_I             1            473                 3250
ZIGGY2_CODE5_I             1            473                 3250
ZIGGY2_CODE6_I             1            473                 3250
ZIGGY2_CODE7_I             1            473                 3250
ZIGGY2_CODE8_I             1            473                 3250
ZIGGY2_CODE9_I             1            473                 3250
ZIGGY2_CODE10_I            1            473                 3250
ZIGGY2_CODE11_I            1            473                 3250
ZIGGY2_CODE12_I            1            473                 3250
ZIGGY2_CODE13_I            1            473                 3250
ZIGGY2_CODE14_I            1            473                 3250
ZIGGY2_CODE15_I            1            473                 3250
ZIGGY2_CODE16_I            1            473                 3250
ZIGGY2_CODE17_I            1            473                 3250
ZIGGY2_CODE18_I            1            473                 3250
ZIGGY2_CODE19_I            1            473                 3250
ZIGGY2_CODE20_I            1            473                 3250

 

If we now perform the same Update followed by the commit:

SQL> update ziggy2 set name='THE RISE AND FALL OF ZIGGY STARDUST AND THE SPIDERS FROM MARS';

200,000 rows updated.
Elapsed: 00:00:33.390

SQL> commit;

Commit complete.

If we look at the current size of the table after the update:

SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'ZIGGY2');

PL/SQL procedure successfully completed.

SQL> analyze table ziggy2 compute statistics;

Table ZIGGY2 analyzed.

SQL> select table_name, num_rows, blocks, empty_blocks, avg_space, avg_row_len, chain_cnt 
from user_tables where table_name='ZIGGY2';

   TABLE_NAME    NUM_ROWS   BLOCKS     EMPTY_BLOCKS    AVG_SPACE    AVG_ROW_LEN    CHAIN_CNT
_____________ ___________ _________ _______________ ____________ ______________ ____________
ZIGGY2             200000      4654              82          367            169            0

We notice it has increased to 4654 blocks (previously 3268). But the table is not quite as large in size as in the first demo, where the table grew to 4906 blocks (so 252 fewer blocks).

The increase in table size is now due entirely as a result in the increased NAME column values, as Oracle has not had to consume any storage for pointers in the original table blocks to denote a new location of the rows, as the ROWIDs are now updated in the indexes on the fly.

So this is a positive, a DECREASE in the comparative size of table after such updates that migrate rows.

And of course, there are no migrated/chained rows.

But if we look at the index statistics after the commit:

SQL> select index_name, blevel, leaf_blocks, clustering_factor 
from user_indexes where table_name='ZIGGY2';

        INDEX_NAME    BLEVEL    LEAF_BLOCKS    CLUSTERING_FACTOR
__________________ _________ ______________ ____________________
ZIGGY2_ID_I                2            945               109061
ZIGGY2_CODE1_I             2            945               109061
ZIGGY2_CODE2_I             2            945               109061
ZIGGY2_CODE3_I             2            945               109061
ZIGGY2_CODE4_I             2            945               109061
ZIGGY2_CODE5_I             2            945               109061
ZIGGY2_CODE6_I             2            945               109061
ZIGGY2_CODE7_I             2            945               109061
ZIGGY2_CODE8_I             2            945               109061
ZIGGY2_CODE9_I             2            945               109061
ZIGGY2_CODE10_I            2            945               109061
ZIGGY2_CODE11_I            2            945               109061
ZIGGY2_CODE12_I            2            945               109061
ZIGGY2_CODE13_I            2            945               109061
ZIGGY2_CODE14_I            2            945               109061
ZIGGY2_CODE15_I            2            945               109061
ZIGGY2_CODE16_I            2            945               109061
ZIGGY2_CODE17_I            2            945               109061
ZIGGY2_CODE18_I            2            945               109061
ZIGGY2_CODE19_I            2            945               109061
ZIGGY2_CODE20_I            2            945               109061

 

We notice that ALL the indexes have significantly increased in size and now have 945 leaf blocks (previously it was just 473 leaf blocks). Additionally as a result of this increase in index size, the BLEVEL of the indexes has also increased and are now 2 (previously it was 1).

Here’s the thing. As I’ve discussed many times before, when Oracle performs an “Update” of an index entry, this is actually implemented as a Delete/Insert operation. By changing the ROWID of an index entry, Oracle first deletes the original index entry and inserts a new index entry with the new ROWID. So the previous index entry remains (with the space likely eventually reused by another new index entry in the future).

So these “on the fly” updates of the indexes to keep the ROWIDs current due to row migrations increases the likelihood of index block splits and the subsequent increase in index storage allocations.

In very rare, extreme cases (and this demo is indeed an extreme case as I’m updating all rows in my table), this extra index storage could potentially result in an increase in the index BLEVEL.

However, in most scenarios, this increase in index storage is likely to be moderate and result in extra index storage that will eventually be consumed by subsequent new rows anyways.

If instead of the commit operation, we instead performed a rollback:

SQL> rollback;

Rollback complete.

Elapsed: 00:00:36.639

We notice that the rollback takes considerably longer at 00:00:36.639 (previously in the first demo, it was just 00:00:06.919).

As the ROWIDs are now all updated on the fly on all the corresponding indexes, there’s that much more data that needs to be rolled back within these indexes.

If we look at the current size of the table after the rollback:

SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'ZIGGY2');

PL/SQL procedure successfully completed.

SQL> analyze table ziggy2 compute statistics;

Table ZIGGY2 analyzed.

SQL> select table_name, num_rows, blocks, empty_blocks, avg_space, avg_row_len, chain_cnt
from user_tables where table_name='ZIGGY2';

   TABLE_NAME    NUM_ROWS    BLOCKS    EMPTY_BLOCKS    AVG_SPACE    AVG_ROW_LEN    CHAIN_CNT
_____________ ___________ _________ _______________ ____________ ______________ ____________
ZIGGY2             200000      4654              82         2823            113            0

We notice it has increased to the same 4654 blocks as with the commit. The extra storage remains allocated even after the rollback operation.

That’s again because Oracle does NOT deallocate any additional storage that might have been consumed during the original transaction. We notice that the AVG_SPACE has again increased substantially as a result (now 2823, previously it was just 857).

If we look at the current state of the indexes after the rollback:

 

SQL> select index_name, blevel, leaf_blocks, clustering_factor 
from user_indexes where table_name='ZIGGY2';

        INDEX_NAME    BLEVEL    LEAF_BLOCKS    CLUSTERING_FACTOR
__________________ _________ ______________ ____________________
ZIGGY2_ID_I                2            945                 3250
ZIGGY2_CODE1_I             2            945                 3250
ZIGGY2_CODE2_I             2            945                 3250
ZIGGY2_CODE3_I             2            945                 3250
ZIGGY2_CODE4_I             2            945                 3250
ZIGGY2_CODE5_I             2            945                 3250
ZIGGY2_CODE6_I             2            945                 3250
ZIGGY2_CODE7_I             2            945                 3250
ZIGGY2_CODE8_I             2            945                 3250
ZIGGY2_CODE9_I             2            945                 3250
ZIGGY2_CODE10_I            2            945                 3250
ZIGGY2_CODE11_I            2            945                 3250
ZIGGY2_CODE12_I            2            945                 3250
ZIGGY2_CODE13_I            2            945                 3250
ZIGGY2_CODE14_I            2            945                 3250
ZIGGY2_CODE15_I            2            945                 3250
ZIGGY2_CODE16_I            2            945                 3250
ZIGGY2_CODE17_I            2            945                 3250
ZIGGY2_CODE18_I            2            945                 3250
ZIGGY2_CODE19_I            2            945                 3250
ZIGGY2_CODE20_I            2            945                 3250

We notice that they all remain at their increased size of 945 leaf blocks and with the Blevel of 2.

Again, when Oracle performs the rollback, Oracle does NOT undo all the index block splits and leaves all the additional storage allocated to the indexes.

So, just a word of caution.

Updating all the ROWIDs on the fly when a row migrates does not come for free. There’s additional resources that need to be consumed during these updates AND there is a potential issue with indexes having to perform additional index block splits and consume additional storage (at least immediately) after such operations.

If you have applications that makes bulk changes to data that can result in rollbacks, again, it’s just worth noting the extra storage that may be consumed as a result (until the additional data is finally added to the table).

Yes, index rebuilds can be performed to reduce the subsequent size of these inflated indexes.

BUT, for those of you with sharp eyes, you might also have noted another potential issue with this new behaviour, which I’ll discuss in my next post… 🙂

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.
add a comment

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:

SQL> CREATE TABLE big_ziggy(id number, code1 number, code2 number, code3 number, code4 number, code5 number, code6 number, code7 number, code8 number, code9 number, code10 number, code11 number, code12 number, code13 number, code14 number, code15 number, code16 number, code17 number, code18 number, code19 number, code20 number, name varchar2(142)) PCTFREE 0;

Table BIG_ZIGGY created.

SQL> INSERT INTO big_ziggy SELECT rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, 'BOWIE' FROM dual CONNECT BY LEVEL <= 100000;

100,000 rows inserted.

SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_table_stats(ownname=> null, tabname=> 'BIG_ZIGGY');

PL/SQL procedure successfully completed.

 

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

SQL> analyze table big_ziggy compute statistics;

Table BIG_ZIGGY analyzed.

SQL> select table_name, num_rows, chain_cnt from user_tables where table_name='BIG_ZIGGY';

   TABLE_NAME    NUM_ROWS    CHAIN_CNT
_____________ ___________ ____________
BIG_ZIGGY          100000            0

 

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

SQL> create index big_ziggy_id_i on big_ziggy(id);

Index BIG_ZIGGY_ID_I created.

SQL> create index big_ziggy_code1_i on big_ziggy(code1);

Index BIG_ZIGGY_CODE1_I created.

SQL> create index big_ziggy_code2_i on big_ziggy(code2);

Index BIG_ZIGGY_CODE2_I created.

SQL> create index big_ziggy_code3_i on big_ziggy(code3);

Index BIG_ZIGGY_CODE3_I created.

SQL> create index big_ziggy_code4_i on big_ziggy(code4);

Index BIG_ZIGGY_CODE4_I created.

SQL> create index big_ziggy_code5_i on big_ziggy(code5);

Index BIG_ZIGGY_CODE5_I created.

SQL> create index big_ziggy_code6_i on big_ziggy(code6);

Index BIG_ZIGGY_CODE6_I created.

SQL> create index big_ziggy_code7_i on big_ziggy(code7);

Index BIG_ZIGGY_CODE7_I created.

SQL> create index big_ziggy_code8_i on big_ziggy(code8);

Index BIG_ZIGGY_CODE8_I created.

SQL> create index big_ziggy_code9_i on big_ziggy(code9);

Index BIG_ZIGGY_CODE9_I created.

SQL> create index big_ziggy_code10_i on big_ziggy(code10);

Index BIG_ZIGGY_CODE10_I created.

SQL> create index big_ziggy_code11_i on big_ziggy(code11);

Index BIG_ZIGGY_CODE11_I created.

SQL> create index big_ziggy_code12_i on big_ziggy(code12);

Index BIG_ZIGGY_CODE12_I created.

SQL> create index big_ziggy_code13_i on big_ziggy(code13);

Index BIG_ZIGGY_CODE13_I created.

SQL> create index big_ziggy_code14_i on big_ziggy(code14);

Index BIG_ZIGGY_CODE14_I created.

SQL> create index big_ziggy_code15_i on big_ziggy(code15);

Index BIG_ZIGGY_CODE15_I created.

SQL> create index big_ziggy_code16_i on big_ziggy(code16);

Index BIG_ZIGGY_CODE16_I created.

SQL> create index big_ziggy_code17_i on big_ziggy(code17);

Index BIG_ZIGGY_CODE17_I created.

SQL> create index big_ziggy_code18_i on big_ziggy(code18);

Index BIG_ZIGGY_CODE18_I created.

SQL> create index big_ziggy_code19_i on big_ziggy(code19);

Index BIG_ZIGGY_CODE19_I created.

SQL> create index big_ziggy_code20_i on big_ziggy(code20);

Index BIG_ZIGGY_CODE20_I created.

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

SQL> update big_ziggy set name='THE RISE AND FALL OF ZIGGY STARDUST AND THE SPIDERS FROM MARS';

100,000 rows updated.

PLAN_TABLE_OUTPUT
____________________________________________________________________________________
SQL_ID 53xtnn8mmtwj5, child number 0
-------------------------------------
update big_ziggy set name='THE RISE AND FALL OF ZIGGY STARDUST AND THE
SPIDERS FROM MARS'

Plan hash value: 1689330390

---------------------------------------------------------
| Id | Operation                  | Name      | E-Rows  |
---------------------------------------------------------
|  0 | UPDATE STATEMENT           |           |         |
|  1 |  UPDATE                    | BIG_ZIGGY |         |
|  2 |   TABLE ACCESS STORAGE FULL| BIG_ZIGGY |    100K |
---------------------------------------------------------

Note
-----
   - automatic DOP: Computed Degree of Parallelism is 1
   - PDML disabled because object is not decorated with parallel clause
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level

Statistics
-----------------------------------------------------------
        345 CPU used by this session
        347 CPU used when call started
        399 DB time
    3442830 RM usage
          5 Requests to/from client
        491 Session total flash IO requests
   25403392 cell physical IO interconnect bytes
      48814 consistent gets
      10111 consistent gets examination
      10111 consistent gets examination (fastpath)
      48814 consistent gets from cache
      38703 consistent gets pin
      38702 consistent gets pin (fastpath)
     544587 db block gets
     544587 db block gets from cache
     538582 db block gets from cache (fastpath)
        127 enqueue releases
        129 enqueue requests
       3086 gcs affinity lock grants
        803 gcs data block access records
          3 ges messages sent
      33574 global enqueue gets sync
      33573 global enqueue releases
         43 messages sent
        483 non-idle wait count
         44 non-idle wait time
          8 opened cursors cumulative
          1 opened cursors current
         71 physical read requests optimized
        420 physical read total IO requests
   25403392 physical read total bytes
    3219456 physical read total bytes optimized
          1 pinned cursors current
          4 process last non-idle time
         55 recursive calls
          1 recursive cpu usage
     593401 session logical reads
         42 user I/O wait time
          6 user calls
Elapsed: 00:00:04.532

SQL> commit

Commit complete.

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:

SQL> analyze table big_ziggy compute statistics;

Table BIG_ZIGGY analyzed.

SQL> select table_name, num_rows, chain_cnt from user_tables where table_name='BIG_ZIGGY';

  TABLE_NAME     NUM_ROWS    CHAIN_CNT
_____________ ___________ ____________
BIG_ZIGGY          100000        28323

 

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:

SQL> CREATE TABLE big_ziggy2(id number, code1 number, code2 number, code3 number, code4 number, code5 number, code6 number, code7 number, code8 number, code9 number, code10 number, code11 number, code12 number, code13 number, code14 number, code15 number, code16 number, code17 number, code18 number, code19 number, code20 number, name varchar2(142)) PCTFREE 0 ENABLE ROW MOVEMENT;

Table BIG_ZIGGY2 created.

SQL> INSERT INTO big_ziggy2 SELECT rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, 'BOWIE' FROM dual CONNECT BY LEVEL <= 100000;

100,000 rows inserted.

SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_table_stats(ownname=> null, tabname=> 'BIG_ZIGGY2');

PL/SQL procedure successfully completed.

SQL> analyze table big_ziggy2 compute statistics;

Table BIG_ZIGGY2 analyzed.

SQL> select table_name, num_rows, chain_cnt from user_tables where table_name='BIG_ZIGGY2';

   TABLE_NAME    NUM_ROWS    CHAIN_CNT
_____________ ___________ ____________
BIG_ZIGGY2         100000            0

SQL> create index big_ziggy2_id_i on big_ziggy2(id);

Index BIG_ZIGGY2_ID_I created.

SQL> create index big_ziggy2_code1_i on big_ziggy2(code1);

Index BIG_ZIGGY2_CODE1_I created.

SQL> create index big_ziggy2_code2_i on big_ziggy2(code2);

Index BIG_ZIGGY2_CODE2_I created.

SQL> create index big_ziggy2_code3_i on big_ziggy2(code3);

Index BIG_ZIGGY2_CODE3_I created.

SQL> create index big_ziggy2_code4_i on big_ziggy2(code4);

Index BIG_ZIGGY2_CODE4_I created.

SQL> create index big_ziggy2_code5_i on big_ziggy2(code5);

Index BIG_ZIGGY2_CODE5_I created.

SQL> create index big_ziggy2_code6_i on big_ziggy2(code6);

Index BIG_ZIGGY2_CODE6_I created.

SQL> create index big_ziggy2_code7_i on big_ziggy2(code7);

Index BIG_ZIGGY2_CODE7_I created.

SQL> create index big_ziggy2_code8_i on big_ziggy2(code8);

Index BIG_ZIGGY2_CODE8_I created.

SQL> create index big_ziggy2_code9_i on big_ziggy2(code9);

Index BIG_ZIGGY2_CODE9_I created.

SQL> create index big_ziggy2_code10_i on big_ziggy2(code10);

Index BIG_ZIGGY2_CODE10_I created.

SQL> create index big_ziggy2_code11_i on big_ziggy2(code11);

Index BIG_ZIGGY2_CODE11_I created.

SQL> create index big_ziggy2_code12_i on big_ziggy2(code12);

Index BIG_ZIGGY2_CODE12_I created.

SQL> create index big_ziggy2_code13_i on big_ziggy2(code13);

Index BIG_ZIGGY2_CODE13_I created.

SQL> create index big_ziggy2_code14_i on big_ziggy2(code14);

Index BIG_ZIGGY2_CODE14_I created.

SQL> create index big_ziggy2_code15_i on big_ziggy2(code15);

Index BIG_ZIGGY2_CODE15_I created.

SQL> create index big_ziggy2_code16_i on big_ziggy2(code16);

Index BIG_ZIGGY2_CODE16_I created.

SQL> create index big_ziggy2_code17_i on big_ziggy2(code17);

Index BIG_ZIGGY2_CODE17_I created.

SQL> create index big_ziggy2_code18_i on big_ziggy2(code18);

Index BIG_ZIGGY2_CODE18_I created.

SQL> create index big_ziggy2_code19_i on big_ziggy2(code19);

Index BIG_ZIGGY2_CODE19_I created.

SQL> create index big_ziggy2_code20_i on big_ziggy2(code20);

Index BIG_ZIGGY2_CODE20_I created.

If we now repeat the same UPDATE statement:

SQL> update big_ziggy2 set name='THE RISE AND FALL OF ZIGGY STARDUST AND THE SPIDERS FROM MARS';

100,000 rows updated.

PLAN_TABLE_OUTPUT
____________________________________________________________________________________
SQL_ID gupa6k30c341n, child number 0
-------------------------------------
update big_ziggy2 set name='THE RISE AND FALL OF ZIGGY STARDUST AND THE
SPIDERS FROM MARS'

Plan hash value: 3856369697

----------------------------------------------------------
| Id | Operation                  | Name       | E-Rows  |
----------------------------------------------------------
|  0 | UPDATE STATEMENT           |            |         |
|  1 |  UPDATE                    | BIG_ZIGGY2 |         |
|  2 |   TABLE ACCESS STORAGE FULL| BIG_ZIGGY2 |    100K |
----------------------------------------------------------

Note
-----
   - automatic DOP: Computed Degree of Parallelism is 1
   - PDML disabled because object is not decorated with parallel clause
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level

Statistics
-----------------------------------------------------------
       1310 CPU used by this session
       1310 CPU used when call started
       1732 DB time
   13104856 RM usage
          5 Requests to/from client
         12 Session IORM flash wait time
      13343 Session total flash IO requests
  235888640 cell physical IO interconnect bytes
      36437 consistent gets
        994 consistent gets examination
        994 consistent gets examination (fastpath)
      36437 consistent gets from cache
      35443 consistent gets pin
      35275 consistent gets pin (fastpath)
    2574278 db block gets
    2574278 db block gets from cache
    1418826 db block gets from cache (fastpath)
       5729 enqueue releases
       5731 enqueue requests
      23745 gcs affinity lock grants
      11119 gcs data block access records
         25 ges messages sent
       1165 global enqueue gets sync
       1164 global enqueue releases
        215 messages sent
       8254 non-idle wait count
        476 non-idle wait time
         31 opened cursors cumulative
       5324 physical read requests optimized
       8019 physical read total IO requests
  235888640 physical read total bytes
   63856640 physical read total bytes optimized
         17 process last non-idle time
        160 recursive calls
          7 recursive cpu usage
    2610715 session logical reads
        475 user I/O wait time
          6 user calls
Elapsed: 00:00:17.600

SQL> commit

Commit complete.

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:

SQL> analyze table big_ziggy2 compute statistics;

Table BIG_ZIGGY2 analyzed.

SQL> select table_name, num_rows, chain_cnt from user_tables where table_name='BIG_ZIGGY2';

   TABLE_NAME    NUM_ROWS    CHAIN_CNT
_____________ ___________ ____________
BIG_ZIGGY2         100000            0

 

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…

Advantages To Updating ROWID When Rows Migrate (“Fantastic Voyage”) February 13, 2023

Posted by Richard Foote in 19c, Autonomous Database, Autonomous Transaction Processing, Changing ROWID, Migrated Rows, Oracle, Oracle Blog, Oracle Cloud, Oracle General, Oracle Statistics, Performance Tuning, ROWID.
1 comment so far

In my last post, I discussed how with Oracle Autonomous Databases, when a row migrates and the ENABLE ROW MOVEMENT clause is specified for a table (be it Partitioned or Non-Partitioned), the ROWID of such rows are now updated on the fly. In non-autonomous database environments, such ROWIDs would NOT be updated, with a pointer in the previous table blocks pointing to the new physical location of the migrated row (as I previously discussed here).

So what’s the advantage of this new behaviour? Why might Oracle have made this change?

Well, the obvious benefit is that subsequent index scans that need to access migrated rows will have ROWIDs that directly point to the new, correct physical location of the row. Previously, indexes still had ROWIDs that reference the original row location and an additional table block access was required to access the row in its new physical location.

To illustrate this reduction in table block accesses, I’ll run a simple SQL that reads all 10,000 rows via an index from the BOWIE table that did not have the ENABLE ROW MOVEMENT clause when most rows were updated with significantly increased row sizes (as created in my previous post):

SQL> select /*+ index (bowie) */ * from bowie where id between 1 and 10000;

10,000 rows selected.

PLAN_TABLE_OUTPUT
_______________________________________________________________________________________________________________
SQL_ID 5gum0cs9pb3zf, child number 0
-------------------------------------
select /*+ index (bowie) */ * from bowie where id between 1 and 10000

Plan hash value: 1405654398

------------------------------------------------------------------------------------------------------------
| Id | Operation                           | Name       | Starts | E-Rows | A-Rows | A-Time     | Buffers  |
------------------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                    |            |      1 |        |  10000 |00:00:00.03 |    18866 |
|  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| BOWIE      |      1 |  10000 |  10000 |00:00:00.03 |    18866 |
|* 2 |   INDEX RANGE SCAN                  | BOWIE_ID_I |      1 |  10000 |  10000 |00:00:00.01 |      688 |
------------------------------------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
_____________________________________________________________________________________________________
Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ID">=1 AND "ID"<=10000)

Note
-----
   - automatic DOP: Computed Degree of Parallelism is 1 because of no expensive parallel operation

Statistics
-----------------------------------------------------------
          9 CPU used by this session
          9 CPU used when call started
         13 DB time
     136499 RM usage
        707 Requests to/from client
        706 SQL*Net roundtrips to/from client
      19508 buffer is not pinned count
      10216 buffer is pinned count
       5273 bytes received via SQL*Net from client
     201460 bytes sent via SQL*Net to client
          2 calls to get snapshot scn: kcmgss
          2 calls to kcmgcs
      18866 consistent gets
          1 consistent gets examination
          1 consistent gets examination (fastpath)
      18866 consistent gets from cache
      18865 consistent gets pin
      18865 consistent gets pin (fastpath)
          1 cursor authentications
          2 execute count
          2 global enqueue gets sync
          2 global enqueue releases
          1 index range scans
  154550272 logical read bytes from cache
      18865 no work - consistent read gets
        721 non-idle wait count
          1 non-idle wait time
          2 opened cursors cumulative
          1 opened cursors current
          2 parse count (total)
          1 parse time cpu
         20 process last non-idle time
      18866 session logical reads
          1 sorts (memory)
       2024 sorts (rows)
      10000 table fetch by rowid
       9059 table fetch continued row
        707 user calls

I’m using a SQLcl connection to my autonomous database here to more easily list a bunch of useful statistics.

The 2 statistics I just want to highlight are the number of consistent gets (18866) and the number of table fetch continued rows (9059).

If we compare this with the exactly same SQL on the exact same data, but this time on the BOWIE2 table that did have ENABLE ROW MOVEMENT enabled and thus had the ROWIDs updated on the fly when most of its rows migrated:

SQL> select /*+ index (bowie2) */ * from bowie2 where id between 1 and 10000;

10,000 rows selected.

PLAN_TABLE_OUTPUT
________________________________________________________________________________________________________________
SQL_ID c346wwr8f4hfu, child number 0
-------------------------------------
select /*+ index (bowie2) */ * from bowie2 where id between 1 and 10000

Plan hash value: 3243780227

-------------------------------------------------------------------------------------------------------------
| Id | Operation                           | Name        | Starts | E-Rows | A-Rows | A-Time     | Buffers  |
-------------------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                    |             |      1 |        |  10000 |00:00:00.02 |     4443 |
|  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| BOWIE2      |      1 |  10000 |  10000 |00:00:00.02 |     4443 |
|* 2 |   INDEX RANGE SCAN                  | BOWIE2_ID_I |      1 |  10000 |  10000 |00:00:00.01 |      710 |
-------------------------------------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
_____________________________________________________________________________________________________
Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ID">=1 AND "ID"<=10000)

Note
-----
   - automatic DOP: Computed Degree of Parallelism is 1 because of no expensive parallel operation

Statistics
-----------------------------------------------------------
          8 CPU used by this session
          8 CPU used when call started
         13 DB time
        340 OS Involuntary context switches
       2532 OS Page reclaims
         14 OS System time used
         45 OS User time used
       2425 OS Voluntary context switches
     188462 RM usage
        707 Requests to/from client
        706 SQL*Net roundtrips to/from client
       2244 Server Data Segments In
       2244 Server Data Segments Out
      62270 Server Elapsed Time (msec) Last Data Sent
   35307000 Server Time (usec) Busy Sending Data
       2596 Server Time (usec) Round Trip Time
         72 Server Time (usec) Round Trip Time Variance
     869824 Server Total Bytes Acked
      40188 Server Total Bytes Received
       5063 buffer is not pinned count
      15602 buffer is pinned count
       5274 bytes received via SQL*Net from client
     201450 bytes sent via SQL*Net to client
          2 calls to get snapshot scn: kcmgss
          2 calls to kcmgcs
       4443 consistent gets
          1 consistent gets examination
          1 consistent gets examination (fastpath)
       4443 consistent gets from cache
       4442 consistent gets pin
       4442 consistent gets pin (fastpath)
          2 execute count
          1 index range scans
   36397056 logical read bytes from cache
       4442 no work - consistent read gets
        720 non-idle wait count
          5 non-idle wait time
          2 opened cursors cumulative
          1 opened cursors current
          2 parse count (total)
        173 process last non-idle time
         14 session cursor cache count
          1 session cursor cache hits
       4443 session logical reads
          1 sorts (memory)
       2024 sorts (rows)
      10000 table fetch by rowid
        707 user calls

In this case, the number of consistent gets (4443) is much less than the previous 18866 and there are no table fetch continued row listed.

Now just a couple of points to make here.

Firstly, this is a tiny table and so the actual overall benefits here are relatively trivial, especially considering this all sits on an Exadata platform, where much of this data is effectively cached.

But as the saying goes, data may be updated once but accessed 10s of 1000s of times and so tiny savings can be considerable if SQLs are executed very frequently and/or tables are much larger and so less well cached within the database or the Exadata storage cells as a result.

You can determine if there’s potentially a migrated row problem by checking out CHAIN_CNT after analyzing a table:

SQL> analyze table bowie compute statistics;

Table BOWIE analyzed.

SQL> analyze table bowie2 compute statistics;

Table BOWIE2 analyzed.

SQL> select table_name, chain_cnt from user_tables where table_name in ('BOWIE', 'BOWIE2');

   TABLE_NAME    CHAIN_CNT
_____________ ____________
BOWIE                 9059
BOWIE2                   0

 

Note that CHAIN_CNT can also be a result of large rows that simply can’t fit within a data block, so you need to know your data to fully appreciate this figure. In this scenario, all 9059 chained rows are indeed associated with the migration of rows when the row length was substantially increased by an UPDATE statement.

A method of addressing ROWIDs that still point to the original table block following a row migration, is to reorganise the table (which can now be performed ONLINE):

SQL> alter table bowie move online;

Table BOWIE altered.

SQL> analyze table bowie compute statistics;

Table BOWIE analyzed.

SQL> select table_name, chain_cnt from user_tables where table_name in ('BOWIE', 'BOWIE2');

   TABLE_NAME    CHAIN_CNT
_____________ ____________
BOWIE2                   0
BOWIE                    0

As we can see, there are no longer any Chained Rows associated with the previously migrated rows.

This will now reduce the consistent gets and the overall overheads associated with accessing these previously migrated (chained) rows via an index, as we can now directly access their current table blocks via the correct ROWIDs.

If we now re-run the first SQL:

SQL> select /*+ index (bowie) */ * from bowie where id between 1 and 10000;

10,000 rows selected.

PLAN_TABLE_OUTPUT
_______________________________________________________________________________________________________________
SQL_ID 5gum0cs9pb3zf, child number 0
-------------------------------------
select /*+ index (bowie) */ * from bowie where id between 1 and 10000

Plan hash value: 1405654398

------------------------------------------------------------------------------------------------------------
| Id | Operation                           | Name       | Starts | E-Rows | A-Rows | A-Time     | Buffers  |
------------------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                    |            |      1 |        |  10000 |00:00:00.02 |     2677 |
|  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| BOWIE      |      1 |  10000 |  10000 |00:00:00.02 |     2677 |
|* 2 |   INDEX RANGE SCAN                  | BOWIE_ID_I |      1 |  10000 |  10000 |00:00:00.01 |      688 |
------------------------------------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
_____________________________________________________________________________________________________
Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ID">=1 AND "ID"<=10000)

Note
-----
   - automatic DOP: Computed Degree of Parallelism is 1 because of no expensive parallel operation

Statistics
-----------------------------------------------------------
          4 CPU used by this session
          4 CPU used when call started
       1850 Cached Commit SCN referenced
          5 DB time
     150963 RM usage
        707 Requests to/from client
        706 SQL*Net roundtrips to/from client
       3319 buffer is not pinned count
      17346 buffer is pinned count
       5273 bytes received via SQL*Net from client
     201475 bytes sent via SQL*Net to client
          2 calls to get snapshot scn: kcmgss
          2 calls to kcmgcs
       2677 consistent gets
          1 consistent gets examination
          1 consistent gets examination (fastpath)
       2677 consistent gets from cache
       2676 consistent gets pin
       2676 consistent gets pin (fastpath)
          2 execute count
          1 index range scans
   21929984 logical read bytes from cache
       2676 no work - consistent read gets
        720 non-idle wait count
          2 non-idle wait time
          2 opened cursors cumulative
          1 opened cursors current
          2 parse count (total)
         15 process last non-idle time
          1 session cursor cache count
          1 session cursor cache hits
       2677 session logical reads
          1 sorts (memory)
       2024 sorts (rows)
      10000 table fetch by rowid
        707 user calls

The consistent gets has gone way down to just 2677, down from the previous 18866…

In my next post, I’ll highlight some of the disadvantages with this new approached on how autonomous databases handle migrated rows in relation to now maintaining ROWIDs on the fly (and the discerning reader might even find a clue or two within this very post)… 🙂

When Does A ROWID Change? Part V (“The Wedding”) February 7, 2023

Posted by Richard Foote in 19c, 19c New Features, Autonomous Database, Autonomous Transaction Processing, Changing ROWID, Index Internals, Oracle, Oracle Cloud, Oracle General, Oracle Indexes, Richard's Blog, ROWID.
6 comments

It’s been a busy period. First Christmas, then the wedding of my beautiful daughter, then a nice get-a-way to get over the wedding of my beautiful daughter, and then a busy period with work.

But now I’m back 🙂

In this series on when does a ROWID change, I previously discussed how a row is generally “migrated”, but the ROWID remains unchanged, when a row is updated such that it can no longer fit within its current block. Hence the general rule has always been that the ROWID of a row does not change (although I also previously discussed various exceptions to this general rule).

However, things change in an Oracle Autonomous Database, when looking at the behaviour of the ROWID after a row migrates…

To illustrate, I’m going to run a similar demo as previously, but this time within (one of my free) Transaction Processing Autonomous Databases. I start by creating and populating a basic table, with the PCTFREE set to 0 to ensure my data blocks are initially nicely filled:

SQL> create table bowie (id number, name varchar2(142)) pctfree 0;

Table BOWIE created.

SQL> insert into bowie select rownum, 'BOWIE' from dual connect by level <=10000;

10,000 rows inserted.

SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'BOWIE');

PL/SQL procedure successfully completed.

SQL> create index bowie_id_i on bowie(id);

Index BOWIE_ID_I created.

 

Let’s just take note of a few random ROWID values:

SQL> select id, rowid from bowie where id in (42, 424, 4242) order by id;

     ID                 ROWID
_______ _____________________
     42 AAApUqAAAAACKD/AAp
    424 AAApUqAAAAACKD/AGn
   4242 AAApUqAAAAACKGEAHF

 

I’ll next update the rows with the NAME column value that is significantly larger than previously, to force the migration of many of my existing rows:

SQL> update bowie set name='THE RISE AND FALL OF ZIGGY STARDUST AND THE SPIDERS FROM MARS';

10,000 rows updated.

SQL> commit;

Commit complete.

 

If we now look at the ROWID of these same rows:

SQL> select id, rowid from bowie where id in (42, 424, 4242) order by id;

     ID                 ROWID
_______ _____________________
     42 AAApUqAAAAACKD/AAp
    424 AAApUqAAAAACKD/AGn
   4242 AAApUqAAAAACKGEAHF

 

We notice that they have NOT changed.

So the default behaviour in an Autonomous Database is as it has always been, that even though rows are migrated, it does NOT change the resultant ROWIDs.

This is an important point if you do NOT want your ROWIDs to change when a row is migrated (in the example perhaps that you have applications that explicitly use stored ROWIDs and are dependant on them not changing).

I’ll next run the same demo again, but with one key difference. This time, I’m explicitly setting ENABLE ROW MOVEMENT in the creation of my non-partitioned table:

 

SQL> create table bowie2 (id number, name varchar2(142)) pctfree 0 enable row movement;

Table BOWIE2 created.

SQL> insert into bowie2 select rownum, 'BOWIE' from dual connect by level <=10000;

10,000 rows inserted.

SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'BOWIE2');

PL/SQL procedure successfully completed.

SQL> create index bowie2_id_i on bowie2(id);

Index BOWIE2_ID_I created.

 

Let’s again have a look at the current ROWID of a few random rows:

SQL> select id, rowid from bowie2 where id in (42, 424, 4242) order by id;

     ID                 ROWID
_______ _____________________
     42 AAApUxAAAAACKIfAAp
    424 AAApUxAAAAACKIfAGn
   4242 AAApUxAAAAACKIkAHF

 

Let’s now perform the same update as before, forcing the migration of rows in the table:

SQL> update bowie2 set name='THE RISE AND FALL OF ZIGGY STARDUST AND THE SPIDERS FROM MARS';

10,000 rows updated.

SQL> commit;

Commit complete.

 

Now, as I discussed previously, in a non-autonomous environment, on a non-partitioned table, ENABLE ROW MOVEMENT would have no impact in this scenario and the ROWIDs would NOT have changed for any of these migrated rows.

But if we look at the ROWIDs in this autonomous database environment:

SQL> select id, rowid from bowie2 where id in (42, 424, 4242) order by id;

     ID                 ROWID
_______ _____________________
     42 AAApUxAAAAACKJqABX
    424 AAApUxAAAAACKJuAAJ
   4242 AAApUxAAAAACKMJABN

We can see that they have all indeed changed.

When a row migrates in an autonomous database environment AND we set the ENABLE ROW MOVEMENT on a non-partitioned table, the ROWIDs are indeed updated on the fly.

If we had an application that relied on these ROWIDs not changing:

SQL> select id from bowie2 where rowid in ('AAApUxAAAAACKIfAAp', 'AAApUxAAAAACKIfAGn', 'AAApUxAAAAACKIkAHF');

no rows selected

Well, the results would be “disappointing” (or downright disastrous if they then happen to select completed different rows)…

However, if we use an indexed key to fetched the required rows:

SQL> select * from bowie2 where id in (42, 424, 4242);

     ID                                                             NAME
_______ ________________________________________________________________
     42 THE RISE AND FALL OF ZIGGY STARDUST AND THE SPIDERS FROM MARS
    424 THE RISE AND FALL OF ZIGGY STARDUST AND THE SPIDERS FROM MARS
   4242 THE RISE AND FALL OF ZIGGY STARDUST AND THE SPIDERS FROM MARS

PLAN_TABLE_OUTPUT
_________________________________________________________________________________________________________________
SQL_ID atz1zbtyptu6n, child number 0
-------------------------------------
select * from bowie2 where id in (42, 424, 4242)

Plan hash value: 1734578469

--------------------------------------------------------------------------------------------------------------
| Id | Operation                            | Name        | Starts | E-Rows | A-Rows | A-Time     | Buffers  |
--------------------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                     |             |      1 |        |      3 |00:00:00.01 |        8 |
|  1 |  INLIST ITERATOR                     |             |      1 |        |      3 |00:00:00.01 |        8 |
|  2 |   TABLE ACCESS BY INDEX ROWID BATCHED| BOWIE2      |      3 |      3 |      3 |00:00:00.01 |        8 |
|* 3 |    INDEX RANGE SCAN                  | BOWIE2_ID_I |      3 |      3 |      3 |00:00:00.01 |        5 |
--------------------------------------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
_____________________________________________________________________________________________________

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access(("ID"=42 OR "ID"=424 OR "ID"=4242))

Note
-----
   - automatic DOP: Computed Degree of Parallelism is 1 because of no expensive parallel operation

 

They thankfully have indeed been correctly updated within the index and can successfully access the required rows.

So the decision is entirely yours. If you want to keep to the existing behaviour in relation to the non-changing of ROWIDs of migrated rows, do NOT set ENABLE ROW MOVEMENT on the tables in the autonomous database environments.

If you do want to adopt this new behaviour, then simply set ENABLE ROW MOVEMENT.

I’ll discuss the advantages and disadvantages of this new behaviour in future posts…