Advantages To Updating ROWID When Rows Migrate (“Fantastic Voyage”)

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)… 🙂