jump to navigation

Merry Christmas and Happy New Year!! (“The Jean Genie”) December 22, 2022

Posted by Richard Foote in Christmas, David Bowie, Richard's Blog, Richard's Musings, The Jean Genie.
1 comment so far

I would like to take this opportunity to wish all my readers who celebrate the festive season a very Merry Christmas and a most happy, peaceful and prosperous New Year.

2023 promises to be a big year for me, in that I’ll be officially retiring from work early in the year, after close to 40 years working in IT. I’ve had an absolute blast and met and worked with some truly amazing people, but it’s time to move on and enjoy other things that life has to offer.

But I’ll hopefully keep my hand in enough to perhaps attend the odd Oracle conference during the year and say my farewells and have a quiet drink with as many of my many Oracle-related friends and colleagues as I can. So you haven’t perhaps heard the last from me quite yet.

My Christmas gift this year is of course David Bowie related. 50 years ago, Bowie had a huge hit in the UK (and elsewhere) with the glam rock classic that is “The Jean Genie”. 50 years ago to the day, it was sitting in the UK 1972 Christmas charts at No. 16, but would reach as far as No. 2 in the coming weeks.

This is the official video, directed by the late, great Mick Rock. Enjoy !!

 

When Does A ROWID Change? Part IV (“Mass Production”) December 21, 2022

Posted by Richard Foote in Attribute Clustering, Autonomous Data Warehouse, Autonomous Database, Autonomous Transaction Processing, Changing ROWID, Clustering Factor, Data Clustering, Flashback, Move Partitions, Oracle, Oracle Blog, Oracle Cloud, Oracle General, Oracle Indexes, Partitioning, Richard's Blog, ROWID.
add a comment

In Part II in this series, I discussed how the update of the partitioned key column of a row that results in the row being moved to a different partition, will result in the ROWID of such rows changing.

However, there a quite a number of other user initiated actions in which ROWIDs can easily change (as indeed discussed in Connor McDonald’s video on this subject).

Some of these include:

  • Moving a table or partition, as this results in the segment being reorganised, with all associated rows being physically relocated and their associated ROWIDs changing
  • Altering a non-partitioned table such that it be now be partitioned, which again results in the physical relocation of all rows and their ROWIDs changing (which BTW, can potentially occur on a Autonomous Database without any user intervention)
  • Altering the partitioning strategy of a partitioned table, again changes the physical location of all rows
  • Hybrid Columnar Compression (HCC), which by packing rows more tightly, can more likely result in the physical relocation of a row during subsequent DML statements
  • Altering a table to Shrink Space, which attempts to move rows between table blocks to pack rows more tightly, again potentially resulting in rows physically moving and the changing of their associated ROWIDs
  • Flashback of a table, which results in rows being deleted and inserted and hence the change of their associated ROWIDs

I’ll illustrate an example of all this, with one of the key reasons why you may want to re-organise a table (and implicitly change all the ROWIDs of a table).

I’ll start by creating and populating a simple little table, with a CODE column that has very poorly clustered data:

SQL> create table bowie (id number, code number, name varchar2(42));

Table created.

SQL> insert into bowie select rownum, mod(rownum, 500), 'DAVID BOWIE' from dual connect by level <=1000000;

1000000 rows created.

SQL> commit;

Commit complete.

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

PL/SQL procedure successfully completed.

Let’s now create an index on this CODE column:

SQL> create index bowie_code_i on bowie(code);

Index created.

We take note of the ROWIDs of a few random rows:

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

        ID ROWID
---------- ------------------
        42 AAASn1AAMAAAgB2AAp
      4242 AAASn1AAMAAAgCHACL
    424242 AAASn1AAMAAAgbtAAJ

If we run a simple query with a predicate based on the CODE column:

SQL> select * from bowie where code=42;

2000 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1845943507

---------------------------------------------------------------------------
| Id  | Operation          | Name  | Rows | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       | 2000 | 42000 |    1004 (2)| 00:00:01 |
| * 1 |  TABLE ACCESS FULL | BOWIE | 2000 | 42000 |    1004 (2)| 00:00:01 |
---------------------------------------------------------------------------

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

   1 - filter("CODE"=42)

Statistics
----------------------------------------------------------
          0 recursive calls
          0 db block gets
       3596 consistent gets
          0 physical reads
          0 redo size
      20757 bytes sent via SQL*Net to client
         52 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
       2000 rows processed

We notice the CBO has chosen to ignore the index and use a FTS instead, even though only 2000 rows in a 1M row table (just 0.2%) are returned.

Why?

Because the clustering of the CODE data is terrible, with the required values littered throughout the table. If we look at the Clustering Factor of the index:

SQL> select index_name, leaf_blocks, clustering_factor from user_indexes where index_name='BOWIE_CODE_I';

INDEX_NAME   LEAF_BLOCKS CLUSTERING_FACTOR
------------ ----------- -----------------
BOWIE_CODE_I        2063           1000000

We notice the index has the worst possible Clustering Factor value of 1000000.

So to improve the performance of this (say critical) query, we can add a Clustering Attribute to this table based on the CODE column and then reorganise the table:

SQL> alter table bowie add clustering by linear order (code);

Table altered.

SQL> alter table bowie move online;

Table altered.

If we now look at the Clustering Factor of the index:

SQL> select index_name, leaf_blocks, clustering_factor from user_indexes where index_name='BOWIE_CODE_I';

INDEX_NAME   LEAF_BLOCKS CLUSTERING_FACTOR
------------ ----------- -----------------
BOWIE_CODE_I        2063              3568

We can see it has substantially improved, down to just 3568 from the previous 1000000 value, as the data is now perfectly clustered based on the CODE column.

If we now re-run the query:

SQL> select * from bowie where code=42;

2000 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 853003755

----------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name         | Rows | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |              | 2000 | 42000 |      15 (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED | BOWIE        | 2000 | 42000 |      15 (0)| 00:00:01 |
| * 2 |   INDEX RANGE SCAN                   | BOWIE_CODE_I | 2000 |       |       7 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

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

   2 - access("CODE"=42) 

Statistics
----------------------------------------------------------
          0 recursive calls
          0 db block gets
         17 consistent gets
          0 physical reads
          0 redo size
      50735 bytes sent via SQL*Net to client
         52 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
       2000 rows processed

The CBO now choses to use the index and the query is much more efficient as a result (consistent gets down to just 17 from the previous 3596).

So all is now much better, except for any application that was reliant on using ROWIDs to fetch the data, as all ROWIDs have now changed:

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

        ID ROWID
---------- ------------------
        42 AAASn6AAMAAAACvAEf
      4242 AAASn6AAMAAAiRaAA4
    424242 AAASn6AAMAAAiRWAEQ

So there are many ways in which the ROWID of a row can potentially change.

And now there’s another key manner in which a ROWID can very easily change in Oracle Autonomous Database environments, as I’ll next discuss…

When Does A ROWID Change? Part III (“Arriving Somewhere But Not Here”) December 13, 2022

Posted by Richard Foote in Autonomous Database, Changing ROWID, Index Internals, Local Indexes, Oracle, Oracle Blog, Oracle Cloud, Oracle General, Oracle Indexes, Oracle Indexing Internals Webinar, Partitioning, Performance Tuning, Richard's Blog, ROWID, Secondary Indexes.
2 comments

In Part II of this series, I discussed how updating the Partitioned Key of a row from a Partitioned table will result in the row physically moving and the associated ROWID changing.

One of the reasons why changing the ROWID has historically has not been the default behaviour and requires the explicit setting of the ENABLE ROW MOVEMENT clause for Partitioned tables is because changing a ROWID comes at a cost. The cost being not only having to delete and re-insert the row within the table, but also delete and re-insert the associated index entry for each corresponding index on the table.

To illustrate, I’m going to create and populate another simple little Partitioned Table:

SQL> CREATE TABLE big_bowie2(id number, code1 number, code2 number, code3 number, code4 number, code5 number, code6 number, code7 number, code8 number, code9 number, code10 number, release_date date, name varchar2(42)) PARTITION BY RANGE (release_date)
(PARTITION ALBUMS_2021 VALUES LESS THAN (TO_DATE('01-JAN-2022', 'DD-MON-YYYY')),
PARTITION ALBUMS_2022 VALUES LESS THAN (MAXVALUE)) ENABLE ROW MOVEMENT;

Table created.

SQL> INSERT INTO big_bowie2 SELECT rownum, mod(rownum,1000), mod(rownum,1000), mod(rownum,1000), mod(rownum,100
ownum,1000), mod(rownum,1000), mod(rownum,1000), mod(rownum,1000), mod(rownum,1000), mod(rownum,1000), sysdate-
m,500), 'DAVID BOWIE' FROM dual CONNECT BY LEVEL <= 10000;

10000 rows created.

SQL> commit;

Commit complete.

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

PL/SQL procedure successfully completed.

I’m now going to create a number of basic Global indexes on this table:

SQL> create index big_bowie2_id_i on big_bowie2(id);

Index created.

SQL> create index big_bowie2_code1_i on big_bowie2(code1);

Index created.

SQL> create index big_bowie2_code2_i on big_bowie2(code2);

Index created.

SQL> create index big_bowie2_code3_i on big_bowie2(code3);

Index created.

SQL> create index big_bowie2_code4_i on big_bowie2(code4);

Index created.

SQL> create index big_bowie2_code5_i on big_bowie2(code5);

Index created.

SQL> create index big_bowie2_code6_i on big_bowie2(code6);

Index created.

SQL> create index big_bowie2_code7_i on big_bowie2(code7);

Index created.

SQL> create index big_bowie2_code8_i on big_bowie2(code8);

Index created.

SQL> create index big_bowie2_code9_i on big_bowie2(code9);

Index created.

SQL> create index big_bowie2_code10_i on big_bowie2(code10);

Index created.

If I run a simple single row UPDATE that updates a non-indexed, non-partitioned key column:

SQL> update big_bowie2 set name='ZIGGY STARDUST' where id=424;

1 row updated.

Execution Plan
----------------------------------------------------------
Plan hash value: 3590621923

-------------------------------------------------------------------------------------
| Id | Operation          | Name            | Rows | Bytes | Cost (%CPU) | Time     |
-------------------------------------------------------------------------------------
|  0 | UPDATE STATEMENT   |                 |    1 |    16 |       2 (0) | 00:00:01 |
|  1 |  UPDATE            | BIG_BOWIE2      |      |       |             |          |
|* 2 |   INDEX RANGE SCAN | BIG_BOWIE2_ID_I |    1 |    16 |       1 (0) | 00:00:01 |
-------------------------------------------------------------------------------------

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

   2 - access("ID"=424)

Statistics
----------------------------------------------------------
          1 recursive calls
          1 db block gets
          2 consistent gets
          0 physical reads
        328 redo size
        204 bytes sent via SQL*Net to client
         41 bytes received via SQL*Net from client
          1 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          1 rows processed

We can see that the number of db block gets is just 1 and consistent gets just 2, as only the one table block needs to be updated and easily accessed via the index on the ID column.

If we now run a single row update of an indexed column:

SQL> update big_bowie2 set code1=42 where id = 424;

1 row updated.

Execution Plan
----------------------------------------------------------
Plan hash value: 3590621923

-------------------------------------------------------------------------------------
| Id | Operation          | Name            | Rows | Bytes | Cost (%CPU) | Time     |
-------------------------------------------------------------------------------------
|  0 | UPDATE STATEMENT   |                 |    1 |     8 |       2 (0) | 00:00:01 |
|  1 |  UPDATE            | BIG_BOWIE2      |      |       |             |          |
|* 2 |   INDEX RANGE SCAN | BIG_BOWIE2_ID_I |    1 |     8 |       1 (0) | 00:00:01 |
-------------------------------------------------------------------------------------

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

   2 - access("ID"=424)

Statistics
----------------------------------------------------------
          1 recursive calls
          5 db block gets
          2 consistent gets
          1 physical reads
        948 redo size
        204 bytes sent via SQL*Net to client
         41 bytes received via SQL*Net from client
          1 SQL*Net roundtrips to/from client
          1 sorts (memory)
          0 sorts (disk)
          1 rows processed

We can see that the number of db block gets increases to 5, as not only does the table block have to be updated but so also do the associated index blocks.

If we now finally run a single row update on the partitioned table’s partition key column that results in the row having to physically move to another partition:

SQL> update big_bowie2 set release_date='06-DEC-22' where id = 424;

1 row updated.

Execution Plan
----------------------------------------------------------
Plan hash value: 3590621923

-------------------------------------------------------------------------------------
| Id | Operation          | Name            | Rows | Bytes | Cost (%CPU) | Time     |
-------------------------------------------------------------------------------------
|  0 | UPDATE STATEMENT   |                 |    1 |    64 |       2 (0) | 00:00:01 |
|  1 |  UPDATE            | BIG_BOWIE2      |      |       |             |          |
|* 2 |   INDEX RANGE SCAN | BIG_BOWIE2_ID_I |    1 |    64 |       1 (0) | 00:00:01 |
-------------------------------------------------------------------------------------

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

   2 - access("ID"=424)

Statistics
----------------------------------------------------------
          1 recursive calls
         49 db block gets
          3 consistent gets
          0 physical reads
       5996 redo size
        204 bytes sent via SQL*Net to client
         41 bytes received via SQL*Net from client
          1 SQL*Net roundtrips to/from client
         11 sorts (memory)
          0 sorts (disk)
          1 rows processed

We see that the number of db block gets jumps significantly to 49, as all the corresponding indexes require their associated index entries to be likewise deleted/re-inserted in order to change all their ROWIDs.

So this additional cost of updating the indexes has been a cost that Oracle has traditionally attempted to avoid, by generally not changing the ROWID when performing an update of a row.

Of course, the update of a Partitioned Key column is not the only manner in which ROWIDs have previously easily changed as we’ll see in Part IV…

UPDATE: As my buddie Martin Widlake makes in this comment. it’s also well worth mentioning the increase in associated redo (as redo is an excellent measurement of “work” the Oracle Database has to perform), if Oracle has to change the ROWID of a row and make the necessary changes to all its corresponding indexes. In the example above, the redo increases significantly from 328 bytes to 5996 bytes, when Oracle has to move the row to another partition and so update the ROWID on the 11 indexes. More on all this when I discuss the changes implemented with the current Autonomous Databases…

When Does A ROWID Change? Part II (“You’ve Got A Habit Of Leaving”) December 9, 2022

Posted by Richard Foote in Autonomous Database, CBO, Changing ROWID, Global Indexes, Multiple Indexes, Oracle, Oracle 21c, Oracle Blog, Oracle Cloud, Oracle General, Partitioning, Performance Tuning, Richard's Blog, ROWID.
3 comments

In my previous post, I discussed how a row is generally “migrated”, but the ROWID remains unchanged, when a row is updated such that it can no longer fit into its current block. Hence the general rule has always been that the ROWID of a row does not change.

However, even before the changes now present with Oracle Autonomous Databases (to be discussed in future posts), there has always been (since Oracle 8) one classic scenario when this “ROWID never changes after an update” rule has not been true.

To illustrate, I’m going to create and populate a basic little Range-based Partitioned table, with the RELEASE_DATE column being the partitioned column:

SQL> CREATE TABLE big_bowie(id number, release_date date, name varchar2(42)) PARTITION BY RANGE (release_date)
(PARTITION ALBUMS_2021 VALUES LESS THAN (TO_DATE('01-JAN-2022', 'DD-MON-YYYY')),
PARTITION ALBUMS_2022 VALUES LESS THAN (MAXVALUE));

Table created.

SQL> INSERT INTO big_bowie SELECT rownum, sysdate-mod(rownum,500), 'DAVID BOWIE' FROM dual CONNECT BY LEVEL <= 10000;

10000 rows created.

SQL> commit;

Commit complete.

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

PL/SQL procedure successfully completed.

Let’s look at the current ROWIDs of a few random rows:

SQL> select id, release_date, rowid from big_bowie where id in (424, 444, 482) order by id;

        ID RELEASE_D ROWID
---------- --------- ------------------
       424 08-OCT-21 AAASe9AAMAAAAj7ABU
       444 18-SEP-21 AAASe9AAMAAAAj7ABo
       482 11-AUG-21 AAASe9AAMAAAAj7ACO

I’m now going to try and update for these rows, the partitioned column value, such that they would now logically belong in the other partition:

SQL> update big_bowie set release_date='06-DEC-22' where id in (424, 444, 482);
update big_bowie set release_date='06-DEC-22' where id in (424, 444, 482)
*
ERROR at line 1:
ORA-14402: updating partition key column would cause a partition change

I now get a very key and important error. By default, Oracle does not allow you to update a row if it results in the row having to physically move to a different partition.

I suspect there are at least 3 good reasons for this default restriction.

One is to protect the business integrity of the data, where it might just not make any business sense for a row to be updated in this manner.

The second is that it protects any applications out there that explicitly uses ROWIDs and relies on the ROWIDs not suddenly changing behind the scenes.

And finally, it protects perhaps valuable database resources and ensures that the database does not have to incur any additional workloads, that would be necessary if such an operation were to proceed.

But we have the ability and control to override this default behaviour in the following manner with the ENABLE ROW MOVEMENT clause:

SQL> alter table big_bowie enable row movement;

Table altered.

If we now try and update these rows again:

SQL> update big_bowie set release_date='06-DEC-22' where id in (424, 444, 482);

3 rows updated.

SQL> commit;

Commit complete.

The updates are now successful.

As these rows no longer logically belong in the previous partition, they have to be physically moved to its new partition. This is effectively implemented by deleting the rows from the previous partition and then re-inserting them in the new partition segment.

If we now look the ROWIDs of these updated rows:

SQL> select id, release_date, rowid from big_bowie where id in (424, 444, 482) order by id;

        ID RELEASE_D ROWID
---------- --------- ------------------
       424 06-DEC-22 AAASe+AAMAAAATQABR
       444 06-DEC-22 AAASe+AAMAAAATQABS
       482 06-DEC-22 AAASe+AAMAAAATQABT

We notice that they now all have different ROWIDs, because they indeed now all exist in a different physical location.

In my next post, I’ll highlight but one obvious disadvantage and consequence of allowing rows to be physically moved in this manner…

When Does A ROWID Change? Part I (“Fearless”) December 7, 2022

Posted by Richard Foote in Autonomous Database, Block Dumps, Changing ROWID, Oracle, Oracle Blog, Oracle Cloud, Oracle General, Oracle Indexes, Oracle Table Internals, Oracle19c, Pink Floyd, Richard's Blog, ROWID.
3 comments

Recently, my mate Connor McDonald caused a tad of a storm when he disclosed that the once sacred, (almost) unchangeable ROWID can now indeed potentially easily change, without the DBA doing a thing.

You can watch his excellent video on the subject here.

As the humble ROWID is a critical component of any index, I thought it worthwhile to have a deep dive discussion on when a ROWID can and can’t change and some of the key changes that have been introduced within Oracle’s Autonomous database environments.

If you’re a developer who explicitly uses the ROWID in your applications, you might want to pay extra attention to these changes.

I thought I’ll begin first though by discussing how the ROWID currently doesn’t generally change…

The ROWID is basically just a pointer stored in indexes that effectively points to the physical location of a row within a table that’s associated with the specific indexed key. It consists of the Data Object ID (if it’s a Global Index associated to a Partitioned Table, as the Relative File ID is no longer a unique value and so needs this to determine the appropriate tablespace), a Relative File ID, a Data Block ID within the Relative File and the Row Location ID within the Data Block.

Now although it has never been a completely risk free approach to manually store and directly use these ROWIDs to fetch a row from a table, it’s a technique that has been frequently used by developers for 3 very good reasons.

The first reason is that it’s one of the most efficient ways to fetch a required row, because the database can go directly to the physical location and directly access the required row, without having to even read a single index block.

The second reason is because it has always been fully supported by Oracle to do so, with the required syntax well documented. Indeed, Oracle APEX includes the base functionality to store and access rows directly via their associated ROWIDs.

The third reason is because it’s generally well understood that the ROWID doesn’t change, except for a very few (generally) well known scenarios, and so using the ROWID to access data within an application is viewed as being reasonable safe.

When a row is inserted into a table, each corresponding index on the table (generally) has a new index entry also inserted, including its associated ROWID. Now, if the row never moves from its current physical location, there is no need to ever worry about the ROWID subsequently changing.

So the big question is, when can a row physically move AND the associated ROWID change?

Logically, a scenario that springs to mind is when a row is updated and made bigger and there’s no longer room within the current data block to store the larger row. Does the row move to another block with sufficient free space and result in the ROWID to change?

To check out this scenario, I’ll create and populate a basic table, with PCTFREE set to 0, so once the blocks within the table are filled, there is precious little space for rows to subsequently grow:

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

Table created.

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

10000 rows created.

SQL> commit;

Commit complete.

SQL> create index bowie_id_i on bowie(id);

Index created.

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

PL/SQL procedure successfully completed.

 

For good measure, I’ve also included the ENABLE ROW MOVEMENT clause, which is usually associated with Partitioned Tables (as I’ll discuss in Part II in this series).

Let’s have a look at the ROWIDs of a few random rows:

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

ID         ROWID
---------- ------------------
42         AAASe4AAMAAAACHAAp
424        AAASe4AAMAAAACHAGn
4242       AAASe4AAMAAAACNAHV

If we look at a partial block dump of one of the table blocks:

tab 0, row 0, @0x1312
tl: 12 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 02
col 1: [ 5] 42 4f 57 49 45
tab 0, row 1, @0x131e
tl: 12 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 03
col 1: [ 5] 42 4f 57 49 45
tab 0, row 2, @0x132a
tl: 12 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 04
col 1: [ 5] 42 4f 57 49 45
tab 0, row 3, @0x1336
tl: 12 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 05
col 1: [ 5] 42 4f 57 49 45
tab 0, row 4, @0x1342
tl: 12 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 06
col 1: [ 5] 42 4f 57 49 45

The above partial block dump shows the first 5 rows within the block, with the contents of the 2 table columns listed (in hex format).

If we access a row via an index:

SQL> select * from bowie where id=42;

ID         NAME
---------- --------------------------------------------------------------
42         THE RISE AND FALL OF ZIGGY STARDUST AND THE SPIDERS FROM MARS

Execution Plan
--------------------------------------------------------------------------------------------------
|  Id | Operation                            | Name       | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |            |    1 |    66 |       2 (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED | BOWIE      |    1 |    66 |       2 (0)| 00:00:01 |
| * 2 |   INDEX RANGE SCAN                   | BOWIE_ID_I |    1 |       |       1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

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

   2 - access("ID"=42)

Statistics
----------------------------------------------------------
          0 recursive calls
          0 db block gets
          4 consistent gets
          0 physical reads
          0 redo size
        702 bytes sent via SQL*Net to client
         52 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          1 rows processed

We can see in this example that Oracle requires 4 consistent gets, 3 of which are accesses to the index.

If we access this table via its ROWID:

SQL> select * from bowie where rowid='AAASe4AAMAAAACHAAp';

ID         NAME
---------- --------------------------------------------------------------
42         THE RISE AND FALL OF ZIGGY STARDUST AND THE SPIDERS FROM MARS

Execution Plan
------------------------------------------------------------------------------------
| Id | Operation                   | Name  | Rows | Bytes | Cost (%CPU) | Time     |
------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT            |       |    1 |    10 |       1 (0) | 00:00:01 |
|  1 |  TABLE ACCESS BY USER ROWID | BOWIE |    1 |    10 |       1 (0) | 00:00:01 |
------------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
          0 recursive calls
          0 db block gets
          1 consistent gets
          0 physical reads
          0 redo size
        698 bytes sent via SQL*Net to client
         52 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          1 rows processed

We can see that this only requires just the 1 consistent get (vs. 4 when using the index) to access the row.

So we can see the appeal of using the ROWID to access a row.

If we now update the rows within the table and make them substantially larger so they can no longer fit within the currently filled blocks:

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

10000 rows updated.

SQL> commit;

Commit complete.

And now look again at the ROWIDs of these selected rows:

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

ID         ROWID
---------- ------------------
42         AAASe4AAMAAAACHAAp
424        AAASe4AAMAAAACHAGn
4242       AAASe4AAMAAAACNAHV

We notice that the ROWIDs all remain the same.

This has always historically been the behaviour here. If we update a row and the updated row can no longer fit within the current block, the row “migrates” to another table block with sufficient free space. BUT the associated ROWIDs do NOT change and the associated indexes are NOT updated.

Rather, the row data within the updated table is replaced with a “pointer”, that points to the new physical location of the migrated row. The advantage here being that Oracle only has to update the table with this new pointer, rather having to update the associated ROWIDs of all the (possibly many) associated indexes (noting that such an update would actually result in a delete followed by a re-insert of each index entry).

The disadvantage of course is that to now access this migrated row via an index requires an extra hop, to first read the initial table block and then to follow the pointer and access the actual block that now contains the row. Note if this row is forced to be migrated again because it grows again and can’t be housed in the current block, this pointer in the initial block is updated to reflect the newer location, so at least there is only ever the one extra hop.

If we look at a new partial block dump of the previously accessed block:

tab 0, row 0, @0x1f8f
tl: 9 fb: --H----- lb: 0x2 cc: 0
nrid: 0x030000bf.4b
tab 0, row 1, @0x1f86
tl: 9 fb: --H----- lb: 0x2 cc: 0
nrid: 0x030000bf.4c
tab 0, row 2, @0x1f7d
tl: 9 fb: --H----- lb: 0x2 cc: 0
nrid: 0x030000bf.4d
tab 0, row 3, @0x1f74
tl: 9 fb: --H----- lb: 0x2 cc: 0
nrid: 0x030000bf.4e
tab 0, row 4, @0x1f6b
tl: 9 fb: --H----- lb: 0x2 cc: 0
nrid: 0x030000b8.0

We notice that the rows with their 2 columns have been replaced with a logical nrid pointer (consisting of a relative block address and row location within the block), that effectively points to the new physical location of the row.

Note we can still use the same, unchanged ROWID to access the same table rows:

SQL> select * from bowie where rowid='AAASe4AAMAAAACHAAp';

ID         NAME
---------- --------------------------------------------------------------
42         THE RISE AND FALL OF ZIGGY STARDUST AND THE SPIDERS FROM MARS

Execution Plan
------------------------------------------------------------------------------------
| Id | Operation                   | Name  | Rows | Bytes | Cost (%CPU) | Time     |
------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT            |       |    1 |    10 |       1 (0) | 00:00:01 |
|  1 |  TABLE ACCESS BY USER ROWID | BOWIE |    1 |    10 |       1 (0) | 00:00:01 |
------------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
          0 recursive calls
          0 db block gets
          2 consistent gets
          0 physical reads
          0 redo size
        698 bytes sent via SQL*Net to client
         52 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          1 rows processed

This ROWID access still works fine, except it has now increased to 2 consistent gets, one to access the initial block referenced by the ROWID and the extra consistent get to follow the pointer and access the new physical location of the row.

So historically, we haven’t had to worry about updates changing the ROWID of a row (except perhaps at looking at reducing the number of these migrated rows).

Well, except for one clear example as I’ll discuss in Part II

Automatic Indexing: Potential Locking Issues Part II (“Don’t Stop”) December 5, 2022

Posted by Richard Foote in 19c, 19c New Features, Automatic Indexing, Autonomous Database, CBO, Exadata, Full Table Scans, Invisible Indexes, Locking Issues, Oracle, Oracle Cloud, Oracle Cost Based Optimizer, Oracle General, Oracle Indexes.
add a comment

In my previous post, I highlighted how a long transaction can potentially cause the creation of an Automatic Index to hang due to the inability of the Automatic Indexing process to obtain the necessary locks.

However, these locks can have a much wider consequence, as it’s the entire Automatic Indexing process that is forced to hang, not just the creation of a specific index. This is due to the fact that Automatic Indexing works in a serial fashion, working on one index at a time, in order to put the brakes on the amount of resources that Automatic Indexing can potentially consume.

Therefore, it’s not just the creation of the specifically locked automatic index that is impacted, but the subsequent creation of all Automatic Indexes. No other Automatic Index can be created until the locking issue is resolved.

To highlight, I’m going to create and populate other table:

SQL> create table david_bowie (id number, code number, name varchar2(42));

Table created.

SQL> insert into david_bowie select rownum, mod(rownum, 1000000)+1, 'David Bowie' from dual connect by level <= 10000000;

10000000 rows created.

SQL> commit;

Commit complete.

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

PL/SQL procedure successfully completed.

I’ll next run an SQL several times that is forced to perform a Full Table Scan because of a missing index:

SQL> select * from david_bowie where code=42; 10 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 1390211489 --------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 10 | 230 | 6714 (2)| 00:00:01 | | * 1 | TABLE ACCESS FULL | DAVID_BOWIE | 10 | 230 | 6714 (2)| 00:00:01 | --------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - storage("CODE"=42) filter("CODE"=42) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 48130 consistent gets 38657 physical reads 0 redo size 885 bytes sent via SQL*Net to client 52 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 10 rows processed However, if we look at the current Automatic Indexing report: SQL> select dbms_auto_index.report_last_activity() report from dual; REPORT -------------------------------------------------------------------------------- GENERAL INFORMATION ------------------------------------------------------------------------------- Activity start : 01-DEC-2022 07:12:31 Activity end : 05-DEC-2022 12:15:42 Executions completed : 0 Executions interrupted : 0 Executions with fatal error : 0 ------------------------------------------------------------------------------- SUMMARY (AUTO INDEXES) ------------------------------------------------------------------------------- Index candidates : 0 Indexes created : 0 Space used : 0 B Indexes dropped : 0 SQL statements verified : 0 SQL statements improved : 0 SQL plan baselines created : 0 Overall improvement factor : 1x ------------------------------------------------------------------------------- SUMMARY (MANUAL INDEXES) ------------------------------------------------------------------------------- Unused indexes : 0 Space used : 0 B Unusable indexes : 0 ------------------------------------------------------------------------------- ERRORS -------------------------------------------------------------------------------- ------------- No errors found. -------------------------------------------------------------------------------- -------------

 

We can see that the Automatic Indexing process is STILL hanging days later from the still uncommitted transaction. Therefore, it’s impossible for an Automatic Index to be created for this new workload, or indeed ANY new workload, until the locking issue is resolved, with the completion of the associated locking transaction.

We can easily see the troublesome lock:

SQL> select * from dba_waiters;

WAITING_SESSION WAITING_CON_ID HOLDING_SESSION HOLDING_CON_ID LOCK_TYPE   MODE_HELD MODE_REQUESTED   LOCK_ID1   LOCK_ID2
--------------- -------------- --------------- -------------- ----------- --------- -------------- ---------- ----------
            164              3             167              3 Transaction Exclusive Share              327694      10623

 

As a consequence, no new Automatic Index can be created for this new workload:

SQL> select index_name, auto, constraint_index, visibility, status, num_rows, leaf_blocks
from user_indexes where table_name='DAVID_BOWIE';

no rows selected

And the existing workload remains inefficient:

SQL> select * from david_bowie where code=42;

10 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1390211489

---------------------------------------------------------------------------------
|  Id | Operation          | Name        | Rows | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |             |   10 |   230 |    6714 (2)| 00:00:01 |
| * 1 |  TABLE ACCESS FULL | DAVID_BOWIE |   10 |   230 |    6714 (2)| 00:00:01 |
---------------------------------------------------------------------------------

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

    1 - storage("CODE"=42)
        filter("CODE"=42)

Statistics
----------------------------------------------------------
          0 recursive calls
          0 db block gets
      48130 consistent gets
      38657 physical reads
          0 redo size
        885 bytes sent via SQL*Net to client
         52 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
         10 rows processed

 

Once the locking transaction is finally completed:

SQL> insert into bowie_busy values (10000001, 42, 'Ziggy Stardust');

1 row created.

SQL> commit;

Commit complete.

The Automatic Indexing process can again resume and the new Automatic Indexes can finally be created as necessary:

SQL> select dbms_auto_index.report_last_activity() report from dual;

REPORT
--------------------------------------------------------------------------------
GENERAL INFORMATION
-------------------------------------------------------------------------------
Activity start              : 05-DEC-2022 12:30:30
Activity end                : 05-DEC-2022 12:31:22
Executions completed        : 1
Executions interrupted      : 0
Executions with fatal error : 0
-------------------------------------------------------------------------------

SUMMARY (AUTO INDEXES)
-------------------------------------------------------------------------------
Index candidates                      : 0
Indexes created (visible / invisible) : 2 (0 / 2)
Space used (visible / invisible)      : 287.31 MB (0 B / 287.31 MB)
Indexes dropped                       : 0
SQL statements verified               : 3
SQL statements improved               : 0
SQL plan baselines created            : 0
Overall improvement factor            : 1x
-------------------------------------------------------------------------------

SUMMARY (MANUAL INDEXES)
-------------------------------------------------------------------------------
Unused indexes   : 0
Space used       : 0 B
Unusable indexes : 0
-------------------------------------------------------------------------------

INDEX DETAILS
-------------------------------------------------------------------------------
1. The following indexes were created:
-------------------------------------------------------------------------------
---------------------------------------------------------------------------
| Owner | Table       | Index                | Key  | Type   | Properties |
---------------------------------------------------------------------------
| BOWIE | BOWIE_BUSY  | SYS_AI_8pkdh6q096qvs | CODE | B-TREE | NONE       |
| BOWIE | DAVID_BOWIE | SYS_AI_czmkjhqr21732 | CODE | B-TREE | NONE       |
---------------------------------------------------------------------------
-------------------------------------------------------------------------------

ERRORS
--------------------------------------------------------------------------------
-------------
No errors found.
--------------------------------------------------------------------------------
-------------

 

If you find that the Automatic Indexing process has hung, check to make sure there are no long locks on associated underlying tables that could be causing the whole Automatic Index process to freeze…

 

NOTE: This post is dedicated to the memory of Christine McVie, who recently passed away…

Automatic Indexing: Potential Locking Issues Part I (“Rattle That Lock”) December 1, 2022

Posted by Richard Foote in 19c, Automatic Indexing, Autonomous Database, Exadata, Full Table Scans, Invisible Indexes, Locking Issues, Oracle, Oracle 21c, Oracle Blog, Oracle Cloud, Oracle General, Oracle Indexes, Oracle19c, Unusable Indexes.
1 comment so far

 

I’ve discussed previously locking issues associated with the creation of indexes. Although things have changed and improved over the years, even with the ONLINE option currently, an index creation process still requires (albeit brief and non-escalating) locks on the underlining table.

Basically, there needs to be a brief period where there isn’t an active transaction on the underlining table for the index creation process to complete, else it will forced to wait and hang. Oracle requires a table lock on the underlining table at the start of the CREATE or REBUILD process (to guarantee data dictionary information) and a lock at the end of the process (to merge index changes made during the rebuild into the final index structure).

So how do these index lock requirements potentially impact the Automatic Indexing process?

To investigate, I’ll create and populate a basic table with a highly selective CODE column:

SQL> create table bowie_busy (id number constraint bowie_busy_pk primary key, code number, name varchar2(42));

Table created.

SQL> insert into bowie_busy select rownum, mod(rownum, 1000000)+1, 'David Bowie' from dual connect by level <= 10000000;

10000000 rows created.

SQL> commit;

Commit complete.

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

PL/SQL procedure successfully completed.

 

In a second session, I’ll insert a new row but NOT commit the change, thereby creating a extended transaction:

SQL> insert into bowie_busy values (10000001, 42, 'Ziggy Stardust');

1 row created.

 

Back in the original session, I’ll run the following SQL numerous times:

 

SQL> select * from bowie_busy where code=42;

10 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 3896751453

--------------------------------------------------------------------------------
| Id  | Operation          | Name       | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |            |   10 |   230 |    6714 (2)| 00:00:01 |
| * 1 |  TABLE ACCESS FULL | BOWIE_BUSY |   10 |   230 |    6714 (2)| 00:00:01 |
--------------------------------------------------------------------------------

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

1 - storage("CODE"=42)
    filter("CODE"=42)

Statistics
----------------------------------------------------------
          0 recursive calls
          0 db block gets
      71423 consistent gets
      38657 physical reads
          0 redo size
        885 bytes sent via SQL*Net to client
         52 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
         10 rows processed

 

Without an associated index in place, the CBO currently has no choice but to perform a Full Table Scan. But with the SQL only returning 10 rows from the 10M table, clearly an index would be beneficial.

But how does the existing transaction and associated locks on table impact the Automatic Indexing process?

There’s nothing magical here. With the current transaction in place on the underlying table, the index creation process simply can’t be completed. If we look at the status of the Automatic Index:

SQL> select index_name, auto, constraint_index, visibility, status, num_rows, leaf_blocks from user_indexes where table_
name='BOWIE_BUSY';

INDEX_NAME                     AUT CON VISIBILIT STATUS     NUM_ROWS LEAF_BLOCKS
------------------------------ --- --- --------- -------- ---------- -----------
BOWIE_BUSY_PK                  NO  YES VISIBLE   VALID      10000000       19856
SYS_AI_8pkdh6q096qvs           YES NO  INVISIBLE UNUSABLE   10000000       23058

It remains in its initial INVISIBLE/USABLE state.

If we look at the Automatic Indexing monitoring report, some 6 HOURS after the initial running of the Automatic Index process for this index:

SQL> select dbms_auto_index.report_last_activity() report from dual;

REPORT
--------------------------------------------------------------------------------
GENERAL INFORMATION
-------------------------------------------------------------------------------
Activity start              : 01-DEC-2022 07:12:31
Activity end                : 01-DEC-2022 13:05:53
Executions completed        : 0
Executions interrupted      : 0
Executions with fatal error : 0
-------------------------------------------------------------------------------

SUMMARY (AUTO INDEXES)
-------------------------------------------------------------------------------
Index candidates           : 0
Indexes created            : 0
Space used                 : 0 B
Indexes dropped            : 0
SQL statements verified    : 0
SQL statements improved    : 0
SQL plan baselines created : 0
Overall improvement factor : 1x
-------------------------------------------------------------------------------

SUMMARY (MANUAL INDEXES)
-------------------------------------------------------------------------------
Unused indexes   : 0
Space used       : 0 B
Unusable indexes : 0
-------------------------------------------------------------------------------

ERRORS
--------------------------------------------------------------------------------
-------------
No errors found.
--------------------------------------------------------------------------------
-------------

 

We notice that the whole Automatic Indexing process has been locked out and left in a hanging state (the times between the activity start/end times just keep climbing, with 0 executions of the Automatic Indexing process completed).

Without a VISIBLE/USABLE automatic index in place, if we re-run the SQL again:

SQL> select * from bowie_busy where code=42;

10 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 3896751453

--------------------------------------------------------------------------------
| Id  | Operation          | Name       | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |            |   10 |   230 |    6714 (2)| 00:00:01 |
| * 1 |  TABLE ACCESS FULL | BOWIE_BUSY |   10 |   230 |    6714 (2)| 00:00:01 |
--------------------------------------------------------------------------------

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

1 - storage("CODE"=42)
    filter("CODE"=42)

Statistics
----------------------------------------------------------
          0 recursive calls
          0 db block gets
      71423 consistent gets
      38657 physical reads
          0 redo size
        885 bytes sent via SQL*Net to client
         52 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
         10 rows processed

 

The CBO has again no choice but to still perform the highly inefficient Full Table Scan.

And the required Automatic Index won’t be able to be created until the existing transaction on the underlying table has completed.

HOWEVER, as we’ll see in Part II, the possible ramifications of this locking transaction goes way past the impact it has on just this SQL or specific automatic index…