The Fake Index Trap (“Nowhere Now”) May 2, 2023
Posted by Richard Foote in CBO, Drop Index, Fake Indexes, Index Internals, NOSEGMENT Option, Online DDL, Oracle, Oracle 21c, Oracle Bugs, Oracle General, Oracle Indexes, Oracle Indexing Internals Webinar, Oracle19c, Tablespace Management, Virtual Indexes.add a comment
In a recent correspondence, I was alerted to an issue in relation to the use of Virtual/Fake/Nosegment Indexes that I wasn’t aware of previously. Having a play, it appears this issue is still present in at least Oracle Database 21c, so I thought it worth a mention in case anyone else happens to fall into this trap.
I’ve discussed Virtual/Fake/Nosegment Indexes a number of times previously. These are indexes that do not exist as a physical segment (and so can be created almost immediately without consuming any storage), that can be used to determine if an index could potentially be used by the CBO if it were to be actually created.
Although such Fake Indexes don’t physically exist, they can cause issues if forgotten…
To illustrate this issue, I’ll start by creating a new tablespace:
SQL> create tablespace BOWIE_TS datafile 'C:\ORADATA\ZIGGY\ZIGGYPDB1\BOWIE_TS.DBF' size 100M; Tablespace created.
Next, I’ll create and populate a table in this BOWIE_TS tablespace:
SQL> create table bowie_test (id number, name varchar2(42)) tablespace bowie_ts; Table created. SQL> insert into bowie_test select rownum, 'DAVID BOWIE' from dual connect by level <=10000; 10000 rows created. SQL> commit; Commit complete. SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'BOWIE_TEST'); PL/SQL procedure successfully completed.
I’ll next create a Virtual/Fake index, using the NOSEGMENT option:
SQL> create index bowie_test_id_i on bowie_test(id) nosegment tablespace bowie_ts; Index created.
We note this Fake Index is NOT listed in either USER_INDEXES or USER_SEGMENTS:
SQL> select index_name, tablespace_name from user_indexes where table_name='BOWIE_TEST';
no rows selected
SQL> select segment_name, segment_type, tablespace_name from user_segments
where segment_name='BOWIE_TEST_ID_I';
no rows selected
If we run a basic, highly selective query on this table:
SQL> select * from bowie_test where id=42;
Execution Plan
----------------------------------------------------------
Plan hash value: 65548668
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 16 | 11 (0) | 00:00:01 |
|* 1 | TABLE ACCESS FULL | BOWIE_TEST | 1 | 16 | 11 (0) | 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"=42)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
38 consistent gets
0 physical reads
0 redo size
648 bytes sent via SQL*Net to client
52 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
We notice the CBO uses a FTS. The Fake Index is NOT considered by default.
However, if we set the session as follows and re-run the query:
SQL> alter session set "_use_nosegment_indexes" = true;
Session altered.
SQL> select * from bowie_test where id=42;
Execution Plan
----------------------------------------------------------
Plan hash value: 1280686875
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 16 | 2 (0) | 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED | BOWIE_TEST | 1 | 16 | 2 (0) | 00:00:01 |
|* 2 | INDEX RANGE SCAN | BOWIE_TEST_ID_I | 1 | | 1 (0) | 00:00:01 |
-------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=42)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
38 consistent gets
0 physical reads
0 redo size
648 bytes sent via SQL*Net to client
52 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
We can see the CBO appears to now use the Fake Index, but as it doesn’t actually physically exist, actually uses a FTS behind the scenes (the number of consistent gets is evidence of this). But at least we now know the CBO would at least consider such an index if it physically existed.
We now decide to drop the tablespace and so first try to MOVE the table to another tablespace using the ONLINE option:
SQL> alter table bowie_test move online tablespace users; alter table bowie_test move online tablespace users * ERROR at line 1: ORA-14808: table does not support ONLINE MOVE TABLE because of the presence of nosegment index
The error message clearly states we can’t move the table ONLINE if such a Fake/Nosegment Index exists. This is our official warning of the potential danger to come…
We try to move the table using the default OFFLINE method:
SQL> alter table bowie_test move tablespace users; Table altered.
We have now successfully moved the table to another tablespace.
If we check to see if we have any other segments within the tablespace yto be dropped:
SQL> select segment_name from dba_segments where tablespace_name='BOWIE_TS'; no rows selected
Oracle tells us that no, we do NOT have any current segments in this tablespace.
So it’s now safe to purge and drop this tablespace (or so we think):
SQL> purge tablespace bowie_ts; Tablespace purged. SQL> drop tablespace bowie_ts; Tablespace dropped.
The tablespace has been successfully dropped.
However, if we now re-run the query on this table:
SQL> select * from bowie_test where id=42; select * from bowie_test where id=42 * ERROR at line 1: ORA-00959: tablespace 'BOWIE_TS' does not exist
We get this unexpected error that the tablespace BOWIE_TS does not exist.
BUT, we already know the tablespace doesn’t exist, we’ve just dropped it !!!
So why are we getting this error?
It’s all due to the damn Fake Index we created previously.
Although there is no physical index segment for our Fake Index, there are still some internal Data Dictionary links between the Fake Index and the tablespace it was associated with. The tablespace is gone, but NOT the Fake Index.
The only place where fake indexes can be easily found within Oracle, is within the USER_OBJECTS view:
SQL> select o.object_name, o.object_type, o.status from user_objects o left join user_indexes i on o.object_name=i.index_name where o.object_type='INDEX' and i.index_name is null; OBJECT_NAME OBJECT_TYPE STATUS ------------------------------ ----------------------- ------- BOWIE_TEST_ID_I INDEX VALID
To eliminate this error, we have to first drop the Fake Index associated with the dropped tablespace:
SQL> drop index bowie_test_id_i; Index dropped.
We can now safely run the query without error:
SQL> select * from bowie_test where id=42;
ID NAME
---------- ------------------------------------------
42 DAVID BOWIE
So if you do ever create Fake Indexes, don’t forget to drop them once you’ve finished experimenting with them.
ESPECIALLY if you ever decide to drop the tablespace into which they were associated. This is explained in part in Oracle Support Doc ID 1361049.1.
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…
Some Things To Consider Now ROWIDs Are Updated When Rows Migrate Part II (“Look Back In Anger”) February 24, 2023
Posted by Richard Foote in 19c, Autonomous Database, Autonomous Transaction Processing, BLEVEL, Changing ROWID, Index Internals, Leaf Blocks, Migrated Rows, Oracle, Oracle Blog, Oracle Cloud, Oracle General, Oracle Indexes, Oracle19c, Richard's Blog, ROWID.4 comments
Some weekend reading…
In my previous post, I discussed a couple of potential areas of concern with the ROWIDs of migrated rows now being updated on the fly in Oracle Autonomous Databases, namely that it can cause issues with applications that reply on stored ROWIDs not changing and that there are additional resources required to maintain such ROWIDs in corresponding indexes, especially if there are many indexes on a table.
In this post, I’ll discuss another issue to just bear in mind with this change in behaviour.
To illustrate, I’ll run a demo similar to the previous post, first creating and populating a table with no ENABLE ROW MOVEMENT set:
SQL> create table ziggy(id number, code1 number, code2 number, code3 number, code4 number, code5 number, code6 number, code7 number, code8 number, code9 number, code10 number, code11 number, code12 number, code13 number, code14 number, code15 number, code16 number, code17 number, code18 number, code19 number, code20 number, name varchar2(142)) PCTFREE 0; Table ZIGGY created. SQL> insert into ziggy SELECT rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, 'BOWIE' FROM dual CONNECT BY LEVEL <= 200000; 200,000 rows inserted. SQL> commit; Commit complete. SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'ZIGGY'); PL/SQL procedure successfully completed. SQL> analyze table ziggy compute statistics; Table ZIGGY analyzed.
I’ll next create a bunch of indexes on the table:
SQL> create index ziggy_id_i on ziggy(id); Index ZIGGY_ID_I created. SQL> create index ziggy_code1_i on ziggy(code1); Index ZIGGY_CODE1_I created. SQL> create index ziggy_code2_i on ziggy(code2); Index ZIGGY_CODE2_I created. SQL> create index ziggy_code3_i on ziggy(code3); Index ZIGGY_CODE3_I created. SQL> create index ziggy_code4_i on ziggy(code4); Index ZIGGY_CODE4_I created. SQL> create index ziggy_code5_i on ziggy(code5); Index ZIGGY_CODE5_I created. SQL> create index ziggy_code6_i on ziggy(code6); Index ZIGGY_CODE6_I created. SQL> create index ziggy_code7_i on ziggy(code7); Index ZIGGY_CODE7_I created. SQL> create index ziggy_code8_i on ziggy(code8); Index ZIGGY_CODE8_I created. SQL> create index ziggy_code9_i on ziggy(code9); Index ZIGGY_CODE9_I created. SQL> create index ziggy_code10_i on ziggy(code10); Index ZIGGY_CODE10_I created. SQL> create index ziggy_code11_i on ziggy(code11); Index ZIGGY_CODE11_I created. SQL> create index ziggy_code12_i on ziggy(code12); Index ZIGGY_CODE12_I created. SQL> create index ziggy_code13_i on ziggy(code13); Index ZIGGY_CODE13_I created. SQL> create index ziggy_code14_i on ziggy(code14); Index ZIGGY_CODE14_I created. SQL> create index ziggy_code15_i on ziggy(code15); Index ZIGGY_CODE15_I created. SQL> create index ziggy_code16_i on ziggy(code16); Index ZIGGY_CODE16_I created. SQL> create index ziggy_code17_i on ziggy(code17); Index ZIGGY_CODE17_I created. SQL> create index ziggy_code18_i on ziggy(code18); Index ZIGGY_CODE18_I created. SQL> create index ziggy_code19_i on ziggy(code19); Index ZIGGY_CODE19_I created. SQL> create index ziggy_code20_i on ziggy(code20); Index ZIGGY_CODE20_I created.
Let’s take note of the size of the table and its associated indexes:
SQL> select table_name, num_rows, blocks, empty_blocks, avg_space, avg_row_len, chain_cnt
from user_tables where table_name='ZIGGY';
TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE AVG_ROW_LEN CHAIN_CNT
_____________ ___________ _________ _______________ ____________ ______________ ____________
ZIGGY 200000 3268 60 857 113 0
SQL> select index_name, blevel, leaf_blocks, clustering_factor from user_indexes
where table_name='ZIGGY';
INDEX_NAME BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR
_________________ _________ ______________ ____________________
ZIGGY_CODE20_I 1 473 3250
ZIGGY_ID_I 1 473 3250
ZIGGY_CODE1_I 1 473 3250
ZIGGY_CODE2_I 1 473 3250
ZIGGY_CODE3_I 1 473 3250
ZIGGY_CODE4_I 1 473 3250
ZIGGY_CODE5_I 1 473 3250
ZIGGY_CODE6_I 1 473 3250
ZIGGY_CODE7_I 1 473 3250
ZIGGY_CODE8_I 1 473 3250
ZIGGY_CODE9_I 1 473 3250
ZIGGY_CODE10_I 1 473 3250
ZIGGY_CODE11_I 1 473 3250
ZIGGY_CODE12_I 1 473 3250
ZIGGY_CODE13_I 1 473 3250
ZIGGY_CODE14_I 1 473 3250
ZIGGY_CODE15_I 1 473 3250
ZIGGY_CODE16_I 1 473 3250
ZIGGY_CODE17_I 1 473 3250
ZIGGY_CODE18_I 1 473 3250
ZIGGY_CODE19_I 1 473 3250
We next perform an update on the table that will increase the row size sufficiently to result in a bunch of migrated rows:
SQL> update ziggy set name='THE RISE AND FALL OF ZIGGY STARDUST AND THE SPIDERS FROM MARS'; 200,000 rows updated. Elapsed: 00:00:07.716
I’ll then perform a COMMIT and look at differences in the table statistics:
SQL> commit; Commit complete. SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'ZIGGY'); PL/SQL procedure successfully completed. SQL> analyze table ziggy compute statistics; Table ZIGGY analyzed. SQL> select table_name, num_rows, blocks, empty_blocks, avg_space, avg_row_len, chain_cnt from user_tables where table_name='ZIGGY'; TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE AVG_ROW_LEN CHAIN_CNT _____________ ___________ _________ _______________ ____________ ______________ ____________ ZIGGY 200000 4906 86 415 170 56186
We can see that there are indeed a bunch of migrated/chained rows, some 56186 of them.
We also notice that the size of the table has increased to 4906 blocks (previously is was 3268). This increase is in large part due to the increased size of the NAME column value, but also partly due to the storage allocated to pointers that are stored in the original block to denote the new location of the migrated rows (as discussed previously here).
If we look at the current state of the indexes:
SQL> select index_name, blevel, leaf_blocks, clustering_factor
from user_indexes where table_name='ZIGGY';
INDEX_NAME BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR
_________________ _________ ______________ ____________________
ZIGGY_CODE7_I 1 473 3250
ZIGGY_CODE8_I 1 473 3250
ZIGGY_CODE9_I 1 473 3250
ZIGGY_CODE10_I 1 473 3250
ZIGGY_CODE11_I 1 473 3250
ZIGGY_CODE12_I 1 473 3250
ZIGGY_CODE13_I 1 473 3250
ZIGGY_CODE14_I 1 473 3250
ZIGGY_CODE15_I 1 473 3250
ZIGGY_CODE16_I 1 473 3250
ZIGGY_CODE17_I 1 473 3250
ZIGGY_CODE18_I 1 473 3250
ZIGGY_CODE19_I 1 473 3250
ZIGGY_CODE20_I 1 473 3250
ZIGGY_ID_I 1 473 3250
ZIGGY_CODE1_I 1 473 3250
ZIGGY_CODE2_I 1 473 3250
ZIGGY_CODE3_I 1 473 3250
ZIGGY_CODE4_I 1 473 3250
ZIGGY_CODE5_I 1 473 3250
ZIGGY_CODE6_I 1 473 3250
We notice that the indexes remain unchanged. As the table does NOT have ENABLE ROW MOVEMENT set, the indexes are NOT updated at all as part of the migrated row process (thus the same behaviour as non-autonomous database environments).
However, if I perform the same demo but instead perform a ROLLBACK of the transaction rather than the commit:
SQL> rollback; Rollback complete. Elapsed: 00:00:06.919
Note that the rollback takes 00:00:06.919 to complete.
If we now look at the size of the table and corresponding indexes:
SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'ZIGGY'); PL/SQL procedure successfully completed. SQL> analyze table ziggy compute statistics; Table ZIGGY analyzed. SQL> select table_name, num_rows, blocks, empty_blocks, avg_space, avg_row_len, chain_cnt from user_tables where table_name='ZIGGY'; TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE AVG_ROW_LEN CHAIN_CNT _____________ ___________ _________ _______________ ____________ ______________ ____________ ZIGGY 200000 4906 86 2899 113 0
We notice that the size of the table has increased to be the same 4906 blocks as when we performed the commit. The extra storage remains allocated even after the rollback operation.
That’s because Oracle does NOT deallocate any additional storage that might have been consumed during the original transaction. We notice that the AVG_SPACE has increased substantially as a result (now 2899, previously it was just 857).
If we look at the current state of the indexes after the rollback:
SQL> select index_name, blevel, leaf_blocks, clustering_factor
from user_indexes where table_name='ZIGGY';
INDEX_NAME BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR
_________________ _________ ______________ ____________________
ZIGGY_CODE20_I 1 473 3250
ZIGGY_ID_I 1 473 3250
ZIGGY_CODE1_I 1 473 3250
ZIGGY_CODE2_I 1 473 3250
ZIGGY_CODE3_I 1 473 3250
ZIGGY_CODE4_I 1 473 3250
ZIGGY_CODE5_I 1 473 3250
ZIGGY_CODE6_I 1 473 3250
ZIGGY_CODE7_I 1 473 3250
ZIGGY_CODE8_I 1 473 3250
ZIGGY_CODE9_I 1 473 3250
ZIGGY_CODE10_I 1 473 3250
ZIGGY_CODE11_I 1 473 3250
ZIGGY_CODE12_I 1 473 3250
ZIGGY_CODE13_I 1 473 3250
ZIGGY_CODE14_I 1 473 3250
ZIGGY_CODE15_I 1 473 3250
ZIGGY_CODE16_I 1 473 3250
ZIGGY_CODE17_I 1 473 3250
ZIGGY_CODE18_I 1 473 3250
ZIGGY_CODE19_I 1 473 3250
We notice that all the indexes again remain unchanged. As the indexes are not updated during the transaction, this is of course to be expected.
Let’s now repeat the same demo, but this time on a table with ENABLE ROW MOVEMENT set:
SQL> create table ziggy2(id number, code1 number, code2 number, code3 number, code4 number, code5 number, code6 number, code7 number, code8 number, code9 number, code10 number, code11 number, code12 number, code13 number, code14 number, code15 number, code16 number, code17 number, code18 number, code19 number, code20 number, name varchar2(142)) PCTFREE 0 ENABLE ROW MOVEMENT; Table ZIGGY2 created. SQL> insert into ziggy2 SELECT rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, 'BOWIE' FROM dual CONNECT BY LEVEL <= 200000; 200,000 rows inserted. SQL> commit; Commit complete. SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'ZIGGY2'); PL/SQL procedure successfully completed. SQL> analyze table ziggy2 compute statistics; Table ZIGGY2 analyzed. SQL> create index ziggy2_id_i on ziggy2(id); Index ZIGGY2_ID_I created. SQL> create index ziggy2_code1_i on ziggy2(code1); Index ZIGGY2_CODE1_I created. SQL> create index ziggy2_code2_i on ziggy2(code2); Index ZIGGY2_CODE2_I created. SQL> create index ziggy2_code3_i on ziggy2(code3); Index ZIGGY2_CODE3_I created. SQL> create index ziggy2_code4_i on ziggy2(code4); Index ZIGGY2_CODE4_I created. SQL> create index ziggy2_code5_i on ziggy2(code5); Index ZIGGY2_CODE5_I created. SQL> create index ziggy2_code6_i on ziggy2(code6); Index ZIGGY2_CODE6_I created. SQL> create index ziggy2_code7_i on ziggy2(code7); Index ZIGGY2_CODE7_I created. SQL> create index ziggy2_code8_i on ziggy2(code8); Index ZIGGY2_CODE8_I created. SQL> create index ziggy2_code9_i on ziggy2(code9); Index ZIGGY2_CODE9_I created. SQL> create index ziggy2_code10_i on ziggy2(code10); Index ZIGGY2_CODE10_I created. SQL> create index ziggy2_code11_i on ziggy2(code11); Index ZIGGY2_CODE11_I created. SQL> create index ziggy2_code12_i on ziggy2(code12); Index ZIGGY2_CODE12_I created. SQL> create index ziggy2_code13_i on ziggy2(code13); Index ZIGGY2_CODE13_I created. SQL> create index ziggy2_code14_i on ziggy2(code14); Index ZIGGY2_CODE14_I created. SQL> create index ziggy2_code15_i on ziggy2(code15); Index ZIGGY2_CODE15_I created. SQL> create index ziggy2_code16_i on ziggy2(code16); Index ZIGGY2_CODE16_I created. SQL> create index ziggy2_code17_i on ziggy2(code17); Index ZIGGY2_CODE17_I created. SQL> create index ziggy2_code18_i on ziggy2(code18); Index ZIGGY2_CODE18_I created. SQL> create index ziggy2_code19_i on ziggy2(code19); Index ZIGGY2_CODE19_I created. SQL> create index ziggy2_code20_i on ziggy2(code20); Index ZIGGY2_CODE20_I created.
The table and indexes all have the same initial size as the previous example:
SQL> select table_name, num_rows, blocks, empty_blocks, avg_space, avg_row_len, chain_cnt
from user_tables where table_name='ZIGGY2';
TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE AVG_ROW_LEN CHAIN_CNT
_____________ ___________ _________ _______________ ____________ ______________ ____________
ZIGGY2 200000 3268 60 857 113 0
SQL> select index_name, blevel, leaf_blocks, clustering_factor from user_indexes
where table_name='ZIGGY2';
INDEX_NAME BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR
__________________ _________ ______________ ____________________
ZIGGY2_ID_I 1 473 3250
ZIGGY2_CODE1_I 1 473 3250
ZIGGY2_CODE2_I 1 473 3250
ZIGGY2_CODE3_I 1 473 3250
ZIGGY2_CODE4_I 1 473 3250
ZIGGY2_CODE5_I 1 473 3250
ZIGGY2_CODE6_I 1 473 3250
ZIGGY2_CODE7_I 1 473 3250
ZIGGY2_CODE8_I 1 473 3250
ZIGGY2_CODE9_I 1 473 3250
ZIGGY2_CODE10_I 1 473 3250
ZIGGY2_CODE11_I 1 473 3250
ZIGGY2_CODE12_I 1 473 3250
ZIGGY2_CODE13_I 1 473 3250
ZIGGY2_CODE14_I 1 473 3250
ZIGGY2_CODE15_I 1 473 3250
ZIGGY2_CODE16_I 1 473 3250
ZIGGY2_CODE17_I 1 473 3250
ZIGGY2_CODE18_I 1 473 3250
ZIGGY2_CODE19_I 1 473 3250
ZIGGY2_CODE20_I 1 473 3250
If we now perform the same Update followed by the commit:
SQL> update ziggy2 set name='THE RISE AND FALL OF ZIGGY STARDUST AND THE SPIDERS FROM MARS'; 200,000 rows updated. Elapsed: 00:00:33.390 SQL> commit; Commit complete.
If we look at the current size of the table after the update:
SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'ZIGGY2'); PL/SQL procedure successfully completed. SQL> analyze table ziggy2 compute statistics; Table ZIGGY2 analyzed. SQL> select table_name, num_rows, blocks, empty_blocks, avg_space, avg_row_len, chain_cnt from user_tables where table_name='ZIGGY2'; TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE AVG_ROW_LEN CHAIN_CNT _____________ ___________ _________ _______________ ____________ ______________ ____________ ZIGGY2 200000 4654 82 367 169 0
We notice it has increased to 4654 blocks (previously 3268). But the table is not quite as large in size as in the first demo, where the table grew to 4906 blocks (so 252 fewer blocks).
The increase in table size is now due entirely as a result in the increased NAME column values, as Oracle has not had to consume any storage for pointers in the original table blocks to denote a new location of the rows, as the ROWIDs are now updated in the indexes on the fly.
So this is a positive, a DECREASE in the comparative size of table after such updates that migrate rows.
And of course, there are no migrated/chained rows.
But if we look at the index statistics after the commit:
SQL> select index_name, blevel, leaf_blocks, clustering_factor
from user_indexes where table_name='ZIGGY2';
INDEX_NAME BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR
__________________ _________ ______________ ____________________
ZIGGY2_ID_I 2 945 109061
ZIGGY2_CODE1_I 2 945 109061
ZIGGY2_CODE2_I 2 945 109061
ZIGGY2_CODE3_I 2 945 109061
ZIGGY2_CODE4_I 2 945 109061
ZIGGY2_CODE5_I 2 945 109061
ZIGGY2_CODE6_I 2 945 109061
ZIGGY2_CODE7_I 2 945 109061
ZIGGY2_CODE8_I 2 945 109061
ZIGGY2_CODE9_I 2 945 109061
ZIGGY2_CODE10_I 2 945 109061
ZIGGY2_CODE11_I 2 945 109061
ZIGGY2_CODE12_I 2 945 109061
ZIGGY2_CODE13_I 2 945 109061
ZIGGY2_CODE14_I 2 945 109061
ZIGGY2_CODE15_I 2 945 109061
ZIGGY2_CODE16_I 2 945 109061
ZIGGY2_CODE17_I 2 945 109061
ZIGGY2_CODE18_I 2 945 109061
ZIGGY2_CODE19_I 2 945 109061
ZIGGY2_CODE20_I 2 945 109061
We notice that ALL the indexes have significantly increased in size and now have 945 leaf blocks (previously it was just 473 leaf blocks). Additionally as a result of this increase in index size, the BLEVEL of the indexes has also increased and are now 2 (previously it was 1).
Here’s the thing. As I’ve discussed many times before, when Oracle performs an “Update” of an index entry, this is actually implemented as a Delete/Insert operation. By changing the ROWID of an index entry, Oracle first deletes the original index entry and inserts a new index entry with the new ROWID. So the previous index entry remains (with the space likely eventually reused by another new index entry in the future).
So these “on the fly” updates of the indexes to keep the ROWIDs current due to row migrations increases the likelihood of index block splits and the subsequent increase in index storage allocations.
In very rare, extreme cases (and this demo is indeed an extreme case as I’m updating all rows in my table), this extra index storage could potentially result in an increase in the index BLEVEL.
However, in most scenarios, this increase in index storage is likely to be moderate and result in extra index storage that will eventually be consumed by subsequent new rows anyways.
If instead of the commit operation, we instead performed a rollback:
SQL> rollback; Rollback complete. Elapsed: 00:00:36.639
We notice that the rollback takes considerably longer at 00:00:36.639 (previously in the first demo, it was just 00:00:06.919).
As the ROWIDs are now all updated on the fly on all the corresponding indexes, there’s that much more data that needs to be rolled back within these indexes.
If we look at the current size of the table after the rollback:
SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'ZIGGY2'); PL/SQL procedure successfully completed. SQL> analyze table ziggy2 compute statistics; Table ZIGGY2 analyzed. SQL> select table_name, num_rows, blocks, empty_blocks, avg_space, avg_row_len, chain_cnt from user_tables where table_name='ZIGGY2'; TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE AVG_ROW_LEN CHAIN_CNT _____________ ___________ _________ _______________ ____________ ______________ ____________ ZIGGY2 200000 4654 82 2823 113 0
We notice it has increased to the same 4654 blocks as with the commit. The extra storage remains allocated even after the rollback operation.
That’s again because Oracle does NOT deallocate any additional storage that might have been consumed during the original transaction. We notice that the AVG_SPACE has again increased substantially as a result (now 2823, previously it was just 857).
If we look at the current state of the indexes after the rollback:
SQL> select index_name, blevel, leaf_blocks, clustering_factor
from user_indexes where table_name='ZIGGY2';
INDEX_NAME BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR
__________________ _________ ______________ ____________________
ZIGGY2_ID_I 2 945 3250
ZIGGY2_CODE1_I 2 945 3250
ZIGGY2_CODE2_I 2 945 3250
ZIGGY2_CODE3_I 2 945 3250
ZIGGY2_CODE4_I 2 945 3250
ZIGGY2_CODE5_I 2 945 3250
ZIGGY2_CODE6_I 2 945 3250
ZIGGY2_CODE7_I 2 945 3250
ZIGGY2_CODE8_I 2 945 3250
ZIGGY2_CODE9_I 2 945 3250
ZIGGY2_CODE10_I 2 945 3250
ZIGGY2_CODE11_I 2 945 3250
ZIGGY2_CODE12_I 2 945 3250
ZIGGY2_CODE13_I 2 945 3250
ZIGGY2_CODE14_I 2 945 3250
ZIGGY2_CODE15_I 2 945 3250
ZIGGY2_CODE16_I 2 945 3250
ZIGGY2_CODE17_I 2 945 3250
ZIGGY2_CODE18_I 2 945 3250
ZIGGY2_CODE19_I 2 945 3250
ZIGGY2_CODE20_I 2 945 3250
We notice that they all remain at their increased size of 945 leaf blocks and with the Blevel of 2.
Again, when Oracle performs the rollback, Oracle does NOT undo all the index block splits and leaves all the additional storage allocated to the indexes.
So, just a word of caution.
Updating all the ROWIDs on the fly when a row migrates does not come for free. There’s additional resources that need to be consumed during these updates AND there is a potential issue with indexes having to perform additional index block splits and consume additional storage (at least immediately) after such operations.
If you have applications that makes bulk changes to data that can result in rollbacks, again, it’s just worth noting the extra storage that may be consumed as a result (until the additional data is finally added to the table).
Yes, index rebuilds can be performed to reduce the subsequent size of these inflated indexes.
BUT, for those of you with sharp eyes, you might also have noted another potential issue with this new behaviour, which I’ll discuss in my next post… 🙂
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…
When Does A ROWID Change? Part III (“Arriving Somewhere But Not Here”) December 13, 2022
Posted by Richard Foote in Autonomous Database, Changing ROWID, Index Internals, Local Indexes, Oracle, Oracle Blog, Oracle Cloud, Oracle General, Oracle Indexes, Oracle Indexing Internals Webinar, Partitioning, Performance Tuning, Richard's Blog, ROWID, Secondary Indexes.2 comments
In Part II of this series, I discussed how updating the Partitioned Key of a row from a Partitioned table will result in the row physically moving and the associated ROWID changing.
One of the reasons why changing the ROWID has historically has not been the default behaviour and requires the explicit setting of the ENABLE ROW MOVEMENT clause for Partitioned tables is because changing a ROWID comes at a cost. The cost being not only having to delete and re-insert the row within the table, but also delete and re-insert the associated index entry for each corresponding index on the table.
To illustrate, I’m going to create and populate another simple little Partitioned Table:
SQL> CREATE TABLE big_bowie2(id number, code1 number, code2 number, code3 number, code4 number, code5 number, code6 number, code7 number, code8 number, code9 number, code10 number, release_date date, name varchar2(42)) PARTITION BY RANGE (release_date)
(PARTITION ALBUMS_2021 VALUES LESS THAN (TO_DATE('01-JAN-2022', 'DD-MON-YYYY')),
PARTITION ALBUMS_2022 VALUES LESS THAN (MAXVALUE)) ENABLE ROW MOVEMENT;
Table created.
SQL> INSERT INTO big_bowie2 SELECT rownum, mod(rownum,1000), mod(rownum,1000), mod(rownum,1000), mod(rownum,100
ownum,1000), mod(rownum,1000), mod(rownum,1000), mod(rownum,1000), mod(rownum,1000), mod(rownum,1000), sysdate-
m,500), 'DAVID BOWIE' FROM dual CONNECT BY LEVEL <= 10000;
10000 rows created.
SQL> commit;
Commit complete.
SQL> exec dbms_stats.gather_table_stats(ownname=> null, tabname=> 'BIG_BOWIE2');
PL/SQL procedure successfully completed.
I’m now going to create a number of basic Global indexes on this table:
SQL> create index big_bowie2_id_i on big_bowie2(id); Index created. SQL> create index big_bowie2_code1_i on big_bowie2(code1); Index created. SQL> create index big_bowie2_code2_i on big_bowie2(code2); Index created. SQL> create index big_bowie2_code3_i on big_bowie2(code3); Index created. SQL> create index big_bowie2_code4_i on big_bowie2(code4); Index created. SQL> create index big_bowie2_code5_i on big_bowie2(code5); Index created. SQL> create index big_bowie2_code6_i on big_bowie2(code6); Index created. SQL> create index big_bowie2_code7_i on big_bowie2(code7); Index created. SQL> create index big_bowie2_code8_i on big_bowie2(code8); Index created. SQL> create index big_bowie2_code9_i on big_bowie2(code9); Index created. SQL> create index big_bowie2_code10_i on big_bowie2(code10); Index created.
If I run a simple single row UPDATE that updates a non-indexed, non-partitioned key column:
SQL> update big_bowie2 set name='ZIGGY STARDUST' where id=424;
1 row updated.
Execution Plan
----------------------------------------------------------
Plan hash value: 3590621923
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
-------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 1 | 16 | 2 (0) | 00:00:01 |
| 1 | UPDATE | BIG_BOWIE2 | | | | |
|* 2 | INDEX RANGE SCAN | BIG_BOWIE2_ID_I | 1 | 16 | 1 (0) | 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=424)
Statistics
----------------------------------------------------------
1 recursive calls
1 db block gets
2 consistent gets
0 physical reads
328 redo size
204 bytes sent via SQL*Net to client
41 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
We can see that the number of db block gets is just 1 and consistent gets just 2, as only the one table block needs to be updated and easily accessed via the index on the ID column.
If we now run a single row update of an indexed column:
SQL> update big_bowie2 set code1=42 where id = 424;
1 row updated.
Execution Plan
----------------------------------------------------------
Plan hash value: 3590621923
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
-------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 1 | 8 | 2 (0) | 00:00:01 |
| 1 | UPDATE | BIG_BOWIE2 | | | | |
|* 2 | INDEX RANGE SCAN | BIG_BOWIE2_ID_I | 1 | 8 | 1 (0) | 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=424)
Statistics
----------------------------------------------------------
1 recursive calls
5 db block gets
2 consistent gets
1 physical reads
948 redo size
204 bytes sent via SQL*Net to client
41 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
We can see that the number of db block gets increases to 5, as not only does the table block have to be updated but so also do the associated index blocks.
If we now finally run a single row update on the partitioned table’s partition key column that results in the row having to physically move to another partition:
SQL> update big_bowie2 set release_date='06-DEC-22' where id = 424;
1 row updated.
Execution Plan
----------------------------------------------------------
Plan hash value: 3590621923
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
-------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 1 | 64 | 2 (0) | 00:00:01 |
| 1 | UPDATE | BIG_BOWIE2 | | | | |
|* 2 | INDEX RANGE SCAN | BIG_BOWIE2_ID_I | 1 | 64 | 1 (0) | 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=424)
Statistics
----------------------------------------------------------
1 recursive calls
49 db block gets
3 consistent gets
0 physical reads
5996 redo size
204 bytes sent via SQL*Net to client
41 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
11 sorts (memory)
0 sorts (disk)
1 rows processed
We see that the number of db block gets jumps significantly to 49, as all the corresponding indexes require their associated index entries to be likewise deleted/re-inserted in order to change all their ROWIDs.
So this additional cost of updating the indexes has been a cost that Oracle has traditionally attempted to avoid, by generally not changing the ROWID when performing an update of a row.
Of course, the update of a Partitioned Key column is not the only manner in which ROWIDs have previously easily changed as we’ll see in Part IV…
UPDATE: As my buddie Martin Widlake makes in this comment. it’s also well worth mentioning the increase in associated redo (as redo is an excellent measurement of “work” the Oracle Database has to perform), if Oracle has to change the ROWID of a row and make the necessary changes to all its corresponding indexes. In the example above, the redo increases significantly from 328 bytes to 5996 bytes, when Oracle has to move the row to another partition and so update the ROWID on the 11 indexes. More on all this when I discuss the changes implemented with the current Autonomous Databases…
Costing Concatenated Indexes With Range Scan Predicates Part II (Coming Back To Life) July 27, 2022
Posted by Richard Foote in Automatic Indexing, CBO, Column Statistics, Concatenated Indexes, Explain Plan For Index, Full Table Scans, Index Access Path, Index Column Order, Index Column Reorder, Index Internals, Index statistics, Leaf Blocks, Non-Equality Predicates, Oracle, Oracle Blog, Oracle Cost Based Optimizer, Oracle General, Oracle Index Seminar, Oracle Indexes, Oracle Statistics, Performance Tuning, Richard Foote Training.add a comment
In my previous Part I post, I discussed how the CBO basically stops the index leaf block access calculations after a non-equality predicate. This means that for an index with the leading indexed column being accessed via an unselective non-equality predicate, a large percentage of the index’s leaf blocks might need to be scanned, making the index access path unviable.
In the example in Part I, an index on the ID, CODE columns was too expensive due to the unselective range-scan predicate based on the leading ID column.
To provide the CBO a potentially much more efficient access path, we need an index with the more selective CODE predicate to be the leading column:
SQL> CREATE INDEX radiohead_code_id_i ON radiohead(code, id); Index created. SQL> SELECT index_name, blevel, leaf_blocks, clustering_factor FROM user_indexes WHERE index_name = 'RADIOHEAD_CODE_ID_I'; INDEX_NAME BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR ----------------------------- ---------- ----------- ----------------- RADIOHEAD_CODE_ID_I 1 265 98619
If we now re-run the previous query:
SQL> SELECT * FROM radiohead WHERE id BETWEEN 1000 AND 5000 AND CODE = 140;
Execution Plan
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 72 | 6 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| RADIOHEAD | 4 | 72 | 6 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | RADIOHEAD_CODE_ID_I | 4 | | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
806 bytes sent via SQL*Net to client
608 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
4 rows processed
We notice the CBO is now using this new index, as the costs for this index-based plan have dropped significantly, down to just 6 (from the previous 116). This overall cost of 6 is lower than the cost of 105 for the Full Table Scan and hence the reason why this index-based plan is now chosen by the CBO.
This is all due entirely to the significant drop in costs in accessing the index itself, now just 2 (from the previous 112).
Importantly, these much lower costs are accurate as we can tell via the reduced number of consistent reads, now just 7 (from 114 from the previous index-based plan).
If we now look at the associated costings:
Effective Index Selectivity = CODE selectivity x ID selectivity
= (1/10000) x ((5000-1000)/(10000-1) + 2 x (1/10000))
= 0.0001 x ((4000/9999) + 0.0002)
= 0.0001 x 0.40024)
= 0.000040024
Effective Table Selectivity = same as Index Selectivity
= 0.000040024
The effective index selectivity of 0.000040024 is now much lower than the previous (0.40024), as the CBO can now consider the product of the selectivities of both columns).
If we now plug this improved effective index selectivity into the index path costing calculations:
Index IO Cost = blevel +
ceil(effective index selectivity x leaf_blocks) +
ceil(effective table selectivity x clustering_factor)
Index IO Cost = 1 + ceil(0.000040024 x 265) + ceil(0.000040024 x 99034)
= 1 + 1 + 4
= 2 + 4
= 6
Index Access Cost = IO Costs + CPU Costs (in this plan, 0% of total costs and so unchanged from the IO costs)
= 2 + 4
= 6
We can see how the respective 2 and 6 improved CBO index costings are derived.
So again, it’s important to note that Automatic Indexing is doing entirely the correct thing with these examples, when it creates an index with the equality based predicate columns as the leading columns of the index…
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: Scenarios Where Automatic Indexes NOT Created Part II (“Ragazzo Solo, Ragazza Sola” April 27, 2022
Posted by Richard Foote in 19c, 21c New Features, Automatic Indexing, Autonomous Database, Autonomous Transaction Processing, CBO, Constraints, Exadata, Foreign Keys, Full Table Scans, Index Internals, Oracle, Oracle 21c, Oracle Blog, Oracle Cloud, Oracle Cost Based Optimizer, Oracle General, Oracle Indexes, Oracle19c, Performance Tuning.1 comment so far
In my last post, I discussed how Automatic Indexing doesn’t create an automatic index in the scenario where the minimum or maximum of a column is required.
Another scenario when an automatic index is not created is when we hit issues associated with a missing index on a Foreign Key (FK) constraint.
As I’ve discussed many times previously, if you delete a parent record without an index on the dependant FK constraints, you hit a number of issues including having to perform a (potentially expensive and problematic) Full Table Scan (FTS) on the child tables and the associated locking problems.
To illustrate, I’ll first create a small parent table:
SQL> create table daddy (id number constraint daddy_pk primary key , name varchar2(42)); Table created. SQL> insert into daddy select rownum, 'David Bowie '|| rownum from dual connect by level <= 10000; 10000 rows created. SQL> commit; Commit complete. SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'DADDY'); PL/SQL procedure successfully completed.
And then a somewhat larger child table, with no index on the associated foreign key constraint:
SQL> create table kiddy (id number constraint kiddy_pk primary key , code1 number constraint daddy_fk references daddy(id), code2 number, code3 number, name varchar2(42)); Table created. SQL> insert into kiddy select rownum, mod(rownum,1000)+1000 , mod(rownum, 10000)+1, mod(rownum, 100000)+1, 'Ziggy Stardust '|| rownum from dual connect by level <= 10000000; 10000000 rows created. SQL> commit; Commit complete. SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'KIDDY'); PL/SQL procedure successfully completed.
If we delete a number of parent rows, for example:
SQL> delete from daddy where id = 101;
1 row deleted.
Execution Plan
----------------------------------------------------------
Plan hash value: 1477800718
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
-------------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 1 | 4 | 1 (0) | 00:00:01 |
| 1 | DELETE | DADDY | | | | |
|* 2 | INDEX UNIQUE SCAN | DADDY_PK | 1 | 4 | 1 (0) | 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=101)
Statistics
----------------------------------------------------------
18 recursive calls
13 db block gets
117462 consistent gets
22292 physical reads
4645500 redo size
204 bytes sent via SQL*Net to client
41 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
We notice that even though we only delete one row from a relatively small table, we perform a large number of consistent gets (117462) due to the necessary FTS on the child table, as Oracle is forced to check the table for any possible FK violations. Without an index on the child CODE1 column, Oracle has no choice but to perform the relatively expensive FTS.
Additionally, if we have an existing transaction of a child table (in Session 1):
SQL> insert into kiddy values (10000001,1042,1042,1042,'Iggy Pop'); 1 row created.
And then in another session attempt to delete a parent row (in Session 2):
SQL> delete from daddy where id = 112;
The delete hangs in a locked state due to the child transaction in Session 1. This can lead to further locking issues in other sessions (Session 3):
insert into kiddy values (10000002,1042,1042,1042,'Iggy Pop');
The FTS on the child table and these associated locks can all be avoided by having an index on the FK constraint, as the index can then be used to effectively police the constraint during such delete operations.
What does AI do in this scenario?
Currently, nothing.
I’ve been unable to ever get AI to create a usable automatic index in this scenario. In Oracle Database 19c, I’ve not been able to get an AI created at all. In Oracle Database 21c, the best I’ve seen has been a Unusable/Invisible AI:
SQL> select index_name, index_type, auto, constraint_index, visibility, status, num_rows from user_indexes where table_n ame='KIDDY'; INDEX_NAME INDEX_TYPE AUT CON VISIBILIT STATUS NUM_ROWS ------------------------------ --------------------------- --- --- --------- -------- ---------- KIDDY_PK NORMAL NO YES VISIBLE VALID 10000004 SYS_AI_31thttf8v6r35 NORMAL YES NO INVISIBLE UNUSABLE 10000004 SQL> select index_name, column_name, column_position from user_ind_columns where table_name='KIDDY'; INDEX_NAME COLUMN_NAME COLUMN_POSITION ------------------------------ --------------- --------------- KIDDY_PK ID 1 SYS_AI_31thttf8v6r35 CODE1 1
So you may need to manually create such an index on the FK constraint to improve performance and eliminate these locking issues:
SQL> create index kiddy_code1_i on kiddy(code1);
Index created.
SQL> delete from daddy where id = 142;
1 row deleted.
Execution Plan
----------------------------------------------------------
Plan hash value: 1477800718
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
-------------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 1 | 4 | 1 (0) | 00:00:01 |
| 1 | DELETE | DADDY | | | | |
|* 2 | INDEX UNIQUE SCAN | DADDY_PK | 1 | 4 | 1 (0) | 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=142)
Statistics
----------------------------------------------------------
1 recursive calls
8 db block gets
2 consistent gets
2 physical reads
132 redo size
204 bytes sent via SQL*Net to client
41 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
Consistent gets have dropped off massively (down to just 8) as Oracle can now use the index to avoid the FTS search on the child table. The associated locking issues are eliminated as well.
Note: As always, this AI behaviour can always change in the future…
Automatic Indexing: Deferred Invalidations (“The Post War Dream”) April 19, 2022
Posted by Richard Foote in 21c New Features, Automatic Indexing, Autonomous Database, Autonomous Transaction Processing, CBO, Deferred Invalidation, Exadata, Function Based Indexes, Index Access Path, Index Internals, JSON, Oracle, Oracle Blog, Oracle Cloud, Oracle Cost Based Optimizer, Oracle Indexes, Richard's Blog.2 comments
In my previous post on how JSON expressions can now be automatically indexed, I mentioned there was an outstanding issue with the associated CBO execution plan, immediately post the creation of the automatic index:
SQL> select * from bowie_json where json_value(bowie_order, '$.PONumber')='42';
Execution Plan
----------------------------------------------------------
Plan hash value: 832017402
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20000 | 12M | 1524 (1) | 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED | BOWIE_JSON | 20000 | 12M | 1524 (1) | 00:00:01 |
|* 2 | INDEX RANGE SCAN | SYS_AI_ayvj257jd93cv | 8000 | | 3 (0) | 00:00:01 |
------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access(JSON_VALUE("BOWIE_ORDER" /*+ LOB_BY_VALUE */ FORMAT OSON , '$.PONumber' RETURNING
VARCHAR2(4000) ERROR ON ERROR NULL ON EMPTY)='42')
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
234168 consistent gets
200279 physical reads
0 redo size
1595 bytes sent via SQL*Net to client
526 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
If we look at the number of recursive calls, we notice that it remains at 0. If we look at both the number of consistent gets (234168) and physical reads (200279), they both remain very high and identical to that of the previous Full Table Scan plan.
Basically, although autotrace suggests the newly created automatic index is being used, in fact the previous Full Table Scan plan is still being invoked. (Note: this of course is one of the dangers of the autotrace plan, in that it might not display the actual plan being invoked).
So what’s going on here?
The Oracle Database 21c New Features Guide makes the following point: “an enhancement has been introduced to reduce the overhead of cursor invalidations when a new automatic index is created”.
Oracle 12.2 introduced a new feature in which one can now defer the invalidation of dependent SQL cursors when an index is created or modified. I’ve of course discussed this previously in this 12.2 Index Deferred Invalidation post.
When an automatic index is created in 21c, the current SQL cursors are NOT invalidated (to reduce the overhead of having to potentially reparse of large number of current SQL cursors). However, this means that currently inefficient SQL statements will keep their existing sub-optimal execution plans post the creation of newly created automatic indexes, until the existing SQL cursors aged out.
At which point, the new CBO plan using the automatic index will actually be invoked:
SQL> select * from bowie_json where json_value(bowie_order, '$.PONumber')='42';
Execution Plan
----------------------------------------------------------
Plan hash value: 832017402
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 671 | 4 (0) | 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED | BOWIE_JSON | 1 | 671 | 4 (0) | 00:00:01 |
|* 2 | INDEX RANGE SCAN | SYS_AI_ayvj257jd93cv | 1 | | 3 (0) | 00:00:01 |
------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access(JSON_VALUE("BOWIE_ORDER" /*+ LOB_BY_VALUE */ FORMAT OSON , '$.PONumber' RETURNING
VARCHAR2(4000) ERROR ON ERROR NULL ON EMPTY)='42')
Statistics
----------------------------------------------------------
30 recursive calls
0 db block gets
46 consistent gets
11 physical reads
0 redo size
1595 bytes sent via SQL*Net to client
526 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
So just be aware in Oracle Database 21c that your beautifully created automatic indexes may not actually get used as desired for a period of time…
Oracle 19c Automatic Indexing: Indexing With Stale Statistics Part II (Survive) October 7, 2020
Posted by Richard Foote in 19c, 19c New Features, Automatic Indexing, Autonomous Data Warehouse, Autonomous Database, Autonomous Transaction Processing, CBO, Exadata, Full Table Scans, Index Internals, Index statistics, Oracle, Oracle General, Oracle Indexes, Oracle Statistics, Oracle19c, Performance Tuning, Stale Statistics.1 comment so far
In my previous post, I discussed how having stale statistics, usually a bad idea, is especially problematic with regard Automatic Indexes as it usually results in viable automatic indexes only being created in an UNUSABLE/INVISIBLE state.
If we were to now to collect the missing statistics:
SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'BOWIE_STALE'); PL/SQL procedure successfully completed. SQL> select table_name, num_rows, blocks, last_analyzed from user_tables where table_name='BOWIE_STALE'; TABLE_NAME NUM_ROWS BLOCKS LAST_ANAL -------------------- ---------- ---------- --------- BOWIE_STALE 10000000 39677 06-JUL-20 SQL> select column_name, num_distinct, density, histogram, last_analyzed from user_tab_cols where table_name='BOWIE_STALE'; COLUMN_NAME NUM_DISTINCT DENSITY HISTOGRAM LAST_ANAL -------------------- ------------ ---------- --------------- --------- ID 10000000 0 HYBRID 06-JUL-20 CODE 971092 .000001 HYBRID 06-JUL-20 NAME 1 4.9416E-08 FREQUENCY 06-JUL-20
If we now repeatedly re-run the problematic query many times:
SQL> select * from bowie_stale where code=42;
10 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 65903426
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 230 | 544 (14)| 00:00:01 |
|* 1 | TABLE ACCESS STORAGE FULL| BOWIE_STALE | 10 | 230 | 544 (14)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - storage("CODE"=42)
filter("CODE"=42)
Note
-----
- automatic DOP: Computed Degree of Parallelism is 1
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
39430 consistent gets
39421 physical reads
0 redo size
610 bytes sent via SQL*Net to client
361 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 is forced to use the FTS as the current Automatic Index is in an UNUSABLE/INVISIBLE state.
If we wait for the next Automatic Indexing reporting period:
SQL> select dbms_auto_index.report_last_activity('text', 'ALL', 'ALL' ) report from dual;
REPORT
--------------------------------------------------------------------------------
GENERAL INFORMATION
-------------------------------------------------------------------------------
Activity start : 06-JUL-2020 05:12:42
Activity end : 06-JUL-2020 05:13:34
Executions completed : 1
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 : 0x
-------------------------------------------------------------------------------
SUMMARY (MANUAL INDEXES)
-------------------------------------------------------------------------------
Unused indexes : 0
Space used : 0 B
Unusable indexes : 0
-------------------------------------------------------------------------------
We notice that the Automatic Indexing process has nothing to report. Even though the problematic query is repeatedly executed, the SQL is now effectively on a blacklist and is not re-considered by the Automatic Indexing process.
If we look at the index details on the table:
SQL> select index_name, auto, constraint_index, visibility, compression, status, num_rows, leaf_blocks, clustering_factor from user_indexes where table_name='BOWIE_STALE'; INDEX_NAME AUT CON VISIBILIT COMPRESSION STATUS NUM_ROWS LEAF_BLOCKS CLUSTERING_FACTOR ---------------------- --- --- --------- ------------- -------- ---------- ----------- ----------------- BOWIE_STALE_PK NO YES VISIBLE DISABLED VALID 10000000 20164 59110 SYS_AI_300kk2unp8tr0 YES NO INVISIBLE ADVANCED LOW UNUSABLE 10000000 23058 4147514
So the Automatic Index (SYS_AI_300kk2unp8tr0) is still UNUSABLE and INVISIBLE and can not be used by the CBO.
NOTE: In earlier patches of Oracle Database 19c (I’m using version 19.5.0.0.0 in this demo), I identified some scenarios after stale statistics when indexes were created in but in a VALID/INVISIBLE state, such that they could still not be used by the CBO in general database sessions.
If we simply re-run the same queries again from the time when the dependant object statistics were stale, any SQL is just ignored by the Automatic Indexing process.
As such, if we now subsequently re-run the problematic query again:
SQL> select * from bowie_stale where code=42;
10 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 65903426
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 230 | 544 (14)| 00:00:01 |
|* 1 | TABLE ACCESS STORAGE FULL| BOWIE_STALE | 10 | 230 | 544 (14)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - storage("CODE"=42)
filter("CODE"=42)
Note
-----
- automatic DOP: Computed Degree of Parallelism is 1
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
39430 consistent gets
39421 physical reads
0 redo size
610 bytes sent via SQL*Net to client
361 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed
Again, the CBO has no choice here with no viable VALID/VISIBLE index present but to perform a FTS, even though its getting the cardinality estimates spot on since statistics gathering.
In Part III I’ll discuss how to get this query to finally use the Automatic Index and improve its performance, although if you’re a regular reader of the blog you should already know the solution…
Oracle 19c Automatic Indexing: Poor Data Clustering With Autonomous Databases Part III (Star) August 11, 2020
Posted by Richard Foote in 19c, 19c New Features, Attribute Clustering, Automatic Indexing, Autonomous Data Warehouse, Autonomous Database, Autonomous Transaction Processing, CBO, Clustering Factor, Data Clustering, Exadata, Index Access Path, Index Internals, Index statistics, Oracle, Oracle Cost Based Optimizer, Oracle Indexes, Performance Tuning.2 comments
In Part I we looked at a scenario where an index was deemed to be too inefficient for Automatic Indexing to create a VALID index, because of the poor clustering of data within the table.
In Part II we improved the data clustering but the previous SQLs could still not generate a new Automatic Index because they had effectively been blacklisted.
So how do we get Automatic Indexing to improve the performance of these queries?
Basically, we need to run some new SQL statements to those previously run which have not been blacklisted, that can make the Automatic Indexing process kick in and create the necessary indexes.
For example, if we now run the following SQL statements that have not previously run:
select * from nickcave where code=1; select * from nickcave where code=2; select * from nickcave where code=3;
And now wait for the next Automatic Indexing process period and look at the following (partial) Automatic Indexing report:
REPORT
--------------------------------------------------------------------------------
GENERAL INFORMATION
-------------------------------------------------------------------------------
Activity start : 22-JUN-2020 04:26:31
Activity end : 22-JUN-2020 04:27:25
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) : 167.77 MB (167.77 MB / 0 B)
Indexes dropped : 0
SQL statements verified : 3
SQL statements improved (improvement factor) : 3 (76x)
SQL plan baselines created : 0
Overall improvement factor : 76x
INDEX DETAILS
-------------------------------------------------------------------------------
The following indexes were created:
------------------------------------------------------------------------
| Owner | Table | Index | Key | Type | Properties |
------------------------------------------------------------------------
| BOWIE | NICKCAVE | SYS_AI_dh8pumfww3f4r | CODE | B-TREE | NONE |
------------------------------------------------------------------------
VERIFICATION DETAILS
-------------------------------------------------------------------------------
The performance of the following statements improved:
-------------------------------------------------------------------------------
Parsing Schema Name : BOWIE
SQL ID : 5k1wmtu7um5q9
SQL Text : select * from nickcave where code=1
Improvement Factor : 76x
Execution Statistics:
-----------------------------
Original Plan Auto Index Plan
---------------------------- ----------------------------
Elapsed Time (s): 1725103 106145
CPU Time (s): 1534305 62314
Buffer Gets: 291835 779
Optimizer Cost: 9125 792
Disk Reads: 0 197
Direct Writes: 0 0
Rows Processed: 500000 100000
Executions: 5 1
We can see that an index has indeed now been created on the CODE column because one of the new statements is now deemed to be 76x more efficient thanks to the new index.
If we look at details of this new Automatic Index:
SQL> select index_name, auto, constraint_index, visibility, compression, status, num_rows, leaf_blocks, clustering_factor from user_indexes where table_name='NICKCAVE'; INDEX_NAME AUT CON VISIBILIT COMPRESSION STATUS NUM_ROWS LEAF_BLOCKS CLUSTERING_FACTOR -------------------- --- --- --------- ------------- -------- ---------- ----------- ----------------- SYS_AI_dh8pumfww3f4r YES NO VISIBLE DISABLED VALID 10000000 19518 57983 SQL> select index_name, column_name, column_position from user_ind_columns where table_name='NICKCAVE' order by index_name, column_position; INDEX_NAME COLUMN_NAME COLUMN_POSITION -------------------- -------------------- --------------- SYS_AI_dh8pumfww3f4r CODE 1
We can see that the index is now indeed VALID and VISIBLE with a much improved Clustering Factor at just 57983.
If we now re-run newer SQL statement:
SQL> select * from nickcave where code=1;
100000 rows selected.
Execution Plan
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100K | 3613K | 792 (2) | 00:00:01 |
| 1 | PX COORDINATOR | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | 100K | 3613K | 792 (2) | 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID BATCHED| NICKCAVE | 100K | 3613K | 792 (2) | 00:00:01 |
| 4 | BUFFER SORT | | | | | |
| 5 | PX RECEIVE | | 100K | | 205 (4) | 00:00:01 |
| 6 | PX SEND HASH (BLOCK ADDRESS) | :TQ10000 | 100K | | 205 (4) | 00:00:01 |
| 7 | PX SELECTOR | | | | | |
|* 8 | INDEX RANGE SCAN | SYS_AI_dh8pumfww3f4r | 100K | | 205 (4) | 00:00:01 |
--------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
8 - access("CODE"=1)
Statistics
----------------------------------------------------------
12 recursive calls
0 db block gets
779 consistent gets
0 physical reads
176 redo size
2363897 bytes sent via SQL*Net to client
73914 bytes received via SQL*Net from client
6668 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
100000 rows processed
We notice the SQL statement is now indeed using this new Automatic Index.
If we now re-run our original SQL statement that had been using a FTS execution plan and that we couldn’t make Automatic Indexing create a VALID index because when originally run, the data clustering was too poor within the table:
SQL> select * from nickcave where code=42;
100000 rows selected.
Execution Plan
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100K | 3613K | 792 (2) | 00:00:01 |
| 1 | PX COORDINATOR | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | 100K | 3613K | 792 (2) | 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID BATCHED| NICKCAVE | 100K | 3613K | 792 (2) | 00:00:01 |
| 4 | BUFFER SORT | | | | | |
| 5 | PX RECEIVE | | 100K | | 205 (4) | 00:00:01 |
| 6 | PX SEND HASH (BLOCK ADDRESS) | :TQ10000 | 100K | | 205 (4) | 00:00:01 |
| 7 | PX SELECTOR | | | | | |
|* 8 | INDEX RANGE SCAN | SYS_AI_dh8pumfww3f4r | 100K | | 205 (4) | 00:00:01 |
--------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
8 - access("CODE"=42)
Statistics
----------------------------------------------------------
14 recursive calls
4 db block gets
780 consistent gets
198 physical reads
15224 redo size
2363897 bytes sent via SQL*Net to client
73914 bytes received via SQL*Net from client
6668 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
100000 rows processed
This query is now also finally using the newly created index, because the CBO now too deems it to be more efficient with an index based execution plan.
The moral of the story. Automatic Indexing may initially deem a potential index to not be efficient enough to be created. However, things may change such as the clustering of table data (or the distribution of data values, etc. etc.) that may make a new index now viable. This though requires a NEW SQL statement to be executed, such that a non-blacklisted SQL can invoke the Automatic Indexing process to create the necessary Automatic Index.
Of course, things may change in the future. Future releases may have the facility to automatically re-cluster the data in tables optimally based on existing workloads and may also have a mechanism to identify that things have sufficient “changed” such that previously “failed” SQL statements from an Automatic Indexing perspective may warrant reevaluation.
This has only been tested up to version Oracle Database 19.5 of the Oracle Autonomous Database environments.
Richard Foote's Oracle Blog 