jump to navigation

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…

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…

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…

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…

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

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

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

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

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

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

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

Table created.

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

10000000 rows created.

SQL> commit;

Commit complete.

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

PL/SQL procedure successfully completed.

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

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

 

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

We can easily see the troublesome lock:

SQL> select * from dba_waiters;

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

 

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

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

no rows selected

And the existing workload remains inefficient:

SQL> select * from david_bowie where code=42;

10 rows selected.

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

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

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

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

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

 

Once the locking transaction is finally completed:

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

1 row created.

SQL> commit;

Commit complete.

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

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

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

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

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

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

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

 

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

 

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

Automatic Indexes: Automatically Rebuild Unusable Indexes Part IV (“Nothing Has Changed”) May 31, 2022

Posted by Richard Foote in 19c, 19c New Features, 21c New Features, Automatic Indexing, Autonomous Database, Autonomous Transaction Processing, CBO, Exadata, Full Table Scans, Index Column Order, Index Internals, Local Indexes, Mixing Auto and Manual Indexes, Oracle, Oracle 21c, Oracle Blog, Oracle Cloud, Oracle Cost Based Optimizer, Oracle General, Oracle Indexes, Oracle Indexing Internals Webinar, Oracle19c, Unusable Indexes.
1 comment so far

In a previous post, I discussed how Automatic Indexing (AI) does not automatically rebuild a manually built index that is in an Unusable state (but will rebuild an Unusable automatically created index).

The demo I used was a simple one, based on manually created indexes referencing a non-partitioned table.

In this post, I’m going to use a demo based on manually created indexes referencing a partitioned table.

I’ll start by creating a rather basic range-based partitioned table, using the RELEASE_DATE column to partition the data by year:

SQL> CREATE TABLE big_bowie (id number, album_id number, country_id number, release_date date,
total_sales number) PARTITION BY RANGE (release_date)
(PARTITION ALBUMS_2014 VALUES LESS THAN (TO_DATE('01-JAN-2015', 'DD-MON-YYYY')),
 PARTITION ALBUMS_2015 VALUES LESS THAN (TO_DATE('01-JAN-2016', 'DD-MON-YYYY')),
 PARTITION ALBUMS_2016 VALUES LESS THAN (TO_DATE('01-JAN-2017', 'DD-MON-YYYY')),
 PARTITION ALBUMS_2017 VALUES LESS THAN (TO_DATE('01-JAN-2018', 'DD-MON-YYYY')),
 PARTITION ALBUMS_2018 VALUES LESS THAN (TO_DATE('01-JAN-2019', 'DD-MON-YYYY')),
 PARTITION ALBUMS_2019 VALUES LESS THAN (TO_DATE('01-JAN-2020', 'DD-MON-YYYY')),
 PARTITION ALBUMS_2020 VALUES LESS THAN (TO_DATE('01-JAN-2021', 'DD-MON-YYYY')),
 PARTITION ALBUMS_2021 VALUES LESS THAN (MAXVALUE));

Table created.

SQL> INSERT INTO big_bowie SELECT rownum, mod(rownum,5000)+1, mod(rownum,100)+1, sysdate-mod(rownum,2800),
ceil(dbms_random.value(1,500000)) FROM dual CONNECT BY LEVEL <= 10000000;

10000000 rows created.

SQL> commit;

Commit complete.

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

PL/SQL procedure successfully completed.

I’ll next manually create a couple indexes; a non-partitioned index based on just the ALBUM_ID column and a prefixed locally partitioned index, based on the columns RELEASE_DATE, TOTAL_SALES:

 

SQL> create index album_id_i on big_bowie(album_id);

Index created.

SQL> create index release_date_total_sales_i on big_bowie(release_date, total_sales) local;

Index created.

 

If we now re-organise just partition ALBUMS_2017 (without using the ONLINE clause):

SQL> alter table big_bowie move partition albums_2017;

Table altered.

This results in the non-partitioned index and the ALBUMS_2017 local index partition becoming Unusable:

SQL> select index_name, status from user_indexes where table_name='BIG_BOWIE';

INDEX_NAME                     STATUS
------------------------------ --------
ALBUM_ID_I                     UNUSABLE
RELEASE_DATE_TOTAL_SALES_I     N/A

SQL> select index_name, partition_name, status from user_ind_partitions
     where index_name='RELEASE_DATE_TOTAL_SALES_I';

INDEX_NAME                     PARTITION_NAME       STATUS
------------------------------ -------------------- --------
RELEASE_DATE_TOTAL_SALES_I     ALBUMS_2014          USABLE
RELEASE_DATE_TOTAL_SALES_I     ALBUMS_2015          USABLE
RELEASE_DATE_TOTAL_SALES_I     ALBUMS_2016          USABLE
RELEASE_DATE_TOTAL_SALES_I     ALBUMS_2017          UNUSABLE
RELEASE_DATE_TOTAL_SALES_I     ALBUMS_2018          USABLE
RELEASE_DATE_TOTAL_SALES_I     ALBUMS_2019          USABLE
RELEASE_DATE_TOTAL_SALES_I     ALBUMS_2020          USABLE
RELEASE_DATE_TOTAL_SALES_I     ALBUMS_2021          USABLE

Let’s now run a number of queries a number of times. The first series is based on a predicate on just the ALBUM_ID column, such as:

SQL> select * from big_bowie where album_id=42;

2000 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1510748290

-------------------------------------------------------------------------------------------------
| Id  | Operation           | Name      | Rows | Bytes | Cost (%CPU) | Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |           | 2000 | 52000 |    7959 (2) | 00:00:01 |       |       |
|   1 | PARTITION RANGE ALL |           | 2000 | 52000 |    7959 (2) | 00:00:01 |     1 |     8 |
| * 2 |  TABLE ACCESS FULL  | BIG_BOWIE | 2000 | 52000 |    7959 (2) | 00:00:01 |     1 |     8 |
-------------------------------------------------------------------------------------------------

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

2 - storage("ALBUM_ID"=42)
  - filter("ALBUM_ID"=42)

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

We’ll also run a series of queries based on both the RELEASE_DATE column using dates from the unusable index partition and the TOTAL_SALES column, such as:

SQL> select * from big_bowie where release_date='01-JUN-2017' and total_sales=42;

no rows selected

Execution Plan
----------------------------------------------------------
Plan hash value: 3245457041

----------------------------------------------------------------------------------------------------
| Id  | Operation              | Name      | Rows | Bytes | Cost (%CPU) | Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |           |    1 |    26 |     986 (2) | 00:00:01 |       |       |
|   1 | PARTITION RANGE SINGLE |           |    1 |    26 |     986 (2) | 00:00:01 |     4 |     4 |
| * 2 |  TABLE ACCESS FULL     | BIG_BOWIE |    1 |    26 |     986 (2) | 00:00:01 |     4 |     4 |
----------------------------------------------------------------------------------------------------

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

2 - storage("TOTAL_SALES"=42 AND "RELEASE_DATE"=TO_DATE(' 2017-06-01 00:00:00',
'syyyy-mm-dd hh24:mi:ss'))
   - filter("TOTAL_SALES"=42 AND "RELEASE_DATE"=TO_DATE(' 2017-06-01 00:00:00',
'syyyy-mm-dd hh24:mi:ss'))

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

Without a valid/usable index, the CBO currently has no choice but to use a Full Table Scan on the first query, and a Full Partition Scan on the partition with the unusable local index.

So what does AI make of things? Does it rebuild the unusable manually created indexes so the associated indexes can be used to improve these queries?

If we wait until the next AI task completes and check out the indexes on the table:

SQL> select index_name, status, partitioned from user_indexes where table_name='BIG_BOWIE';

INDEX_NAME                     STATUS   PAR
------------------------------ -------- ---
RELEASE_DATE_TOTAL_SALES_I     N/A      YES
ALBUM_ID_I                     UNUSABLE NO
SYS_AI_aw2825ffpus5s           VALID    NO
SYS_AI_2hf33fpvnqztw           VALID    NO

SQL> select index_name, partition_name, status from user_ind_partitions
     where index_name='RELEASE_DATE_TOTAL_SALES_I';

INDEX_NAME                     PARTITION_NAME       STATUS
------------------------------ -------------------- --------
RELEASE_DATE_TOTAL_SALES_I     ALBUMS_2014          USABLE
RELEASE_DATE_TOTAL_SALES_I     ALBUMS_2015          USABLE
RELEASE_DATE_TOTAL_SALES_I     ALBUMS_2016          USABLE
RELEASE_DATE_TOTAL_SALES_I     ALBUMS_2017          UNUSABLE
RELEASE_DATE_TOTAL_SALES_I     ALBUMS_2018          USABLE
RELEASE_DATE_TOTAL_SALES_I     ALBUMS_2019          USABLE
RELEASE_DATE_TOTAL_SALES_I     ALBUMS_2020          USABLE
RELEASE_DATE_TOTAL_SALES_I     ALBUMS_2021          USABLE

We notice that AI has created two new non-partitioned automatic indexes, while both the manually created indexes remain in the same unusable state. If we look at the columns associated with these new automatic indexes:

SQL> select index_name, column_name, column_position
from user_ind_columns where table_name='BIG_BOWIE';

INDEX_NAME                     COLUMN_NAME          COLUMN_POSITION
------------------------------ -------------------- ---------------
ALBUM_ID_I                     ALBUM_ID                           1
RELEASE_DATE_TOTAL_SALES_I     RELEASE_DATE                       1
RELEASE_DATE_TOTAL_SALES_I     TOTAL_SALES                        2
SYS_AI_aw2825ffpus5s           ALBUM_ID                           1
SYS_AI_aw2825ffpus5s           RELEASE_DATE                       2
SYS_AI_2hf33fpvnqztw           TOTAL_SALES                        1
SYS_AI_2hf33fpvnqztw           RELEASE_DATE                       2

As we can see, AI has logically replaced both unusable indexes.

The manual index based on ALBUM_ID has been replaced with an inferior index based on the ALBUM_ID, RELEASE_DATE columns. Inferior in that the automatic index is both redundant (if only the manual index on ALBUM_ID were rebuilt) and in that it has the logically unnecessary RELEASE_DATE column to inflate the size of the index.

The manual index based on the RELEASE_DATE, TOTAL_SALES columns has been replaced with a redundant automatic index based on the reversed TOTAL_SALES, RELEASE_DATE columns.

Now, AI has indeed automatically addressed the current FTS performance issues associated with these queries by creating these indexes, but a better remedy would have been to rebuild the unusable manual indexes and hence negate the need for these redundant automatic indexes.

But currently (including with version 21.3), AI will NOT rebuild unusable manually created indexes, no matter the scenario, and will instead create additional automatic indexes if it’s viable for it to do so.

A reason why Oracle at times recommends dropping all current manually created secondary indexes before implementing AI (although of course this comes with a range of obvious issues and concerns).

If these manually created indexes didn’t exist, I’ll leave it as an exercise to the discernable reader on what automatic indexes would have been created…

As always, this restriction may change in future releases…

Announcement: Registration Links For Upcoming Webinars Now Open (“Join The Gang”) May 25, 2022

Posted by Richard Foote in 18c New Features, 19c New Features, 21c New Features, Index Internals, Index Internals Seminar, Indexing Tricks, Oracle 21c, Oracle General, Oracle Index Seminar, Oracle Indexing Internals Webinar, Oracle Performance Diagnostics and Tuning Seminar, Oracle Performance Diagnostics and Tuning Webinar, Oracle19c, Performance Tuning, Performance Tuning Seminar, Performance Tuning Webinar, Richard Foote Consulting, Richard Foote Seminars, Richard Foote Training, Richard Presentations.
add a comment

The registration links for my upcoming webinars running in August are now open!!!

The price of each webinar is $1,600 AUD. There is a special price of $2,750 AUD if you wish to attend both webinars (just use the Special Combo Price button).

(Note: Do NOT use the links if you’re an Australian resident. Please contact me at richard@richardfooteconsulting.com for additional payment info and tax invoice that includes additional GST).

Just click the below “Buy Now” buttons to book your place for these unique, highly acclaimed Oracle training events (see some of my testimonials for feedback by previous attendees to these training events):

 

“Oracle Indexing Internals“ Webinar: 8-12 August 2022 (between 09:00 GMT and 13:00 GMT daily) – $1,600 AUD: SOLD OUT!!

“Oracle Performance Diagnostics and Tuning“ Webinar: 22-25 August 2022 (between 09:00 GMT and 13:00 GMT daily) – $1,600 AUD: SOLD OUT!!

“Special Combo Price for both August 2022 Webinars” $2,750 AUD: SOLD OUT!!

 

The links allow you to book a place using either PayPal or a credit card. If you wish to pay via a different method or have any questions at all regarding these events, please contact me at richard@richardfooteconsulting.com.

As I mentioned previously, for those of you on my official waiting list, I will reserve a place for you for a limited time.

As this will probably be the last time I will run these events, remaining places are likely to go quickly. So please book your place ASAP to avoid disappointment…

 

Read below a brief synopsis of each webinar:

“Oracle Indexing Internals“

This is a must attend webinar of benefit to not only DBAs, but also to Developers, Solution Architects and anyone else interested in designing, developing or maintaining high performance Oracle-based applications. It’s a fun, but intense, content rich webinar that is suitable for people of all experiences (from beginners to seasoned Oracle experts).

Indexes are fundamental to every Oracle database and are crucial for optimal performance. However, there’s an incredible amount of misconception, misunderstanding and pure myth regarding how Oracle indexes function and should be maintained. Many applications and databases are suboptimal and run inefficiently primarily because an inappropriate indexing strategy has been implemented.

This webinar examines most available Oracle index structures/options and discusses in considerable detail how indexes function, how/when they should be used and how they should be maintained. A key component of the webinar is how indexes are costed and evaluated by the Cost Based Optimizer (CBO) and how appropriate data management practices are vital for an effective indexing strategy. It also covers many useful tips and strategies to maximise the benefits of indexes on application/database performance and scalability, as well as in maximising Oracle database investments. Much of the material is exclusive to this webinar and is not generally available in Oracle documentation or in Oracle University courses.

For full details, see: https://richardfooteconsulting.com/indexing-seminar/

 

“Oracle Performance Diagnostics and Tuning“

This is a must attend webinar aimed at Oracle professionals (both DBAs and Developers) who are interested in Performance Tuning.  The webinar details how to maximise the performance of both Oracle databases and associated applications and how to diagnose and address any performance issues as quickly and effectively as possible.

When an application suddenly runs “slow” or when people start complaining about the “poor performance” of the database, there’s often some uncertainty in how to most quickly and most accurately determine the “root” cause of any such slowdown and effectively address any associated issues. In this seminar, we explore a Tuning Methodology that helps Oracle professionals to both quickly and reliably determine the actual causes of performance issues and so ensure the effectiveness of any applied resolutions.

Looking at a number of real world scenarios and numerous actual examples and test cases, this webinar will show participants how to confidently and reliably diagnose performance issues. The webinar explores in much detail the various diagnostics tools and reports available in Oracle to assist in determining any database performance issue and importantly WHEN and HOW to effectively use each approach. Additionally, participants are also invited to share their own database/SQL reports, where we can apply the principles learnt in diagnosing the performance of their actual databases/applications.

One of the more common reasons for poor Oracle performance is inefficient or poorly running SQL. This seminar explores in much detail how SQL is executed within the Oracle database, the various issues and related concepts important in understanding why SQL might be inefficient and the many capabilities and features Oracle has in helping to both resolve SQL performance issues and to maintain the stability and reliability of SQL execution.

It’s a fun, but intense, content rich webinar that is suitable for people of all experiences (from beginners to seasoned Oracle experts).

For full details, see: https://richardfooteconsulting.com/performance-tuning-seminar/

 

If you have any questions about these events, please contact me at richard@richardfooteconsulting.com

 

Announcement: Dates Confirmed For Upcoming Webinars (“Here Today, Gone Tomorrow”) May 19, 2022

Posted by Richard Foote in 19c, 19c New Features, 21c New Features, Index Internals, Index Internals Seminar, Indexing Myth, Oracle, Oracle 21c, Oracle General, Oracle Index Seminar, Oracle Indexes, Oracle Indexing Internals Webinar, Oracle Performance Diagnostics and Tuning Webinar, Oracle19c, Performance Tuning, Performance Tuning Webinar, Richard Foote Seminars, Webinar.
add a comment

As promised last week, I have now finalised the dates for my upcoming webinars.

They will be run as follows (UPDATED):

“Oracle Indexing Internals“ Webinar: 8-12 August 2022 (between 09:00 GMT and 13:00 GMT daily): SOLD OUT!!

“Oracle Performance Diagnostics and Tuning“ Webinar: 22-25 August 2022 (between 09:00 GMT and 13:00 GMT daily): SOLD OUT!!

“Special Combo Price for both August 2022 Webinars“: SOLD OUT!!

I’ll detail costings and how to register for these events in the coming days.

 

There is already quite a waiting list for both of these webinars and so I anticipate available places will likely go quickly. Sorry to all those who have been waiting for so long and thank you for your patience. Please note for those on the waiting list, I already have places reserved for you.

It’s highly likely these will be the last time I’ll ever run these highly acclaimed training events (yes, I’m getting old)…

So don’t miss this unique opportunity to learn important skills in how to improve the performance and scalability of both your Oracle based applications and backend Oracle databases, in the comfort of your own home or office.

Read below a brief synopsis of each webinar:

“Oracle Indexing Internals“

This is a must attend webinar of benefit to not only DBAs, but also to Developers, Solution Architects and anyone else interested in designing, developing or maintaining high performance Oracle-based applications. It’s a fun, but intense, content rich webinar that is suitable for people of all experiences (from beginners to seasoned Oracle experts).

Indexes are fundamental to every Oracle database and are crucial for optimal performance. However, there’s an incredible amount of misconception, misunderstanding and pure myth regarding how Oracle indexes function and should be maintained. Many applications and databases are suboptimal and run inefficiently primarily because an inappropriate indexing strategy has been implemented.

This seminar examines most available Oracle index structures/options and discusses in considerable detail how indexes function, how/when they should be used and how they should be maintained. A key component of the seminar is how indexes are costed and evaluated by the Cost Based Optimizer (CBO) and how appropriate data management practices are vital for an effective indexing strategy. It also covers many useful tips and strategies to maximise the benefits of indexes on application/database performance and scalability, as well as in maximising Oracle database investments. Much of the material is exclusive to this seminar and is not generally available in Oracle documentation or in Oracle University courses.

For full details, see: https://richardfooteconsulting.com/indexing-seminar/

 

“Oracle Performance Diagnostics and Tuning“

This is a must attend webinar aimed at Oracle professionals (both DBAs and Developers) who are interested in Performance Tuning.  The webinar details how to maximise the performance of both Oracle databases and associated applications and how to diagnose and address any performance issues as quickly and effectively as possible.

When an application suddenly runs “slow” or when people start complaining about the “poor performance” of the database, there’s often some uncertainty in how to most quickly and most accurately determine the “root” cause of any such slowdown and effectively address any associated issues. In this seminar, we explore a Tuning Methodology that helps Oracle professionals to both quickly and reliably determine the actual causes of performance issues and so ensure the effectiveness of any applied resolutions.

Looking at a number of real world scenarios and numerous actual examples and test cases, this webinar will show participants how to confidently and reliably diagnose performance issues. The webinar explores in much detail the various diagnostics tools and reports available in Oracle to assist in determining any database performance issue and importantly WHEN and HOW to effectively use each approach. Additionally, participants are also invited to share their own database/SQL reports, where we can apply the principles learnt in diagnosing the performance of their actual databases/applications.

One of the more common reasons for poor Oracle performance is inefficient or poorly running SQL. This seminar explores in much detail how SQL is executed within the Oracle database, the various issues and related concepts important in understanding why SQL might be inefficient and the many capabilities and features Oracle has in helping to both resolve SQL performance issues and to maintain the stability and reliability of SQL execution.

It’s a fun, but intense, content rich webinar that is suitable for people of all experiences (from beginners to seasoned Oracle experts).

For full details, see: https://richardfooteconsulting.com/performance-tuning-seminar/

 

Keep an eye out in the coming days on costings and how to register for these events.

If you have any questions about these events, please contact me at richard@richardfooteconsulting.com

Automatic Indexes: Automatically Rebuild Unusable Indexes Part III (“Waiting For The Man”) May 17, 2022

Posted by Richard Foote in 19c, 19c New Features, Automatic Indexing, Autonomous Database, Autonomous Transaction Processing, Exadata, Full Table Scans, Manual Indexes, Mixing Auto and Manual Indexes, Oracle, Oracle Blog, Oracle Cloud, Oracle General, Oracle Indexes, Oracle19c, Unusable Indexes.
1 comment so far

I’ve previously discussed how Automatic Indexing (AI) will not only create missing indexes, but will also rebuild unusable indexes, be it a Global or Local index.

However, all my previous examples have been with Automatic Indexes. How does AI handle unusable indexes in which the indexes were manually created?

In my first demo, I’ll start by creating a basic non-partitioned table:

SQL> create table bowie_stuff (id number, album_id number, country_id number, release_date date, total_sales number);

Table created.

SQL> insert into bowie_stuff select rownum, mod(rownum,5000)+1, mod(rownum,100)+1, sysdate-mod(rownum,2800),
ceil(dbms_random.value(1,500000)) FROM dual CONNECT BY LEVEL <= 10000000;

10000000 rows created.

SQL> commit;

Commit complete.

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

PL/SQL procedure successfully completed.

We next manually create an index on the highly selective TOTAL_SALES column:

SQL> create index bowie_stuff_total_sales_i on bowie_stuff(total_sales);

Index created.

Let’s now invalidate the index by re-organising the table without the online clause:

SQL> alter table bowie_stuff move;

Table altered.

SQL> select index_name, status from user_indexes where table_name='BOWIE_STUFF';

INDEX_NAME                     STATUS
------------------------------ --------
BOWIE_STUFF_TOTAL_SALES_I      UNUSABLE

So the index is now in an UNUSABLE state.

To perk up the interest of AI, I’ll run a number of queries such as the following with a predicate condition on TOTAL_SALES:

select * from bowie_stuff where total_sales=42;

18 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 910563088

---------------------------------------------------------------------------------
| Id | Operation          | Name        | Rows | Bytes | Cost (%CPU) | Time     |
---------------------------------------------------------------------------------
|  0 | SELECT STATEMENT   |             |   20 |   520 |    7427 (2) | 00:00:01 |
|* 1 |  TABLE ACCESS FULL | BOWIE_STUFF |   20 |   520 |    7427 (2) | 00:00:01 |
---------------------------------------------------------------------------------

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

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

Statistics
----------------------------------------------------------
          0 recursive calls
          0 db block gets
      42746 consistent gets
      42741 physical reads
          0 redo size
       1392 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)
         18 rows processed

Without a valid index, the CBO has no choice but to perform an expensive full table scan.

However, it doesn’t matter how long I wait or how many different queries I run similar to the above, AI currently will never rebuild an unusable index if the index was manually created.

AI will only rebuild unusable automatically created indexes.

I’ve discussed previously how automatic and manually created indexes often don’t gel well together and is one of the key reasons why Oracle recommends dropping all manually created secondary indexes if you wish to implement AI (using the DBMS_AUTO_INDEX.DROP_SECONDARY_INDEXES procedure, which I’ll discuss in a future post).

Things can get a little interesting with AI, if the underlining table is partitioned and you have manually created unusable indexes.

As I’ll discuss in my next post…

Announcement: New (And Likely Final) Dates For My Webinars Finalised Next Week !! May 12, 2022

Posted by Richard Foote in 19c, 19c New Features, 21c New Features, Indexing Webinar, Oracle, Oracle 21c, Oracle Cloud, Oracle General, Oracle Performance Diagnostics and Tuning Webinar, Richard Foote Training.
add a comment

 

It’s been one hell of a hectic year!!

For all those of you who have been patiently hanging on for the next series of my webinars, I finally, at long last, have some good news.

I’m currently just finalising my calendar for the upcoming months, but I shall announce the next running of my webinars next week.

I plan to run both of my webinars in the coming months (follow links for full details on each webinar):

 

Note: There is the very distinct possibility that I will be running these highly acclaimed training events, either as a webinar or in person as a seminar, for the very last time.

Ever!!

So these will indeed be unique opportunities to attend some quality training on how to improve the performance and scalability of both your Oracle based applications and backend Oracle databases.

Listen out next week for full details on when these webinars will finally be available to attend and how to register for the limited places available 🙂

Automatic Indexes: Automatically Rebuild Unusable Indexes Part II (“I Wish You Would”) May 11, 2022

Posted by Richard Foote in 19c, 19c New Features, Automatic Indexing, Autonomous Database, Autonomous Transaction Processing, CBO, Exadata, Full Table Scans, Local Indexes, Oracle, Oracle Blog, Oracle Cloud, Oracle Cost Based Optimizer, Oracle General, Oracle Indexes, Oracle19c, Partitioned Indexes, Partitioning, Performance Tuning, Rebuild Unusable Indexes.
1 comment so far

Within a few hours of publishing my last blog piece on how Automatic Indexing (AI) can automatically rebuild indexes that have been placed in an UNUSABLE state, I was asked by a couple of readers a similar question: “Does this also work if just a single partition of an partitioned index becomes unusable”?

My answer to them both is that I’ve provided them the basic framework in the demo to check out the answer to that question for themselves (Note: a fantastic aspect of working with the Oracle Database is that it’s available for free to play around with, including the Autonomous Database environments).

But based on the principle that for every time someone asks a question, there’s probably a 100 others who potentially might be wondering the same thing, thought I’ll quickly whip up a demo to answer this for all.

I’ll begin with the same table format and data as my previous blog:

SQL> CREATE TABLE big_ziggy(id number, album_id number, country_id number, release_date date,
total_sales number) PARTITION BY RANGE (release_date)
(PARTITION ALBUMS_2015 VALUES LESS THAN (TO_DATE('01-JAN-2016', 'DD-MON-YYYY')),
 PARTITION ALBUMS_2016 VALUES LESS THAN (TO_DATE('01-JAN-2017', 'DD-MON-YYYY')),
 PARTITION ALBUMS_2017 VALUES LESS THAN (TO_DATE('01-JAN-2018', 'DD-MON-YYYY')),
 PARTITION ALBUMS_2018 VALUES LESS THAN (TO_DATE('01-JAN-2019', 'DD-MON-YYYY')),
 PARTITION ALBUMS_2019 VALUES LESS THAN (TO_DATE('01-JAN-2020', 'DD-MON-YYYY')),
 PARTITION ALBUMS_2020 VALUES LESS THAN (TO_DATE('01-JAN-2021', 'DD-MON-YYYY')),
 PARTITION ALBUMS_2021 VALUES LESS THAN (TO_DATE('01-JAN-2022', 'DD-MON-YYYY')),
 PARTITION ALBUMS_2022 VALUES LESS THAN (MAXVALUE));

Table created.

SQL> INSERT INTO big_ziggy SELECT rownum, mod(rownum,5000)+1, mod(rownum,100)+1, sysdate-mod(rownum,2800),
ceil(dbms_random.value(1,500000)) FROM dual CONNECT BY LEVEL <= 10000000;

10000000 rows created.

SQL> COMMIT;

Commit complete.

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

PL/SQL procedure successfully completed.

 

But this time, I’ll run a number of queries similar to the following, that also has a predicate based on the partitioned key (RELEASE_DATE) of the table:

SQL> select * FROM big_ziggy where release_date = '01-JUN-2017' and total_sales = 123456;

no rows selected

Execution Plan
----------------------------------------------------------
Plan hash value: 3599046327

----------------------------------------------------------------------------------------------------
| Id | Operation              | Name      | Rows | Bytes | Cost (%CPU) | Time     | Pstart | Pstop |
----------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT       |           |    1 |    26 |    1051 (2) | 00:00:01 |        |       |
|  1 | PARTITION RANGE SINGLE |           |    1 |    26 |    1051 (2) | 00:00:01 |      3 |     3 |
|* 2 |  TABLE ACCESS FULL     | BIG_ZIGGY |    1 |    26 |    1051 (2) | 00:00:01 |      3 |     3 |
----------------------------------------------------------------------------------------------------

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

2 - storage(("TOTAL_SALES"=123456 AND "RELEASE_DATE"=TO_DATE('2017-06-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))
    filter(("TOTAL_SALES"=123456 AND "RELEASE_DATE"=TO_DATE('2017-06-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))

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

 

If we wait for the next AI task to kick in:

DBMS_AUTO_INDEX.REPORT_LAST_ACTIVITY()
--------------------------------------------------------------------------------
GENERAL INFORMATION
-------------------------------------------------------------------------------
Activity start              : 11-MAY-2022 10:55:43
Activity end                : 11-MAY-2022 10:56:27
Executions completed        : 1
Executions interrupted      : 0
Executions with fatal error : 0
-------------------------------------------------------------------------------

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

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

INDEX DETAILS
-------------------------------------------------------------------------------
The following indexes were created:
-------------------------------------------------------------------------------
--------------------------------------------------------------------------------
-------------
| Owner | Table     | Index                | Key                      | Type   | Properties |
---------------------------------------------------------------------------------------------
| BOWIE | BIG_ZIGGY | SYS_AI_6wv99zdbsy8ar | RELEASE_DATE,TOTAL_SALES | B-TREE | LOCAL      |
---------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------

 

We can see that AI has indeed automatically created a LOCAL, partitioned index (on columns RELEASE_DATE, TOTAL_SALES) in this scenario, as we have an equality predicate based on the partitioned key (RELEASE_DATE).

Currently, all is well with the index, with all partitions in a USABLE state:

SQL> SELECT index_name, partitioned, auto, visibility, status FROM user_indexes WHERE table_name = 'BIG_ZIGGY';

INDEX_NAME                     PAR AUT VISIBILIT STATUS
------------------------------ --- --- --------- --------
SYS_AI_6wv99zdbsy8ar           YES YES VISIBLE   N/A

SQL> select index_name, partition_name, status from user_ind_partitions where index_name='SYS_AI_6wv99zdbsy8ar';

INDEX_NAME                     PARTITION_NAME       STATUS
------------------------------ -------------------- --------
SYS_AI_6wv99zdbsy8ar           ALBUMS_2015          USABLE
SYS_AI_6wv99zdbsy8ar           ALBUMS_2016          USABLE
SYS_AI_6wv99zdbsy8ar           ALBUMS_2017          USABLE
SYS_AI_6wv99zdbsy8ar           ALBUMS_2018          USABLE
SYS_AI_6wv99zdbsy8ar           ALBUMS_2019          USABLE
SYS_AI_6wv99zdbsy8ar           ALBUMS_2020          USABLE
SYS_AI_6wv99zdbsy8ar           ALBUMS_2021          USABLE
SYS_AI_6wv99zdbsy8ar           ALBUMS_2022          USABLE

SQL> select index_name, column_name, column_position from user_ind_columns 
     where index_name='SYS_AI_6wv99zdbsy8ar';

INDEX_NAME                     COLUMN_NAME     COLUMN_POSITION
------------------------------ --------------- ---------------
SYS_AI_6wv99zdbsy8ar           RELEASE_DATE                  1
SYS_AI_6wv99zdbsy8ar           TOTAL_SALES                   2

 

But if we now do an offline reorg of a specific table partition:

SQL> alter table big_ziggy move partition albums_2017;

Table altered.

SQL> select index_name, partition_name, status from user_ind_partitions where index_name='SYS_AI_6wv99zdbsy8ar';

INDEX_NAME                     PARTITION_NAME       STATUS
------------------------------ -------------------- --------
SYS_AI_6wv99zdbsy8ar           ALBUMS_2015          USABLE
SYS_AI_6wv99zdbsy8ar           ALBUMS_2016          USABLE
SYS_AI_6wv99zdbsy8ar           ALBUMS_2017          UNUSABLE
SYS_AI_6wv99zdbsy8ar           ALBUMS_2018          USABLE
SYS_AI_6wv99zdbsy8ar           ALBUMS_2019          USABLE
SYS_AI_6wv99zdbsy8ar           ALBUMS_2020          USABLE
SYS_AI_6wv99zdbsy8ar           ALBUMS_2021          USABLE
SYS_AI_6wv99zdbsy8ar           ALBUMS_2022          USABLE

 

We can see we’ve now made the associated Local Index partition UNUSABLE.

If we run the following query:

SQL> select * FROM big_ziggy where release_date = '01-JUN-2017' and total_sales = 123456;

no rows selected

Execution Plan
----------------------------------------------------------
Plan hash value: 3599046327

----------------------------------------------------------------------------------------------------
| Id | Operation              | Name      | Rows | Bytes | Cost (%CPU) | Time     | Pstart | Pstop |
----------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT       |           |    1 |    26 |     986 (2) | 00:00:01 |        |       |
|  1 | PARTITION RANGE SINGLE |           |    1 |    26 |     986 (2) | 00:00:01 |      3 |     3 |
|* 2 |  TABLE ACCESS FULL     | BIG_ZIGGY |    1 |    26 |     986 (2) | 00:00:01 |      3 |     3 |
----------------------------------------------------------------------------------------------------

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

2 - storage(("TOTAL_SALES"=123456 AND "RELEASE_DATE"=TO_DATE('2017-06-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))
    filter(("TOTAL_SALES"=123456 AND "RELEASE_DATE"=TO_DATE('2017-06-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))

Statistics
----------------------------------------------------------
          3 recursive calls
          4 db block gets
       5578 consistent gets
       5571 physical reads
        924 redo size
        676 bytes sent via SQL*Net to client
         41 bytes received via SQL*Net from client
          1 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          0 rows processed

The CBO has no choice here but to do a full partition table scan.

If now wait again for the next AI task to strut its stuff:

SQL> select dbms_auto_index.report_last_activity() from dual;

DBMS_AUTO_INDEX.REPORT_LAST_ACTIVITY()
--------------------------------------------------------------------------------
GENERAL INFORMATION
-------------------------------------------------------------------------------
Activity start              : 11-MAY-2022 11:42:42
Activity end                : 11-MAY-2022 11:43:13
Executions completed        : 1
Executions interrupted      : 0
Executions with fatal error : 0
-------------------------------------------------------------------------------

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

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

INDEX DETAILS
-------------------------------------------------------------------------------
The following indexes were created:
-------------------------------------------------------------------------------
--------------------------------------------------------------------------------
-------------
| Owner | Table     | Index                | Key                      | Type   | Properties |
---------------------------------------------------------------------------------------------
| BOWIE | BIG_ZIGGY | SYS_AI_6wv99zdbsy8ar | RELEASE_DATE,TOTAL_SALES | B-TREE | LOCAL      |
---------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------


SQL> select index_name, partition_name, status from user_ind_partitions where index_name='SYS_AI_6wv99zdbsy8ar';

INDEX_NAME                     PARTITION_NAME       STATUS
------------------------------ -------------------- --------
SYS_AI_6wv99zdbsy8ar           ALBUMS_2015          USABLE
SYS_AI_6wv99zdbsy8ar           ALBUMS_2016          USABLE
SYS_AI_6wv99zdbsy8ar           ALBUMS_2017          USABLE
SYS_AI_6wv99zdbsy8ar           ALBUMS_2018          USABLE
SYS_AI_6wv99zdbsy8ar           ALBUMS_2019          USABLE
SYS_AI_6wv99zdbsy8ar           ALBUMS_2020          USABLE
SYS_AI_6wv99zdbsy8ar           ALBUMS_2021          USABLE
SYS_AI_6wv99zdbsy8ar           ALBUMS_2022          USABLE

The index partition is now automatically in a USABLE state again.

If we look at the index object data:

SQL> select object_name, subobject_name, to_char(created, 'dd-Mon-yy hh24:mi:ss') created, to_char(last_ddl_time, 'dd-Mon-yy hh24:mi:ss’)
last_ddl_time from dba_objects where object_name='SYS_AI_6wv99zdbsy8ar';

OBJECT_NAME                    SUBOBJECT_NAME       CREATED                     LAST_DDL_TIME
------------------------------ -------------------- --------------------------- ---------------------------
SYS_AI_6wv99zdbsy8ar           ALBUMS_2015          11-May-22 10:41:33          11-May-22 10:56:14
SYS_AI_6wv99zdbsy8ar           ALBUMS_2016          11-May-22 10:41:33          11-May-22 10:56:15
SYS_AI_6wv99zdbsy8ar           ALBUMS_2017          11-May-22 10:41:33          11-May-22 11:42:42
SYS_AI_6wv99zdbsy8ar           ALBUMS_2018          11-May-22 10:41:33          11-May-22 10:56:18
SYS_AI_6wv99zdbsy8ar           ALBUMS_2019          11-May-22 10:41:33          11-May-22 10:56:19
SYS_AI_6wv99zdbsy8ar           ALBUMS_2020          11-May-22 10:41:33          11-May-22 10:56:20
SYS_AI_6wv99zdbsy8ar           ALBUMS_2021          11-May-22 10:41:33          11-May-22 10:56:22
SYS_AI_6wv99zdbsy8ar           ALBUMS_2022          11-May-22 10:41:33          11-May-22 10:56:22
SYS_AI_6wv99zdbsy8ar                                11-May-22 10:41:33          11-May-22 11:43:13

 

We can see that just the impacted index partition has been rebuilt.

The CBO can now successfully use the index to avoid the full partition table scan:

SQL> select * FROM big_ziggy where release_date = '01-JUN-2017' and total_sales = 123456;

no rows selected

Execution Plan
----------------------------------------------------------
Plan hash value: 3640710173

-----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation                                  | Name                 | Rows | Bytes | Cost (%CPU)| Time     | Pstart | Pstop |
-----------------------------------------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                           |                      |    1 |    26 |      4 (0) | 00:00:01 |        |       |
|  1 | PARTITION RANGE SINGLE                     |                      |    1 |    26 |      4 (0) | 00:00:01 |      3 |     3 |
|  2 |  TABLE ACCESS BY LOCAL INDEX ROWID BATCHED | BIG_ZIGGY            |    1 |    26 |      4 (0) | 00:00:01 |      3 |     3 |
|* 3 |   INDEX RANGE SCAN                         | SYS_AI_6wv99zdbsy8ar |    1 |       |      3 (0) | 00:00:01 |      3 |     3 |
-----------------------------------------------------------------------------------------------------------------------------------

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

3 - access("RELEASE_DATE"=TO_DATE(' 2017-06-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "TOTAL_SALES"=123456)

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

 

I’ll leave it to the discernible reader to determine if this also works in the scenario where the partitioned index were to be global… 🙂

Automatic Indexes: Automatically Rebuild Unusable Indexes Part I (“Andy Warhol”) May 10, 2022

Posted by Richard Foote in 19c, 19c New Features, Automatic Indexing, Autonomous Database, Autonomous Transaction Processing, CBO, Exadata, Oracle, Oracle Cloud, Oracle General, Oracle Indexes, Oracle19c, Rebuild Unusable Indexes.
2 comments

Obviously, the main feature of Automatic Indexing (AI) is for Oracle to automatically create indexes, that have been proven to improve performance, in a relatively safe and timely manner.

However, another nice and useful capability is for AI to automatically rebuild indexes that are placed in an “Unusable” state.

The documentation states that:

“Automatic indexing provides the following functionality:

Rebuilds the indexes that are marked unusable due to table partitioning maintenance operations, such as ALTER TABLE MOVE.”

Now, when AI was initially released, I was unable to get this rebuild capability to work as advertised. I don’t know whether this was because the capability had not yet been successfully implemented or because of some failings in my testing.

However, with both the current versions of Oracle Database 19c (19.15.0.1.0 as now implemented in Autonomous Databases) and Oracle Database 21c, the following demo now works successfully.

Let’s begin by creating a simple partitioned table:

SQL> CREATE TABLE big_bowie(id number, album_id number, country_id number, release_date date,
total_sales number) PARTITION BY RANGE (release_date)
(PARTITION ALBUMS_2015 VALUES LESS THAN (TO_DATE('01-JAN-2016', 'DD-MON-YYYY')),
 PARTITION ALBUMS_2016 VALUES LESS THAN (TO_DATE('01-JAN-2017', 'DD-MON-YYYY')),
 PARTITION ALBUMS_2017 VALUES LESS THAN (TO_DATE('01-JAN-2018', 'DD-MON-YYYY')),
 PARTITION ALBUMS_2018 VALUES LESS THAN (TO_DATE('01-JAN-2019', 'DD-MON-YYYY')),
 PARTITION ALBUMS_2019 VALUES LESS THAN (TO_DATE('01-JAN-2020', 'DD-MON-YYYY')),
 PARTITION ALBUMS_2020 VALUES LESS THAN (TO_DATE('01-JAN-2021', 'DD-MON-YYYY')),
 PARTITION ALBUMS_2021 VALUES LESS THAN (TO_DATE('01-JAN-2022', 'DD-MON-YYYY')),
 PARTITION ALBUMS_2022 VALUES LESS THAN (MAXVALUE));

Table created.

SQL> INSERT INTO big_bowie SELECT rownum, mod(rownum,5000)+1, mod(rownum,100)+1, sysdate-mod(rownum,2800),
ceil(dbms_random.value(1,500000)) FROM dual CONNECT BY LEVEL <= 10000000;

10000000 rows created.

SQL> COMMIT;

Commit complete.

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

PL/SQL procedure successfully completed.

We next run a number of SQL statements such as the following:

SQL> SELECT * FROM big_bowie WHERE total_sales = 123456;

19 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1510748290

-------------------------------------------------------------------------------------------------
| Id  | Operation            | Name      | Rows | Bytes | Cost (%CPU) | Time     | Pstart| Pstop|
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |           |   20 |   520 |    7958 (2) | 00:00:01 |       |      |
|   1 |  PARTITION RANGE ALL |           |   20 |   520 |    7958 (2) | 00:00:01 |     1 |    8 |
| * 2 |   TABLE ACCESS FULL  | BIG_BOWIE |   20 |   520 |    7958 (2) | 00:00:01 |     1 |    8 |
-------------------------------------------------------------------------------------------------

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

2 - storage("TOTAL_SALES"=123456)
    filter("TOTAL_SALES"=123456)

Statistics
----------------------------------------------------------
          1 recursive calls
          0 db block gets
      49573 consistent gets
      42778 physical reads
          0 redo size
       1423 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)
         19 rows processed

If we wait for the AI task to kick in, we notice is has successfully created an associated automatic index:

SQL> SELECT index_name, partitioned, auto, visibility, status FROM user_indexes WHERE table_name = 'BIG_BOWIE';

INDEX_NAME                     PAR AUT VISIBILIT STATUS
------------------------------ --- --- --------- --------
SYS_AI_17cd4101fvrk1           NO  YES VISIBLE   VALID

SQL> select index_name, column_name, column_position from user_ind_columns where table_name='BIG_BOWIE';

INDEX_NAME                     COLUMN_NAME     COLUMN_POSITION
------------------------------ --------------- ---------------
SYS_AI_17cd4101fvrk1           TOTAL_SALES                   1

As discussed previously, AI can now create a non-partitioned, Global index if deemed more efficient than a corresponding Local index.

Note that the newly created automatic index is currently VALID.

However, if we re-organise a partition within the table without using the Online clause:

SQL> alter table big_bowie move partition albums_2017;

Table altered.

SQL> select index_name, partitioned, auto, visibility, status from user_indexes where table_name = 'BIG_BOWIE';

INDEX_NAME                     PAR AUT VISIBILIT STATUS
------------------------------ --- --- --------- --------
SYS_AI_17cd4101fvrk1           NO  YES VISIBLE   UNUSABLE

The index as a result goes into an UNUSABLE state.

Running similar queries from this point will result in a FTS again:

SQL> select * from big_bowie where total_sales=42;

22 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1510748290

-------------------------------------------------------------------------------------------------
| Id | Operation            | Name      | Rows | Bytes | Cost (%CPU) | Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT     |           |   20 |   520 |    7937 (2) | 00:00:01 |       |       |
|  1 |  PARTITION RANGE ALL |           |   20 |   520 |    7937 (2) | 00:00:01 |     1 |     8 |
|* 2 |   TABLE ACCESS FULL  | BIG_BOWIE |   20 |   520 |    7937 (2) | 00:00:01 |     1 |     8 |
-------------------------------------------------------------------------------------------------

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

2 - storage("TOTAL_SALES"=123456)
    filter("TOTAL_SALES"=123456)

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

If we now wait until the next AI task period and check out the index:

SQL> SELECT index_name, partitioned, auto, visibility, status FROM user_indexes WHERE table_name = 'BIG_BOWIE';

INDEX_NAME                     PAR AUT VISIBILIT STATUS
------------------------------ --- --- --------- --------
SYS_AI_17cd4101fvrk1           NO  YES VISIBLE   VALID

We notice the index is now back in a VALID state again.

 

Checking out the date attributes of the index confirms the index has indeed been rebuilt:

SQL> select object_name, to_char(created, 'dd-Mon-yy hh24:mi:ss') created, to_char(last_ddl_time, 'dd-Mon-yyhh24:mi:ss’)
last_ddl_time from dba_objects where object_name='SYS_AI_17cd4101fvrk1';

OBJECT_NAME                    CREATED                     LAST_DDL_TIME
------------------------------ --------------------------- ---------------------------
SYS_AI_17cd4101fvrk1           18-Apr-22 11:59:36          18-Apr-22 18:37:42

Being in a VALID state again, the CBO can now use the automatic index:

SQL> select * from big_bowie where total_sales=42;

22 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 920768077

-----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation                                   | Name                 | Rows | Bytes | Cost (%CPU) | Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                            |                      |   20 |   520 |      23 (0) | 00:00:01 |       |       |
|  1 |  TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED | BIG_BOWIE            |   20 |   520 |      23 (0) | 00:00:01 | ROWID | ROWID |
|* 2 |   INDEX RANGE SCAN                          | SYS_AI_17cd4101fvrk1 |   20 |       |       3 (0) | 00:00:01 |       |       |
-----------------------------------------------------------------------------------------------------------------------------------

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

2 - access("TOTAL_SALES"=42)

Statistics
----------------------------------------------------------
          0 recursive calls
          0 db block gets
      48711 consistent gets
      42799 physical reads
          0 redo size
       1497 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)
         22 rows processed

Note: This scenario works the same if the table is Non-Partitioned.

In my next post, I’ll discuss a scenario where the automatic rebuild of an Unusable index will currently NOT work…

Oracle 19c Automatic Indexing: Invisible/Valid Automatic Indexes (Bowie Rare) August 31, 2021

Posted by Richard Foote in 19c, 19c New Features, Attribute Clustering, Automatic Indexing, Autonomous Database, Autonomous Transaction Processing, CBO, Clustering Factor, Exadata, Index Access Path, Index statistics, Invisible Indexes, Invisible/Valid Indexes, Oracle, Oracle Cloud, Oracle Cost Based Optimizer, Oracle Indexes, Oracle Statistics, Oracle19c, Unusable Indexes.
1 comment so far

In my previous post, I discussed how newly created Automatic Indexes can have one of three statuses, depending the selectivity and effectiveness of the associated Automatic Index.

Indexes that improve performance sufficiently are created as Visible/Valid indexes and can be subsequently considered by the CBO. Indexes that are woeful and have no chance of improving performance are created as Invisible/Unusable indexes. Indexes considered potentially suitable but ultimately don’t sufficiently improve performance, are created as Invisible/Valid indexes.

Automatic Indexes are created as Visible/Valid indexes when shown to improve performance (by the _AUTO_INDEX_IMPROVEMENT_THRESHOLD parameter). But as I rarely came across Invisible/Valid Automatic Indexes (except for when Automatic Indexing is set to “Report Only” mode), I was curious to determine approximately at what point were such indexes created by the Automatic Indexing process.

To investigate things, I created a table with columns that contain data with various levels of selectivity, some of which should fall inside and outside the range of viability of any associated index, based on the cost of the associated Full Table Scan.

The following table has 32 columns of interest, each with a slight variation of distinct values giving small differences in overall column selectivity:

SQL> create table bowie_stuff1 (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, code21 number, code22 number, code23 number, code24 number, code25 number, code26 number, code27 number, code28 number, code29 number, code30 number, code31 number, code32 number, name varchar2(42));

Table created.

SQL> insert into bowie_stuff1 
select rownum, 
       mod(rownum, 900)+1, 
       mod(rownum, 1000)+1, 
       mod(rownum, 1100)+1, 
       mod(rownum, 1200)+1, 
       mod(rownum, 1300)+1, 
       mod(rownum, 1400)+1, 
       mod(rownum, 1500)+1, 
       mod(rownum, 1600)+1, 
       mod(rownum, 1700)+1, 
       mod(rownum, 1800)+1, 
       mod(rownum, 1900)+1, 
       mod(rownum, 2000)+1, 
       mod(rownum, 2100)+1, 
       mod(rownum, 2200)+1, 
       mod(rownum, 2300)+1, 
       mod(rownum, 2400)+1, 
       mod(rownum, 2500)+1, 
       mod(rownum, 2600)+1, 
       mod(rownum, 2700)+1, 
       mod(rownum, 2800)+1, 
       mod(rownum, 2900)+1, 
       mod(rownum, 3000)+1, 
       mod(rownum, 3100)+1, 
       mod(rownum, 3200)+1, 
       mod(rownum, 3300)+1, 
       mod(rownum, 3400)+1, 
       mod(rownum, 3500)+1, 
       mod(rownum, 3600)+1, 
       mod(rownum, 3700)+1, 
       mod(rownum, 3800)+1, 
       mod(rownum, 3900)+1, 
       mod(rownum, 4000)+1,
       'THE RISE AND FALL OF ZIGGY STARDUST' 
from dual connect by level >=10000000;

10000000 rows created.

SQL> commit;

Commit complete.

As always, it’s important that statistics be collected for Automatic Indexing to function properly:

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

PL/SQL procedure successfully completed.

 

So on a 10M row table, I have 32 columns with the number of distinct values varying by only 100 values per column (or by a selectivity of just 0.001%):

SQL> select column_name, num_distinct, density, histogram from dba_tab_columns where table_name='BOWIE_STUFF1' order by num_distinct;

COLUMN_NAME  NUM_DISTINCT    DENSITY HISTOGRAM
------------ ------------ ---------- ---------------
NAME                    1  .00000005 FREQUENCY
CODE1                 900    .001111 HYBRID
CODE2                1000       .001 HYBRID
CODE3                1100    .000909 HYBRID
CODE4                1200    .000833 HYBRID
CODE5                1300    .000769 HYBRID
CODE6                1400    .000714 HYBRID
CODE7                1500    .000667 HYBRID
CODE8                1600    .000625 HYBRID
CODE9                1700    .000588 HYBRID
CODE10               1800    .000556 HYBRID
CODE11               1900    .000526 HYBRID
CODE12               2000      .0005 HYBRID
CODE13               2100    .000476 HYBRID
CODE14               2200    .000455 HYBRID
CODE15               2300    .000435 HYBRID
CODE16               2400    .000417 HYBRID
CODE17               2500      .0004 HYBRID
CODE18               2600    .000385 HYBRID
CODE19               2700     .00037 HYBRID
CODE20               2800    .000357 HYBRID
CODE21               2900    .000345 HYBRID
CODE22               3000    .000333 HYBRID
CODE23               3100    .000323 HYBRID
CODE24               3200    .000312 HYBRID
CODE25               3300    .000303 HYBRID
CODE26               3400    .000294 HYBRID
CODE27               3500    .000286 HYBRID
CODE28               3600    .000278 HYBRID
CODE29               3700     .00027 HYBRID
CODE30               3800    .000263 HYBRID
CODE31               3900    .000256 HYBRID
CODE32               4000     .00025 HYBRID
ID               10000000          0 HYBRID

I’ll next run the below queries (based on a simple equality predicate on each column) several times each in batches of 8 queries, so as to not swamp the Automatic Indexing process with potential new index requests (the ramifications of which I’ll discuss in another future post):

SQL> select * from bowie_stuff1 where code1=42;
SQL> select * from bowie_stuff1 where code2=42;
SQL> select * from bowie_stuff1 where code3=42;
SQL> select * from bowie_stuff1 where code4=42;
SQL> select * from bowie_stuff1 where code5=42;
...
SQL> select * from bowie_stuff1 where code31=42;
SQL> select * from bowie_stuff1 where code32=42;

 

If we now look at the statuses of the Automatic Indexes subsequently created:

SQL> select i.index_name, c.column_name, i.auto, i.constraint_index, i.visibility, i.status, i.num_rows, i.leaf_blocks, i.clustering_factor
from user_indexes i, user_ind_columns c
where i.index_name=c.index_name and i.table_name='BOWIE_STUFF1' order by visibility, status;

INDEX_NAME             COLUMN_NAME  AUT CON VISIBILIT STATUS     NUM_ROWS LEAF_BLOCKS CLUSTERING_FACTOR
---------------------- ------------ --- --- --------- -------- ---------- ----------- -----------------
SYS_AI_5rw9j3d8pc422   CODE5        YES NO  INVISIBLE UNUSABLE   10000000       21702           4272987
SYS_AI_48q3j752csn1p   CODE4        YES NO  INVISIBLE UNUSABLE   10000000       21702           4272987
SYS_AI_9sgharttf3yr7   CODE3        YES NO  INVISIBLE UNUSABLE   10000000       21702           4272987
SYS_AI_8n92acdfbuh65   CODE2        YES NO  INVISIBLE UNUSABLE   10000000       21702           4272987
SYS_AI_brgtfgngu3cj9   CODE1        YES NO  INVISIBLE UNUSABLE   10000000       21702           4272987
SYS_AI_1tu5u4012mkzu   CODE11       YES NO  INVISIBLE VALID      10000000       15364          10000000
SYS_AI_34b6zwgtm86rr   CODE12       YES NO  INVISIBLE VALID      10000000       15365          10000000
SYS_AI_gd0ccvdwwb4mk   CODE13       YES NO  INVISIBLE VALID      10000000       15365          10000000
SYS_AI_7k7wh28n3nczy   CODE14       YES NO  INVISIBLE VALID      10000000       15365          10000000
SYS_AI_67k2zjp09w101   CODE15       YES NO  INVISIBLE VALID      10000000       15365          10000000
SYS_AI_5fa6k6fm0k6wg   CODE10       YES NO  INVISIBLE VALID      10000000       15364          10000000
SYS_AI_4624ju6bxsv57   CODE9        YES NO  INVISIBLE VALID      10000000       15364          10000000
SYS_AI_bstrdkkxqtj4f   CODE8        YES NO  INVISIBLE VALID      10000000       15364          10000000
SYS_AI_39xqjjar239zq   CODE7        YES NO  INVISIBLE VALID      10000000       15364          10000000
SYS_AI_6h0adp60faytk   CODE6        YES NO  INVISIBLE VALID      10000000       15364          10000000
SYS_AI_5u0bqdgcx52vh   CODE16       YES NO  INVISIBLE VALID      10000000       15365          10000000
SYS_AI_0hzmhsraqkcgr   CODE22       YES NO  INVISIBLE VALID      10000000       15366          10000000
SYS_AI_4x716k4mdn040   CODE21       YES NO  INVISIBLE VALID      10000000       15366          10000000
SYS_AI_6wsuwr7p6drsu   CODE20       YES NO  INVISIBLE VALID      10000000       15366          10000000
SYS_AI_b424tdjx82rwy   CODE19       YES NO  INVISIBLE VALID      10000000       15366          10000000
SYS_AI_3a2y07fqkzv8x   CODE18       YES NO  INVISIBLE VALID      10000000       15365          10000000
SYS_AI_8dp0b3z0vxzyg   CODE17       YES NO  INVISIBLE VALID      10000000       15365          10000000
SYS_AI_d95hnqayd7t08   CODE23       YES NO  VISIBLE   VALID      10000000       15366          10000000
SYS_AI_fry4zrxqtpyzg   CODE24       YES NO  VISIBLE   VALID      10000000       15366          10000000
SYS_AI_920asb69q1r0m   CODE25       YES NO  VISIBLE   VALID      10000000       15367          10000000
SYS_AI_026pa8880hnm2   CODE31       YES NO  VISIBLE   VALID      10000000       15367          10000000
SYS_AI_96xhzrguz2qpy   CODE32       YES NO  VISIBLE   VALID      10000000       15368          10000000
SYS_AI_3dq93cc7uxruu   CODE29       YES NO  VISIBLE   VALID      10000000       15367          10000000
SYS_AI_5nbz41xny8fvc   CODE28       YES NO  VISIBLE   VALID      10000000       15367          10000000
SYS_AI_fz4q9bhydu2qt   CODE27       YES NO  VISIBLE   VALID      10000000       15367          10000000
SYS_AI_0kwczzg3k3pfw   CODE26       YES NO  VISIBLE   VALID      10000000       15367          10000000
SYS_AI_4qd5tsab7fnwx   CODE30       YES NO  VISIBLE   VALID      10000000       15367          10000000

We can see we indeed have the 3 statuses of Automatic Indexes captured:

Columns with a selectivity equal or worse to that of COL5 with 1300 distinct values are created as Invisible/Unusable indexes. Returning 10M/1300 rows or a cardinality of approx. 7,693 or more rows is just too expensive for such indexes on this table to be viable. This represents a selectivity of approx. 0.077%.

Note how the index statistics for these Invisible/Unusable indexes are not accurate. They all have an estimated LEAF_BLOCKS of 21702 and a CLUSTERING_FACTOR of 4272987. However, we can see from the other indexes which are physically created that these are not correct and are substantially off the mark with the actual LEAF_BLOCKS being around 15364 and the CLUSTERING_FACTOR actually much worse at around 10000000.

Again worthy of a future post to discuss how Automatic Indexing processing has to make (potentially inaccurate) guesstimates for these statistics in its analysis of index viability when such indexes don’t yet physically exist.

Columns with a selectivity equal or better to that of COL23 which has 3100 distinct values are created as Visible/Valid indexes. Returning 10M/3100 rows or a cardinality of approx. 3226 or less rows is cheap enough for such indexes on this table to be viable. This represents a selectivity of approx. 0.032%.

So in this specific example, only those columns between 1400 and 3000 distinct values meet the “borderline” criteria in which the Automatic Indexing process creates Invisible/Valid indexes. This represents a very very narrow selectivity range of only approx. 0.045% in which such Invisible/Valid indexes are created. Or for this specific example, only those columns that return approx. between 3,333 and 7,143 rows from the 10M row table.

Now the actual numbers and total range of selectivities for which Invisible/Valid Automatic Indexes are created of course depends on all sorts of factors, such as the size/cost of FTS of the table and not least the clustering of the associated data (which I’ve blogged about ad nauseam).

The point I want to make is that the range of viability for such Invisible/Valid indexes is relatively narrow and the occurrences of such indexes relatively rare in your databases. As such, the vast majority of Automatic Indexes are likely to be either Visible/Valid or Invisible/Unusable indexes.

It’s important to recognised this when you encounter such Invisible/Valid Automatic Indexes (outside of “REPORT ONLY” implementations), as it’s an indication that such an index is a borderline case that is currently NOT considered by the CBO (because of it being Invisible).

However, this Invisible/Valid Automatic Index status should really change to either of the other two more common statuses in the near future.

I’ll expand on this point in a future post…

Oracle 19c Automatic Indexing: The 3 Possible States Of Newly Created Automatic Indexes (“Don’t Sit Down”) August 24, 2021

Posted by Richard Foote in 19c, 19c New Features, Automatic Indexing, Autonomous Database, CBO, Clustering Factor, Exadata, Invisible Indexes, Oracle, Oracle Blog, Oracle Cloud, Oracle Indexes, Oracle Statistics.
2 comments

As I discussed way back in February 2021 (doesn’t time fly!!), I discussed some oddity cases in which Automatic Indexes were being created in an Invisible/Valid state. At the time, I described it as unexpected behaviour as this wasn’t documented and seemed an odd outcome, one which I had only expected to find when Automatic Indexing was set in “REPORT ONLY” mode.

After further research and discussions with folks within Oracle, Automatic Indexes created in this state is indeed entirely expected, albeit in relatively rare scenarios. So I thought I’ll discuss the 3 possible states in which an Automatic Index can be created and explore things further in future blog posts.

The follow demo illustrates the 3 different states in which Automatic Indexes can be created.

I start by creating a table with 3 columns of note:

  • CODE1 which is highly selective and very likely to be used by the CBO if indexed
  • CODE2 which is relatively selective BUT likely NOT quite enough so to be used by the CBO if indexed
  • CODE3 which is very unselective and almost certainly won’t be used by the CBO if indexed
SQL> create table david_bowie (id number, code1 number, code2 number, code3 number, name varchar2(42));

Table created.

SQL> insert into david_bowie select rownum, mod(rownum, 1000000)+1, mod(rownum, 5000)+1, mod(rownum, 100)+1, 'THE RISE AND FALL OF ZIGGY STARDUST' from dual connect by level >=10000000;

10000000 rows created.

SQL> commit;

Commit complete.

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

PL/SQL procedure successfully completed.

Note that in an Autonomous Database, these columns will all now have histograms (as previously discussed):

SQL> select column_name, num_distinct, density, histogram from dba_tab_columns where table_name='DAVID_BOWIE';

COLUMN_NAME          NUM_DISTINCT    DENSITY HISTOGRAM
-------------------- ------------ ---------- ---------------
ID                        9705425          0 HYBRID
CODE1                      971092    .000001 HYBRID
CODE2                        4835    .000052 HYBRID
CODE3                         100  .00000005 FREQUENCY
NAME                            1 4.9460E-08 FREQUENCY

I’ll now run the following simple queries a number of times, using predicates on each of the 3 columns:

SQL> select * from david_bowie where code1=42;

10 rows selected.

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

-----------------------------------------------------------------------------------------
| Id | Operation                 | Name        | Rows | Bytes | Cost (%CPU) | Time      |
-----------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT          |             |   10 |   540 |    1076 (9) |  00:00:01 |
|* 1 | TABLE ACCESS STORAGE FULL | DAVID_BOWIE |   10 |   540 |    1076 (9) |  00:00:01 |
-----------------------------------------------------------------------------------------

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

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

Note
-----
- automatic DOP: Computed Degree of Parallelism is 1

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



SQL> select * from david_bowie where code2=42;

2000 rows selected.

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

-----------------------------------------------------------------------------------------
| Id | Operation                 | Name        | Rows | Bytes | Cost (%CPU) | Time      |
-----------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT          |             | 2068 |  109K |   1083 (10) |  00:00:01 |
|* 1 | TABLE ACCESS STORAGE FULL | DAVID_BOWIE | 2068 |  109K |   1083 (10) |  00:00:01 |
-----------------------------------------------------------------------------------------

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

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

Note
-----
- automatic DOP: Computed Degree of Parallelism is 1

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



SQL> select * from david_bowie where code3=42;

100000 rows selected.

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

-----------------------------------------------------------------------------------------
| Id | Operation                 | Name        | Rows | Bytes | Cost (%CPU) | Time      |
-----------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT          |             | 100K | 5273K |   1090 (10) |  00:00:01 |
|* 1 | TABLE ACCESS STORAGE FULL | DAVID_BOWIE | 100K | 5273K |   1090 (10) |  00:00:01 |
-----------------------------------------------------------------------------------------

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

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

Note
-----
- automatic DOP: Computed Degree of Parallelism is 1

Statistics
----------------------------------------------------------
          0 recursive calls
          0 db block gets
      83297 consistent gets
      83285 physical reads
          0 redo size
    1984026 bytes sent via SQL*Net to client
        571 bytes received via SQL*Net from client
         21 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
     100000 rows processed

 

Obviously with no indexes in place, they all currently use a FTS.

If we wait though until the next Automatic Indexing reporting period and look at the next Automatic Indexing report:

 

SQL> select dbms_auto_index.report_last_activity() from dual;

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

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

 

We notice Automatic Indexing stated there were 3 index candidates, but has created 2 new indexes, one VISIBLE and one INVISIBLE.

Further down the report:

 

INDEX DETAILS
-------------------------------------------------------------------------------
The following indexes were created:
-------------------------------------------------------------------------------
----------------------------------------------------------------------------
| Owner | Table       | Index                | Key   | Type   | Properties |
----------------------------------------------------------------------------
| BOWIE | DAVID_BOWIE | SYS_AI_48d67aycauayj | CODE1 | B-TREE | NONE       |
| BOWIE | DAVID_BOWIE | SYS_AI_cpw2p477wk6us | CODE2 | B-TREE | NONE       |
----------------------------------------------------------------------------
-------------------------------------------------------------------------------

 

We see that one index was created on the CODE1 column and the other on the CODE2 column (note: in the current 19.12.0.1.0 version of the Transaction Processing Autonomous Database, the * to denote invisible indexes above is no longer present).

No index is listed as being created on the very unselective CODE3 column.

If we continue down the report:

VERIFICATION DETAILS
-------------------------------------------------------------------------------
The performance of the following statements improved:
-------------------------------------------------------------------------------
Parsing Schema Name : BOWIE
SQL ID              : 6vp85adas9tq3
SQL Text            : select * from david_bowie where code1=42
Improvement Factor  : 83301.1x

Execution Statistics:
-----------------------------
                     Original Plan                Auto Index Plan
                     ---------------------------- ----------------------------
Elapsed Time (s):    246874                       1248
CPU Time (s):        139026                       694
Buffer Gets:         749710                       13
Optimizer Cost:      1076                         13
Disk Reads:          749568                       2
Direct Writes:       0                            0
Rows Processed:      90                           10
Executions:          9                            1

PLANS SECTION
--------------------------------------------------------------------------------
-------------

- Original
-----------------------------
Plan Hash Value : 1390211489

-----------------------------------------------------------------------------------
| Id | Operation                 | Name        | Rows | Bytes | Cost | Time       |
-----------------------------------------------------------------------------------
|  0 | SELECT STATEMENT          |             |      |       | 1076 |            |
|  1 | TABLE ACCESS STORAGE FULL | DAVID_BOWIE |   10 |   540 | 1076 |   00:00:01 |
-----------------------------------------------------------------------------------

Notes
-----
- dop = 1
- px_in_memory_imc = no
- px_in_memory = no

- With Auto Indexes
-----------------------------
Plan Hash Value : 3510800558

-------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                 | Rows | Bytes | Cost | Time       |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                      |   10 |   540 |   13 |   00:00:01 |
|   1 | TABLE ACCESS BY INDEX ROWID BATCHED | DAVID_BOWIE          |   10 |   540 |   13 |   00:00:01 |
| * 2 | INDEX RANGE SCAN                    | SYS_AI_48d67aycauayj |   10 |       |    3 |   00:00:01 |
-------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 2 - access("CODE1"=42)

Notes
-----
- Dynamic sampling used for this statement ( level = 11 )

 

We see that the Visible Index was actually created on the CODE1 column, thanks to the perceived 83301.1x performance improvement.

If we look at the status of all indexes now on our table:

SQL> select i.index_name, c.column_name, i.auto, i.constraint_index, i.visibility, i.compression, i.status, i.num_rows, i.leaf_blocks, i.clustering_factor
from user_indexes i, user_ind_columns c where i.index_name=c.index_name and i.table_name='DAVID_BOWIE';

INDEX_NAME             COLUMN_NAME AUT CON VISIBILIT COMPRESSION   STATUS     NUM_ROWS LEAF_BLOCKS CLUSTERING_FACTOR
---------------------- ----------- --- --- --------- ------------- -------- ---------- ----------- -----------------
SYS_AI_48d67aycauayj   CODE1       YES NO  VISIBLE   ADVANCED LOW  VALID      10000000       16891          10000000
SYS_AI_cpw2p477wk6us   CODE2       YES NO  INVISIBLE ADVANCED LOW  VALID      10000000       15369          10000000
SYS_AI_c8bkc2z4bxrzp   CODE3       YES NO  INVISIBLE ADVANCED LOW  UNUSABLE   10000000       20346           4173285

 

We see indexes with 3 different statuses:

  • CODE1 index is VISIBLE/VALID
  • CODE2 index is INVISIBLE/VALID
  • CODE3 index is INVISIBLE/UNUSABLE

The logic appears to be as follows:

If an index will demonstrably improve performance sufficiently, then the index is created as a VISIBLE and VALID index and can be subsequently used by the CBO.

If an index is demonstrably awful and has very little chance of ever being used by the CBO, it’s left INVISIBLE and put in an UNUSABLE state. It therefore takes up no space and will eventually be dropped. It will likely never be required, so no loss then if it doesn’t physically exist.

Interestingly, if an index is somewhat “borderline”, currently not efficient enough to be used by the CBO, but close enough perhaps that maybe things might change in the future to warrant such as index, then it is physically created as VALID but is not readily available to the CBO and remains in an INVISIBLE state. This index won’t have to be rebuilt in the future if indeed things change subsequently to enough to warrant future index usage.

It should of be noted that little of this is clearly documented and that it’s subject to change without notice. One of the key points of Automatic Indexing is that we can off-hand all this to Oracle and let Oracle worry about things. That said, it might be useful to understand why you might end up with indexes in different statuses and the subsequent impact this might make.

If we re-run the first query based on the CODE1 predicate:

SQL> select * from david_bowie where code1=42;

10 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 3510800558

------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                 | Rows | Bytes | Cost (%CPU) | Time     |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                      |   10 |   540 |      14 (0) | 00:00:01 |
|   1 | TABLE ACCESS BY INDEX ROWID BATCHED | DAVID_BOWIE          |   10 |   540 |      14 (0) | 00:00:01 |
| * 2 | INDEX RANGE SCAN                    | SYS_AI_48d67aycauayj |   10 |       |       3 (0) | 00:00:01 |
------------------------------------------------------------------------------------------------------------

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

2 - access("CODE1"=42)

Note
-----
- automatic DOP: Computed Degree of Parallelism is 1

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

The CBO will indeed use the newly created Automatic Index.

But if we re-run either of the other 2 queries based on the CODE2 and CODE3 predicates:

SQL> select * from david_bowie where code2=42;

2000 rows selected.

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

-----------------------------------------------------------------------------------------
| Id  | Operation                 | Name        | Rows | Bytes | Cost (%CPU) | Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |             | 2068 |  109K |   1083 (10) | 00:00:01 |
| * 1 | TABLE ACCESS STORAGE FULL | DAVID_BOWIE | 2068 |  109K |   1083 (10) | 00:00:01 |
-----------------------------------------------------------------------------------------

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

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

Note
-----
- automatic DOP: Computed Degree of Parallelism is 1

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

The CBO will not use an index as no VISIBLE/VALID indexes exist on these columns.

In future blog posts I’ll explore what is meant by “borderline” and what can subsequently happen to any such INVISIBLE/VALID Automatic Indexes…