Richard Foote's Oracle Blog

The End Of Richard Foote’s Oracle Blog (“Rock ‘n’ Roll Suicide”) July 20, 2023

Posted by Richard Foote in Richard Foote Art, Richard Foote Farewell.
28 comments

NOTE -THIS BLOG IS NO LONGER BEING MAINTAINED!!!

(Content may no longer be up to date or accurate with current versions of Oracle)

Of all my many Oracle blog posts, this particular post will remain with me the longest, for not only is it the last blog post I’ve done in a while, but it’s the last blog post I’ll ever do!!! (Yes, this statement is a complete ripoff of David Bowie’s famous 3rd July 1973 farewell speech, when he killed off his Ziggy Stardust character.)

I can distinctly remember a number of Oracle colleagues saying way back in 2007 when I first started blogging, why on earth am I focusing on Oracle Indexing, surely after a few months I’ll run out of ideas or new topics to discuss. I certainly had no idea or expectation that it would last so long or that it would garner over 2.75 million hits.

But after all these years and over 500 Oracle Database related articles, with most of them indeed discussing Oracle Indexing, the time has come to finally pull up stumps.

After retiring from Oracle training and consulting, and closing down my business earlier in the year, my initial plan had been to do a “Jonathan Lewis”, retire from work, but continue to keep my hand in and continue to blog and attend Oracle conferences for at least a while. But in the intervening months, I’ve found that all my other hobbies and interests have completely taken over all my time, and I now have neither the inclination nor motivation to do all the hard work that’s necessary to truly keep up with Oracle Database technology. This blog has never really been about just rehashing what’s already in the Oracle Documentation, but about trying to discover those not-so-obvious but useful details regarding how to effectively index an Oracle database and discussing in detail the reasons for all the many misperceptions regarding how Oracle indexes actually work, should be used, and should be maintained. But that requires time, lots of time – that I no longer have.

So it’s certainly with mixed feelings that I’ve decided to leave my life in the Oracle Database universe and explore other new and exciting things to do in life.

I would like to sincerely thank all of you, the readers of my blog, for all the wonderful feedback and insightful comments I’ve received over the years. You have ultimately been my source of inspiration and motivation for keeping this blog going for so many years. I would also like to take this opportunity to thank everyone in the Oracle Database community that I’ve had the pleasure of meeting at so many conferences in so many different places all over the world. I’ve made so many friends and had so many great times, that it has really been a fabulous ride. I want to give a special shoutout to all my fellow members within the Oracle OakTable and the Oracle ACE Program for letting me be a part of such a distinguished group of Oracle professionals. Although I’ve had the honor of again being eligible to be an Oracle ACE Director for 2023-2024, I’ve decided to turn down the offer and move on.

I have had many many many highlights in my Oracle career, working with many amazing colleagues and addressing the complex performance problems of many Oracle databases. Since my first public presentation at the ACT Oracle User Group conference in 1997, some highlights include the infamous “Good DBA Bad DBA” presentation at Oracle OpenWorld in Brisbane in 1999, being one of the first 4 nominated AUSOUG Oracle Masters, and being given a Lifetime Achievement Award by the UKOUG, even though I had only attended the two UKOUG conferences at the time (yes, I took it as a positive and not a sign to stay away), but my main highlight was really just meeting so many wonderful people around the world.

As this blog is free to administer, I have no plans to take it down, although I might disable the ability to post comments at some point. So I’ll leave this site up as a lasting legacy of my time in the Oracle universe, with most of the content still highly relevant and still attracting many curious visitors each week.

One of the activities that takes up much of my time is my art, and with commissions now coming in, it is the perfect way to spend sunny days whilst listening to the music of David Bowie. You can keep track of me and my art work at richardfooteart.com. Here’s just a sample of my art:

I sincerely wish you all the very best for the future and happy Oracle indexing 🙂

Bye-bye.

The Fake Index Trap (“Nowhere Now”) May 2, 2023

Posted by Richard Foote in CBO, Drop Index, Fake Indexes, Index Internals, NOSEGMENT Option, Online DDL, Oracle, Oracle 21c, Oracle Bugs, Oracle General, Oracle Indexes, Oracle Indexing Internals Webinar, Oracle19c, Tablespace Management, Virtual Indexes.
1 comment so far

In a recent correspondence, I was alerted to an issue in relation to the use of Virtual/Fake/Nosegment Indexes that I wasn’t aware of previously. Having a play, it appears this issue is still present in at least Oracle Database 21c, so I thought it worth a mention in case anyone else happens to fall into this trap.

I’ve discussed Virtual/Fake/Nosegment Indexes a number of times previously. These are indexes that do not exist as a physical segment (and so can be created almost immediately without consuming any storage), that can be used to determine if an index could potentially be used by the CBO if it were to be actually created.

Although such Fake Indexes don’t physically exist, they can cause issues if forgotten…

To illustrate this issue, I’ll start by creating a new tablespace:

SQL> create tablespace BOWIE_TS datafile 'C:\ORADATA\ZIGGY\ZIGGYPDB1\BOWIE_TS.DBF' size 100M;

Tablespace created.

Next, I’ll create and populate a table in this BOWIE_TS tablespace:

SQL> create table bowie_test (id number, name varchar2(42)) tablespace bowie_ts;

Table created.

SQL> insert into bowie_test select rownum, '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=>'BOWIE_TEST');

PL/SQL procedure successfully completed.

I’ll next create a Virtual/Fake index, using the NOSEGMENT option:

SQL> create index bowie_test_id_i on bowie_test(id) nosegment tablespace bowie_ts;

Index created.

We note this Fake Index is NOT listed in either USER_INDEXES or USER_SEGMENTS:

SQL> select index_name, tablespace_name from user_indexes where table_name='BOWIE_TEST';

no rows selected

SQL> select segment_name, segment_type, tablespace_name from user_segments
     where segment_name='BOWIE_TEST_ID_I';

no rows selected

If we run a basic, highly selective query on this table:

SQL> select * from bowie_test where id=42;

Execution Plan
----------------------------------------------------------
Plan hash value: 65548668

--------------------------------------------------------------------------------
| Id | Operation          | Name       | Rows | Bytes | Cost (%CPU) | Time     |
--------------------------------------------------------------------------------
|  0 | SELECT STATEMENT   |            |    1 |    16 |      11 (0) | 00:00:01 |
|* 1 |  TABLE ACCESS FULL | BOWIE_TEST |    1 |    16 |      11 (0) | 00:00:01 |
--------------------------------------------------------------------------------

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

     1 - filter("ID"=42)

Statistics
----------------------------------------------------------
          0 recursive calls
          0 db block gets
         38 consistent gets
          0 physical reads
          0 redo size
        648 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 notice the CBO uses a FTS. The Fake Index is NOT considered by default.

However, if we set the session as follows and re-run the query:

SQL> alter session set "_use_nosegment_indexes" = true;

Session altered.

SQL> select * from bowie_test where id=42;

Execution Plan
----------------------------------------------------------
Plan hash value: 1280686875

-------------------------------------------------------------------------------------------------------
| Id | Operation                            | Name            | Rows | Bytes | Cost (%CPU) | Time     |
-------------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                     |                 |    1 |    16 |       2 (0) | 00:00:01 |
|  1 |  TABLE ACCESS BY INDEX ROWID BATCHED | BOWIE_TEST      |    1 |    16 |       2 (0) | 00:00:01 |
|* 2 |   INDEX RANGE SCAN                   | BOWIE_TEST_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
         38 consistent gets
          0 physical reads
          0 redo size
        648 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 the CBO appears to now use the Fake Index, but as it doesn’t actually physically exist, actually uses a FTS behind the scenes (the number of consistent gets is evidence of this). But at least we now know the CBO would at least consider such an index if it physically existed.

We now decide to drop the tablespace and so first try to MOVE the table to another tablespace using the ONLINE option:

SQL> alter table bowie_test move online tablespace users;
alter table bowie_test move online tablespace users
*
ERROR at line 1:
ORA-14808: table does not support ONLINE MOVE TABLE because of the presence of nosegment index

The error message clearly states we can’t move the table ONLINE if such a Fake/Nosegment Index exists. This is our official warning of the potential danger to come…

We try to move the table using the default OFFLINE method:

SQL> alter table bowie_test move tablespace users;

Table altered.

We have now successfully moved the table to another tablespace.

If we check to see if we have any other segments within the tablespace yto be dropped:

SQL> select segment_name from dba_segments where tablespace_name='BOWIE_TS';

no rows selected

Oracle tells us that no, we do NOT have any current segments in this tablespace.

So it’s now safe to purge and drop this tablespace (or so we think):

SQL> purge tablespace bowie_ts;

Tablespace purged.

SQL> drop tablespace bowie_ts;

Tablespace dropped.

The tablespace has been successfully dropped.

However, if we now re-run the query on this table:

SQL> select * from bowie_test where id=42;
select * from bowie_test where id=42
*
ERROR at line 1:
ORA-00959: tablespace 'BOWIE_TS' does not exist

We get this unexpected error that the tablespace BOWIE_TS does not exist.

BUT, we already know the tablespace doesn’t exist, we’ve just dropped it !!!

So why are we getting this error?

It’s all due to the damn Fake Index we created previously.

Although there is no physical index segment for our Fake Index, there are still some internal Data Dictionary links between the Fake Index and the tablespace it was associated with. The tablespace is gone, but NOT the Fake Index.

The only place where fake indexes can be easily found within Oracle, is within the USER_OBJECTS view:

SQL> select o.object_name, o.object_type, o.status
from user_objects o left join user_indexes i
on o.object_name=i.index_name
where o.object_type='INDEX' and i.index_name is null;

OBJECT_NAME                    OBJECT_TYPE             STATUS
------------------------------ ----------------------- -------
BOWIE_TEST_ID_I                INDEX                   VALID

To eliminate this error, we have to first drop the Fake Index associated with the dropped tablespace:

SQL> drop index bowie_test_id_i;

Index dropped.

We can now safely run the query without error:

SQL> select * from bowie_test where id=42;

        ID NAME
---------- ------------------------------------------
42         DAVID BOWIE

So if you do ever create Fake Indexes, don’t forget to drop them once you’ve finished experimenting with them.

ESPECIALLY if you ever decide to drop the tablespace into which they were associated. This is explained in part in Oracle Support Doc ID 1361049.1.

Announcement: Oracle Database 23c Free Version Now Available to Developers April 4, 2023

Posted by Richard Foote in Oracle, Oracle 23c, Oracle 23c Free Developers Edition, Oracle Blog, Oracle General.
add a comment

In rather exciting news in the world of Oracle databases, Oracle today announced the availability of Oracle Database 23c Free Developer Release.

Oracle Database 23c Free Developer Release is available for download as a Docker Image, VirtualBox VM, or Linux RPM installation file, without requiring a user account or login. A Windows version is planned to follow shortly.

This is a nice change by Oracle, giving initial access to a new version of an Oracle Database specifically to the developer community. And importantly of course, it’s FREE !!

And 23c will be an important database release, with it being the next Long-Term Support Oracle database release. So a big well done to Gerald Venzl and the team at Oracle for making the early availability of 23c possible in this way.

For more information including a list of some of the cool new features available, you can visit the official Oracle Database 23c Free Developer Announcement Page.

I’ll of course be writing a series of Oracle 23c indexing new features related articles in due course. So stay tuned… 🙂

CBO Costing Plans With Migrated Rows Part II (“New Killer Star”) March 28, 2023

Posted by Richard Foote in CBO, Index Access Path, Index statistics, Leaf Blocks, Migrated Rows, Non-Equality Predicates, Oracle, Oracle Blog, Oracle Cost Based Optimizer, Oracle General, Oracle Indexes, Oracle Statistics, Performance Tuning, Richard's Blog, ROWID.
add a comment

I’ve spent the past few months discussing Migrated Rows, in large part thanks to an excellent 15 minute video by Connor McDonald on how ROWIDs can now be updated on the fly in Oracle Autonomous databases.

Well 14 such posts later, I have finally reached the end of this topic (for now at least). So, an average of about 1 post per minute of video 🙂

In my previous post, I discussed how the CBO costs execution plans with tables that have migrated rows, when the statistics are collected as recommended via the DBMS_STATS package. In summary, migrated rows are basically just ignored, with the CBO blissfully unaware of the existence of any such migrated rows.

As I discussed, if I want to easily see how many migrated rows I have in a table, I can potentially use the ANALYZE command as follows:

SQL> analyze table bowie compute statistics;

Table BOWIE analyzed.

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

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

As you can now see, the table currently has 56186 migrated rows (yes CHAIN_CNT can potentially count rows that simply can’t fit within a single block, but all these rows are definitely migrated rows as per the demo in my previous post).

Now, it had always been my belief that although you can use the ANALYZE command to count out these migrated rows, the CBO would simply ignore this statistic in its calculations.

But I was wrong.

If we now re-run the query from the previous post:

SQL> select * from bowie where id > 1 and id < 1001;

999 rows selected.

PLAN_TABLE_OUTPUT
_______________________________________________________________________________________________________________
SQL_ID b1vwpu2rgn8p5, child number 0
-------------------------------------
select * from bowie where id > 1 and id < 1001

Plan hash value: 1405654398

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

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

   2 - access("ID">1 AND "ID"<1001)

Statistics
-----------------------------------------------------------
            1 CPU used by this session
            1 CPU used when call started
            1 DB time
         9193 RM usage
            3 Requests to/from client
            2 SQL*Net roundtrips to/from client
          664 buffer is not pinned count
         1662 buffer is pinned count
          323 bytes received via SQL*Net from client
       171333 bytes sent via SQL*Net to client
            2 calls to get snapshot scn: kcmgss
            2 calls to kcmgcs
          666 consistent gets
            1 consistent gets examination
            1 consistent gets examination (fastpath)
          666 consistent gets from cache
          665 consistent gets pin
          665 consistent gets pin (fastpath)
            2 execute count
            1 index range scans
      5455872 logical read bytes from cache
          665 no work - consistent read gets
           39 non-idle wait count
            2 opened cursors cumulative
            1 opened cursors current
            2 parse count (total)
            2 process last non-idle time
            1 session cursor cache hits
          666 session logical reads
            1 sorts (memory)
         2024 sorts (rows)
          999 table fetch by rowid
          327 table fetch continued row
            3 user calls

SQL> SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(sql_id=>'b1vwpu2rgn8p5',format=>'ALLSTATS LAST +cost +bytes'));

PLAN_TABLE_OUTPUT
____________________________________________________________________________________________________________________________________
SQL_ID b1vwpu2rgn8p5, child number 0
-------------------------------------

select * from bowie where id > 1 and id < 1001

Plan hash value: 1405654398

---------------------------------------------------------------------------------------------------------------------------------
| Id | Operation                            | Name       | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows | A-Time     | Buffers |
---------------------------------------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                     |            |      1 |        |       |   302 (100)|    999 |00:00:00.01 |     666 |
|  1 |  TABLE ACCESS BY INDEX ROWID BATCHED | BOWIE      |      1 |    999 |   141K|   302   (0)|    999 |00:00:00.01 |     666 |
|* 2 |   INDEX RANGE SCAN                   | BOWIE_ID_I |      1 |    999 |       |     4   (0)|    999 |00:00:00.01 |       4 |
---------------------------------------------------------------------------------------------------------------------------------

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

    2 - access("ID">1 AND "ID"<1001)

We can see that the cost of the plan has now changed.

Although the cost of reading the index itself is still the same with a cost of 4, the overall cost of the plan has increased to 302 (previously it was 21).

So the difference in plan costs is 302 – 21 = 281. And it’s pretty easy to see where this extra comes from…

The extra costs is basically query selectivity x no of migrated rows

Extra costs = 0.005 x 56186 = 281.

So the index scan costing formula should really be updated to be:

Index Scan Cost = blevel +
ceil(effective index selectivity x leaf_blocks) +
ceil(effective table selectivity x clustering_factor) +
ceil(effective table selectivity x chain_cnt)

Now, IMHO, this new cost is actually more accurate and better matches the true cost of now using the index, which requires 666 Consistent Gets (previously, before the rows migrated, the index plan required just 18 Consistent Gets).

So in some respects, this new cost might not be a bad thing. But then again, a sudden change in such costings due to a flood of new migrated rows might result in an unexpected and undesired plan changes that have been carefully crafted for statistics generated with the conventional DBMS_STATS collection method.

However, it’s not sufficient to simply collect fresh statistics using DBMS_STATS to get the previous CBO costings where migrated rows are ignored:

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

PL/SQL procedure successfully completed.

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

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

Simply collecting fresh statistics does NOT clear out the CHAIN_CNT statistic and so the CBO costings remain the same as with ANALYZE command:

SQL> SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(sql_id=>'b1vwpu2rgn8p5',format=>'ALLSTATS LAST +cost +bytes'));

PLAN_TABLE_OUTPUT
____________________________________________________________________________________________________________________________________
SQL_ID b1vwpu2rgn8p5, child number 0
-------------------------------------

select * from bowie where id > 1 and id < 1001

Plan hash value: 1405654398

---------------------------------------------------------------------------------------------------------------------------------
| Id | Operation                            | Name       | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows | A-Time     | Buffers |
---------------------------------------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                     |            |      1 |        |       |   302 (100)|    999 |00:00:00.01 |     666 |
|  1 |  TABLE ACCESS BY INDEX ROWID BATCHED | BOWIE      |      1 |    999 |   141K|   302   (0)|    999 |00:00:00.01 |     666 |
|* 2 |   INDEX RANGE SCAN                   | BOWIE_ID_I |      1 |    999 |       |     4   (0)|    999 |00:00:00.01 |       4 |
---------------------------------------------------------------------------------------------------------------------------------

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

   2 - access("ID">1 AND "ID"<1001)

You need to first delete the table statistics to remove the CHAIN_CNT statistic (which of course comes with obvious dangers now there are no table statistics present) before you collect fresh statistics using DBMS_STATS:

SQL> execute dbms_stats.delete_table_stats(ownname=>null, tabname=>'BOWIE');

PL/SQL procedure successfully completed.

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

PL/SQL procedure successfully completed.

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

   TABLE_NAME    NUM_ROWS    BLOCKS    EMPTY_BLOCKS    AVG_SPACE    AVG_ROW_LEN    CHAIN_CNT
_____________ ___________ _________ _______________ ____________ ______________ ____________
BOWIE              200000      4906               0            0            167            0

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

   INDEX_NAME    BLEVEL    LEAF_BLOCKS    CLUSTERING_FACTOR
_____________ _________ ______________ ____________________
BOWIE_ID_I            1            473                 3250

The CHAIN_CNT statistic has finally been cleared to 0 and the CBO costings now returned to as it was previously when such migrated rows were ignored:

SQL> SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(sql_id=>'b1vwpu2rgn8p5',format=>'ALLSTATS LAST +cost +bytes'));

PLAN_TABLE_OUTPUT
____________________________________________________________________________________________________________________________________
SQL_ID b1vwpu2rgn8p5, child number 0
-------------------------------------

select * from bowie where id > 1 and id < 1001

Plan hash value: 1405654398

---------------------------------------------------------------------------------------------------------------------------------
| Id | Operation                            | Name       | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows | A-Time     | Buffers |
---------------------------------------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                     |            |      1 |        |       |    21 (100)|    999 |00:00:00.01 |     666 |
|  1 |  TABLE ACCESS BY INDEX ROWID BATCHED | BOWIE      |      1 |   1000 |   163K|    21   (0)|    999 |00:00:00.01 |     666 |
|* 2 |   INDEX RANGE SCAN                   | BOWIE_ID_I |      1 |   1000 |       |     4   (0)|    999 |00:00:00.01 |       4 |
---------------------------------------------------------------------------------------------------------------------------------

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

   2 - access("ID">1 AND "ID"<1001)

The CBO costs are now back to the previous 21.

So I’m a little in two minds about this. I think the statistics generated and used by the CBO are better with the ANALYZE command, but would still suggest collecting the necessary statistics using the recommended DBMS_STATS approach. Perhaps Oracle giving us the option to collect these additional statistics using DBMS_STATS might be a useful enhancement… 🤷‍♂️

Now, at one point in time, a long long time ago, I’m reasonably sure the CBO previously didn’t use the CHAIN_CNT statistic. However, it came as no real surprise when I researched when the CBO had started using the CHAIN_CNT statistic in its calculations, that Jonathan Lewis had already written on this subject way way back in April 2009 🙂

So Oracle definitely had this behaviour all the way back to at least 9i and continues to behave this way in 21c. Ah well, better late than never I guess to finally realise how all this actually works…

UPDATE (29 March 2023): Jonathan Lewis can kindly confirmed with me that CHAIN_CNT was definitely ignored back in version 8.1.7.4 and that this changed to the current behaviour in either 9.0 or 9.2.

CBO Costing Plans With Migrated Rows Part I (“Ignoreland”) March 21, 2023

Posted by Richard Foote in BLEVEL, CBO, Clustering Factor, Data Clustering, Index Access Path, Index Height, Index statistics, Leaf Blocks, Migrated Rows, Non-Equality Predicates, Oracle, Oracle Blog, Oracle Cost Based Optimizer, Oracle General, Oracle Indexes, Oracle Statistics, Performance Tuning, Richard's Blog, ROWID.
3 comments

Whilst recently blogging about Migrated Rows and specifically changes to how ROWIDs are now maintained on the fly in Oracle Autonomous Databases, I made a discovery regarding how the Cost-Based Optimizer (CBO) costs such plans. This is one of the key reasons why I blog, not only to try and share odd titbits about how Oracle works, but also to hopefully learn much myself in the process.

Imagine my surprise in not only learning that Oracle and the CBO works differently to how I had always thought Oracle worked in this respect, but that this behaviour has been the case since at least Oracle 9i.

In Part I, I’ll use the same example of migrated rows as I’ve used in the past few blog posts and initially show how the CBO generally costs such plans (and by which I had incorrectly assumed ALWAYS costed such plans).

Let’s start by creating and populating a tightly packed table (in an environment where ROWIDs are NOT updated on the fly):

SQL> create table bowie(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 BOWIE created.

SQL> insert into bowie 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.

I’ll next create an index on the well clustered ID column (as the rows are inserted in ID column order within the table):

SQL> create index bowie_id_i on bowie(id);

Index BOWIE_ID_I created.

Next, we’ll use the Oracle recommended method of collecting table/index statistics, by using the DBMS_STATS package:

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

PL/SQL procedure successfully completed.

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

   TABLE_NAME    NUM_ROWS    BLOCKS    EMPTY_BLOCKS    AVG_SPACE    AVG_ROW_LEN    CHAIN_CNT
_____________ ___________ _________ _______________ ____________ ______________ ____________
BOWIE              200000      3268               0            0            111            0

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

   INDEX_NAME    BLEVEL    LEAF_BLOCKS    CLUSTERING_FACTOR
_____________ _________ ______________ ____________________
BOWIE_ID_I            1            473                 3250

Note the key index statistics here: BLEVEL=1, LEAF_BLOCKS=473 and the near perfect CLUSTERING_FACTOR=3250.

If we run the following query featuring a non-equality range predicate:

SQL> select * from bowie where id > 1 and id < 1001;

999 rows selected.

PLAN_TABLE_OUTPUT
_______________________________________________________________________________________________________________
SQL_ID b1vwpu2rgn8p5, child number 0
-------------------------------------
select * from bowie where id > 1 and id < 1001

Plan hash value: 1405654398

------------------------------------------------------------------------------------------------------------
| Id | Operation                            | Name       | Starts | E-Rows | A-Rows | A-Time     | Buffers |
------------------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                     |            |      1 |        |    999 |00:00:00.01 |      18 |
|  1 |  TABLE ACCESS BY INDEX ROWID BATCHED | BOWIE      |      1 |   1000 |    999 |00:00:00.01 |      18 |
|* 2 |   INDEX RANGE SCAN                   | BOWIE_ID_I |      1 |   1000 |    999 |00:00:00.01 |       4 |
------------------------------------------------------------------------------------------------------------

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

   2 - access("ID">1 AND "ID"<1001)

Statistics
-----------------------------------------------------------
          1 CPU used by this session
          1 CPU used when call started
          1 DB time
       7678 RM usage
          3 Requests to/from client
          2 SQL*Net roundtrips to/from client
         16 buffer is not pinned count
       1983 buffer is pinned count
        323 bytes received via SQL*Net from client
     171383 bytes sent via SQL*Net to client
          2 calls to get snapshot scn: kcmgss
          2 calls to kcmgcs
         18 consistent gets
          1 consistent gets examination
          1 consistent gets examination (fastpath)
         18 consistent gets from cache
         17 consistent gets pin
         17 consistent gets pin (fastpath)
          2 execute count
          1 index range scans
     147456 logical read bytes from cache
         17 no work - consistent read gets
         40 non-idle wait count
          2 opened cursors cumulative
          1 opened cursors current
          2 parse count (total)
          2 process last non-idle time
          1 session cursor cache count
          1 session cursor cache hits
         18 session logical reads
          1 sorts (memory)
       2024 sorts (rows)
        999 table fetch by rowid
          3 user calls

We notice that the CBO indeed uses the index.

They key statistic to note here is that Consistent Gets is just 18, which is extremely low considering we’re returning 999 rows. This is due to the fact the index is currently extremely efficient as it can fetch multiple rows by visiting the same table block due to the excellent clustering/ordering of the required ID column values (and also due to my high arraysize session setting).

If we look at the CBO costings for this plan:

SQL> SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(sql_id=>'b1vwpu2rgn8p5',format=>'ALLSTATS LAST +cost +bytes'));

PLAN_TABLE_OUTPUT
____________________________________________________________________________________________________________________________________
SQL_ID b1vwpu2rgn8p5, child number 0

-------------------------------------

select * from bowie where id > 1 and id < 1001

Plan hash value: 1405654398

---------------------------------------------------------------------------------------------------------------------------------
| Id | Operation                            | Name       | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows | A-Time     |Buffers |
---------------------------------------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                     |            |      1 |        |       |    21 (100)|    999 |00:00:00.01 |     18 |
|  1 |  TABLE ACCESS BY INDEX ROWID BATCHED | BOWIE      |      1 |   1000 |   108K|      21 (0)|    999 |00:00:00.01 |     18 |
|* 2 |   INDEX RANGE SCAN                   | BOWIE_ID_I |      1 |   1000 |       |       4 (0)|    999 |00:00:00.01 |      4 |
---------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("ID">1 AND "ID"<1001)

I’ve previously discussed many times how the CBO costs index access paths, but it’s always useful to go over this again, as it’s the most common question I get asked when I visit customer sites.

The KEY statistic the CBO has to determine is the estimated Selectivity of the query (the estimated percentage of rows to be returned), as this is the driver of all the subsequent CBO calculations.

The Selectivity of this range-based predicate query is calculated as follows:

Selectivity = (Highest Bound Value – Lowest Bound Value) / (Highest Value – Lowest Value)
= (1001-1) /(200000-1)
= 1000/199999
= approx. 0.005

Once Oracle has the selectivity, it can calculate the query Cardinality (estimated number of rows) as follows:

Cardinality = Selectivity x No of Rows

Cardinality = 0.005 x 200000 = 1000 rows

This is our visual window into the likelihood that the CBO has made an accurate decision with its execution plan. If the cardinality estimates are reasonably accurate, then the CBO is likely to generate a good plan. If the cardinality estimates are way off, then the CBO is more likely to generate an inappropriate plan.

The CBO cardinality estimate in the above plan is 1000 rows, whereas the number of rows actually returned is 999 rows.

So indeed, the CBO has got the cardinality almost spot on (except for a trivial rounding error) and so we have a high degree of confidence that the CBO is using the correct selectivity estimates when they get plugged into the following CBO formula for costing an index range scan (using this selectivity of 0.005 and the index statistics listed above):

Index Scan Cost = (blevel + ceil(effective index selectivity x leaf_blocks)) + ceil(effective table selectivity x clustering_factor)

= (1 + ceil(0.005 x 467)) + ceil(0.005 x 3250)
= (1 + 3) + 17
= 4 + 17 = 21

So we can clearly see where the CBO gets its costings for both reading the index during the Index Range Scan (4) and for the plan as a whole (21).

The CBO cost of 21 very closely resembles the 18 consistent gets accessed when the plan is executed. This to me suggests that the CBO has indeed costed this plan very accurately and appropriately.

It’s interesting to note in the above execution plan that Oracle is attributing 100% of this cost of 21 to CPU (21 (100)). That will be a discussion for another day…

OK, let’s now perform an update on the table, increasing the size of the rows such that I generate a bunch of migrated rows:

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

200,000 rows updated.

SQL> commit;

Commit complete.

If we now collect fresh statistics again using DBMS_STATS:

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

PL/SQL procedure successfully completed.

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

   TABLE_NAME    NUM_ROWS    BLOCKS    EMPTY_BLOCKS    AVG_SPACE    AVG_ROW_LEN    CHAIN_CNT
_____________ ___________ _________ _______________ ____________ ______________ ____________
BOWIE              200000      4906               0            0            167            0

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

   INDEX_NAME    BLEVEL    LEAF_BLOCKS    CLUSTERING_FACTOR
_____________ _________ ______________ ____________________
BOWIE_ID_I            1            473                 3250

We notice that none of the key statistics have changed, except for the number of Table Blocks (now 4906, previously it was 3268) and the Average Row Length has also increased (now 167, previously it was 111). Both of these can of course be attributed to the increase in the size of the values now stored in the NAME column following the Update.

Importantly, notice that collecting statistics via DBMS_STATS does NOT collect data for the CHAIN_CNT statistic, it remains at 0 even though many migrated rows were actually generated by the Update statement (as we’ll see below).

Increasing the Table Blocks will result in an associated increase in the cost of reading this table via a Full Table Scan (FTS).

We notice that none of the index-related statistics changed following the Update statement (as in this example, Oracle does NOT update the ROWIDs of any of the migrated rows, Oracle simply stores a pointer in the original block to denote the new physical location of the migrated rows as previously discussed).

So if we only INCREASE the cost of a FTS (via having more Table Blocks) but keep intact all the previous index related statistics, then the CBO is certainly going to again select the same Index Range Scan plan, as the plan will have the same (cheaper than FTS) costings as before.

If we re-run the query again:

SQL> select * from bowie where id > 1 and id < 1001;

999 rows selected.

PLAN_TABLE_OUTPUT
_______________________________________________________________________________________________________________
SQL_ID b1vwpu2rgn8p5, child number 0
-------------------------------------
select * from bowie where id > 1 and id < 1001

Plan hash value: 1405654398

------------------------------------------------------------------------------------------------------------
| Id | Operation                            | Name       | Starts | E-Rows | A-Rows | A-Time     | Buffers |
------------------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                     |            |      1 |        |    999 |00:00:00.01 |     666 |
|  1 |  TABLE ACCESS BY INDEX ROWID BATCHED | BOWIE      |      1 |   1000 |    999 |00:00:00.01 |     666 |
|* 2 |   INDEX RANGE SCAN                   | BOWIE_ID_I |      1 |   1000 |    999 |00:00:00.01 |       4 |
------------------------------------------------------------------------------------------------------------

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

   2 - access("ID">1 AND "ID"<1001)

Statistics
-----------------------------------------------------------
          1 CPU used by this session
          1 CPU used when call started
       7709 RM usage
          3 Requests to/from client
          2 SQL*Net roundtrips to/from client
        664 buffer is not pinned count
       1662 buffer is pinned count
        323 bytes received via SQL*Net from client
     171500 bytes sent via SQL*Net to client
          2 calls to get snapshot scn: kcmgss
          2 calls to kcmgcs
        666 consistent gets
          1 consistent gets examination
          1 consistent gets examination (fastpath)
        666 consistent gets from cache
        665 consistent gets pin
        665 consistent gets pin (fastpath)
          2 execute count
          1 index range scans
    5455872 logical read bytes from cache
        665 no work - consistent read gets
         39 non-idle wait count
          1 non-idle wait time
          2 opened cursors cumulative
          1 opened cursors current
          2 parse count (total)
          1 process last non-idle time
          2 session cursor cache count
        666 session logical reads
          1 sorts (memory)
       2024 sorts (rows)
        999 table fetch by rowid
        327 table fetch continued row
          3 user calls

We notice that indeed it’s the same Index Range Scan plan as before.

But we notice that the number of Consistent Gets has increased substantially to 666 (previously it was just 18). The reason for this large jump is due to the now 327 table fetch continued rows that need to be accessed due to the newly migrated rows following the Update. This number is then doubled (so 2 x 327 = 654) to represent the approximate additional Consistent Gets we now need to perform, as Oracle needs to read the additional table block to access the migrated row’s new physical location AND to now re-read the original table block to access the next row to be fetched (previously Oracle could read all the required consecutive rows required from the same table block within the one consistent get).

So it’s now actually substantially more expensive to read the required 1000 rows via this index due to this increase in necessary consistent gets.

But if we look at the actual cost of this plan now:

SQL> SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(sql_id=>'b1vwpu2rgn8p5',format=>'ALLSTATS LAST +cost +bytes'));

PLAN_TABLE_OUTPUT
____________________________________________________________________________________________________________________________________
SQL_ID b1vwpu2rgn8p5, child number 0

-------------------------------------

select * from bowie where id > 1 and id < 1001

Plan hash value: 1405654398

---------------------------------------------------------------------------------------------------------------------------------
| Id | Operation                            | Name       | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows | A-Time     |Buffers |
---------------------------------------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                     |            |      1 |        |       |    21 (100)|    999 |00:00:00.01 |    666 |
|  1 |  TABLE ACCESS BY INDEX ROWID BATCHED | BOWIE      |      1 |   1000 |   163K|      21 (0)|    999 |00:00:00.01 |    666 |
|* 2 |   INDEX RANGE SCAN                   | BOWIE_ID_I |      1 |   1000 |       |       4 (0)|    999 |00:00:00.01 |      4 |
---------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("ID">1 AND "ID"<1001)

We notice that as expected (as none of the index-related statistics have changed), that despite being much more expensive to now use this index, the costs of this plan (4 for reading the index and 21 overall) remain unchanged.

I would argue that these CBO costs are no longer as accurate as the 21 total CBO cost does not so closely represent the actual 666 consistent gets now required.

Now, the 327 table fetch continued row statistics from the previous run is clear proof we indeed have migrated rows following the Update statement.

But if we want to confirm how many migrated rows we now have in the table, we can use the ANALYZE command to collect these additional statistics:

SQL> analyze table bowie compute statistics;

Table BOWIE analyzed.

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

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

We notice that we now have a CHAIN_CNT of 56186.

Now this statistic can represent any row that is not housed inside a single table block (for which there could be a number of possible reasons, such as a row simply being too long to fit in a single table block), but as all rows are still relatively tiny, we can be certain that indeed all 56186 chained rows represent migrated rows.

Now that I’ve gone and used ANALYZE, primarily to generate this CHAIN_CNT statistic, my previous understanding of how the CBO costs migrated rows crumbles away, as I’ll discuss in my next post…

Migrated Rows In Oracle Data Warehouse Autonomous Databases (“Sit Down. Stand Up.”) March 14, 2023

Posted by Richard Foote in 19c, 19c New Features, Autonomous Data Warehouse, Autonomous Database, Changing ROWID, Migrated Rows, Oracle, Oracle Cloud, Oracle Indexes, Oracle19c, ROWID.
add a comment

In all my recent discussions on how Oracle can now update ROWIDs on the fly when a row migrates, I’ve mentioned how this only occurs on tables in which the ENABLE ROW MOVEMENT clause has been set.

So you have the option on whether you wish this new behaviour to occur by simply not setting ENABLE ROW MOVEMENT on tables where you want the previous behavior of the ROWIDs not changing when a row migrates (and for Oracle to simply have a pointer in the original table block to denote the new location of the row). You may not what ROWIDs to suddenly change on you for example if you have an application that explicitly stores ROWIDs and relies on them not changing for the application to correctly fetch data.

However, all my previous tests and examples have been run on Oracle Transaction Processing Autonomous Database environments, but as Phil Goldenberg mentioned in this comment, things unfortunately behave somewhat differently in Oracle Data Warehouse Autonomous Database environments.

To illustrate, a simple little demo as usual, but this time using an Oracle Data Warehouse Autonomous Database environment…

Let’s start by creating and populating a tightly packed table, but without setting the ENABLE ROW MOVEMENT clause:

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.

Let’s now create an index based on the ID column:

SQL> create index bowie_id_i on bowie(id);

Index BOWIE_ID_I created.

SQL> select index_name, num_rows, blevel, leaf_blocks from user_indexes where table_name='BOWIE';

   INDEX_NAME    NUM_ROWS    BLEVEL    LEAF_BLOCKS
_____________ ___________ _________ ______________
BOWIE_ID_I          10000         1             23

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 AAASTdAAAAAAJq0AAp
    424 AAASTdAAAAAAJq0AIH
   4242 AAASTdAAAAAAJq1ADP

If we store these ROWIDs, we can use them to directly access a row of interest very efficiently:

SQL> select id from bowie where rowid='AAASTdAAAAAAJq0AAp';

   ID
_____
   42

Next, I’ll update the table, increasing the size of the rows such that I generate a bunch of migrated 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.

As discussed previously, if this were an Oracle Transaction Processing Autonomous Database environment, because I haven’t set ENABLE ROW MOVEMENT on this table, the ROWIDs of any migrated rows would NOT have changed.

But here in this Oracle Data Warehouse Database environment:

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

     ID                 ROWID
_______ _____________________
     42 AAASTdAAAAAAJq3AAp
    424 AAASTdAAAAAAJq3AIH
   4242 AAASTdAAAAAAJrcAFd

We can see that all these rows now have a new ROWID.

If I now re-run my previous query that relied on the ROWIDs not changing:

SQL> select id from bowie where rowid='AAASTdAAAAAAJq0AAp';

no rows selected

We can see that the query no longer returns the required row.

And yet, if we ANALYZE the table:

SQL> analyze table bowie compute statistics;

Table BOWIE analyzed.

SQL> select table_name, num_rows, blocks, chain_cnt, row_movement from user_tables where table_name='BOWIE';

   TABLE_NAME    NUM_ROWS    BLOCKS    CHAIN_CNT    ROW_MOVEMENT
_____________ ___________ _________ ____________ _______________
BOWIE               10000        28            0 DISABLED

We can see that we have no migrated/chained rows listed, even though ENABLE ROW MOVEMENT is disabled.

I think this a little unfortunate, because I quite like the concept of ROWIDs being updated when a row migrates, but I also like the option of being able to revert to the previous behaviour if necessary.

I have no idea how this works in the other Oracle Autonomous Database environments (other than Transaction Processing), but regardless, this behaviour can potentially change on any of the environments at any time.

So, what’s the moral of the story? Well there’s a couple.

Firstly, in Oracle Autonomous Database environments, you’re meant to have a “hands-off” attitude and just let Oracle handle all this day to day stuff. So you can’t necessarily rely on the behaviour of the database to be as consistent as in environments where you control all the levers. Indexes might come and go, tables might suddenly get partitioned, ROWIDs might change when a row migrates, etc. etc. etc. etc.

And secondly, it’s becoming an even worse idea for applications to explicitly store and rely on ROWIDs not changing for such applications function properly. Especially, if you use Oracle Autonomous Database environments…

Annoying Bug When Trying To Create Always Free TP Autonomous Database (“Supernaturally”) March 10, 2023

Posted by Richard Foote in Autonomous Database, Autonomous Transaction Processing, Create Autonomous Database Bug, Oracle, Oracle Blog, Oracle Bugs.
add a comment

I was having all sorts of problems with one of my Always Free Oracle Transaction Processing Autonomous Databases, mostly around space and configuration issues within its parent Container Database, that I just gave up, blew it away and just tried to create a fresh one. That’s the beauty of this great free capability, it’s so easy to just throw away and create new databases in only a few moments.

Well, usually it’s easy…

I now encounter an issue trying to create a new Always Free autonomous database via a really annoying bug. I thought I’ll share the issue (and the fix) in case anyone else encounters this issue (which was in the Australia East Sydney data centre).

So I go into OCI, then into the Autonomous Database screen and select Create Autonomous Database which gets you into the following screen (if you don’t know how to get into this screen, this post is likely to be of no interest to you):

So you fill in the new Database details and then scroll down:

Now if you select you want to create a Transaction Processing database and scroll down:

We now hit the damn bug. For some reason, when you select the Transaction Processing option, the OCPU auto scaling option gets selected and there’s no way to turn the thing off (you can’t just click on the greyed tick icon).

Which is unfortunate because this option is NOT allowed if you want to create an ALWAYS FREE Autonomous Database.

If you scroll down and continue to create your always free autonomous database by clicking on the Create Autonomous Database button:

You get an error saying you can’t because you’re using an option that’s not permitted with always free databases. The OCPU auto scaling option that you can’t now turn off!!!

However, there is a way to get out of this pickle and create the always free autonomous database. Firstly you need to scroll up again:

And unselect the Show only Always Free configuration options button.

This now makes the OCPU auto scaling tick button available to be unselected.

So now you can turn off the damn OCPU auto scaling option that you never wanted in the first place.

Now don’t forget to go back and select the Show only Always Free configuration options button again to ensure you don’t in fact use any options or sizings that prevents the creation of an Always Free autonomous database.

Alternatively, you can just toggle the Show only Always Free configuration options button, as turning it back on again will also remove the OCPU auto scaling tick. But if you do this, make sure you’ve selected which type of autonomous database you want, as changing the selection will automatically re-enable OCPU auto scaling and you’ll have to repeat the fixing process.

You can now finally click the Create Autonomous Database at the bottom of the screen and successfully create your free always free autonomous database.

So it’s a pain, but at least I found a relatively simple workaround.

As I mentioned previously, I encounter this issue when connected to the Australia East Sydney data centre. I have no idea if this occurs in other Oracle data centres as well?

I don’t currently have a Support Identifier, so if you do or you work for Oracle Corporation, feel free to raise a Service Request for this issue… 🙂

UPDATE 15 March 2023: This issue has now been resolved by Oracle. A huge thank you to Yasin Baskan for so very promptly addressing this issue.

Possible Impact To Clustering Factor Now ROWIDs Are Updated When Rows Migrate Part III (“Dancing With The Big Boys”) March 9, 2023

Posted by Richard Foote in 19c, 19c New Features, Attribute Clustering, Autonomous Data Warehouse, Autonomous Database, Autonomous Transaction Processing, CBO, Changing ROWID, Clustering Factor, Data Clustering, Full Table Scans, Index Access Path, Index Internals, Index Rebuild, Index statistics, Leaf Blocks, Migrated Rows, Oracle, Oracle 21c, Oracle Blog, Oracle Cloud, Oracle Cost Based Optimizer, Oracle General, Oracle Indexes, Oracle19c, ROWID.
add a comment

In my previous post, I discussed how you can best reorg a table that has a significant number of migrated rows impact the Clustering Factor of important indexes, when such tables have the ENABLED ROW MOVEMENT disabled.

In this post I’ll discuss resolving similar issues, but when ROWIDs are updated on the fly when rows are migrated in Oracle Autonomous Databases.

As I discussed previously, by updating indexes with the new ROWIDs when rows migrate, such indexes can potentially increase in size as they store both old/new index entries concurrently AND due to the increased likelihood of associated index block splits. Additionally, such indexes can also have their Clustering Factor directly impacted when migrated rows disrupt the otherwise tight clustering of specific columns.

As such, we may want to address these issues to improve the performance of impacted queries. But it’s important we address these issues appropriately…

To illustrate all this, I’m going to re-run the same demo as my previous post, but on a table with ENABLE ROW MOVEMENT enabled.

I’ll start by creating and populating a tightly packed table with ENABLE ROW MOVEMENT enabled and with data inserted in ID column order:

SQL> create table bowie2(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 BOWIE2 created.

SQL> insert into bowie2 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.

I’ll now create an index on this well ordered/clustered ID column:

SQL> create index bowie2_id_i on bowie2(id);

Index BOWIE2_ID_I created.

Next, I’ll update the table, increasing the size of the rows such that I generate a bunch of migrated rows:

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

200,000 rows updated.

SQL> commit;

Commit complete.

If we check the number of migrated rows:

SQL> analyze table bowie2 compute statistics;

Table BOWIE2 analyzed.

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

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

We notice there are indeed 0 migrated rows. This is because in Oracle Autonomous Databases, the associated ROWIDs of migrated rows as updated on the fly in this scenario.

If we check the current Clustering Factor of the index:

SQL> execute dbms_stats.delete_table_stats(ownname=>null, tabname=>'BOWIE2');

PL/SQL procedure successfully completed.

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

PL/SQL procedure successfully completed.

SQL> select table_name, num_rows, blocks from user_tables where table_name='BOWIE2';

   TABLE_NAME    NUM_ROWS    BLOCKS
_____________ ___________ _________
BOWIE2             200000      4654

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

    INDEX_NAME    BLEVEL    LEAF_BLOCKS    CLUSTERING_FACTOR
______________ _________ ______________ ____________________
BOWIE2_ID_I            2            945               109061

We can see that although the data was initially inserted in ID column order, we now have a relatively poor Clustering Factor at 109061 as the migrated rows have disrupted this previously perfect clustering.

We also notice that the BLEVEL has increased from 1 to now be 2 and the number of Leaf Blocks has increased to 945 from 473 after the rows migrated (as I discussed previously).

If we now run a query that returns 4200 rows from a 200,000 row table:

SQL> select * from bowie2 where id between 1 and 4200;

4,200 rows selected.

PLAN_TABLE_OUTPUT
_____________________________________________________________________________________________________
SQL_ID 25qktyn35b662, child number 0
-------------------------------------
select * from bowie2 where id between 1 and 4200

Plan hash value: 1495904576

----------------------------------------------------------------------------------------------
| Id | Operation                  | Name   | Starts | E-Rows | A-Rows | A-Time     | Buffers |
----------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT           |        |      1 |        |   4200 |00:00:00.02 |    4572 |
|* 1 |  TABLE ACCESS STORAGE FULL | BOWIE2 |      1 |   4200 |   4200 |00:00:00.02 |    4572 |
----------------------------------------------------------------------------------------------

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

   1 - storage(("ID"<=4200 AND "ID">=1))
       filter(("ID"<=4200 AND "ID">=1))

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
          4 DB time
      37101 RM usage
          3 Requests to/from client
          2 SQL*Net roundtrips to/from client
          2 buffer is not pinned count
        325 bytes received via SQL*Net from client
     461965 bytes sent via SQL*Net to client
          2 calls to get snapshot scn: kcmgss
         14 calls to kcmgcs
       4572 consistent gets
       4572 consistent gets from cache
       4572 consistent gets pin
       4572 consistent gets pin (fastpath)
          2 execute count
   37453824 logical read bytes from cache
       4560 no work - consistent read gets
         72 non-idle wait count
          2 opened cursors cumulative
          1 opened cursors current
          2 parse count (total)
          1 process last non-idle time
          1 session cursor cache count
          1 session cursor cache hits
       4572 session logical reads
          1 sorts (memory)
       2024 sorts (rows)
       4560 table scan blocks gotten
     252948 table scan disk non-IMC rows gotten
     252948 table scan rows gotten
          1 table scans (short tables)
          3 user calls

SQL> SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(sql_id=>'25qktyn35b662',format=>'ALLSTATS LAST +cost +bytes'));

PLAN_TABLE_OUTPUT
______________________________________________________________________________________________________________________
SQL_ID 25qktyn35b662, child number 0
-------------------------------------
select * from bowie2 where id between 1 and 4200

Plan hash value: 1495904576

-------------------------------------------------------------------------------------------------------------------
| Id | Operation                  | Name   | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows | A-Time     | Buffers |
-------------------------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT           |        |      1 |        |       |  1264 (100)|   4200 |00:00:00.02 |    4572 |
|* 1 |  TABLE ACCESS STORAGE FULL | BOWIE2 |      1 |   4200 |   684K|    1264 (1)|   4200 |00:00:00.02 |    4572 |
-------------------------------------------------------------------------------------------------------------------

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

   1 - storage(("ID"<=4200 AND "ID">=1))
       filter(("ID"<=4200 AND "ID">=1))

We can see that Oracle has decided to perform a Full Table Scan (FTS) and not use the index.

The Clustering Factor of the ID column is now so bad, that returning 4200 rows via such an index is just too expensive. The FTS is now deemed the cheaper option by the CBO.

We notice that the CBO cost of the FTS is 1264.

If we run a query that forces the use of the index:

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

4,200 rows selected.

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

Plan hash value: 3243780227

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

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

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


Statistics
-----------------------------------------------------------
          2 CPU used by this session
          2 CPU used when call started
          2 DB time
      14531 RM usage
          3 Requests to/from client
          2 SQL*Net roundtrips to/from client
       2646 buffer is not pinned count
       5755 buffer is pinned count
        348 bytes received via SQL*Net from client
     462143 bytes sent via SQL*Net to client
          2 calls to get snapshot scn: kcmgss
          2 calls to kcmgcs
       2665 consistent gets
          2 consistent gets examination
          2 consistent gets examination (fastpath)
       2665 consistent gets from cache
       2663 consistent gets pin
       2663 consistent gets pin (fastpath)
          2 execute count
          1 index range scans
   21831680 logical read bytes from cache
       2663 no work - consistent read gets
         73 non-idle wait count
          2 opened cursors cumulative
          1 opened cursors current
          2 parse count (total)
          3 process last non-idle time
          2 session cursor cache count
       2665 session logical reads
          1 sorts (memory)
       2024 sorts (rows)
       4200 table fetch by rowid
          3 user calls

SQL> SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(sql_id=>'bzm2vhchqpq7w',format=>'ALLSTATS LAST +cost +bytes'));

PLAN_TABLE_OUTPUT
_____________________________________________________________________________________________________________________________________
SQL_ID bzm2vhchqpq7w, child number 0

-------------------------------------

select /*+ index (bowie2) */ * from bowie2 where id between 1 and 4200

Plan hash value: 3243780227

----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation                            | Name        | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows | A-Time     | Buffers |
----------------------------------------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                     |             |      1 |        |       |  2314 (100)|   4200 |00:00:00.01 |    2665 |
|  1 |  TABLE ACCESS BY INDEX ROWID BATCHED | BOWIE2      |      1 |   4200 |   684K|    2314 (1)|   4200 |00:00:00.01 |    2665 |
|* 2 |   INDEX RANGE SCAN                   | BOWIE2_ID_I |      1 |   4200 |       |      22 (0)|   4200 |00:00:00.01 |      21 |
----------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

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

The cost of the Index Range Scan plan has an overall cost of 2314, greater than the 1264 cost of the FTS plan.

Notice that the cost of using just the index within the plan is currently 22.

So the vast majority of the cost of this plan (2314 – 22 = 2292) is in Oracle having to access so many different table blocks due to the poor index Clustering Factor and NOT in the increased size of the index.

As I’ve discussed numerous times, you can potentially make an index smaller by rebuilding the index (if there’s free space within the index), but the impact on the Clustering Factor will be nothing but “disappointing”…

If we just rebuild the index:

SQL> alter index bowie2_id_i rebuild online;

Index BOWIE2_ID_I altered.

And now look at the new index related statistics:

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

    INDEX_NAME    BLEVEL    LEAF_BLOCKS    CLUSTERING_FACTOR
______________ _________ ______________ ____________________
BOWIE2_ID_I            1            473               109061

We notice that the index has indeed decreased in size, back to what is was before the row migrated following the Update (Blevel=1 and Leaf Blocks=473).

But the Clustering Factor remains unchanged at 109061.

If we now re-run the query:

SQL> select * from bowie2 where id between 1 and 4200;

4,200 rows selected.

PLAN_TABLE_OUTPUT
_____________________________________________________________________________________________________
SQL_ID 25qktyn35b662, child number 0
-------------------------------------
select * from bowie2 where id between 1 and 4200

Plan hash value: 1495904576

----------------------------------------------------------------------------------------------
| Id | Operation                  | Name   | Starts | E-Rows | A-Rows | A-Time     | Buffers |
----------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT           |        |      1 |        |   4200 |00:00:00.02 |    4572 |
|* 1 |  TABLE ACCESS STORAGE FULL | BOWIE2 |      1 |   4200 |   4200 |00:00:00.02 |    4572 |
----------------------------------------------------------------------------------------------

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

   1 - storage(("ID"<=4200 AND "ID">=1))
       filter(("ID"<=4200 AND "ID">=1))

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

Statistics
-----------------------------------------------------------
          3 CPU used by this session
          3 CPU used when call started
          3 DB time
      31738 RM usage
          3 Requests to/from client
          2 SQL*Net roundtrips to/from client
          2 buffer is not pinned count
        325 bytes received via SQL*Net from client
     461972 bytes sent via SQL*Net to client
          2 calls to get snapshot scn: kcmgss
         14 calls to kcmgcs
       4572 consistent gets
       4572 consistent gets from cache
       4572 consistent gets pin
       4572 consistent gets pin (fastpath)
          2 execute count
   37453824 logical read bytes from cache
       4560 no work - consistent read gets
         73 non-idle wait count
          2 opened cursors cumulative
          1 opened cursors current
          2 parse count (total)
          3 process last non-idle time
          2 session cursor cache count
       4572 session logical reads
          1 sorts (memory)
       2024 sorts (rows)
       4560 table scan blocks gotten
     252948 table scan disk non-IMC rows gotten
     252948 table scan rows gotten
          1 table scans (short tables)
          3 user calls

SQL> SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(sql_id=>'25qktyn35b662',format=>'ALLSTATS LAST +cost +bytes'));

PLAN_TABLE_OUTPUT
______________________________________________________________________________________________________________________
SQL_ID 25qktyn35b662, child number 0
-------------------------------------
select * from bowie2 where id between 1 and 4200

Plan hash value: 1495904576

-------------------------------------------------------------------------------------------------------------------
| Id | Operation                  | Name   | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows | A-Time     | Buffers |
-------------------------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT           |        |      1 |        |       |  1264 (100)|   4200 |00:00:00.02 |    4572 |
|* 1 |  TABLE ACCESS STORAGE FULL | BOWIE2 |      1 |   4200 |   684K|    1264 (1)|   4200 |00:00:00.02 |    4572 |
-------------------------------------------------------------------------------------------------------------------

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

   1 - storage(("ID"<=4200 AND "ID">=1))
       filter(("ID"<=4200 AND "ID">=1))

The CBO decides to still use a FTS instead of the index.

If we look at the cost now of using the index for this query:

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

4,200 rows selected.

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

Plan hash value: 3243780227

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

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

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

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

Statistics
-----------------------------------------------------------
          2 CPU used by this session
          2 CPU used when call started
          1 DB time
      13484 RM usage
          3 Requests to/from client
          2 SQL*Net roundtrips to/from client
       2646 buffer is not pinned count
       5755 buffer is pinned count
        347 bytes received via SQL*Net from client
     461972 bytes sent via SQL*Net to client
          2 calls to get snapshot scn: kcmgss
          2 calls to kcmgcs
       2655 consistent gets
          1 consistent gets examination
          1 consistent gets examination (fastpath)
       2655 consistent gets from cache
       2654 consistent gets pin
       2654 consistent gets pin (fastpath)
          2 execute count
          1 index range scans
   21749760 logical read bytes from cache
       2654 no work - consistent read gets
         73 non-idle wait count
          2 opened cursors cumulative
          1 opened cursors current
          2 parse count (total)
          1 process last non-idle time
          1 session cursor cache count
          1 session cursor cache hits
       2655 session logical reads
          1 sorts (memory)
       2024 sorts (rows)
       4200 table fetch by rowid
          3 user calls

SQL> SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(sql_id=>'bzm2vhchqpq7w',format=>'ALLSTATS LAST +cost +bytes'));

PLAN_TABLE_OUTPUT
_____________________________________________________________________________________________________________________________________
SQL_ID bzm2vhchqpq7w, child number 0

-------------------------------------

select /*+ index (bowie2) */ * from bowie2 where id between 1 and 4200

Plan hash value: 3243780227

----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation                            | Name        | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows | A-Time     | Buffers |
----------------------------------------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                     |             |      1 |        |       |  2303 (100)|   4200 |00:00:00.01 |    2655 |
|  1 |  TABLE ACCESS BY INDEX ROWID BATCHED | BOWIE2      |      1 |   4200 |   684K|    2303 (1)|   4200 |00:00:00.01 |    2655 |
|* 2 |   INDEX RANGE SCAN                   | BOWIE2_ID_I |      1 |   4200 |       |      11 (0)|   4200 |00:00:00.01 |      11 |
----------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

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

We notice the cost of the index has only moderately gone down to 2303 (previously it was 2314).

This reduction of 11 in the CBO cost is due entirely to the fact the index is now approximately 1/2 the size as it was before the index rebuild and has thus reduced the cost of reading the index blocks to 11 within the execution plan (previously it was 22).

But the vast majority of the cost within the Index Range Scan plan comes again with accessing the table blocks, which remains unchanged due to the unchanged Clustering Factor.

To reduce the Clustering Factor, we need to change the clustering of the data with the TABLE.

So, to improve the performance of this potentially important query, we need to re-cluster the data just as we did in the example in my previous post when we had migrated rows listed and ROWIDs were not updated on the fly.

We can now add an appropriate Clustering Attribute before we perform the table reorg:

SQL> alter table bowie2 add clustering by linear order (id);

Table BOWIE2 altered.

SQL> alter table bowie2 move online;

Table BOWIE2 altered.

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

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

PL/SQL procedure successfully completed.

SQL> select table_name, num_rows, blocks from user_tables where table_name='BOWIE2';

   TABLE_NAME    NUM_ROWS    BLOCKS
_____________ ___________ _________
BOWIE2             200000      4936

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

    INDEX_NAME    BLEVEL    LEAF_BLOCKS    CLUSTERING_FACTOR
______________ _________ ______________ ____________________
BOWIE2_ID_I            1            473                 4850

The Clustering Factor has been reduced down to the almost perfect 4850, down from the previous 109061.

If we now re-run the query:

SQL> select * from bowie2 where id between 1 and 4200;

4,200 rows selected.

PLAN_TABLE_OUTPUT
________________________________________________________________________________________________________________
SQL_ID 25qktyn35b662, child number 0
-------------------------------------
select * from bowie2 where id between 1 and 4200

Plan hash value: 3243780227

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

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

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

Statistics
-----------------------------------------------------------
          1 CPU used by this session
          1 CPU used when call started
         90 Cached Commit SCN referenced
      11345 RM usage
          3 Requests to/from client
          2 SQL*Net roundtrips to/from client
         93 buffer is not pinned count
       8308 buffer is pinned count
        325 bytes received via SQL*Net from client
     462117 bytes sent via SQL*Net to client
          2 calls to get snapshot scn: kcmgss
          2 calls to kcmgcs
        102 consistent gets
          1 consistent gets examination
          1 consistent gets examination (fastpath)
        102 consistent gets from cache
        101 consistent gets pin
        101 consistent gets pin (fastpath)
          2 execute count
          1 index range scans
     835584 logical read bytes from cache
        101 no work - consistent read gets
         72 non-idle wait count
          2 opened cursors cumulative
          1 opened cursors current
          2 parse count (total)
          2 process last non-idle time
          1 session cursor cache count
          1 session cursor cache hits
        102 session logical reads
          1 sorts (memory)
       2024 sorts (rows)
       4200 table fetch by rowid
          3 user calls

We can see the query now automatically uses the index and only requires just 102 consistent gets, down from 4572 when it performed the FTS.

If we look at the cost of this new plan:

SQL> SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(sql_id=>'25qktyn35b662',format=>'ALLSTATS LAST +cost +bytes'));

PLAN_TABLE_OUTPUT
_____________________________________________________________________________________________________________________________________
SQL_ID 25qktyn35b662, child number 0

-------------------------------------

select * from bowie2 where id between 1 and 4200

Plan hash value: 3243780227

----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation                            | Name        | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows | A-Time     | Buffers |
----------------------------------------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                     |             |      1 |        |       |   113 (100)|   4200 |00:00:00.01 |     102 |
|  1 |  TABLE ACCESS BY INDEX ROWID BATCHED | BOWIE2      |      1 |   4200 |   684K|     113 (0)|   4200 |00:00:00.01 |     102 |
|* 2 |   INDEX RANGE SCAN                   | BOWIE2_ID_I |      1 |   4200 |       |      11 (0)|   4200 |00:00:00.01 |      11 |
----------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

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

We can see the plan has a cost of just 113, which is both much more accurate and close to the 102 consistent gets and much less than the previous cost of 1340 for the FTS plan.

So in specific examples where migrated rows significantly impact the Clustering Factor of indexes important to our applications, including when ROWIDs are updated on the fly in Oracle Autonomous Databases, we may need to appropriately reorg such tables to repair the Clustering Factor of impacted indexes.

I’ve mentioned a number of times in this series how tables in Oracle Autonomous Databases with ENABLE ROW MOVEMENT have their ROWIDs updated on the fly when a row migrates. In my next post, I’ll discuss how even tables that don’t have the ENABLE ROW MOVEMENT clause set can still have their ROWIDs updated on the fly when a row migrates…

Oracle ACE Director Award 2022 March 8, 2023

Posted by Richard Foote in Oracle ACE Director, Richard's Musings.
add a comment

I received a very nice surprise in the mail today, my Oracle ACE Director Award for 2022.

A big thank you to Jen and the whole team at Oracle who support all the Oracle ACE folks so well.

Hopefully, I’ll have the opportunity to present at an Oracle conference or two sometime in the near future… 🙂

Possible Impact To Clustering Factor Now ROWIDs Are Updated When Rows Migrate Part II (“Dancing Out In Space”) March 7, 2023

Posted by Richard Foote in 19c, 19c New Features, Attribute Clustering, Autonomous Data Warehouse, Autonomous Database, Autonomous Transaction Processing, CBO, Changing ROWID, Clustering Factor, Data Clustering, David Bowie, Full Table Scans, Index Access Path, Index Internals, Index Rebuild, Index statistics, Leaf Blocks, Migrated Rows, Oracle, Oracle 21c, Oracle Blog, Oracle Cloud, Oracle Cost Based Optimizer, Oracle General, Oracle Indexes, Oracle Statistics, Oracle19c, Performance Tuning, Richard's Musings, ROWID.
1 comment so far

In my previous post, I discussed how the clustering of data can be impacted if rows migrate and how this in turn can have a detrimental impact on the efficiency of associated indexes.

In this post, I’ll discuss what you can do (and not do) to remedy things in the relatively unlikely event that you hit this issue with migrated rows.

I’ll just discuss initially the example where the table is defined without ENABLE ROW MOVEMENT enabled in the Transaction Processing Autonomous Database (and so does NOT update ROWIDs on the fly when a row migrates).

I’ll start by again creating and populating a tightly packed table, with the data inserted in ID column order:

SQL> create table bowie(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 BOWIE created.

SQL> insert into bowie 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.

I’ll now create an index on this well ordered/clustered ID column:

SQL> create index bowie_id_i on bowie(id);

Index BOWIE_ID_I created.

Next, I’ll update the table, increasing the size of the rows such that I generate a bunch of migrated rows:

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

200,000 rows updated.

SQL> commit;

Commit complete.

If we check the number of migrated rows:

SQL> analyze table bowie compute statistics;

Table BOWIE analyzed.

SQL> select table_name, num_rows, blocks, empty_blocks, avg_space, avg_row_len, chain_cnt from user_tables

where table_name='BOWIE';

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

We notice there are indeed 56186 migrated rows.

If we check the current Clustering Factor of the index:

SQL> execute dbms_stats.delete_table_stats(ownname=>null, tabname=>'BOWIE');

PL/SQL procedure successfully completed.

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

PL/SQL procedure successfully completed.

SQL> select table_name, num_rows, blocks from user_tables where table_name='BOWIE';

   TABLE_NAME    NUM_ROWS    BLOCKS
_____________ ___________ _________
BOWIE              200000      4906

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

   INDEX_NAME    BLEVEL    LEAF_BLOCKS    CLUSTERING_FACTOR
_____________ _________ ______________ ____________________
BOWIE_ID_I            1            473                 3250

We notice the index still has an excellent Clustering Factor of just 3250. As the ROWIDs are NOT updated in this example when rows migrate, the index retains the same Clustering Factor as before the Update statement.

If we run the following query that returns 4200 rows (as per my previous post):

SQL> select * from bowie where id between 1 and 4200;

4,200 rows selected.

PLAN_TABLE_OUTPUT
_______________________________________________________________________________________________________________
SQL_ID c376kdhy5b0x9, child number 0
-------------------------------------
select * from bowie where id between 1 and 4200

Plan hash value: 1405654398

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

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

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


Statistics
-----------------------------------------------------------
          2 CPU used by this session
          2 CPU used when call started
          3 DB time
      24901 RM usage
          3 Requests to/from client
          2 SQL*Net roundtrips to/from client
       2762 buffer is not pinned count
       7005 buffer is pinned count
        324 bytes received via SQL*Net from client
     461909 bytes sent via SQL*Net to client
          2 calls to get snapshot scn: kcmgss
          2 calls to kcmgcs
       2771 consistent gets
          1 consistent gets examination
          1 consistent gets examination (fastpath)
       2771 consistent gets from cache
       2770 consistent gets pin
       2770 consistent gets pin (fastpath)
          2 execute count
          1 index range scans
   22700032 logical read bytes from cache
       2770 no work - consistent read gets
         73 non-idle wait count
          2 opened cursors cumulative
          1 opened cursors current
          2 parse count (total)
          1 process last non-idle time
          1 session cursor cache count
          1 session cursor cache hits
       2771 session logical reads
          1 sorts (memory)
       2024 sorts (rows)
       4200 table fetch by rowid
       1366 table fetch continued row
          3 user calls

We can see the query currently uses 2771 consistent gets, which is significantly higher than it could be, as Oracle has to visit the original table block and then follow the pointer to the new location for any migrated row that needs to be retrieved.

However, if we look at the cost of the current plan:

SQL> SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(sql_id=>'c376kdhy5b0x9',format=>'ALLSTATS LAST +cost +bytes'));

PLAN_TABLE_OUTPUT
____________________________________________________________________________________________________________________________________
SQL_ID c376kdhy5b0x9, child number 0

-------------------------------------

select * from bowie where id between 1 and 4200

Plan hash value: 1405654398

---------------------------------------------------------------------------------------------------------------------------------
| Id | Operation                            | Name       | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows | A-Time     | Buffers |
---------------------------------------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                     |            |      1 |        |       |    80 (100)|   4200 |00:00:00.01 |    2771 |
|  1 |  TABLE ACCESS BY INDEX ROWID BATCHED | BOWIE      |      1 |   4200 |   684K|      80 (0)|   4200 |00:00:00.01 |    2771 |
|* 2 |   INDEX RANGE SCAN                   | BOWIE_ID_I |      1 |   4200 |       |      11 (0)|   4200 |00:00:00.01 |      11 |
---------------------------------------------------------------------------------------------------------------------------------

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

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

We can see it only has a cost of 80, as Oracle does not consider the additional accesses required now for these migrated rows. With such a perfect Clustering Factor, this cost is not particularly accurate and does not represent the true cost of the 2771 consistent gets now required.

Now there are various ways we can look at fixing this issue with all these migrated rows requiring additional consistent gets to access.

One method is to capture all the ROWIDs of the migrated rows, copy these rows to a temporary holding table, delete these rows and then re-insert them all back into the table from the temporary table.

We can identify the migrated rows by creating the CHAIN_ROWS table as per the Oracle supplied UTLCHAIN.SQL script and then use the ANALYZE command to store their ROWIDs in this CHAIN_ROWS table:

SQL> create table CHAINED_ROWS (
2 owner_name varchar2(128),
3 table_name varchar2(128),
4 cluster_name varchar2(128),
5 partition_name varchar2(128),
6 subpartition_name varchar2(128),
7 head_rowid rowid,
8 analyze_timestamp date
9* );

Table CHAINED_ROWS created.

SQL> analyze table bowie list chained rows;

Table BOWIE analyzed.

SQL> select table_name, head_rowid from chained_rows where table_name='BOWIE' and rownum<=10;

   TABLE_NAME            HEAD_ROWID
_____________ _____________________
BOWIE         AAAqFjAAAAAE6CzAAP
BOWIE         AAAqFjAAAAAE6CzAAR
BOWIE         AAAqFjAAAAAE6CzAAU
BOWIE         AAAqFjAAAAAE6CzAAW
BOWIE         AAAqFjAAAAAE6CzAAZ
BOWIE         AAAqFjAAAAAE6CzAAb
BOWIE         AAAqFjAAAAAE6CzAAe
BOWIE         AAAqFjAAAAAE6CzAAg
BOWIE         AAAqFjAAAAAE6CzAAj
BOWIE         AAAqFjAAAAAE6CzAAl

Another method we can now utilise is to simply MOVE ONLINE the table:

SQL> alter table bowie move online;

Table BOWIE altered.

If we now look at the number of migrated rows after the table reorg:

SQL> analyze table bowie compute statistics;

Table BOWIE analyzed.

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

   TABLE_NAME    NUM_ROWS    BLOCKS    EMPTY_BLOCKS    AVG_SPACE    AVG_ROW_LEN    CHAIN_CNT
_____________ ___________ _________ _______________ ____________ ______________ ____________
BOWIE              200000      4936              56          838            169            0

We can see we no longer have any migrated rows.

BUT, if we now look at the Clustering Factor of this index:

SQL> execute dbms_stats.delete_table_stats(ownname=>null, tabname=>'BOWIE');

PL/SQL procedure successfully completed.

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

PL/SQL procedure successfully completed.

SQL> select table_name, num_rows, blocks from user_tables where table_name='BOWIE';

   TABLE_NAME    NUM_ROWS    BLOCKS
_____________ ___________ _________
BOWIE              200000      4936

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

   INDEX_NAME    BLEVEL    LEAF_BLOCKS    CLUSTERING_FACTOR
_____________ _________ ______________ ____________________
BOWIE_ID_I            1            473               114560

We can see it has now significantly increased to 114560 (previously it was just 3250).

The problem of course is that if the ROWIDs now represent the correct new physical location of the migrated rows, the previously perfect clustering/ordering of the ID column has been impacted.

If we now re-run the query returning the 4200 rows:

SQL> select * from bowie where id between 1 and 4200;

4,200 rows selected.

PLAN_TABLE_OUTPUT
_____________________________________________________________________________________________________
SQL_ID c376kdhy5b0x9, child number 0
-------------------------------------
select * from bowie where id between 1 and 4200

Plan hash value: 1845943507

---------------------------------------------------------------------------------------------
| Id | Operation                  | Name  | Starts | E-Rows | A-Rows | A-Time     | Buffers |
---------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT           |       |      1 |        |   4200 |00:00:00.02 |    4857 |
|* 1 |  TABLE ACCESS STORAGE FULL | BOWIE |      1 |   4200 |   4200 |00:00:00.02 |    4857 |
---------------------------------------------------------------------------------------------

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

   1 - storage(("ID"<=4200 AND "ID">=1))
       filter(("ID"<=4200 AND "ID">=1))

Statistics
-----------------------------------------------------------
          3 CPU used by this session
          3 CPU used when call started
       4849 Cached Commit SCN referenced
          2 DB time
      25870 RM usage
          3 Requests to/from client
          2 SQL*Net roundtrips to/from client
          2 buffer is not pinned count
        324 bytes received via SQL*Net from client
     461962 bytes sent via SQL*Net to client
          2 calls to get snapshot scn: kcmgss
          9 calls to kcmgcs
       4857 consistent gets
       4857 consistent gets from cache
       4857 consistent gets pin
       4857 consistent gets pin (fastpath)
          2 execute count
   39788544 logical read bytes from cache
       4850 no work - consistent read gets
         72 non-idle wait count
          2 opened cursors cumulative
          1 opened cursors current
          2 parse count (total)
          2 process last non-idle time
          1 session cursor cache count
       4857 session logical reads
          1 sorts (memory)
       2024 sorts (rows)
       4850 table scan blocks gotten
     200000 table scan disk non-IMC rows gotten
     200000 table scan rows gotten
          1 table scans (short tables)
          3 user calls

Oracle is now performing a Full Table Scan (FTS). The number of consistent gets now at 4857 is actually worse than when we had the migrated rows (previously at 2771)

The Clustering Factor of the ID column is now so bad, that returning 4200 rows via such an index is just too expensive. The FTS is now deemed the cheaper option by the CBO.

If we look at the CBO cost of using this FTS plan:

SQL> SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(sql_id=>'c376kdhy5b0x9',format=>'ALLSTATS LAST +cost +bytes'));

PLAN_TABLE_OUTPUT
_____________________________________________________________________________________________________________________
SQL_ID c376kdhy5b0x9, child number 0
-------------------------------------
select * from bowie where id between 1 and 4200

Plan hash value: 1845943507

------------------------------------------------------------------------------------------------------------------
| Id | Operation                  | Name  | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows | A-Time     | Buffers |
------------------------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT           |       |      1 |        |       |  1340 (100)|   4200 |00:00:00.02 |    4857 |
|* 1 |  TABLE ACCESS STORAGE FULL | BOWIE |      1 |   4200 |   684K|    1340 (1)|   4200 |00:00:00.02 |    4857 |
------------------------------------------------------------------------------------------------------------------

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

   1 - storage(("ID"<=4200 AND "ID">=1))
       filter(("ID"<=4200 AND "ID">=1))

We can see the cost of this plan is 1340.

If we compare this with the cost of using the (now deemed) inefficient index:

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

4,200 rows selected.

PLAN_TABLE_OUTPUT
_______________________________________________________________________________________________________________
SQL_ID 9215hkzd3v1up, child number 0
-------------------------------------
select /*+ index (bowie) */ * from bowie where id between 1 and 4200

Plan hash value: 1405654398

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

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

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


Statistics
-----------------------------------------------------------
          2 CPU used by this session
          2 CPU used when call started
       2741 Cached Commit SCN referenced
          2 DB time
      12633 RM usage
          3 Requests to/from client
          2 SQL*Net roundtrips to/from client
       2775 buffer is not pinned count
       5626 buffer is pinned count
        345 bytes received via SQL*Net from client
     462170 bytes sent via SQL*Net to client
          2 calls to get snapshot scn: kcmgss
          2 calls to kcmgcs
       2784 consistent gets
          1 consistent gets examination
          1 consistent gets examination (fastpath)
       2784 consistent gets from cache
       2783 consistent gets pin
       2783 consistent gets pin (fastpath)
          2 execute count
          1 index range scans
   22806528 logical read bytes from cache
       2783 no work - consistent read gets
         72 non-idle wait count
          2 opened cursors cumulative
          1 opened cursors current
          2 parse count (total)
          4 process last non-idle time
          1 session cursor cache count
          1 session cursor cache hits
       2784 session logical reads
          1 sorts (memory)
       2024 sorts (rows)
       4200 table fetch by rowid
          3 user calls

SQL> SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(sql_id=>'9215hkzd3v1up',format=>'ALLSTATS LAST +cost +bytes'));

PLAN_TABLE_OUTPUT
____________________________________________________________________________________________________________________________________
SQL_ID 9215hkzd3v1up, child number 0

-------------------------------------

select /*+ index (bowie) */ * from bowie where id between 1 and 4200

Plan hash value: 1405654398

---------------------------------------------------------------------------------------------------------------------------------
| Id | Operation                            | Name       | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows | A-Time     | Buffers |
---------------------------------------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                     |            |      1 |        |       |  2418 (100)|   4200 |00:00:00.01 |    2784 |
|  1 |  TABLE ACCESS BY INDEX ROWID BATCHED | BOWIE      |      1 |   4200 |   684K|    2418 (1)|   4200 |00:00:00.01 |    2784 |
|* 2 |   INDEX RANGE SCAN                   | BOWIE_ID_I |      1 |   4200 |       |      11 (0)|   4200 |00:00:00.01 |      11 |
---------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

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

We can see the CBO cost of the index is now 2418, more than the 1340 cost of using the FTS.

So in the scenario where by migrating a significant number of rows, we impact the Clustering Factor and so the efficiency of vital indexes in our applications, we need to eliminate the migrated rows in a more thoughtful manner.

An option we have available is to first add an appropriate Clustering Attribute before we perform the table reorg:

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

Table BOWIE altered.

SQL> alter table bowie move online;

Table BOWIE altered.

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

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

PL/SQL procedure successfully completed.

SQL> select table_name, num_rows, blocks from user_tables where table_name='BOWIE';

   TABLE_NAME    NUM_ROWS    BLOCKS
_____________ ___________ _________
BOWIE              200000      4936

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

   INDEX_NAME    BLEVEL    LEAF_BLOCKS    CLUSTERING_FACTOR
_____________ _________ ______________ ____________________
BOWIE_ID_I            1            473                 4850

The Clustering Factor has been reduced down to the almost perfect 4850, down from the previous 114560.

If we now re-run the query:

SQL> select * from bowie where id between 1 and 4200;

4,200 rows selected.

PLAN_TABLE_OUTPUT
_______________________________________________________________________________________________________________
SQL_ID c376kdhy5b0x9, child number 0
-------------------------------------
select * from bowie where id between 1 and 4200

Plan hash value: 1405654398

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

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

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


Statistics
-----------------------------------------------------------
          1 CPU used by this session
          1 CPU used when call started
         89 Cached Commit SCN referenced
          1 DB time
      11249 RM usage
          3 Requests to/from client
          2 SQL*Net roundtrips to/from client
         93 buffer is not pinned count
       8308 buffer is pinned count
        324 bytes received via SQL*Net from client
     462165 bytes sent via SQL*Net to client
          2 calls to get snapshot scn: kcmgss
          2 calls to kcmgcs
        102 consistent gets
          1 consistent gets examination
          1 consistent gets examination (fastpath)
        102 consistent gets from cache
        101 consistent gets pin
        101 consistent gets pin (fastpath)
          2 execute count
          1 index range scans
     835584 logical read bytes from cache
        101 no work - consistent read gets
         72 non-idle wait count
          2 opened cursors cumulative
          1 opened cursors current
          2 parse count (total)
          1 process last non-idle time
          1 session cursor cache count
          1 session cursor cache hits
        102 session logical reads
          1 sorts (memory)
       2024 sorts (rows)
       4200 table fetch by rowid
          3 user calls

We can see the query now automatically uses the index and only requires just 102 consistent gets (down from 4857 when it performed the FTS and down from 2771 when we had the migrated rows).

If we look at the cost of this new plan:

SQL> SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(sql_id=>'c376kdhy5b0x9',format=>'ALLSTATS LAST +cost +bytes'));

PLAN_TABLE_OUTPUT
____________________________________________________________________________________________________________________________________
SQL_ID c376kdhy5b0x9, child number 0

-------------------------------------

select * from bowie where id between 1 and 4200

Plan hash value: 1405654398

---------------------------------------------------------------------------------------------------------------------------------
| Id | Operation                            | Name       | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows | A-Time     | Buffers |
---------------------------------------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                     |            |      1 |        |       |   113 (100)|   4200 |00:00:00.01 |     102 |
|  1 |  TABLE ACCESS BY INDEX ROWID BATCHED | BOWIE      |      1 |   4200 |   684K|     113 (0)|   4200 |00:00:00.01 |     102 |
|* 2 |   INDEX RANGE SCAN                   | BOWIE_ID_I |      1 |   4200 |       |      11 (0)|   4200 |00:00:00.01 |      11 |
---------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

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

We can see the plan has a cost of just 113, which is both much more accurate and close to the 102 consistent gets and much less than the previous cost of 1340 for the FTS plan.

So in specific scenarios where by having migrated rows we significantly impact the Clustering Factor of indexes important to our applications, we have to be a little cleverer in how we address the migrated rows.

This can also the case in the new scenario where Oracle automatically updates the ROWIDs of migrated rows, as I’ll discuss in my next post…

Possible Impact To Clustering Factor Now ROWIDs Are Updated When Rows Migrate Part I (“Growin’ Up”) March 1, 2023

Posted by Richard Foote in 19c, 19c New Features, Attribute Clustering, Autonomous Data Warehouse, Autonomous Database, Autonomous Transaction Processing, BLEVEL, CBO, Changing ROWID, Clustering Factor, Data Clustering, Hints, Index Access Path, Index Block Splits, Index Delete Operations, Index Height, Index Internals, Index Rebuild, Index statistics, Leaf Blocks, Migrated Rows, Oracle, Oracle Blog, Oracle Cloud, Oracle Cost Based Optimizer, Oracle General, Oracle Indexes, Oracle Indexing Internals Webinar, Oracle Statistics, Oracle19c, Performance Tuning, Richard Foote Training, Richard's Blog, ROWID.
2 comments

In my previous post I discussed how an index can potentially be somewhat inflated in size after ROWIDs are updated on the fly after a substantial number of rows are migrated.

However, there’s another key “factor” of an index that in some scenarios can be impacted by this new ROWID behaviour with regard migrated rows.

To highlight this scenario, I’ll again start by creating and populating a table with ENABLE ROW MOVEMENT disabled:

SQL> create table bowie(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 BOWIE created.

SQL> insert into bowie 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=>'BOWIE', estimate_percent=> null, no_invalidate=>false);

PL/SQL procedure successfully completed.

I’ll next create an index on the ID column. The important aspect with the ID column is that the data is entered monotonically in ID column order, so the associated index will have an excellent (very low) Clustering Factor:

SQL> create index bowie_id_i on bowie(id);

Index BOWIE_ID_I created.

If we look at some key statistics of the table and index:

SQL> select table_name, num_rows, blocks from user_tables where table_name='BOWIE';

   TABLE_NAME    NUM_ROWS    BLOCKS
_____________ ___________ _________
BOWIE              200000      3268

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

   INDEX_NAME    BLEVEL    LEAF_BLOCKS    CLUSTERING_FACTOR
_____________ _________ ______________ ____________________
BOWIE_ID_I            1            473                 3250

We can see that the number of table blocks is 3268, the number of index leaf blocks is 473 and we indeed have a near perfect Clustering Factor of 3250.

If we run a couple of queries:

SQL> select * from bowie where id between 1 and 1000;

1,000 rows selected.

PLAN_TABLE_OUTPUT
_______________________________________________________________________________________________________________
SQL_ID gz5u92hmjwz1h, child number 0
-------------------------------------
select * from bowie where id between 1 and 1000

Plan hash value: 1405654398

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

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

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

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

Statistics
-----------------------------------------------------------
          1 CPU used by this session
          1 CPU used when call started
       7353 RM usage
          3 Requests to/from client
          2 SQL*Net roundtrips to/from client
         16 buffer is not pinned count
       1985 buffer is pinned count
        324 bytes received via SQL*Net from client
     171305 bytes sent via SQL*Net to client
          2 calls to get snapshot scn: kcmgss
          2 calls to kcmgcs
         18 consistent gets
          1 consistent gets examination
          1 consistent gets examination (fastpath)
         18 consistent gets from cache
         17 consistent gets pin
         17 consistent gets pin (fastpath)
          2 execute count
          1 index range scans
     147456 logical read bytes from cache
         17 no work - consistent read gets
         38 non-idle wait count
          2 opened cursors cumulative
          1 opened cursors current
          2 parse count (total)
          1 process last non-idle time
          2 session cursor cache count
         18 session logical reads
          1 sorts (memory)
       2024 sorts (rows)
       1000 table fetch by rowid
          3 user calls

We can see for this first query that returns 1000 rows, it requires just 18 consistent gets, thanks primarily due to the efficient index with the perfect Clustering Factor.

If we look at the cost of this plan:

SQL> SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(sql_id=>'gz5u92hmjwz1h',format=>'ALLSTATS LAST +cost +bytes'));

PLAN_TABLE_OUTPUT
____________________________________________________________________________________________________________________________________
SQL_ID gz5u92hmjwz1h, child number 0

-------------------------------------

select * from bowie where id between 1 and 1000

Plan hash value: 1405654398

---------------------------------------------------------------------------------------------------------------------------------
| Id | Operation                            | Name       | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows | A-Time     | Buffers |
---------------------------------------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                     |            |      1 |        |       |    21 (100)|   1000 |00:00:00.01 |      18 |
|  1 |  TABLE ACCESS BY INDEX ROWID BATCHED | BOWIE      |      1 |   1000 |   108K|      21 (0)|   1000 |00:00:00.01 |      18 |
|* 2 |   INDEX RANGE SCAN                   | BOWIE_ID_I |      1 |   1000 |       |       4 (0)|   1000 |00:00:00.01 |       4 |
---------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

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

We can see the plan has an accurate cost of just 21.

If we now run a similar query that returns a few more rows:

SQL> select * from bowie where id between 1 and 4200;

4,200 rows selected.

PLAN_TABLE_OUTPUT
_______________________________________________________________________________________________________________
SQL_ID c376kdhy5b0x9, child number 0
-------------------------------------
select * from bowie where id between 1 and 4200

Plan hash value: 1405654398

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

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

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

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

Statistics
-----------------------------------------------------------
          1 CPU used by this session
          1 CPU used when call started
          1 DB time
      11353 RM usage
          3 Requests to/from client
          2 SQL*Net roundtrips to/from client
         59 buffer is not pinned count
       8342 buffer is pinned count
        324 bytes received via SQL*Net from client
     461834 bytes sent via SQL*Net to client
          2 calls to get snapshot scn: kcmgss
          2 calls to kcmgcs
         68 consistent gets
          1 consistent gets examination
          1 consistent gets examination (fastpath)
         68 consistent gets from cache
         67 consistent gets pin
         67 consistent gets pin (fastpath)
          2 execute count
          1 index range scans
     557056 logical read bytes from cache
         67 no work - consistent read gets
         73 non-idle wait count
         2 opened cursors cumulative
         1 opened cursors current
         2 parse count (total)
         1 process last non-idle time
         2 session cursor cache count
        68 session logical reads
         1 sorts (memory)
      2024 sorts (rows)
      4200 table fetch by rowid
         3 user calls

We can see that it only required just 68 consistent gets to return 4200 rows, thanks to the excellent data clustering and associated very low Clustering Factor.

If we look at the cost of this plan:

SQL> SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(sql_id=>'c376kdhy5b0x9',format=>'ALLSTATS LAST +cost +bytes'));

PLAN_TABLE_OUTPUT
____________________________________________________________________________________________________________________________________
SQL_ID c376kdhy5b0x9, child number 0

-------------------------------------

select * from bowie where id between 1 and 4200

Plan hash value: 1405654398

---------------------------------------------------------------------------------------------------------------------------------
| Id | Operation                            | Name       | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows | A-Time     | Buffers |
---------------------------------------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                     |            |      1 |        |       |    80 (100)|   4200 |00:00:00.01 |      68 |
|  1 |  TABLE ACCESS BY INDEX ROWID BATCHED | BOWIE      |      1 |   4200 |   455K|      80 (0)|   4200 |00:00:00.01 |      68 |
|* 2 |   INDEX RANGE SCAN                   | BOWIE_ID_I |      1 |   4200 |       |      11 (0)|   4200 |00:00:00.01 |      11 |
---------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

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

We can see the cost of the plan is currently a relatively accurate 80.

OK, let’s now perform an update on this table that generates a bunch of migrated rows:

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

200,000 rows updated.

SQL> commit;

Commit complete.

If we now look at the table and index statistics:

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

PL/SQL procedure successfully completed.

SQL> select table_name, num_rows, blocks from user_tables where table_name='BOWIE';

   TABLE_NAME    NUM_ROWS    BLOCKS
_____________ ___________ _________
BOWIE              200000      4906

We can see that the table blocks value has increased to 4906 (previously 3268). This as explained previously is to due in large part to the increased NAME column values and also due to the pointers in the original table blocks that point to the new locations of the migrated rows.

This relates to approximately a 50% increase in table blocks.

If we look at the current index statistics:

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

   INDEX_NAME    BLEVEL    LEAF_BLOCKS    CLUSTERING_FACTOR
_____________ _________ ______________ ____________________
BOWIE_ID_I            1            473                 3250

We can see that these values are all unchanged, as the ROWIDs in indexes remain unchanged when a row migrates, when ENABLE ROW MOVEMENT is not set.

Therefore, when we re-run these same queries:

SQL> select * from bowie where id between 1 and 1000;

1,000 rows selected.

PLAN_TABLE_OUTPUT
_______________________________________________________________________________________________________________
SQL_ID gz5u92hmjwz1h, child number 0
-------------------------------------
select * from bowie where id between 1 and 1000

Plan hash value: 1405654398

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

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

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

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

Statistics
-----------------------------------------------------------
          1 DB time
       7967 RM usage
          3 Requests to/from client
          2 SQL*Net roundtrips to/from client
        664 buffer is not pinned count
       1664 buffer is pinned count
        324 bytes received via SQL*Net from client
     171419 bytes sent via SQL*Net to client
          2 calls to get snapshot scn: kcmgss
          2 calls to kcmgcs
        666 consistent gets
          1 consistent gets examination
          1 consistent gets examination (fastpath)
        666 consistent gets from cache
        665 consistent gets pin
        665 consistent gets pin (fastpath)
          2 execute count
          1 index range scans
    5455872 logical read bytes from cache
        665 no work - consistent read gets
         37 non-idle wait count
          2 opened cursors cumulative
          1 opened cursors current
          2 parse count (total)
          1 process last non-idle time
          2 session cursor cache count
        666 session logical reads
          1 sorts (memory)
       2024 sorts (rows)
       1000 table fetch by rowid
        327 table fetch continued row
          3 user calls

The number of consistent gets has increased significantly to 666 (previously it was just 18).

Now we can attributed an increase of approximately 50% of the previous consistent gets (18 x 0.50 = 9) due to the 50% increase in table blocks required now to store the rows due to the increased row size.

We can also attribute an additional 327 consistent gets for the table fetch continued row value listed in the statistics, representing the extra consistent gets required to access the migrated rows from their new physical location.

But 18 + 9 + 327 = 354 still leaves us short of the new 666 consistent gets value.

The problem with having to visit another table block to get a row from its new location is that it means Oracle has to re-access again the original table block to get the next row (rather than reading multiple rows with the same consistent get).

So it’s actually approximately 2 x table fetch continued row, by which the number of consistent gets is going to increase when accessing migrated rows (noting that the last migrated row in a block will only incur a additional consistent get as the next table block accessed will differ regardless).

If we look at the new CBO cost for this plan:

SQL> SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(sql_id=>'gz5u92hmjwz1h',format=>'ALLSTATS LAST +cost +bytes'));

PLAN_TABLE_OUTPUT
____________________________________________________________________________________________________________________________________
SQL_ID gz5u92hmjwz1h, child number 0

-------------------------------------

select * from bowie where id between 1 and 1000

Plan hash value: 1405654398

---------------------------------------------------------------------------------------------------------------------------------
| Id | Operation                            | Name       | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows | A-Time     | Buffers |
---------------------------------------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                     |            |      1 |        |       |    21 (100)|   1000 |00:00:00.01 |     666 |
|  1 |  TABLE ACCESS BY INDEX ROWID BATCHED | BOWIE      |      1 |   1000 |   163K|      21 (0)|   1000 |00:00:00.01 |     666 |
|* 2 |   INDEX RANGE SCAN                   | BOWIE_ID_I |      1 |   1000 |       |       4 (0)|   1000 |00:00:00.01 |       4 |
---------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

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

We notice the CBO cost for this plan remains unchanged at 21.

This is totally to be expected, as the index statistics by which the cost of an index scan is calculated are unchanged.

Considering the rough “rule of thumb” is that the CBO cost of an index scan should be in the ball-park of the number of possible IOs, the fact the plan now uses 666 consistent gets highlights this cost of just 21 is no longer as accurate…

If we look at the second SQL that returns 4200 rows:

SQL> select * from bowie where id between 1 and 4200;

4,200 rows selected.

PLAN_TABLE_OUTPUT
_______________________________________________________________________________________________________________
SQL_ID c376kdhy5b0x9, child number 0
-------------------------------------
select * from bowie where id between 1 and 4200

Plan hash value: 1405654398

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

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

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

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

Statistics
-----------------------------------------------------------
          2 CPU used by this session
          2 CPU used when call started
          2 DB time
      14103 RM usage
          3 Requests to/from client
          2 SQL*Net roundtrips to/from client
       2762 buffer is not pinned count
       7005 buffer is pinned count
        324 bytes received via SQL*Net from client
      461947 bytes sent via SQL*Net to client
           2 calls to get snapshot scn: kcmgss
           2 calls to kcmgcs
        2771 consistent gets
           1 consistent gets examination
           1 consistent gets examination (fastpath)
        2771 consistent gets from cache
        2770 consistent gets pin
        2770 consistent gets pin (fastpath)
           2 execute count
           1 index range scans
    22700032 logical read bytes from cache
        2770 no work - consistent read gets
          72 non-idle wait count
           2 opened cursors cumulative
           1 opened cursors current
           2 parse count (total)
           1 process last non-idle time
           2 session cursor cache count
        2771 session logical reads
           1 sorts (memory)
        2024 sorts (rows)
        4200 table fetch by rowid
        1366 table fetch continued row
           3 user calls

We again notice consistent gets has increased significantly to 2771 (previously it was just 68). Again, these additional consistent gets can not be attributed to the extra size of the table and the additional approximate 2 x 1366 table fetch continued row gets.

If we now look at the cost of this plan:

SQL> SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(sql_id=>'c376kdhy5b0x9',format=>'ALLSTATS LAST +cost +bytes'));

PLAN_TABLE_OUTPUT
________________________________________________________________________________________________________________________
____________

SQL_ID c376kdhy5b0x9, child number 0

-------------------------------------

select * from bowie where id between 1 and 4200

Plan hash value: 1405654398

---------------------------------------------------------------------------------------------------------------------------------
| Id | Operation                            | Name       | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows | A-Time     | Buffers |
---------------------------------------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                     |            |      1 |        |       |    80 (100)|   4200 |00:00:00.01 |    2771 |
|  1 |  TABLE ACCESS BY INDEX ROWID BATCHED | BOWIE      |      1 |   4200 |   684K|      80 (0)|   4200 |00:00:00.01 |    2771 |
|* 2 |   INDEX RANGE SCAN                   | BOWIE_ID_I |      1 |   4200 |       |      11 (0)|   4200 |00:00:00.01 |      11 |
---------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

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

We again notice the CBO cost for this plan remains unchanged at 80, again totally expected as the underlying index statistics have remain unchanged after the update statement.

But again, not necessary as accurate a cost as it was previously…

If we repeat this demo, but this time on a table with ENABLE ROW MOVEMENT enabled:

SQL> create table bowie2(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 BOWIE2 created.

SQL> insert into bowie2 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=>'BOWIE2', estimate_percent=> null, no_invalidate=>false);

PL/SQL procedure successfully completed.

SQL> create index bowie2_id_i on bowie2(id);

Index BOWIE2_ID_I created.

SQL> select table_name, num_rows, blocks from user_tables where table_name='BOWIE2';

   TABLE_NAME    NUM_ROWS    BLOCKS
_____________ ___________ _________
BOWIE2             200000      3268

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

        INDEX_NAME    BLEVEL    LEAF_BLOCKS    CLUSTERING_FACTOR
__________________ _________ ______________ ____________________
BOWIE2_ID_I                1            473                 3250

The table and index statistics are currently identical to the previous demo.

If we run the same two equivalent queries:

SQL> select * from bowie2 where id between 1 and 1000;

1,000 rows selected.

PLAN_TABLE_OUTPUT
________________________________________________________________________________________________________________
SQL_ID gtkw2704bxj7q, child number 0
-------------------------------------
select * from bowie2 where id between 1 and 1000

Plan hash value: 3243780227

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

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

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

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

Statistics
-----------------------------------------------------------
          1 CPU used by this session
          1 CPU used when call started
       7909 RM usage
          3 Requests to/from client
          2 SQL*Net roundtrips to/from client
         16 buffer is not pinned count
       1985 buffer is pinned count
        325 bytes received via SQL*Net from client
     171306 bytes sent via SQL*Net to client
          2 calls to get snapshot scn: kcmgss
          2 calls to kcmgcs
         18 consistent gets
          1 consistent gets examination
          1 consistent gets examination (fastpath)
         18 consistent gets from cache
         17 consistent gets pin
         17 consistent gets pin (fastpath)
          2 execute count
          1 index range scans
     147456 logical read bytes from cache
         17 no work - consistent read gets
         37 non-idle wait count
          2 opened cursors cumulative
          1 opened cursors current
          2 parse count (total)
          1 process last non-idle time
          2 session cursor cache count
         18 session logical reads
          1 sorts (memory)
       2024 sorts (rows)
       1000 table fetch by rowid
     3 user calls

SQL> SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(sql_id=>'gtkw2704bxj7q',format=>'ALLSTATS LAST +cost +bytes'));

PLAN_TABLE_OUTPUT
_____________________________________________________________________________________________________________________________________
SQL_ID gtkw2704bxj7q, child number 0

-------------------------------------

select * from bowie2 where id between 1 and 1000

Plan hash value: 3243780227

----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation                            | Name        | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows | A-Time     | Buffers |
----------------------------------------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                     |             |      1 |        |       |    21 (100)|   1000 |00:00:00.01 |      18 |
|  1 |  TABLE ACCESS BY INDEX ROWID BATCHED | BOWIE2      |      1 |   1000 |   108K|      21 (0)|   1000 |00:00:00.01 |      18 |
|* 2 |   INDEX RANGE SCAN                   | BOWIE2_ID_I |      1 |   1000 |       |       4 (0)|   1000 |00:00:00.01 |       4 |
----------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

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



SQL> select * from bowie2 where id between 1 and 4200;

4,200 rows selected.

PLAN_TABLE_OUTPUT
________________________________________________________________________________________________________________
SQL_ID 25qktyn35b662, child number 0
-------------------------------------
select * from bowie2 where id between 1 and 4200

Plan hash value: 3243780227

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

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

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

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

Statistics
-----------------------------------------------------------
          1 CPU used by this session
          1 CPU used when call started
          2 DB time
      13157 RM usage
          3 Requests to/from client
          2 SQL*Net roundtrips to/from client
         59 buffer is not pinned count
       8342 buffer is pinned count
        325 bytes received via SQL*Net from client
     461838 bytes sent via SQL*Net to client
          2 calls to get snapshot scn: kcmgss
          2 calls to kcmgcs
         68 consistent gets
          1 consistent gets examination
          1 consistent gets examination (fastpath)
         68 consistent gets from cache
         67 consistent gets pin
         67 consistent gets pin (fastpath)
          2 execute count
          1 index range scans
     557056 logical read bytes from cache
         67 no work - consistent read gets
         73 non-idle wait count
          2 opened cursors cumulative
          1 opened cursors current
          2 parse count (total)
          1 process last non-idle time
          2 session cursor cache count
         68 session logical reads
          1 sorts (memory)
       2024 sorts (rows)
       4200 table fetch by rowid
          3 user calls

SQL> SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(sql_id=>'25qktyn35b662',format=>'ALLSTATS LAST +cost +bytes'));

PLAN_TABLE_OUTPUT
_____________________________________________________________________________________________________________________________________
SQL_ID 25qktyn35b662, child number 0

-------------------------------------

select * from bowie2 where id between 1 and 4200

Plan hash value: 3243780227

----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation                            | Name        | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows | A-Time     | Buffers |
----------------------------------------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                     |             |      1 |        |       |    80 (100)|   4200 |00:00:00.01 |      68 |
|  1 |  TABLE ACCESS BY INDEX ROWID BATCHED | BOWIE2      |      1 |   4200 |   455K|      80 (0)|   4200 |00:00:00.01 |      68 |
|* 2 |   INDEX RANGE SCAN                   | BOWIE2_ID_I |      1 |   4200 |       |      11 (0)|   4200 |00:00:00.01 |      11 |
----------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

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

With identical table/index statistics, we notice as expected that both SQLs have the same consistent gets and CBO costs as with the previous demo.

If we now repeat the equivalent Update statement:

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

200,000 rows updated.

SQL> commit;

Commit complete.

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

PL/SQL procedure successfully completed.

If we look at the table statistics:

SQL> select table_name, num_rows, blocks from user_tables where table_name='BOWIE2';

   TABLE_NAME   NUM_ROWS     BLOCKS
_____________ ___________ _________
BOWIE2             200000      4654

We notice the number of table blocks has increased to 4654 due to the increased row lengths, but not as much as with the previous demo (where table blocks increased to 4906) as in this scenario, Oracle does not have to store the row location pointers in the original blocks for the migrated rows.

If we look at the index statistics:

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

    INDEX_NAME    BLEVEL    LEAF_BLOCKS    CLUSTERING_FACTOR
______________ _________ ______________ ____________________
BOWIE2_ID_I            2            945               109061

We notice that these are substantially different from the first demo, where ROWIDs for migrated rows are not updated on the fly.

By now updating the ROWIDs, the indexes can possibly increase in size as they have to store both the previous and new ROWIDs in separate index entries and hence Oracle is more likely to perform additional index block splits (as I discussed in my previous post).

The LEAF_BLOCKS are now 945 (previously 473) and even the BLEVEL has increased from 1 to 2.

Additionally, and perhaps importantly for specific key indexes, the Clustering Factor value of indexes can also be impacted. By migrating rows and physically storing them in different locations, this can potentially detrimentally impact the tight clustering of rows based on specific column values.

The Clustering Factor for the index on the monotonically increased ID column has now increased significantly to 109061, up from the previously perfect 3250.

So columns that have naturally good clustering (e.g.: monotonically increasing values such as IDs and dates) or have been manually well clustered for performance purposes, can have the Clustering Factor of associated indexes detrimentally impacted by migrated rows.

If we re-run the first query:

SQL> select * from bowie2 where id between 1 and 1000;

1,000 rows selected.

PLAN_TABLE_OUTPUT
________________________________________________________________________________________________________________
SQL_ID gtkw2704bxj7q, child number 0
-------------------------------------
select * from bowie2 where id between 1 and 1000

Plan hash value: 3243780227

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

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

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

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

Statistics
-----------------------------------------------------------
          1 CPU used by this session
          1 CPU used when call started
          1 DB time
      15262 RM usage
          3 Requests to/from client
          2 SQL*Net roundtrips to/from client
        634 buffer is not pinned count
       1367 buffer is pinned count
        325 bytes received via SQL*Net from client
     171421 bytes sent via SQL*Net to client
          2 calls to get snapshot scn: kcmgss
          2 calls to kcmgcs
        639 consistent gets
          2 consistent gets examination
          2 consistent gets examination (fastpath)
        639 consistent gets from cache
        637 consistent gets pin
        637 consistent gets pin (fastpath)
          2 execute count
          1 index range scans
    5234688 logical read bytes from cache
        637 no work - consistent read gets
         38 non-idle wait count
          1 non-idle wait time
          2 opened cursors cumulative
          1 opened cursors current
          2 parse count (total)
          1 process last non-idle time
          2 session cursor cache count
        639 session logical reads
          1 sorts (memory)
       2024 sorts (rows)
       1000 table fetch by rowid
          3 user calls

I discussed in a previous post how by updating the ROWIDs of migrated rows we can improve performance, as Oracle can go directly to the correct new physical location of a migrated row.

But for some specific indexes, where data clustering is crucial, and we have a significant number migrated rows, this might not necessarily be the case.

We can see consistent gets here has increased to 639 (previously is was just 21), and so not hugely different from the 666 consistent gets required to fetch the migrated rows when the ROWIDs were not updated in the first demo.

If we look at the CBO costings:

SQL> SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(sql_id=>'gtkw2704bxj7q',format=>'ALLSTATS LAST +cost +bytes'));

PLAN_TABLE_OUTPUT
_____________________________________________________________________________________________________________________________________
SQL_ID gtkw2704bxj7q, child number 0

-------------------------------------

select * from bowie2 where id between 1 and 1000

Plan hash value: 3243780227

----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation                            | Name        | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows | A-Time     | Buffers |
----------------------------------------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                     |             |      1 |        |       |   553 (100)|   1000 |00:00:00.01 |     639 |
|  1 |  TABLE ACCESS BY INDEX ROWID BATCHED | BOWIE2      |      1 |   1000 |   163K|     553 (0)|   1000 |00:00:00.01 |     639 |
|* 2 |   INDEX RANGE SCAN                   | BOWIE2_ID_I |      1 |   1000 |       |       7 (0)|   1000 |00:00:00.01 |       7 |
----------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

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

We can see the CBO cost has increased significantly to 553 (previously it was just 21).

With a much increased Clustering Factor, this will obviously impact the CBO costs of associated index scans.

In very extreme cases, these possible changes in the Clustering Factor can even impact the viability of using the index.

If we re-run the second query returning the 4200 rows:

SQL> select * from bowie2 where id between 1 and 4200;

4,200 rows selected.

PLAN_TABLE_OUTPUT
_____________________________________________________________________________________________________
SQL_ID 25qktyn35b662, child number 0
-------------------------------------
select * from bowie2 where id between 1 and 4200

Plan hash value: 1495904576

----------------------------------------------------------------------------------------------
| Id | Operation                  | Name   | Starts | E-Rows | A-Rows | A-Time     | Buffers |
----------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT           |        |      1 |        |   4200 |00:00:00.02 |    4572 |
|* 1 |  TABLE ACCESS STORAGE FULL | BOWIE2 |      1 |   4200 |   4200 |00:00:00.02 |    4572 |
----------------------------------------------------------------------------------------------

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

   1 - storage(("ID"<=4200 AND "ID">=1))
       filter(("ID"<=4200 AND "ID">=1))

We can see that the CBO has now chosen to perform a Full Table Scan (FTS), rather than use the now less efficient index to return this number of rows.

If we look at the CBO costings of this FTS plan:

SQL> SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(sql_id=>'25qktyn35b662',format=>'ALLSTATS LAST +cost +bytes'));

PLAN_TABLE_OUTPUT
______________________________________________________________________________________________________________________
SQL_ID 25qktyn35b662, child number 0
-------------------------------------
select * from bowie2 where id between 1 and 4200

Plan hash value: 1495904576

-------------------------------------------------------------------------------------------------------------------
| Id | Operation                  | Name   | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows | A-Time     | Buffers |
-------------------------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT           |        |      1 |        |       |  1264 (100)|   4200 |00:00:00.02 |    4572 |
|* 1 |  TABLE ACCESS STORAGE FULL | BOWIE2 |      1 |   4200 |   684K|    1264 (1)|   4200 |00:00:00.02 |    4572 |
-------------------------------------------------------------------------------------------------------------------

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

   1 - storage(("ID"<=4200 AND "ID">=1))
       filter(("ID"<=4200 AND "ID">=1))

The cost of the FTS plan is 1264.

If we compare this is a plan that used the index:

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

4,200 rows selected.

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

Plan hash value: 3243780227

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

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

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

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

Statistics
-----------------------------------------------------------
          2 CPU used by this session
          2 CPU used when call started
          2 DB time
      14531 RM usage
          3 Requests to/from client
          2 SQL*Net roundtrips to/from client
       2646 buffer is not pinned count
       5755 buffer is pinned count
        348 bytes received via SQL*Net from client
     462143 bytes sent via SQL*Net to client
          2 calls to get snapshot scn: kcmgss
          2 calls to kcmgcs
       2665 consistent gets
         2 consistent gets examination
         2 consistent gets examination (fastpath)
      2665 consistent gets from cache
      2663 consistent gets pin
      2663 consistent gets pin (fastpath)
         2 execute count
         1 index range scans
  21831680 logical read bytes from cache
      2663 no work - consistent read gets
        73 non-idle wait count
         2 opened cursors cumulative
         1 opened cursors current
         2 parse count (total)
         3 process last non-idle time
         2 session cursor cache count
      2665 session logical reads
         1 sorts (memory)
      2024 sorts (rows)
      4200 table fetch by rowid
         3 user calls

SQL> SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(sql_id=>'bzm2vhchqpq7w',format=>'ALLSTATS LAST +cost +bytes'));

PLAN_TABLE_OUTPUT
_____________________________________________________________________________________________________________________________________
SQL_ID bzm2vhchqpq7w, child number 0

-------------------------------------

select /*+ index (bowie2) */ * from bowie2 where id between 1 and 4200

Plan hash value: 3243780227

----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation                            | Name        | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows | A-Time     | Buffers |
----------------------------------------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                     |             |      1 |        |       |  2314 (100)|   4200 |00:00:00.01 |    2665 |
|  1 |  TABLE ACCESS BY INDEX ROWID BATCHED | BOWIE2      |      1 |   4200 |   684K|    2314 (1)|   4200 |00:00:00.01 |    2665 |
|* 2 |   INDEX RANGE SCAN                   | BOWIE2_ID_I |      1 |   4200 |       |      22 (0)|   4200 |00:00:00.01 |      21 |
----------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

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

The cost of using the index to retrieve the 4200 rows is 2310, more than the 1264 of the FTS.

For the vast majority of indexes, updating the ROWIDs for migrated rows will result in better performance, as such indexes will be able to directly access the correct new physical location of migrated rows, rather than having to visit the original table block and then follow the stored pointer to the new table block.

But for some very specific indexes, where data clustering is crucial, AND we have a significant number migrated rows, this might not necessarily be the case. The performance benefit might be minimal at best.

That’s more than enough for one post 🙂

In my next post, I’ll discuss how to potentially remedy these performance implications, both for tables with or without ENABLE TABLE MOVEMENT enabled…

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.
4 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…

« older posts

	PP on Costing Concatenated Indexes W…
	Zeeshan Moin on The End Of Richard Foote…
	Richard Foote on The End Of Richard Foote…
	PP on The End Of Richard Foote…
	Ram on The Fake Index Trap (“No…
	Richard Foote on The End Of Richard Foote…
	NormanDunbar on The End Of Richard Foote…
	NormanDunbar on The End Of Richard Foote…
	Richard Foote on The End Of Richard Foote…
	Richard Foote on The End Of Richard Foote…
	Richard Foote on The End Of Richard Foote…
	Richard Foote on The End Of Richard Foote…
	Richard Foote on The End Of Richard Foote…
	Richard Foote on The End Of Richard Foote…
	Sayan Malakshinov on The End Of Richard Foote…

Richard Foote's Oracle Blog

The End Of Richard Foote’s Oracle Blog (“Rock ‘n’ Roll Suicide”) July 20, 2023

NOTE -THIS BLOG IS NO LONGER BEING MAINTAINED!!!

(Content may no longer be up to date or accurate with current versions of Oracle)

The Fake Index Trap (“Nowhere Now”) May 2, 2023

Announcement: Oracle Database 23c Free Version Now Available to Developers April 4, 2023

CBO Costing Plans With Migrated Rows Part II (“New Killer Star”) March 28, 2023

CBO Costing Plans With Migrated Rows Part I (“Ignoreland”) March 21, 2023

Migrated Rows In Oracle Data Warehouse Autonomous Databases (“Sit Down. Stand Up.”) March 14, 2023

Annoying Bug When Trying To Create Always Free TP Autonomous Database (“Supernaturally”) March 10, 2023

Possible Impact To Clustering Factor Now ROWIDs Are Updated When Rows Migrate Part III (“Dancing With The Big Boys”) March 9, 2023

Oracle ACE Director Award 2022 March 8, 2023

Possible Impact To Clustering Factor Now ROWIDs Are Updated When Rows Migrate Part II (“Dancing Out In Space”) March 7, 2023

Possible Impact To Clustering Factor Now ROWIDs Are Updated When Rows Migrate Part I (“Growin’ Up”) March 1, 2023

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

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

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

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

Contact Details

Richard’s Art

Recent Comments

Blog Visits

Recent Posts

Archives

Richard Foote’s Pages

Top Posts

Richard’s Categories

Feeds

Richard Foote's Oracle Blog

The End Of Richard Foote’s Oracle Blog (“Rock ‘n’ Roll Suicide”) July 20, 2023

NOTE -THIS BLOG IS NO LONGER BEING MAINTAINED!!!

(Content may no longer be up to date or accurate with current versions of Oracle)

The Fake Index Trap (“Nowhere Now”) May 2, 2023

Announcement: Oracle Database 23c Free Version Now Available to Developers April 4, 2023

CBO Costing Plans With Migrated Rows Part II (“New Killer Star”) March 28, 2023

CBO Costing Plans With Migrated Rows Part I (“Ignoreland”) March 21, 2023

Migrated Rows In Oracle Data Warehouse Autonomous Databases (“Sit Down. Stand Up.”) March 14, 2023

Annoying Bug When Trying To Create Always Free TP Autonomous Database (“Supernaturally”) March 10, 2023

Possible Impact To Clustering Factor Now ROWIDs Are Updated When Rows Migrate Part III (“Dancing With The Big Boys”) March 9, 2023

Oracle ACE Director Award 2022 March 8, 2023

Possible Impact To Clustering Factor Now ROWIDs Are Updated When Rows Migrate Part II (“Dancing Out In Space”) March 7, 2023

Possible Impact To Clustering Factor Now ROWIDs Are Updated When Rows Migrate Part I (“Growin’ Up”) March 1, 2023

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

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

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

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

Contact Details

Richard’s Art

Recent Comments

Blog Visits

Recent Posts

Archives

Richard Foote’s Pages

Top Posts

Richard’s Categories

Richard's Cloud

Feeds