CBO Costing Plans With Migrated Rows Part II (“New Killer Star”) March 28, 2023
Posted by Richard Foote in CBO, Index Access Path, Index statistics, Leaf Blocks, Migrated Rows, Non-Equality Predicates, Oracle, Oracle Blog, Oracle Cost Based Optimizer, Oracle General, Oracle Indexes, Oracle Statistics, Performance Tuning, Richard's Blog, ROWID.add a comment
I’ve spent the past few months discussing Migrated Rows, in large part thanks to an excellent 15 minute video by Connor McDonald on how ROWIDs can now be updated on the fly in Oracle Autonomous databases.
Well 14 such posts later, I have finally reached the end of this topic (for now at least). So, an average of about 1 post per minute of video 🙂
In my previous post, I discussed how the CBO costs execution plans with tables that have migrated rows, when the statistics are collected as recommended via the DBMS_STATS package. In summary, migrated rows are basically just ignored, with the CBO blissfully unaware of the existence of any such migrated rows.
As I discussed, if I want to easily see how many migrated rows I have in a table, I can potentially use the ANALYZE command as follows:
SQL> analyze table bowie compute statistics;
Table BOWIE analyzed.
SQL> select table_name, num_rows, blocks, empty_blocks, avg_space, avg_row_len, chain_cnt from user_tables
where table_name='BOWIE';
TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE AVG_ROW_LEN CHAIN_CNT
_____________ ___________ _________ _______________ ____________ ______________ ____________
BOWIE 200000 4906 86 415 170 56186
As you can now see, the table currently has 56186 migrated rows (yes CHAIN_CNT can potentially count rows that simply can’t fit within a single block, but all these rows are definitely migrated rows as per the demo in my previous post).
Now, it had always been my belief that although you can use the ANALYZE command to count out these migrated rows, the CBO would simply ignore this statistic in its calculations.
But I was wrong.
If we now re-run the query from the previous post:
SQL> select * from bowie where id > 1 and id < 1001;
999 rows selected.
PLAN_TABLE_OUTPUT
_______________________________________________________________________________________________________________
SQL_ID b1vwpu2rgn8p5, child number 0
-------------------------------------
select * from bowie where id > 1 and id < 1001
Plan hash value: 1405654398
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 999 |00:00:00.01 | 666 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED | BOWIE | 1 | 999 | 999 |00:00:00.01 | 666 |
|* 2 | INDEX RANGE SCAN | BOWIE_ID_I | 1 | 999 | 999 |00:00:00.01 | 4 |
------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID">1 AND "ID"<1001)
Statistics
-----------------------------------------------------------
1 CPU used by this session
1 CPU used when call started
1 DB time
9193 RM usage
3 Requests to/from client
2 SQL*Net roundtrips to/from client
664 buffer is not pinned count
1662 buffer is pinned count
323 bytes received via SQL*Net from client
171333 bytes sent via SQL*Net to client
2 calls to get snapshot scn: kcmgss
2 calls to kcmgcs
666 consistent gets
1 consistent gets examination
1 consistent gets examination (fastpath)
666 consistent gets from cache
665 consistent gets pin
665 consistent gets pin (fastpath)
2 execute count
1 index range scans
5455872 logical read bytes from cache
665 no work - consistent read gets
39 non-idle wait count
2 opened cursors cumulative
1 opened cursors current
2 parse count (total)
2 process last non-idle time
1 session cursor cache hits
666 session logical reads
1 sorts (memory)
2024 sorts (rows)
999 table fetch by rowid
327 table fetch continued row
3 user calls
SQL> SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(sql_id=>'b1vwpu2rgn8p5',format=>'ALLSTATS LAST +cost +bytes'));
PLAN_TABLE_OUTPUT
____________________________________________________________________________________________________________________________________
SQL_ID b1vwpu2rgn8p5, child number 0
-------------------------------------
select * from bowie where id > 1 and id < 1001
Plan hash value: 1405654398
---------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 302 (100)| 999 |00:00:00.01 | 666 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED | BOWIE | 1 | 999 | 141K| 302 (0)| 999 |00:00:00.01 | 666 |
|* 2 | INDEX RANGE SCAN | BOWIE_ID_I | 1 | 999 | | 4 (0)| 999 |00:00:00.01 | 4 |
---------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID">1 AND "ID"<1001)
We can see that the cost of the plan has now changed.
Although the cost of reading the index itself is still the same with a cost of 4, the overall cost of the plan has increased to 302 (previously it was 21).
So the difference in plan costs is 302 – 21 = 281. And it’s pretty easy to see where this extra comes from…
The extra costs is basically query selectivity x no of migrated rows
Extra costs = 0.005 x 56186 = 281.
So the index scan costing formula should really be updated to be:
Index Scan Cost = blevel +
ceil(effective index selectivity x leaf_blocks) +
ceil(effective table selectivity x clustering_factor) +
ceil(effective table selectivity x chain_cnt)
Now, IMHO, this new cost is actually more accurate and better matches the true cost of now using the index, which requires 666 Consistent Gets (previously, before the rows migrated, the index plan required just 18 Consistent Gets).
So in some respects, this new cost might not be a bad thing. But then again, a sudden change in such costings due to a flood of new migrated rows might result in an unexpected and undesired plan changes that have been carefully crafted for statistics generated with the conventional DBMS_STATS collection method.
However, it’s not sufficient to simply collect fresh statistics using DBMS_STATS to get the previous CBO costings where migrated rows are ignored:
SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'BOWIE', estimate_percent=> null, no_invalidate=>false);
PL/SQL procedure successfully completed.
SQL> select table_name, num_rows, blocks, empty_blocks, avg_space, avg_row_len, chain_cnt from user_tables
where table_name='BOWIE';
TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE AVG_ROW_LEN CHAIN_CNT
_____________ ___________ _________ _______________ ____________ ______________ ____________
BOWIE 200000 4906 86 415 167 56186
Simply collecting fresh statistics does NOT clear out the CHAIN_CNT statistic and so the CBO costings remain the same as with ANALYZE command:
SQL> SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(sql_id=>'b1vwpu2rgn8p5',format=>'ALLSTATS LAST +cost +bytes'));
PLAN_TABLE_OUTPUT
____________________________________________________________________________________________________________________________________
SQL_ID b1vwpu2rgn8p5, child number 0
-------------------------------------
select * from bowie where id > 1 and id < 1001
Plan hash value: 1405654398
---------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 302 (100)| 999 |00:00:00.01 | 666 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED | BOWIE | 1 | 999 | 141K| 302 (0)| 999 |00:00:00.01 | 666 |
|* 2 | INDEX RANGE SCAN | BOWIE_ID_I | 1 | 999 | | 4 (0)| 999 |00:00:00.01 | 4 |
---------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID">1 AND "ID"<1001)
You need to first delete the table statistics to remove the CHAIN_CNT statistic (which of course comes with obvious dangers now there are no table statistics present) before you collect fresh statistics using DBMS_STATS:
SQL> execute dbms_stats.delete_table_stats(ownname=>null, tabname=>'BOWIE');
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'BOWIE', estimate_percent=> null, no_invalidate=>false);
PL/SQL procedure successfully completed.
SQL> select table_name, num_rows, blocks, empty_blocks, avg_space, avg_row_len, chain_cnt from user_tables
where table_name='BOWIE';
TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE AVG_ROW_LEN CHAIN_CNT
_____________ ___________ _________ _______________ ____________ ______________ ____________
BOWIE 200000 4906 0 0 167 0
SQL> select index_name, blevel, leaf_blocks, clustering_factor from user_indexes where table_name='BOWIE';
INDEX_NAME BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR
_____________ _________ ______________ ____________________
BOWIE_ID_I 1 473 3250
The CHAIN_CNT statistic has finally been cleared to 0 and the CBO costings now returned to as it was previously when such migrated rows were ignored:
SQL> SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(sql_id=>'b1vwpu2rgn8p5',format=>'ALLSTATS LAST +cost +bytes'));
PLAN_TABLE_OUTPUT
____________________________________________________________________________________________________________________________________
SQL_ID b1vwpu2rgn8p5, child number 0
-------------------------------------
select * from bowie where id > 1 and id < 1001
Plan hash value: 1405654398
---------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 21 (100)| 999 |00:00:00.01 | 666 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED | BOWIE | 1 | 1000 | 163K| 21 (0)| 999 |00:00:00.01 | 666 |
|* 2 | INDEX RANGE SCAN | BOWIE_ID_I | 1 | 1000 | | 4 (0)| 999 |00:00:00.01 | 4 |
---------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID">1 AND "ID"<1001)
The CBO costs are now back to the previous 21.
So I’m a little in two minds about this. I think the statistics generated and used by the CBO are better with the ANALYZE command, but would still suggest collecting the necessary statistics using the recommended DBMS_STATS approach. Perhaps Oracle giving us the option to collect these additional statistics using DBMS_STATS might be a useful enhancement… 🤷♂️
Now, at one point in time, a long long time ago, I’m reasonably sure the CBO previously didn’t use the CHAIN_CNT statistic. However, it came as no real surprise when I researched when the CBO had started using the CHAIN_CNT statistic in its calculations, that Jonathan Lewis had already written on this subject way way back in April 2009 🙂
So Oracle definitely had this behaviour all the way back to at least 9i and continues to behave this way in 21c. Ah well, better late than never I guess to finally realise how all this actually works…
UPDATE (29 March 2023): Jonathan Lewis can kindly confirmed with me that CHAIN_CNT was definitely ignored back in version 8.1.7.4 and that this changed to the current behaviour in either 9.0 or 9.2.
CBO Costing Plans With Migrated Rows Part I (“Ignoreland”) March 21, 2023
Posted by Richard Foote in BLEVEL, CBO, Clustering Factor, Data Clustering, Index Access Path, Index Height, Index statistics, Leaf Blocks, Migrated Rows, Non-Equality Predicates, Oracle, Oracle Blog, Oracle Cost Based Optimizer, Oracle General, Oracle Indexes, Oracle Statistics, Performance Tuning, Richard's Blog, ROWID.3 comments
Whilst recently blogging about Migrated Rows and specifically changes to how ROWIDs are now maintained on the fly in Oracle Autonomous Databases, I made a discovery regarding how the Cost-Based Optimizer (CBO) costs such plans. This is one of the key reasons why I blog, not only to try and share odd titbits about how Oracle works, but also to hopefully learn much myself in the process.
Imagine my surprise in not only learning that Oracle and the CBO works differently to how I had always thought Oracle worked in this respect, but that this behaviour has been the case since at least Oracle 9i.
In Part I, I’ll use the same example of migrated rows as I’ve used in the past few blog posts and initially show how the CBO generally costs such plans (and by which I had incorrectly assumed ALWAYS costed such plans).
Let’s start by creating and populating a tightly packed table (in an environment where ROWIDs are NOT updated on the fly):
SQL> create table bowie(id number, code1 number, code2 number, code3 number, code4 number, code5 number, code6 number, code7 number, code8 number, code9 number, code10 number, code11 number, code12 number, code13 number, code14 number, code15 number, code16 number, code17 number, code18 number, code19 number, code20 number, name varchar2(142)) PCTFREE 0; Table BOWIE created. SQL> insert into bowie SELECT rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, 'BOWIE' FROM dual CONNECT BY LEVEL <= 200000; 200,000 rows inserted. SQL> commit; Commit complete.
I’ll next create an index on the well clustered ID column (as the rows are inserted in ID column order within the table):
SQL> create index bowie_id_i on bowie(id); Index BOWIE_ID_I created.
Next, we’ll use the Oracle recommended method of collecting table/index statistics, by using the DBMS_STATS package:
SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'BOWIE', estimate_percent=> null, no_invalidate=>false); PL/SQL procedure successfully completed. SQL> select table_name, num_rows, blocks, empty_blocks, avg_space, avg_row_len, chain_cnt from user_tables where table_name='BOWIE'; TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE AVG_ROW_LEN CHAIN_CNT _____________ ___________ _________ _______________ ____________ ______________ ____________ BOWIE 200000 3268 0 0 111 0 SQL> select index_name, blevel, leaf_blocks, clustering_factor from user_indexes where table_name='BOWIE'; INDEX_NAME BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR _____________ _________ ______________ ____________________ BOWIE_ID_I 1 473 3250
Note the key index statistics here: BLEVEL=1, LEAF_BLOCKS=473 and the near perfect CLUSTERING_FACTOR=3250.
If we run the following query featuring a non-equality range predicate:
SQL> select * from bowie where id > 1 and id < 1001;
999 rows selected.
PLAN_TABLE_OUTPUT
_______________________________________________________________________________________________________________
SQL_ID b1vwpu2rgn8p5, child number 0
-------------------------------------
select * from bowie where id > 1 and id < 1001
Plan hash value: 1405654398
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 999 |00:00:00.01 | 18 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED | BOWIE | 1 | 1000 | 999 |00:00:00.01 | 18 |
|* 2 | INDEX RANGE SCAN | BOWIE_ID_I | 1 | 1000 | 999 |00:00:00.01 | 4 |
------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID">1 AND "ID"<1001)
Statistics
-----------------------------------------------------------
1 CPU used by this session
1 CPU used when call started
1 DB time
7678 RM usage
3 Requests to/from client
2 SQL*Net roundtrips to/from client
16 buffer is not pinned count
1983 buffer is pinned count
323 bytes received via SQL*Net from client
171383 bytes sent via SQL*Net to client
2 calls to get snapshot scn: kcmgss
2 calls to kcmgcs
18 consistent gets
1 consistent gets examination
1 consistent gets examination (fastpath)
18 consistent gets from cache
17 consistent gets pin
17 consistent gets pin (fastpath)
2 execute count
1 index range scans
147456 logical read bytes from cache
17 no work - consistent read gets
40 non-idle wait count
2 opened cursors cumulative
1 opened cursors current
2 parse count (total)
2 process last non-idle time
1 session cursor cache count
1 session cursor cache hits
18 session logical reads
1 sorts (memory)
2024 sorts (rows)
999 table fetch by rowid
3 user calls
We notice that the CBO indeed uses the index.
They key statistic to note here is that Consistent Gets is just 18, which is extremely low considering we’re returning 999 rows. This is due to the fact the index is currently extremely efficient as it can fetch multiple rows by visiting the same table block due to the excellent clustering/ordering of the required ID column values (and also due to my high arraysize session setting).
If we look at the CBO costings for this plan:
SQL> SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(sql_id=>'b1vwpu2rgn8p5',format=>'ALLSTATS LAST +cost +bytes'));
PLAN_TABLE_OUTPUT
____________________________________________________________________________________________________________________________________
SQL_ID b1vwpu2rgn8p5, child number 0
-------------------------------------
select * from bowie where id > 1 and id < 1001
Plan hash value: 1405654398
---------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows | A-Time |Buffers |
---------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 21 (100)| 999 |00:00:00.01 | 18 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED | BOWIE | 1 | 1000 | 108K| 21 (0)| 999 |00:00:00.01 | 18 |
|* 2 | INDEX RANGE SCAN | BOWIE_ID_I | 1 | 1000 | | 4 (0)| 999 |00:00:00.01 | 4 |
---------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID">1 AND "ID"<1001)
I’ve previously discussed many times how the CBO costs index access paths, but it’s always useful to go over this again, as it’s the most common question I get asked when I visit customer sites.
The KEY statistic the CBO has to determine is the estimated Selectivity of the query (the estimated percentage of rows to be returned), as this is the driver of all the subsequent CBO calculations.
The Selectivity of this range-based predicate query is calculated as follows:
Selectivity = (Highest Bound Value – Lowest Bound Value) / (Highest Value – Lowest Value)
= (1001-1) /(200000-1)
= 1000/199999
= approx. 0.005
Once Oracle has the selectivity, it can calculate the query Cardinality (estimated number of rows) as follows:
Cardinality = Selectivity x No of Rows
Cardinality = 0.005 x 200000 = 1000 rows
This is our visual window into the likelihood that the CBO has made an accurate decision with its execution plan. If the cardinality estimates are reasonably accurate, then the CBO is likely to generate a good plan. If the cardinality estimates are way off, then the CBO is more likely to generate an inappropriate plan.
The CBO cardinality estimate in the above plan is 1000 rows, whereas the number of rows actually returned is 999 rows.
So indeed, the CBO has got the cardinality almost spot on (except for a trivial rounding error) and so we have a high degree of confidence that the CBO is using the correct selectivity estimates when they get plugged into the following CBO formula for costing an index range scan (using this selectivity of 0.005 and the index statistics listed above):
Index Scan Cost = (blevel + ceil(effective index selectivity x leaf_blocks)) + ceil(effective table selectivity x clustering_factor)
= (1 + ceil(0.005 x 467)) + ceil(0.005 x 3250)
= (1 + 3) + 17
= 4 + 17 = 21
So we can clearly see where the CBO gets its costings for both reading the index during the Index Range Scan (4) and for the plan as a whole (21).
The CBO cost of 21 very closely resembles the 18 consistent gets accessed when the plan is executed. This to me suggests that the CBO has indeed costed this plan very accurately and appropriately.
It’s interesting to note in the above execution plan that Oracle is attributing 100% of this cost of 21 to CPU (21 (100)). That will be a discussion for another day…
OK, let’s now perform an update on the table, increasing the size of the rows such that I generate a bunch of migrated rows:
SQL> update bowie set name='THE RISE AND FALL OF BOWIE STARDUST AND THE SPIDERS FROM MARS'; 200,000 rows updated. SQL> commit; Commit complete.
If we now collect fresh statistics again using DBMS_STATS:
SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'BOWIE', estimate_percent=> null, no_invalidate=>false); PL/SQL procedure successfully completed. SQL> select table_name, num_rows, blocks, empty_blocks, avg_space, avg_row_len, chain_cnt from user_tables where table_name='BOWIE'; TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE AVG_ROW_LEN CHAIN_CNT _____________ ___________ _________ _______________ ____________ ______________ ____________ BOWIE 200000 4906 0 0 167 0 SQL> select index_name, blevel, leaf_blocks, clustering_factor from user_indexes where table_name='BOWIE'; INDEX_NAME BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR _____________ _________ ______________ ____________________ BOWIE_ID_I 1 473 3250
We notice that none of the key statistics have changed, except for the number of Table Blocks (now 4906, previously it was 3268) and the Average Row Length has also increased (now 167, previously it was 111). Both of these can of course be attributed to the increase in the size of the values now stored in the NAME column following the Update.
Importantly, notice that collecting statistics via DBMS_STATS does NOT collect data for the CHAIN_CNT statistic, it remains at 0 even though many migrated rows were actually generated by the Update statement (as we’ll see below).
Increasing the Table Blocks will result in an associated increase in the cost of reading this table via a Full Table Scan (FTS).
We notice that none of the index-related statistics changed following the Update statement (as in this example, Oracle does NOT update the ROWIDs of any of the migrated rows, Oracle simply stores a pointer in the original block to denote the new physical location of the migrated rows as previously discussed).
So if we only INCREASE the cost of a FTS (via having more Table Blocks) but keep intact all the previous index related statistics, then the CBO is certainly going to again select the same Index Range Scan plan, as the plan will have the same (cheaper than FTS) costings as before.
If we re-run the query again:
SQL> select * from bowie where id > 1 and id < 1001;
999 rows selected.
PLAN_TABLE_OUTPUT
_______________________________________________________________________________________________________________
SQL_ID b1vwpu2rgn8p5, child number 0
-------------------------------------
select * from bowie where id > 1 and id < 1001
Plan hash value: 1405654398
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 999 |00:00:00.01 | 666 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED | BOWIE | 1 | 1000 | 999 |00:00:00.01 | 666 |
|* 2 | INDEX RANGE SCAN | BOWIE_ID_I | 1 | 1000 | 999 |00:00:00.01 | 4 |
------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID">1 AND "ID"<1001)
Statistics
-----------------------------------------------------------
1 CPU used by this session
1 CPU used when call started
7709 RM usage
3 Requests to/from client
2 SQL*Net roundtrips to/from client
664 buffer is not pinned count
1662 buffer is pinned count
323 bytes received via SQL*Net from client
171500 bytes sent via SQL*Net to client
2 calls to get snapshot scn: kcmgss
2 calls to kcmgcs
666 consistent gets
1 consistent gets examination
1 consistent gets examination (fastpath)
666 consistent gets from cache
665 consistent gets pin
665 consistent gets pin (fastpath)
2 execute count
1 index range scans
5455872 logical read bytes from cache
665 no work - consistent read gets
39 non-idle wait count
1 non-idle wait time
2 opened cursors cumulative
1 opened cursors current
2 parse count (total)
1 process last non-idle time
2 session cursor cache count
666 session logical reads
1 sorts (memory)
2024 sorts (rows)
999 table fetch by rowid
327 table fetch continued row
3 user calls
We notice that indeed it’s the same Index Range Scan plan as before.
But we notice that the number of Consistent Gets has increased substantially to 666 (previously it was just 18). The reason for this large jump is due to the now 327 table fetch continued rows that need to be accessed due to the newly migrated rows following the Update. This number is then doubled (so 2 x 327 = 654) to represent the approximate additional Consistent Gets we now need to perform, as Oracle needs to read the additional table block to access the migrated row’s new physical location AND to now re-read the original table block to access the next row to be fetched (previously Oracle could read all the required consecutive rows required from the same table block within the one consistent get).
So it’s now actually substantially more expensive to read the required 1000 rows via this index due to this increase in necessary consistent gets.
But if we look at the actual cost of this plan now:
SQL> SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(sql_id=>'b1vwpu2rgn8p5',format=>'ALLSTATS LAST +cost +bytes'));
PLAN_TABLE_OUTPUT
____________________________________________________________________________________________________________________________________
SQL_ID b1vwpu2rgn8p5, child number 0
-------------------------------------
select * from bowie where id > 1 and id < 1001
Plan hash value: 1405654398
---------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows | A-Time |Buffers |
---------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 21 (100)| 999 |00:00:00.01 | 666 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED | BOWIE | 1 | 1000 | 163K| 21 (0)| 999 |00:00:00.01 | 666 |
|* 2 | INDEX RANGE SCAN | BOWIE_ID_I | 1 | 1000 | | 4 (0)| 999 |00:00:00.01 | 4 |
---------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID">1 AND "ID"<1001)
We notice that as expected (as none of the index-related statistics have changed), that despite being much more expensive to now use this index, the costs of this plan (4 for reading the index and 21 overall) remain unchanged.
I would argue that these CBO costs are no longer as accurate as the 21 total CBO cost does not so closely represent the actual 666 consistent gets now required.
Now, the 327 table fetch continued row statistics from the previous run is clear proof we indeed have migrated rows following the Update statement.
But if we want to confirm how many migrated rows we now have in the table, we can use the ANALYZE command to collect these additional statistics:
SQL> analyze table bowie compute statistics; Table BOWIE analyzed. SQL> select table_name, num_rows, blocks, empty_blocks, avg_space, avg_row_len, chain_cnt from user_tables where table_name='BOWIE'; TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE AVG_ROW_LEN CHAIN_CNT _____________ ___________ _________ _______________ ____________ ______________ ____________ BOWIE 200000 4906 86 415 170 56186
We notice that we now have a CHAIN_CNT of 56186.
Now this statistic can represent any row that is not housed inside a single table block (for which there could be a number of possible reasons, such as a row simply being too long to fit in a single table block), but as all rows are still relatively tiny, we can be certain that indeed all 56186 chained rows represent migrated rows.
Now that I’ve gone and used ANALYZE, primarily to generate this CHAIN_CNT statistic, my previous understanding of how the CBO costs migrated rows crumbles away, as I’ll discuss in my next post…
Possible Impact To Clustering Factor Now ROWIDs Are Updated When Rows Migrate Part I (“Growin’ Up”) March 1, 2023
Posted by Richard Foote in 19c, 19c New Features, Attribute Clustering, Autonomous Data Warehouse, Autonomous Database, Autonomous Transaction Processing, BLEVEL, CBO, Changing ROWID, Clustering Factor, Data Clustering, Hints, Index Access Path, Index Block Splits, Index Delete Operations, Index Height, Index Internals, Index Rebuild, Index statistics, Leaf Blocks, Migrated Rows, Oracle, Oracle Blog, Oracle Cloud, Oracle Cost Based Optimizer, Oracle General, Oracle Indexes, Oracle Indexing Internals Webinar, Oracle Statistics, Oracle19c, Performance Tuning, Richard Foote Training, Richard's Blog, ROWID.2 comments
In my previous post I discussed how an index can potentially be somewhat inflated in size after ROWIDs are updated on the fly after a substantial number of rows are migrated.
However, there’s another key “factor” of an index that in some scenarios can be impacted by this new ROWID behaviour with regard migrated rows.
To highlight this scenario, I’ll again start by creating and populating a table with ENABLE ROW MOVEMENT disabled:
SQL> create table bowie(id number, code1 number, code2 number, code3 number, code4 number, code5 number, code6 number, code7 number, code8 number, code9 number, code10 number, code11 number, code12 number, code13 number, code14 number, code15 number, code16 number, code17 number, code18 number, code19 number, code20 number, name varchar2(142)) PCTFREE 0; Table BOWIE created. SQL> insert into bowie SELECT rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, 'BOWIE' FROM dual CONNECT BY LEVEL <= 200000; 200,000 rows inserted. SQL> commit; Commit complete. SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'BOWIE', estimate_percent=> null, no_invalidate=>false); PL/SQL procedure successfully completed.
I’ll next create an index on the ID column. The important aspect with the ID column is that the data is entered monotonically in ID column order, so the associated index will have an excellent (very low) Clustering Factor:
SQL> create index bowie_id_i on bowie(id); Index BOWIE_ID_I created.
If we look at some key statistics of the table and index:
SQL> select table_name, num_rows, blocks from user_tables where table_name='BOWIE'; TABLE_NAME NUM_ROWS BLOCKS _____________ ___________ _________ BOWIE 200000 3268 SQL> select index_name, blevel, leaf_blocks, clustering_factor from user_indexes where table_name='BOWIE'; INDEX_NAME BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR _____________ _________ ______________ ____________________ BOWIE_ID_I 1 473 3250
We can see that the number of table blocks is 3268, the number of index leaf blocks is 473 and we indeed have a near perfect Clustering Factor of 3250.
If we run a couple of queries:
SQL> select * from bowie where id between 1 and 1000;
1,000 rows selected.
PLAN_TABLE_OUTPUT
_______________________________________________________________________________________________________________
SQL_ID gz5u92hmjwz1h, child number 0
-------------------------------------
select * from bowie where id between 1 and 1000
Plan hash value: 1405654398
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1000 |00:00:00.01 | 18 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED | BOWIE | 1 | 1000 | 1000 |00:00:00.01 | 18 |
|* 2 | INDEX RANGE SCAN | BOWIE_ID_I | 1 | 1000 | 1000 |00:00:00.01 | 4 |
------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID">=1 AND "ID"<=1000)
Note
-----
- automatic DOP: Computed Degree of Parallelism is 1 because of no expensive parallel operation
Statistics
-----------------------------------------------------------
1 CPU used by this session
1 CPU used when call started
7353 RM usage
3 Requests to/from client
2 SQL*Net roundtrips to/from client
16 buffer is not pinned count
1985 buffer is pinned count
324 bytes received via SQL*Net from client
171305 bytes sent via SQL*Net to client
2 calls to get snapshot scn: kcmgss
2 calls to kcmgcs
18 consistent gets
1 consistent gets examination
1 consistent gets examination (fastpath)
18 consistent gets from cache
17 consistent gets pin
17 consistent gets pin (fastpath)
2 execute count
1 index range scans
147456 logical read bytes from cache
17 no work - consistent read gets
38 non-idle wait count
2 opened cursors cumulative
1 opened cursors current
2 parse count (total)
1 process last non-idle time
2 session cursor cache count
18 session logical reads
1 sorts (memory)
2024 sorts (rows)
1000 table fetch by rowid
3 user calls
We can see for this first query that returns 1000 rows, it requires just 18 consistent gets, thanks primarily due to the efficient index with the perfect Clustering Factor.
If we look at the cost of this plan:
SQL> SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(sql_id=>'gz5u92hmjwz1h',format=>'ALLSTATS LAST +cost +bytes'));
PLAN_TABLE_OUTPUT
____________________________________________________________________________________________________________________________________
SQL_ID gz5u92hmjwz1h, child number 0
-------------------------------------
select * from bowie where id between 1 and 1000
Plan hash value: 1405654398
---------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 21 (100)| 1000 |00:00:00.01 | 18 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED | BOWIE | 1 | 1000 | 108K| 21 (0)| 1000 |00:00:00.01 | 18 |
|* 2 | INDEX RANGE SCAN | BOWIE_ID_I | 1 | 1000 | | 4 (0)| 1000 |00:00:00.01 | 4 |
---------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID">=1 AND "ID"<=1000)
We can see the plan has an accurate cost of just 21.
If we now run a similar query that returns a few more rows:
SQL> select * from bowie where id between 1 and 4200;
4,200 rows selected.
PLAN_TABLE_OUTPUT
_______________________________________________________________________________________________________________
SQL_ID c376kdhy5b0x9, child number 0
-------------------------------------
select * from bowie where id between 1 and 4200
Plan hash value: 1405654398
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 4200 |00:00:00.01 | 68 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED | BOWIE | 1 | 4200 | 4200 |00:00:00.01 | 68 |
|* 2 | INDEX RANGE SCAN | BOWIE_ID_I | 1 | 4200 | 4200 |00:00:00.01 | 11 |
------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID">=1 AND "ID"<=4200)
Note
-----
- automatic DOP: Computed Degree of Parallelism is 1 because of no expensive parallel operation
Statistics
-----------------------------------------------------------
1 CPU used by this session
1 CPU used when call started
1 DB time
11353 RM usage
3 Requests to/from client
2 SQL*Net roundtrips to/from client
59 buffer is not pinned count
8342 buffer is pinned count
324 bytes received via SQL*Net from client
461834 bytes sent via SQL*Net to client
2 calls to get snapshot scn: kcmgss
2 calls to kcmgcs
68 consistent gets
1 consistent gets examination
1 consistent gets examination (fastpath)
68 consistent gets from cache
67 consistent gets pin
67 consistent gets pin (fastpath)
2 execute count
1 index range scans
557056 logical read bytes from cache
67 no work - consistent read gets
73 non-idle wait count
2 opened cursors cumulative
1 opened cursors current
2 parse count (total)
1 process last non-idle time
2 session cursor cache count
68 session logical reads
1 sorts (memory)
2024 sorts (rows)
4200 table fetch by rowid
3 user calls
We can see that it only required just 68 consistent gets to return 4200 rows, thanks to the excellent data clustering and associated very low Clustering Factor.
If we look at the cost of this plan:
SQL> SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(sql_id=>'c376kdhy5b0x9',format=>'ALLSTATS LAST +cost +bytes'));
PLAN_TABLE_OUTPUT
____________________________________________________________________________________________________________________________________
SQL_ID c376kdhy5b0x9, child number 0
-------------------------------------
select * from bowie where id between 1 and 4200
Plan hash value: 1405654398
---------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 80 (100)| 4200 |00:00:00.01 | 68 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED | BOWIE | 1 | 4200 | 455K| 80 (0)| 4200 |00:00:00.01 | 68 |
|* 2 | INDEX RANGE SCAN | BOWIE_ID_I | 1 | 4200 | | 11 (0)| 4200 |00:00:00.01 | 11 |
---------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID">=1 AND "ID"<=4200)
We can see the cost of the plan is currently a relatively accurate 80.
OK, let’s now perform an update on this table that generates a bunch of migrated rows:
SQL> update bowie set name='THE RISE AND FALL OF BOWIE STARDUST AND THE SPIDERS FROM MARS'; 200,000 rows updated. SQL> commit; Commit complete.
If we now look at the table and index statistics:
SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'BOWIE', estimate_percent=> null, no_invalidate=>false); PL/SQL procedure successfully completed. SQL> select table_name, num_rows, blocks from user_tables where table_name='BOWIE'; TABLE_NAME NUM_ROWS BLOCKS _____________ ___________ _________ BOWIE 200000 4906
We can see that the table blocks value has increased to 4906 (previously 3268). This as explained previously is to due in large part to the increased NAME column values and also due to the pointers in the original table blocks that point to the new locations of the migrated rows.
This relates to approximately a 50% increase in table blocks.
If we look at the current index statistics:
SQL> select index_name, blevel, leaf_blocks, clustering_factor from user_indexes where table_name='BOWIE'; INDEX_NAME BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR _____________ _________ ______________ ____________________ BOWIE_ID_I 1 473 3250
We can see that these values are all unchanged, as the ROWIDs in indexes remain unchanged when a row migrates, when ENABLE ROW MOVEMENT is not set.
Therefore, when we re-run these same queries:
SQL> select * from bowie where id between 1 and 1000;
1,000 rows selected.
PLAN_TABLE_OUTPUT
_______________________________________________________________________________________________________________
SQL_ID gz5u92hmjwz1h, child number 0
-------------------------------------
select * from bowie where id between 1 and 1000
Plan hash value: 1405654398
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1000 |00:00:00.01 | 666 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED | BOWIE | 1 | 1000 | 1000 |00:00:00.01 | 666 |
|* 2 | INDEX RANGE SCAN | BOWIE_ID_I | 1 | 1000 | 1000 |00:00:00.01 | 4 |
------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID">=1 AND "ID"<=1000)
Note
-----
- automatic DOP: Computed Degree of Parallelism is 1 because of no expensive parallel operation
Statistics
-----------------------------------------------------------
1 DB time
7967 RM usage
3 Requests to/from client
2 SQL*Net roundtrips to/from client
664 buffer is not pinned count
1664 buffer is pinned count
324 bytes received via SQL*Net from client
171419 bytes sent via SQL*Net to client
2 calls to get snapshot scn: kcmgss
2 calls to kcmgcs
666 consistent gets
1 consistent gets examination
1 consistent gets examination (fastpath)
666 consistent gets from cache
665 consistent gets pin
665 consistent gets pin (fastpath)
2 execute count
1 index range scans
5455872 logical read bytes from cache
665 no work - consistent read gets
37 non-idle wait count
2 opened cursors cumulative
1 opened cursors current
2 parse count (total)
1 process last non-idle time
2 session cursor cache count
666 session logical reads
1 sorts (memory)
2024 sorts (rows)
1000 table fetch by rowid
327 table fetch continued row
3 user calls
The number of consistent gets has increased significantly to 666 (previously it was just 18).
Now we can attributed an increase of approximately 50% of the previous consistent gets (18 x 0.50 = 9) due to the 50% increase in table blocks required now to store the rows due to the increased row size.
We can also attribute an additional 327 consistent gets for the table fetch continued row value listed in the statistics, representing the extra consistent gets required to access the migrated rows from their new physical location.
But 18 + 9 + 327 = 354 still leaves us short of the new 666 consistent gets value.
The problem with having to visit another table block to get a row from its new location is that it means Oracle has to re-access again the original table block to get the next row (rather than reading multiple rows with the same consistent get).
So it’s actually approximately 2 x table fetch continued row, by which the number of consistent gets is going to increase when accessing migrated rows (noting that the last migrated row in a block will only incur a additional consistent get as the next table block accessed will differ regardless).
If we look at the new CBO cost for this plan:
SQL> SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(sql_id=>'gz5u92hmjwz1h',format=>'ALLSTATS LAST +cost +bytes'));
PLAN_TABLE_OUTPUT
____________________________________________________________________________________________________________________________________
SQL_ID gz5u92hmjwz1h, child number 0
-------------------------------------
select * from bowie where id between 1 and 1000
Plan hash value: 1405654398
---------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 21 (100)| 1000 |00:00:00.01 | 666 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED | BOWIE | 1 | 1000 | 163K| 21 (0)| 1000 |00:00:00.01 | 666 |
|* 2 | INDEX RANGE SCAN | BOWIE_ID_I | 1 | 1000 | | 4 (0)| 1000 |00:00:00.01 | 4 |
---------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID">=1 AND "ID"<=1000)
We notice the CBO cost for this plan remains unchanged at 21.
This is totally to be expected, as the index statistics by which the cost of an index scan is calculated are unchanged.
Considering the rough “rule of thumb” is that the CBO cost of an index scan should be in the ball-park of the number of possible IOs, the fact the plan now uses 666 consistent gets highlights this cost of just 21 is no longer as accurate…
If we look at the second SQL that returns 4200 rows:
SQL> select * from bowie where id between 1 and 4200;
4,200 rows selected.
PLAN_TABLE_OUTPUT
_______________________________________________________________________________________________________________
SQL_ID c376kdhy5b0x9, child number 0
-------------------------------------
select * from bowie where id between 1 and 4200
Plan hash value: 1405654398
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 4200 |00:00:00.01 | 2771 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED | BOWIE | 1 | 4200 | 4200 |00:00:00.01 | 2771 |
|* 2 | INDEX RANGE SCAN | BOWIE_ID_I | 1 | 4200 | 4200 |00:00:00.01 | 11 |
------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID">=1 AND "ID"<=4200)
Note
-----
- automatic DOP: Computed Degree of Parallelism is 1 because of no expensive parallel operation
Statistics
-----------------------------------------------------------
2 CPU used by this session
2 CPU used when call started
2 DB time
14103 RM usage
3 Requests to/from client
2 SQL*Net roundtrips to/from client
2762 buffer is not pinned count
7005 buffer is pinned count
324 bytes received via SQL*Net from client
461947 bytes sent via SQL*Net to client
2 calls to get snapshot scn: kcmgss
2 calls to kcmgcs
2771 consistent gets
1 consistent gets examination
1 consistent gets examination (fastpath)
2771 consistent gets from cache
2770 consistent gets pin
2770 consistent gets pin (fastpath)
2 execute count
1 index range scans
22700032 logical read bytes from cache
2770 no work - consistent read gets
72 non-idle wait count
2 opened cursors cumulative
1 opened cursors current
2 parse count (total)
1 process last non-idle time
2 session cursor cache count
2771 session logical reads
1 sorts (memory)
2024 sorts (rows)
4200 table fetch by rowid
1366 table fetch continued row
3 user calls
We again notice consistent gets has increased significantly to 2771 (previously it was just 68). Again, these additional consistent gets can not be attributed to the extra size of the table and the additional approximate 2 x 1366 table fetch continued row gets.
If we now look at the cost of this plan:
SQL> SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(sql_id=>'c376kdhy5b0x9',format=>'ALLSTATS LAST +cost +bytes'));
PLAN_TABLE_OUTPUT
________________________________________________________________________________________________________________________
____________
SQL_ID c376kdhy5b0x9, child number 0
-------------------------------------
select * from bowie where id between 1 and 4200
Plan hash value: 1405654398
---------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 80 (100)| 4200 |00:00:00.01 | 2771 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED | BOWIE | 1 | 4200 | 684K| 80 (0)| 4200 |00:00:00.01 | 2771 |
|* 2 | INDEX RANGE SCAN | BOWIE_ID_I | 1 | 4200 | | 11 (0)| 4200 |00:00:00.01 | 11 |
---------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID">=1 AND "ID"<=4200)
We again notice the CBO cost for this plan remains unchanged at 80, again totally expected as the underlying index statistics have remain unchanged after the update statement.
But again, not necessary as accurate a cost as it was previously…
If we repeat this demo, but this time on a table with ENABLE ROW MOVEMENT enabled:
SQL> create table bowie2(id number, code1 number, code2 number, code3 number, code4 number, code5 number, code6 number, code7 number, code8 number, code9 number, code10 number, code11 number, code12 number, code13 number, code14 number, code15 number, code16 number, code17 number, code18 number, code19 number, code20 number, name varchar2(142)) PCTFREE 0 ENABLE ROW MOVEMENT;
Table BOWIE2 created.
SQL> insert into bowie2 SELECT rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, 'BOWIE' FROM dual CONNECT BY LEVEL <= 200000;
200,000 rows inserted.
SQL> commit;
Commit complete.
SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'BOWIE2', estimate_percent=> null, no_invalidate=>false);
PL/SQL procedure successfully completed.
SQL> create index bowie2_id_i on bowie2(id);
Index BOWIE2_ID_I created.
SQL> select table_name, num_rows, blocks from user_tables where table_name='BOWIE2';
TABLE_NAME NUM_ROWS BLOCKS
_____________ ___________ _________
BOWIE2 200000 3268
SQL> select index_name, blevel, leaf_blocks, clustering_factor from user_indexes where table_name='BOWIE2';
INDEX_NAME BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR
__________________ _________ ______________ ____________________
BOWIE2_ID_I 1 473 3250
The table and index statistics are currently identical to the previous demo.
If we run the same two equivalent queries:
SQL> select * from bowie2 where id between 1 and 1000;
1,000 rows selected.
PLAN_TABLE_OUTPUT
________________________________________________________________________________________________________________
SQL_ID gtkw2704bxj7q, child number 0
-------------------------------------
select * from bowie2 where id between 1 and 1000
Plan hash value: 3243780227
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1000 |00:00:00.01 | 18 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED | BOWIE2 | 1 | 1000 | 1000 |00:00:00.01 | 18 |
|* 2 | INDEX RANGE SCAN | BOWIE2_ID_I | 1 | 1000 | 1000 |00:00:00.01 | 4 |
-------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID">=1 AND "ID"<=1000)
Note
-----
- automatic DOP: Computed Degree of Parallelism is 1 because of no expensive parallel operation
Statistics
-----------------------------------------------------------
1 CPU used by this session
1 CPU used when call started
7909 RM usage
3 Requests to/from client
2 SQL*Net roundtrips to/from client
16 buffer is not pinned count
1985 buffer is pinned count
325 bytes received via SQL*Net from client
171306 bytes sent via SQL*Net to client
2 calls to get snapshot scn: kcmgss
2 calls to kcmgcs
18 consistent gets
1 consistent gets examination
1 consistent gets examination (fastpath)
18 consistent gets from cache
17 consistent gets pin
17 consistent gets pin (fastpath)
2 execute count
1 index range scans
147456 logical read bytes from cache
17 no work - consistent read gets
37 non-idle wait count
2 opened cursors cumulative
1 opened cursors current
2 parse count (total)
1 process last non-idle time
2 session cursor cache count
18 session logical reads
1 sorts (memory)
2024 sorts (rows)
1000 table fetch by rowid
3 user calls
SQL> SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(sql_id=>'gtkw2704bxj7q',format=>'ALLSTATS LAST +cost +bytes'));
PLAN_TABLE_OUTPUT
_____________________________________________________________________________________________________________________________________
SQL_ID gtkw2704bxj7q, child number 0
-------------------------------------
select * from bowie2 where id between 1 and 1000
Plan hash value: 3243780227
----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 21 (100)| 1000 |00:00:00.01 | 18 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED | BOWIE2 | 1 | 1000 | 108K| 21 (0)| 1000 |00:00:00.01 | 18 |
|* 2 | INDEX RANGE SCAN | BOWIE2_ID_I | 1 | 1000 | | 4 (0)| 1000 |00:00:00.01 | 4 |
----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID">=1 AND "ID"<=1000)
SQL> select * from bowie2 where id between 1 and 4200;
4,200 rows selected.
PLAN_TABLE_OUTPUT
________________________________________________________________________________________________________________
SQL_ID 25qktyn35b662, child number 0
-------------------------------------
select * from bowie2 where id between 1 and 4200
Plan hash value: 3243780227
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 4200 |00:00:00.01 | 68 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED | BOWIE2 | 1 | 4200 | 4200 |00:00:00.01 | 68 |
|* 2 | INDEX RANGE SCAN | BOWIE2_ID_I | 1 | 4200 | 4200 |00:00:00.01 | 11 |
-------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID">=1 AND "ID"<=4200)
Note
-----
- automatic DOP: Computed Degree of Parallelism is 1 because of no expensive parallel operation
Statistics
-----------------------------------------------------------
1 CPU used by this session
1 CPU used when call started
2 DB time
13157 RM usage
3 Requests to/from client
2 SQL*Net roundtrips to/from client
59 buffer is not pinned count
8342 buffer is pinned count
325 bytes received via SQL*Net from client
461838 bytes sent via SQL*Net to client
2 calls to get snapshot scn: kcmgss
2 calls to kcmgcs
68 consistent gets
1 consistent gets examination
1 consistent gets examination (fastpath)
68 consistent gets from cache
67 consistent gets pin
67 consistent gets pin (fastpath)
2 execute count
1 index range scans
557056 logical read bytes from cache
67 no work - consistent read gets
73 non-idle wait count
2 opened cursors cumulative
1 opened cursors current
2 parse count (total)
1 process last non-idle time
2 session cursor cache count
68 session logical reads
1 sorts (memory)
2024 sorts (rows)
4200 table fetch by rowid
3 user calls
SQL> SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(sql_id=>'25qktyn35b662',format=>'ALLSTATS LAST +cost +bytes'));
PLAN_TABLE_OUTPUT
_____________________________________________________________________________________________________________________________________
SQL_ID 25qktyn35b662, child number 0
-------------------------------------
select * from bowie2 where id between 1 and 4200
Plan hash value: 3243780227
----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 80 (100)| 4200 |00:00:00.01 | 68 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED | BOWIE2 | 1 | 4200 | 455K| 80 (0)| 4200 |00:00:00.01 | 68 |
|* 2 | INDEX RANGE SCAN | BOWIE2_ID_I | 1 | 4200 | | 11 (0)| 4200 |00:00:00.01 | 11 |
----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID">=1 AND "ID"<=4200)
With identical table/index statistics, we notice as expected that both SQLs have the same consistent gets and CBO costs as with the previous demo.
If we now repeat the equivalent Update statement:
SQL> update bowie2 set name='THE RISE AND FALL OF BOWIE STARDUST AND THE SPIDERS FROM MARS'; 200,000 rows updated. SQL> commit; Commit complete. SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'BOWIE2', estimate_percent=> null, no_invalidate=>false); PL/SQL procedure successfully completed.
If we look at the table statistics:
SQL> select table_name, num_rows, blocks from user_tables where table_name='BOWIE2'; TABLE_NAME NUM_ROWS BLOCKS _____________ ___________ _________ BOWIE2 200000 4654
We notice the number of table blocks has increased to 4654 due to the increased row lengths, but not as much as with the previous demo (where table blocks increased to 4906) as in this scenario, Oracle does not have to store the row location pointers in the original blocks for the migrated rows.
If we look at the index statistics:
SQL> select index_name, blevel, leaf_blocks, clustering_factor from user_indexes where table_name='BOWIE2';
INDEX_NAME BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR
______________ _________ ______________ ____________________
BOWIE2_ID_I 2 945 109061
We notice that these are substantially different from the first demo, where ROWIDs for migrated rows are not updated on the fly.
By now updating the ROWIDs, the indexes can possibly increase in size as they have to store both the previous and new ROWIDs in separate index entries and hence Oracle is more likely to perform additional index block splits (as I discussed in my previous post).
The LEAF_BLOCKS are now 945 (previously 473) and even the BLEVEL has increased from 1 to 2.
Additionally, and perhaps importantly for specific key indexes, the Clustering Factor value of indexes can also be impacted. By migrating rows and physically storing them in different locations, this can potentially detrimentally impact the tight clustering of rows based on specific column values.
The Clustering Factor for the index on the monotonically increased ID column has now increased significantly to 109061, up from the previously perfect 3250.
So columns that have naturally good clustering (e.g.: monotonically increasing values such as IDs and dates) or have been manually well clustered for performance purposes, can have the Clustering Factor of associated indexes detrimentally impacted by migrated rows.
If we re-run the first query:
SQL> select * from bowie2 where id between 1 and 1000;
1,000 rows selected.
PLAN_TABLE_OUTPUT
________________________________________________________________________________________________________________
SQL_ID gtkw2704bxj7q, child number 0
-------------------------------------
select * from bowie2 where id between 1 and 1000
Plan hash value: 3243780227
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1000 |00:00:00.01 | 639 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED | BOWIE2 | 1 | 1000 | 1000 |00:00:00.01 | 639 |
|* 2 | INDEX RANGE SCAN | BOWIE2_ID_I | 1 | 1000 | 1000 |00:00:00.01 | 7 |
-------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID">=1 AND "ID"<=1000)
Note
-----
- automatic DOP: Computed Degree of Parallelism is 1 because of no expensive parallel operation
Statistics
-----------------------------------------------------------
1 CPU used by this session
1 CPU used when call started
1 DB time
15262 RM usage
3 Requests to/from client
2 SQL*Net roundtrips to/from client
634 buffer is not pinned count
1367 buffer is pinned count
325 bytes received via SQL*Net from client
171421 bytes sent via SQL*Net to client
2 calls to get snapshot scn: kcmgss
2 calls to kcmgcs
639 consistent gets
2 consistent gets examination
2 consistent gets examination (fastpath)
639 consistent gets from cache
637 consistent gets pin
637 consistent gets pin (fastpath)
2 execute count
1 index range scans
5234688 logical read bytes from cache
637 no work - consistent read gets
38 non-idle wait count
1 non-idle wait time
2 opened cursors cumulative
1 opened cursors current
2 parse count (total)
1 process last non-idle time
2 session cursor cache count
639 session logical reads
1 sorts (memory)
2024 sorts (rows)
1000 table fetch by rowid
3 user calls
I discussed in a previous post how by updating the ROWIDs of migrated rows we can improve performance, as Oracle can go directly to the correct new physical location of a migrated row.
But for some specific indexes, where data clustering is crucial, and we have a significant number migrated rows, this might not necessarily be the case.
We can see consistent gets here has increased to 639 (previously is was just 21), and so not hugely different from the 666 consistent gets required to fetch the migrated rows when the ROWIDs were not updated in the first demo.
If we look at the CBO costings:
SQL> SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(sql_id=>'gtkw2704bxj7q',format=>'ALLSTATS LAST +cost +bytes'));
PLAN_TABLE_OUTPUT
_____________________________________________________________________________________________________________________________________
SQL_ID gtkw2704bxj7q, child number 0
-------------------------------------
select * from bowie2 where id between 1 and 1000
Plan hash value: 3243780227
----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 553 (100)| 1000 |00:00:00.01 | 639 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED | BOWIE2 | 1 | 1000 | 163K| 553 (0)| 1000 |00:00:00.01 | 639 |
|* 2 | INDEX RANGE SCAN | BOWIE2_ID_I | 1 | 1000 | | 7 (0)| 1000 |00:00:00.01 | 7 |
----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID">=1 AND "ID"<=1000)
We can see the CBO cost has increased significantly to 553 (previously it was just 21).
With a much increased Clustering Factor, this will obviously impact the CBO costs of associated index scans.
In very extreme cases, these possible changes in the Clustering Factor can even impact the viability of using the index.
If we re-run the second query returning the 4200 rows:
SQL> select * from bowie2 where id between 1 and 4200;
4,200 rows selected.
PLAN_TABLE_OUTPUT
_____________________________________________________________________________________________________
SQL_ID 25qktyn35b662, child number 0
-------------------------------------
select * from bowie2 where id between 1 and 4200
Plan hash value: 1495904576
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 4200 |00:00:00.02 | 4572 |
|* 1 | TABLE ACCESS STORAGE FULL | BOWIE2 | 1 | 4200 | 4200 |00:00:00.02 | 4572 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - storage(("ID"<=4200 AND "ID">=1))
filter(("ID"<=4200 AND "ID">=1))
We can see that the CBO has now chosen to perform a Full Table Scan (FTS), rather than use the now less efficient index to return this number of rows.
If we look at the CBO costings of this FTS plan:
SQL> SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(sql_id=>'25qktyn35b662',format=>'ALLSTATS LAST +cost +bytes'));
PLAN_TABLE_OUTPUT
______________________________________________________________________________________________________________________
SQL_ID 25qktyn35b662, child number 0
-------------------------------------
select * from bowie2 where id between 1 and 4200
Plan hash value: 1495904576
-------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 1264 (100)| 4200 |00:00:00.02 | 4572 |
|* 1 | TABLE ACCESS STORAGE FULL | BOWIE2 | 1 | 4200 | 684K| 1264 (1)| 4200 |00:00:00.02 | 4572 |
-------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - storage(("ID"<=4200 AND "ID">=1))
filter(("ID"<=4200 AND "ID">=1))
The cost of the FTS plan is 1264.
If we compare this is a plan that used the index:
SQL> select /*+ index (bowie2) */ * from bowie2 where id between 1 and 4200;
4,200 rows selected.
PLAN_TABLE_OUTPUT
________________________________________________________________________________________________________________
SQL_ID bzm2vhchqpq7w, child number 0
-------------------------------------
select /*+ index (bowie2) */ * from bowie2 where id between 1 and 4200
Plan hash value: 3243780227
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 4200 |00:00:00.01 | 2665 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED | BOWIE2 | 1 | 4200 | 4200 |00:00:00.01 | 2665 |
|* 2 | INDEX RANGE SCAN | BOWIE2_ID_I | 1 | 4200 | 4200 |00:00:00.01 | 21 |
-------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID">=1 AND "ID"<=4200)
Note
-----
- automatic DOP: Computed Degree of Parallelism is 1 because of no expensive parallel operation
Statistics
-----------------------------------------------------------
2 CPU used by this session
2 CPU used when call started
2 DB time
14531 RM usage
3 Requests to/from client
2 SQL*Net roundtrips to/from client
2646 buffer is not pinned count
5755 buffer is pinned count
348 bytes received via SQL*Net from client
462143 bytes sent via SQL*Net to client
2 calls to get snapshot scn: kcmgss
2 calls to kcmgcs
2665 consistent gets
2 consistent gets examination
2 consistent gets examination (fastpath)
2665 consistent gets from cache
2663 consistent gets pin
2663 consistent gets pin (fastpath)
2 execute count
1 index range scans
21831680 logical read bytes from cache
2663 no work - consistent read gets
73 non-idle wait count
2 opened cursors cumulative
1 opened cursors current
2 parse count (total)
3 process last non-idle time
2 session cursor cache count
2665 session logical reads
1 sorts (memory)
2024 sorts (rows)
4200 table fetch by rowid
3 user calls
SQL> SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(sql_id=>'bzm2vhchqpq7w',format=>'ALLSTATS LAST +cost +bytes'));
PLAN_TABLE_OUTPUT
_____________________________________________________________________________________________________________________________________
SQL_ID bzm2vhchqpq7w, child number 0
-------------------------------------
select /*+ index (bowie2) */ * from bowie2 where id between 1 and 4200
Plan hash value: 3243780227
----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 2314 (100)| 4200 |00:00:00.01 | 2665 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED | BOWIE2 | 1 | 4200 | 684K| 2314 (1)| 4200 |00:00:00.01 | 2665 |
|* 2 | INDEX RANGE SCAN | BOWIE2_ID_I | 1 | 4200 | | 22 (0)| 4200 |00:00:00.01 | 21 |
----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID">=1 AND "ID"<=4200)
The cost of using the index to retrieve the 4200 rows is 2310, more than the 1264 of the FTS.
For the vast majority of indexes, updating the ROWIDs for migrated rows will result in better performance, as such indexes will be able to directly access the correct new physical location of migrated rows, rather than having to visit the original table block and then follow the stored pointer to the new table block.
But for some very specific indexes, where data clustering is crucial, AND we have a significant number migrated rows, this might not necessarily be the case. The performance benefit might be minimal at best.
That’s more than enough for one post 🙂
In my next post, I’ll discuss how to potentially remedy these performance implications, both for tables with or without ENABLE TABLE MOVEMENT enabled…
Some Things To Consider Now ROWIDs Are Updated When Rows Migrate Part II (“Look Back In Anger”) February 24, 2023
Posted by Richard Foote in 19c, Autonomous Database, Autonomous Transaction Processing, BLEVEL, Changing ROWID, Index Internals, Leaf Blocks, Migrated Rows, Oracle, Oracle Blog, Oracle Cloud, Oracle General, Oracle Indexes, Oracle19c, Richard's Blog, ROWID.4 comments
Some weekend reading…
In my previous post, I discussed a couple of potential areas of concern with the ROWIDs of migrated rows now being updated on the fly in Oracle Autonomous Databases, namely that it can cause issues with applications that reply on stored ROWIDs not changing and that there are additional resources required to maintain such ROWIDs in corresponding indexes, especially if there are many indexes on a table.
In this post, I’ll discuss another issue to just bear in mind with this change in behaviour.
To illustrate, I’ll run a demo similar to the previous post, first creating and populating a table with no ENABLE ROW MOVEMENT set:
SQL> create table ziggy(id number, code1 number, code2 number, code3 number, code4 number, code5 number, code6 number, code7 number, code8 number, code9 number, code10 number, code11 number, code12 number, code13 number, code14 number, code15 number, code16 number, code17 number, code18 number, code19 number, code20 number, name varchar2(142)) PCTFREE 0; Table ZIGGY created. SQL> insert into ziggy SELECT rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, 'BOWIE' FROM dual CONNECT BY LEVEL <= 200000; 200,000 rows inserted. SQL> commit; Commit complete. SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'ZIGGY'); PL/SQL procedure successfully completed. SQL> analyze table ziggy compute statistics; Table ZIGGY analyzed.
I’ll next create a bunch of indexes on the table:
SQL> create index ziggy_id_i on ziggy(id); Index ZIGGY_ID_I created. SQL> create index ziggy_code1_i on ziggy(code1); Index ZIGGY_CODE1_I created. SQL> create index ziggy_code2_i on ziggy(code2); Index ZIGGY_CODE2_I created. SQL> create index ziggy_code3_i on ziggy(code3); Index ZIGGY_CODE3_I created. SQL> create index ziggy_code4_i on ziggy(code4); Index ZIGGY_CODE4_I created. SQL> create index ziggy_code5_i on ziggy(code5); Index ZIGGY_CODE5_I created. SQL> create index ziggy_code6_i on ziggy(code6); Index ZIGGY_CODE6_I created. SQL> create index ziggy_code7_i on ziggy(code7); Index ZIGGY_CODE7_I created. SQL> create index ziggy_code8_i on ziggy(code8); Index ZIGGY_CODE8_I created. SQL> create index ziggy_code9_i on ziggy(code9); Index ZIGGY_CODE9_I created. SQL> create index ziggy_code10_i on ziggy(code10); Index ZIGGY_CODE10_I created. SQL> create index ziggy_code11_i on ziggy(code11); Index ZIGGY_CODE11_I created. SQL> create index ziggy_code12_i on ziggy(code12); Index ZIGGY_CODE12_I created. SQL> create index ziggy_code13_i on ziggy(code13); Index ZIGGY_CODE13_I created. SQL> create index ziggy_code14_i on ziggy(code14); Index ZIGGY_CODE14_I created. SQL> create index ziggy_code15_i on ziggy(code15); Index ZIGGY_CODE15_I created. SQL> create index ziggy_code16_i on ziggy(code16); Index ZIGGY_CODE16_I created. SQL> create index ziggy_code17_i on ziggy(code17); Index ZIGGY_CODE17_I created. SQL> create index ziggy_code18_i on ziggy(code18); Index ZIGGY_CODE18_I created. SQL> create index ziggy_code19_i on ziggy(code19); Index ZIGGY_CODE19_I created. SQL> create index ziggy_code20_i on ziggy(code20); Index ZIGGY_CODE20_I created.
Let’s take note of the size of the table and its associated indexes:
SQL> select table_name, num_rows, blocks, empty_blocks, avg_space, avg_row_len, chain_cnt
from user_tables where table_name='ZIGGY';
TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE AVG_ROW_LEN CHAIN_CNT
_____________ ___________ _________ _______________ ____________ ______________ ____________
ZIGGY 200000 3268 60 857 113 0
SQL> select index_name, blevel, leaf_blocks, clustering_factor from user_indexes
where table_name='ZIGGY';
INDEX_NAME BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR
_________________ _________ ______________ ____________________
ZIGGY_CODE20_I 1 473 3250
ZIGGY_ID_I 1 473 3250
ZIGGY_CODE1_I 1 473 3250
ZIGGY_CODE2_I 1 473 3250
ZIGGY_CODE3_I 1 473 3250
ZIGGY_CODE4_I 1 473 3250
ZIGGY_CODE5_I 1 473 3250
ZIGGY_CODE6_I 1 473 3250
ZIGGY_CODE7_I 1 473 3250
ZIGGY_CODE8_I 1 473 3250
ZIGGY_CODE9_I 1 473 3250
ZIGGY_CODE10_I 1 473 3250
ZIGGY_CODE11_I 1 473 3250
ZIGGY_CODE12_I 1 473 3250
ZIGGY_CODE13_I 1 473 3250
ZIGGY_CODE14_I 1 473 3250
ZIGGY_CODE15_I 1 473 3250
ZIGGY_CODE16_I 1 473 3250
ZIGGY_CODE17_I 1 473 3250
ZIGGY_CODE18_I 1 473 3250
ZIGGY_CODE19_I 1 473 3250
We next perform an update on the table that will increase the row size sufficiently to result in a bunch of migrated rows:
SQL> update ziggy set name='THE RISE AND FALL OF ZIGGY STARDUST AND THE SPIDERS FROM MARS'; 200,000 rows updated. Elapsed: 00:00:07.716
I’ll then perform a COMMIT and look at differences in the table statistics:
SQL> commit; Commit complete. SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'ZIGGY'); PL/SQL procedure successfully completed. SQL> analyze table ziggy compute statistics; Table ZIGGY analyzed. SQL> select table_name, num_rows, blocks, empty_blocks, avg_space, avg_row_len, chain_cnt from user_tables where table_name='ZIGGY'; TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE AVG_ROW_LEN CHAIN_CNT _____________ ___________ _________ _______________ ____________ ______________ ____________ ZIGGY 200000 4906 86 415 170 56186
We can see that there are indeed a bunch of migrated/chained rows, some 56186 of them.
We also notice that the size of the table has increased to 4906 blocks (previously is was 3268). This increase is in large part due to the increased size of the NAME column value, but also partly due to the storage allocated to pointers that are stored in the original block to denote the new location of the migrated rows (as discussed previously here).
If we look at the current state of the indexes:
SQL> select index_name, blevel, leaf_blocks, clustering_factor
from user_indexes where table_name='ZIGGY';
INDEX_NAME BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR
_________________ _________ ______________ ____________________
ZIGGY_CODE7_I 1 473 3250
ZIGGY_CODE8_I 1 473 3250
ZIGGY_CODE9_I 1 473 3250
ZIGGY_CODE10_I 1 473 3250
ZIGGY_CODE11_I 1 473 3250
ZIGGY_CODE12_I 1 473 3250
ZIGGY_CODE13_I 1 473 3250
ZIGGY_CODE14_I 1 473 3250
ZIGGY_CODE15_I 1 473 3250
ZIGGY_CODE16_I 1 473 3250
ZIGGY_CODE17_I 1 473 3250
ZIGGY_CODE18_I 1 473 3250
ZIGGY_CODE19_I 1 473 3250
ZIGGY_CODE20_I 1 473 3250
ZIGGY_ID_I 1 473 3250
ZIGGY_CODE1_I 1 473 3250
ZIGGY_CODE2_I 1 473 3250
ZIGGY_CODE3_I 1 473 3250
ZIGGY_CODE4_I 1 473 3250
ZIGGY_CODE5_I 1 473 3250
ZIGGY_CODE6_I 1 473 3250
We notice that the indexes remain unchanged. As the table does NOT have ENABLE ROW MOVEMENT set, the indexes are NOT updated at all as part of the migrated row process (thus the same behaviour as non-autonomous database environments).
However, if I perform the same demo but instead perform a ROLLBACK of the transaction rather than the commit:
SQL> rollback; Rollback complete. Elapsed: 00:00:06.919
Note that the rollback takes 00:00:06.919 to complete.
If we now look at the size of the table and corresponding indexes:
SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'ZIGGY'); PL/SQL procedure successfully completed. SQL> analyze table ziggy compute statistics; Table ZIGGY analyzed. SQL> select table_name, num_rows, blocks, empty_blocks, avg_space, avg_row_len, chain_cnt from user_tables where table_name='ZIGGY'; TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE AVG_ROW_LEN CHAIN_CNT _____________ ___________ _________ _______________ ____________ ______________ ____________ ZIGGY 200000 4906 86 2899 113 0
We notice that the size of the table has increased to be the same 4906 blocks as when we performed the commit. The extra storage remains allocated even after the rollback operation.
That’s because Oracle does NOT deallocate any additional storage that might have been consumed during the original transaction. We notice that the AVG_SPACE has increased substantially as a result (now 2899, previously it was just 857).
If we look at the current state of the indexes after the rollback:
SQL> select index_name, blevel, leaf_blocks, clustering_factor
from user_indexes where table_name='ZIGGY';
INDEX_NAME BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR
_________________ _________ ______________ ____________________
ZIGGY_CODE20_I 1 473 3250
ZIGGY_ID_I 1 473 3250
ZIGGY_CODE1_I 1 473 3250
ZIGGY_CODE2_I 1 473 3250
ZIGGY_CODE3_I 1 473 3250
ZIGGY_CODE4_I 1 473 3250
ZIGGY_CODE5_I 1 473 3250
ZIGGY_CODE6_I 1 473 3250
ZIGGY_CODE7_I 1 473 3250
ZIGGY_CODE8_I 1 473 3250
ZIGGY_CODE9_I 1 473 3250
ZIGGY_CODE10_I 1 473 3250
ZIGGY_CODE11_I 1 473 3250
ZIGGY_CODE12_I 1 473 3250
ZIGGY_CODE13_I 1 473 3250
ZIGGY_CODE14_I 1 473 3250
ZIGGY_CODE15_I 1 473 3250
ZIGGY_CODE16_I 1 473 3250
ZIGGY_CODE17_I 1 473 3250
ZIGGY_CODE18_I 1 473 3250
ZIGGY_CODE19_I 1 473 3250
We notice that all the indexes again remain unchanged. As the indexes are not updated during the transaction, this is of course to be expected.
Let’s now repeat the same demo, but this time on a table with ENABLE ROW MOVEMENT set:
SQL> create table ziggy2(id number, code1 number, code2 number, code3 number, code4 number, code5 number, code6 number, code7 number, code8 number, code9 number, code10 number, code11 number, code12 number, code13 number, code14 number, code15 number, code16 number, code17 number, code18 number, code19 number, code20 number, name varchar2(142)) PCTFREE 0 ENABLE ROW MOVEMENT; Table ZIGGY2 created. SQL> insert into ziggy2 SELECT rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, 'BOWIE' FROM dual CONNECT BY LEVEL <= 200000; 200,000 rows inserted. SQL> commit; Commit complete. SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'ZIGGY2'); PL/SQL procedure successfully completed. SQL> analyze table ziggy2 compute statistics; Table ZIGGY2 analyzed. SQL> create index ziggy2_id_i on ziggy2(id); Index ZIGGY2_ID_I created. SQL> create index ziggy2_code1_i on ziggy2(code1); Index ZIGGY2_CODE1_I created. SQL> create index ziggy2_code2_i on ziggy2(code2); Index ZIGGY2_CODE2_I created. SQL> create index ziggy2_code3_i on ziggy2(code3); Index ZIGGY2_CODE3_I created. SQL> create index ziggy2_code4_i on ziggy2(code4); Index ZIGGY2_CODE4_I created. SQL> create index ziggy2_code5_i on ziggy2(code5); Index ZIGGY2_CODE5_I created. SQL> create index ziggy2_code6_i on ziggy2(code6); Index ZIGGY2_CODE6_I created. SQL> create index ziggy2_code7_i on ziggy2(code7); Index ZIGGY2_CODE7_I created. SQL> create index ziggy2_code8_i on ziggy2(code8); Index ZIGGY2_CODE8_I created. SQL> create index ziggy2_code9_i on ziggy2(code9); Index ZIGGY2_CODE9_I created. SQL> create index ziggy2_code10_i on ziggy2(code10); Index ZIGGY2_CODE10_I created. SQL> create index ziggy2_code11_i on ziggy2(code11); Index ZIGGY2_CODE11_I created. SQL> create index ziggy2_code12_i on ziggy2(code12); Index ZIGGY2_CODE12_I created. SQL> create index ziggy2_code13_i on ziggy2(code13); Index ZIGGY2_CODE13_I created. SQL> create index ziggy2_code14_i on ziggy2(code14); Index ZIGGY2_CODE14_I created. SQL> create index ziggy2_code15_i on ziggy2(code15); Index ZIGGY2_CODE15_I created. SQL> create index ziggy2_code16_i on ziggy2(code16); Index ZIGGY2_CODE16_I created. SQL> create index ziggy2_code17_i on ziggy2(code17); Index ZIGGY2_CODE17_I created. SQL> create index ziggy2_code18_i on ziggy2(code18); Index ZIGGY2_CODE18_I created. SQL> create index ziggy2_code19_i on ziggy2(code19); Index ZIGGY2_CODE19_I created. SQL> create index ziggy2_code20_i on ziggy2(code20); Index ZIGGY2_CODE20_I created.
The table and indexes all have the same initial size as the previous example:
SQL> select table_name, num_rows, blocks, empty_blocks, avg_space, avg_row_len, chain_cnt
from user_tables where table_name='ZIGGY2';
TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE AVG_ROW_LEN CHAIN_CNT
_____________ ___________ _________ _______________ ____________ ______________ ____________
ZIGGY2 200000 3268 60 857 113 0
SQL> select index_name, blevel, leaf_blocks, clustering_factor from user_indexes
where table_name='ZIGGY2';
INDEX_NAME BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR
__________________ _________ ______________ ____________________
ZIGGY2_ID_I 1 473 3250
ZIGGY2_CODE1_I 1 473 3250
ZIGGY2_CODE2_I 1 473 3250
ZIGGY2_CODE3_I 1 473 3250
ZIGGY2_CODE4_I 1 473 3250
ZIGGY2_CODE5_I 1 473 3250
ZIGGY2_CODE6_I 1 473 3250
ZIGGY2_CODE7_I 1 473 3250
ZIGGY2_CODE8_I 1 473 3250
ZIGGY2_CODE9_I 1 473 3250
ZIGGY2_CODE10_I 1 473 3250
ZIGGY2_CODE11_I 1 473 3250
ZIGGY2_CODE12_I 1 473 3250
ZIGGY2_CODE13_I 1 473 3250
ZIGGY2_CODE14_I 1 473 3250
ZIGGY2_CODE15_I 1 473 3250
ZIGGY2_CODE16_I 1 473 3250
ZIGGY2_CODE17_I 1 473 3250
ZIGGY2_CODE18_I 1 473 3250
ZIGGY2_CODE19_I 1 473 3250
ZIGGY2_CODE20_I 1 473 3250
If we now perform the same Update followed by the commit:
SQL> update ziggy2 set name='THE RISE AND FALL OF ZIGGY STARDUST AND THE SPIDERS FROM MARS'; 200,000 rows updated. Elapsed: 00:00:33.390 SQL> commit; Commit complete.
If we look at the current size of the table after the update:
SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'ZIGGY2'); PL/SQL procedure successfully completed. SQL> analyze table ziggy2 compute statistics; Table ZIGGY2 analyzed. SQL> select table_name, num_rows, blocks, empty_blocks, avg_space, avg_row_len, chain_cnt from user_tables where table_name='ZIGGY2'; TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE AVG_ROW_LEN CHAIN_CNT _____________ ___________ _________ _______________ ____________ ______________ ____________ ZIGGY2 200000 4654 82 367 169 0
We notice it has increased to 4654 blocks (previously 3268). But the table is not quite as large in size as in the first demo, where the table grew to 4906 blocks (so 252 fewer blocks).
The increase in table size is now due entirely as a result in the increased NAME column values, as Oracle has not had to consume any storage for pointers in the original table blocks to denote a new location of the rows, as the ROWIDs are now updated in the indexes on the fly.
So this is a positive, a DECREASE in the comparative size of table after such updates that migrate rows.
And of course, there are no migrated/chained rows.
But if we look at the index statistics after the commit:
SQL> select index_name, blevel, leaf_blocks, clustering_factor
from user_indexes where table_name='ZIGGY2';
INDEX_NAME BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR
__________________ _________ ______________ ____________________
ZIGGY2_ID_I 2 945 109061
ZIGGY2_CODE1_I 2 945 109061
ZIGGY2_CODE2_I 2 945 109061
ZIGGY2_CODE3_I 2 945 109061
ZIGGY2_CODE4_I 2 945 109061
ZIGGY2_CODE5_I 2 945 109061
ZIGGY2_CODE6_I 2 945 109061
ZIGGY2_CODE7_I 2 945 109061
ZIGGY2_CODE8_I 2 945 109061
ZIGGY2_CODE9_I 2 945 109061
ZIGGY2_CODE10_I 2 945 109061
ZIGGY2_CODE11_I 2 945 109061
ZIGGY2_CODE12_I 2 945 109061
ZIGGY2_CODE13_I 2 945 109061
ZIGGY2_CODE14_I 2 945 109061
ZIGGY2_CODE15_I 2 945 109061
ZIGGY2_CODE16_I 2 945 109061
ZIGGY2_CODE17_I 2 945 109061
ZIGGY2_CODE18_I 2 945 109061
ZIGGY2_CODE19_I 2 945 109061
ZIGGY2_CODE20_I 2 945 109061
We notice that ALL the indexes have significantly increased in size and now have 945 leaf blocks (previously it was just 473 leaf blocks). Additionally as a result of this increase in index size, the BLEVEL of the indexes has also increased and are now 2 (previously it was 1).
Here’s the thing. As I’ve discussed many times before, when Oracle performs an “Update” of an index entry, this is actually implemented as a Delete/Insert operation. By changing the ROWID of an index entry, Oracle first deletes the original index entry and inserts a new index entry with the new ROWID. So the previous index entry remains (with the space likely eventually reused by another new index entry in the future).
So these “on the fly” updates of the indexes to keep the ROWIDs current due to row migrations increases the likelihood of index block splits and the subsequent increase in index storage allocations.
In very rare, extreme cases (and this demo is indeed an extreme case as I’m updating all rows in my table), this extra index storage could potentially result in an increase in the index BLEVEL.
However, in most scenarios, this increase in index storage is likely to be moderate and result in extra index storage that will eventually be consumed by subsequent new rows anyways.
If instead of the commit operation, we instead performed a rollback:
SQL> rollback; Rollback complete. Elapsed: 00:00:36.639
We notice that the rollback takes considerably longer at 00:00:36.639 (previously in the first demo, it was just 00:00:06.919).
As the ROWIDs are now all updated on the fly on all the corresponding indexes, there’s that much more data that needs to be rolled back within these indexes.
If we look at the current size of the table after the rollback:
SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'ZIGGY2'); PL/SQL procedure successfully completed. SQL> analyze table ziggy2 compute statistics; Table ZIGGY2 analyzed. SQL> select table_name, num_rows, blocks, empty_blocks, avg_space, avg_row_len, chain_cnt from user_tables where table_name='ZIGGY2'; TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE AVG_ROW_LEN CHAIN_CNT _____________ ___________ _________ _______________ ____________ ______________ ____________ ZIGGY2 200000 4654 82 2823 113 0
We notice it has increased to the same 4654 blocks as with the commit. The extra storage remains allocated even after the rollback operation.
That’s again because Oracle does NOT deallocate any additional storage that might have been consumed during the original transaction. We notice that the AVG_SPACE has again increased substantially as a result (now 2823, previously it was just 857).
If we look at the current state of the indexes after the rollback:
SQL> select index_name, blevel, leaf_blocks, clustering_factor
from user_indexes where table_name='ZIGGY2';
INDEX_NAME BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR
__________________ _________ ______________ ____________________
ZIGGY2_ID_I 2 945 3250
ZIGGY2_CODE1_I 2 945 3250
ZIGGY2_CODE2_I 2 945 3250
ZIGGY2_CODE3_I 2 945 3250
ZIGGY2_CODE4_I 2 945 3250
ZIGGY2_CODE5_I 2 945 3250
ZIGGY2_CODE6_I 2 945 3250
ZIGGY2_CODE7_I 2 945 3250
ZIGGY2_CODE8_I 2 945 3250
ZIGGY2_CODE9_I 2 945 3250
ZIGGY2_CODE10_I 2 945 3250
ZIGGY2_CODE11_I 2 945 3250
ZIGGY2_CODE12_I 2 945 3250
ZIGGY2_CODE13_I 2 945 3250
ZIGGY2_CODE14_I 2 945 3250
ZIGGY2_CODE15_I 2 945 3250
ZIGGY2_CODE16_I 2 945 3250
ZIGGY2_CODE17_I 2 945 3250
ZIGGY2_CODE18_I 2 945 3250
ZIGGY2_CODE19_I 2 945 3250
ZIGGY2_CODE20_I 2 945 3250
We notice that they all remain at their increased size of 945 leaf blocks and with the Blevel of 2.
Again, when Oracle performs the rollback, Oracle does NOT undo all the index block splits and leaves all the additional storage allocated to the indexes.
So, just a word of caution.
Updating all the ROWIDs on the fly when a row migrates does not come for free. There’s additional resources that need to be consumed during these updates AND there is a potential issue with indexes having to perform additional index block splits and consume additional storage (at least immediately) after such operations.
If you have applications that makes bulk changes to data that can result in rollbacks, again, it’s just worth noting the extra storage that may be consumed as a result (until the additional data is finally added to the table).
Yes, index rebuilds can be performed to reduce the subsequent size of these inflated indexes.
BUT, for those of you with sharp eyes, you might also have noted another potential issue with this new behaviour, which I’ll discuss in my next post… 🙂
Some Things To Consider Now ROWIDs Are Updated When Rows Migrate Part I (“More”) February 22, 2023
Posted by Richard Foote in 19c, Autonomous Database, Autonomous Transaction Processing, Changing ROWID, Migrated Rows, Oracle, Oracle 21c, Oracle Blog, Oracle Cloud, Oracle General, Oracle Indexes, Oracle19c, Pink Floyd, Richard's Blog.add a comment
In my previous post, I discussed the obvious advantage of ROWIDs now being updated when rows migrate in an Oracle Autonomous Database, that being subsequent accesses to these rows via an index being more efficient.
However, there were likely reasons why Oracle has not historically updated ROWIDs on the fly in the past, so it’s worth exploring some of the possible side-effects of this new behaviour.
The most obvious issue will be for those applications that explicitly currently store ROWIDs, to enable the direct and very fast retrieval of such rows without having to read and access additional index blocks. If the ROWID can now suddenly change when a row is simply migrated, then of course these applications will no longer be guaranteed to be able to access these rows via the stored ROWIDs. Worse, it may now be possible for such applications to unknowingly fetch the wrong row, with the ROWID value now potentially associated with an entirely different row.
If this is a legitimate concern, then the remedy is simply to just NOT assign such tables the ENABLE ROW MOVEMENT attribute (which is disabled by default on a table) and the behaviour of migrated rows in association with ROWIDs will remain unchanged in Oracle Autonomous Databases. The risks here can be clearly and easily limited.
The other obvious disadvantage with ROWIDs being updated on the fly when a row migrates is in the additional costs associated with such Update statements in maintaining all the corresponding indexes.
As I discussed previously, these additional costs can be significant, especially if we have many indexes on a table.
To illustrate these extra costs, a simple example.
I’ll first start by creating and populating a table called BIG_ZIGGY (which at 100,000 rows is actually quite tiny, but it does have a number of columns) that does NOT have ENABLE ROW MOVEMENT set. The PCTFREE is set to 0 is ensure the rows are nicely packed in each block:
SQL> CREATE TABLE big_ziggy(id number, code1 number, code2 number, code3 number, code4 number, code5 number, code6 number, code7 number, code8 number, code9 number, code10 number, code11 number, code12 number, code13 number, code14 number, code15 number, code16 number, code17 number, code18 number, code19 number, code20 number, name varchar2(142)) PCTFREE 0; Table BIG_ZIGGY created. SQL> INSERT INTO big_ziggy SELECT rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, 'BOWIE' FROM dual CONNECT BY LEVEL <= 100000; 100,000 rows inserted. SQL> commit; Commit complete. SQL> exec dbms_stats.gather_table_stats(ownname=> null, tabname=> 'BIG_ZIGGY'); PL/SQL procedure successfully completed.
As we’ve only inserted rows, there are currently no migrated rows:
SQL> analyze table big_ziggy compute statistics; Table BIG_ZIGGY analyzed. SQL> select table_name, num_rows, chain_cnt from user_tables where table_name='BIG_ZIGGY'; TABLE_NAME NUM_ROWS CHAIN_CNT _____________ ___________ ____________ BIG_ZIGGY 100000 0
I’ll next create a whole bunch of indexes on many of these columns:
SQL> create index big_ziggy_id_i on big_ziggy(id); Index BIG_ZIGGY_ID_I created. SQL> create index big_ziggy_code1_i on big_ziggy(code1); Index BIG_ZIGGY_CODE1_I created. SQL> create index big_ziggy_code2_i on big_ziggy(code2); Index BIG_ZIGGY_CODE2_I created. SQL> create index big_ziggy_code3_i on big_ziggy(code3); Index BIG_ZIGGY_CODE3_I created. SQL> create index big_ziggy_code4_i on big_ziggy(code4); Index BIG_ZIGGY_CODE4_I created. SQL> create index big_ziggy_code5_i on big_ziggy(code5); Index BIG_ZIGGY_CODE5_I created. SQL> create index big_ziggy_code6_i on big_ziggy(code6); Index BIG_ZIGGY_CODE6_I created. SQL> create index big_ziggy_code7_i on big_ziggy(code7); Index BIG_ZIGGY_CODE7_I created. SQL> create index big_ziggy_code8_i on big_ziggy(code8); Index BIG_ZIGGY_CODE8_I created. SQL> create index big_ziggy_code9_i on big_ziggy(code9); Index BIG_ZIGGY_CODE9_I created. SQL> create index big_ziggy_code10_i on big_ziggy(code10); Index BIG_ZIGGY_CODE10_I created. SQL> create index big_ziggy_code11_i on big_ziggy(code11); Index BIG_ZIGGY_CODE11_I created. SQL> create index big_ziggy_code12_i on big_ziggy(code12); Index BIG_ZIGGY_CODE12_I created. SQL> create index big_ziggy_code13_i on big_ziggy(code13); Index BIG_ZIGGY_CODE13_I created. SQL> create index big_ziggy_code14_i on big_ziggy(code14); Index BIG_ZIGGY_CODE14_I created. SQL> create index big_ziggy_code15_i on big_ziggy(code15); Index BIG_ZIGGY_CODE15_I created. SQL> create index big_ziggy_code16_i on big_ziggy(code16); Index BIG_ZIGGY_CODE16_I created. SQL> create index big_ziggy_code17_i on big_ziggy(code17); Index BIG_ZIGGY_CODE17_I created. SQL> create index big_ziggy_code18_i on big_ziggy(code18); Index BIG_ZIGGY_CODE18_I created. SQL> create index big_ziggy_code19_i on big_ziggy(code19); Index BIG_ZIGGY_CODE19_I created. SQL> create index big_ziggy_code20_i on big_ziggy(code20); Index BIG_ZIGGY_CODE20_I created.
I’ll now run an UPDATE statement, that will increase the row size and result in a number of row migrations:
SQL> update big_ziggy set name='THE RISE AND FALL OF ZIGGY STARDUST AND THE SPIDERS FROM MARS';
100,000 rows updated.
PLAN_TABLE_OUTPUT
____________________________________________________________________________________
SQL_ID 53xtnn8mmtwj5, child number 0
-------------------------------------
update big_ziggy set name='THE RISE AND FALL OF ZIGGY STARDUST AND THE
SPIDERS FROM MARS'
Plan hash value: 1689330390
---------------------------------------------------------
| Id | Operation | Name | E-Rows |
---------------------------------------------------------
| 0 | UPDATE STATEMENT | | |
| 1 | UPDATE | BIG_ZIGGY | |
| 2 | TABLE ACCESS STORAGE FULL| BIG_ZIGGY | 100K |
---------------------------------------------------------
Note
-----
- automatic DOP: Computed Degree of Parallelism is 1
- PDML disabled because object is not decorated with parallel clause
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
Statistics
-----------------------------------------------------------
345 CPU used by this session
347 CPU used when call started
399 DB time
3442830 RM usage
5 Requests to/from client
491 Session total flash IO requests
25403392 cell physical IO interconnect bytes
48814 consistent gets
10111 consistent gets examination
10111 consistent gets examination (fastpath)
48814 consistent gets from cache
38703 consistent gets pin
38702 consistent gets pin (fastpath)
544587 db block gets
544587 db block gets from cache
538582 db block gets from cache (fastpath)
127 enqueue releases
129 enqueue requests
3086 gcs affinity lock grants
803 gcs data block access records
3 ges messages sent
33574 global enqueue gets sync
33573 global enqueue releases
43 messages sent
483 non-idle wait count
44 non-idle wait time
8 opened cursors cumulative
1 opened cursors current
71 physical read requests optimized
420 physical read total IO requests
25403392 physical read total bytes
3219456 physical read total bytes optimized
1 pinned cursors current
4 process last non-idle time
55 recursive calls
1 recursive cpu usage
593401 session logical reads
42 user I/O wait time
6 user calls
Elapsed: 00:00:04.532
SQL> commit
Commit complete.
Note that the CPU used by session is 335, the number of db block gets is 544587 and that the raw elapsed time is 00:00:04.532. We’ll shortly compare these values with those of the same demo, but on a table with ENABLE ROW MOVEMENT set.
If we now check for migrated (chained) rows:
SQL> analyze table big_ziggy compute statistics; Table BIG_ZIGGY analyzed. SQL> select table_name, num_rows, chain_cnt from user_tables where table_name='BIG_ZIGGY'; TABLE_NAME NUM_ROWS CHAIN_CNT _____________ ___________ ____________ BIG_ZIGGY 100000 28323
We notice we indeed have 28323 migrated rows.
We’ll now repeat the exactly same demo, but this time on the BIG_ZIGGY2 table that has ENABLE ROW MOVEMENT set:
SQL> CREATE TABLE big_ziggy2(id number, code1 number, code2 number, code3 number, code4 number, code5 number, code6 number, code7 number, code8 number, code9 number, code10 number, code11 number, code12 number, code13 number, code14 number, code15 number, code16 number, code17 number, code18 number, code19 number, code20 number, name varchar2(142)) PCTFREE 0 ENABLE ROW MOVEMENT; Table BIG_ZIGGY2 created. SQL> INSERT INTO big_ziggy2 SELECT rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, 'BOWIE' FROM dual CONNECT BY LEVEL <= 100000; 100,000 rows inserted. SQL> commit; Commit complete. SQL> exec dbms_stats.gather_table_stats(ownname=> null, tabname=> 'BIG_ZIGGY2'); PL/SQL procedure successfully completed. SQL> analyze table big_ziggy2 compute statistics; Table BIG_ZIGGY2 analyzed. SQL> select table_name, num_rows, chain_cnt from user_tables where table_name='BIG_ZIGGY2'; TABLE_NAME NUM_ROWS CHAIN_CNT _____________ ___________ ____________ BIG_ZIGGY2 100000 0 SQL> create index big_ziggy2_id_i on big_ziggy2(id); Index BIG_ZIGGY2_ID_I created. SQL> create index big_ziggy2_code1_i on big_ziggy2(code1); Index BIG_ZIGGY2_CODE1_I created. SQL> create index big_ziggy2_code2_i on big_ziggy2(code2); Index BIG_ZIGGY2_CODE2_I created. SQL> create index big_ziggy2_code3_i on big_ziggy2(code3); Index BIG_ZIGGY2_CODE3_I created. SQL> create index big_ziggy2_code4_i on big_ziggy2(code4); Index BIG_ZIGGY2_CODE4_I created. SQL> create index big_ziggy2_code5_i on big_ziggy2(code5); Index BIG_ZIGGY2_CODE5_I created. SQL> create index big_ziggy2_code6_i on big_ziggy2(code6); Index BIG_ZIGGY2_CODE6_I created. SQL> create index big_ziggy2_code7_i on big_ziggy2(code7); Index BIG_ZIGGY2_CODE7_I created. SQL> create index big_ziggy2_code8_i on big_ziggy2(code8); Index BIG_ZIGGY2_CODE8_I created. SQL> create index big_ziggy2_code9_i on big_ziggy2(code9); Index BIG_ZIGGY2_CODE9_I created. SQL> create index big_ziggy2_code10_i on big_ziggy2(code10); Index BIG_ZIGGY2_CODE10_I created. SQL> create index big_ziggy2_code11_i on big_ziggy2(code11); Index BIG_ZIGGY2_CODE11_I created. SQL> create index big_ziggy2_code12_i on big_ziggy2(code12); Index BIG_ZIGGY2_CODE12_I created. SQL> create index big_ziggy2_code13_i on big_ziggy2(code13); Index BIG_ZIGGY2_CODE13_I created. SQL> create index big_ziggy2_code14_i on big_ziggy2(code14); Index BIG_ZIGGY2_CODE14_I created. SQL> create index big_ziggy2_code15_i on big_ziggy2(code15); Index BIG_ZIGGY2_CODE15_I created. SQL> create index big_ziggy2_code16_i on big_ziggy2(code16); Index BIG_ZIGGY2_CODE16_I created. SQL> create index big_ziggy2_code17_i on big_ziggy2(code17); Index BIG_ZIGGY2_CODE17_I created. SQL> create index big_ziggy2_code18_i on big_ziggy2(code18); Index BIG_ZIGGY2_CODE18_I created. SQL> create index big_ziggy2_code19_i on big_ziggy2(code19); Index BIG_ZIGGY2_CODE19_I created. SQL> create index big_ziggy2_code20_i on big_ziggy2(code20); Index BIG_ZIGGY2_CODE20_I created.
If we now repeat the same UPDATE statement:
SQL> update big_ziggy2 set name='THE RISE AND FALL OF ZIGGY STARDUST AND THE SPIDERS FROM MARS';
100,000 rows updated.
PLAN_TABLE_OUTPUT
____________________________________________________________________________________
SQL_ID gupa6k30c341n, child number 0
-------------------------------------
update big_ziggy2 set name='THE RISE AND FALL OF ZIGGY STARDUST AND THE
SPIDERS FROM MARS'
Plan hash value: 3856369697
----------------------------------------------------------
| Id | Operation | Name | E-Rows |
----------------------------------------------------------
| 0 | UPDATE STATEMENT | | |
| 1 | UPDATE | BIG_ZIGGY2 | |
| 2 | TABLE ACCESS STORAGE FULL| BIG_ZIGGY2 | 100K |
----------------------------------------------------------
Note
-----
- automatic DOP: Computed Degree of Parallelism is 1
- PDML disabled because object is not decorated with parallel clause
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
Statistics
-----------------------------------------------------------
1310 CPU used by this session
1310 CPU used when call started
1732 DB time
13104856 RM usage
5 Requests to/from client
12 Session IORM flash wait time
13343 Session total flash IO requests
235888640 cell physical IO interconnect bytes
36437 consistent gets
994 consistent gets examination
994 consistent gets examination (fastpath)
36437 consistent gets from cache
35443 consistent gets pin
35275 consistent gets pin (fastpath)
2574278 db block gets
2574278 db block gets from cache
1418826 db block gets from cache (fastpath)
5729 enqueue releases
5731 enqueue requests
23745 gcs affinity lock grants
11119 gcs data block access records
25 ges messages sent
1165 global enqueue gets sync
1164 global enqueue releases
215 messages sent
8254 non-idle wait count
476 non-idle wait time
31 opened cursors cumulative
5324 physical read requests optimized
8019 physical read total IO requests
235888640 physical read total bytes
63856640 physical read total bytes optimized
17 process last non-idle time
160 recursive calls
7 recursive cpu usage
2610715 session logical reads
475 user I/O wait time
6 user calls
Elapsed: 00:00:17.600
SQL> commit
Commit complete.
We notice that this update consumed more resources than the previous example.
Note that the CPU used by session is now 1310 (previously 335), the number of db block gets is now 2574278 (previously 544587) and that the raw elapsed time has increased to 00:00:17.600 (previously it was 00:00:04.532).
SQLcl doesn’t automatically display redo statistics which is a shame and something I’ve only just noticed, but it will have increased significantly as I discussed previously.
However, if we look at the number of migrated rows on the BIG_ZIGGY2 table:
SQL> analyze table big_ziggy2 compute statistics; Table BIG_ZIGGY2 analyzed. SQL> select table_name, num_rows, chain_cnt from user_tables where table_name='BIG_ZIGGY2'; TABLE_NAME NUM_ROWS CHAIN_CNT _____________ ___________ ____________ BIG_ZIGGY2 100000 0
We notice there are no rows considered chained (migrated), as in this scenario on Oracle Autonomous Databases, all rows that moved to a different block had their associated ROWIDs updated on the fly in all the corresponding indexes and as such there was no need to have the pointer in the original block to denote the row’s new location.
So the choice is entirely yours.
If you have applications that rely on stored ROWIDs not changing in the background when a row happens to migrate OR you have applications in which the performance of the UPDATE DML is absolutely paramount and you wish to avoid the overheads associated with updating ROWIDs on the fly (which in an Exadata environment is less likely to be an issue), then do NOT set ENABLE ROW MOVEMENT on the table.
Generally, the improvements associated with more efficient indexed-based accesses overrides the overheads associated with (usually) one-off and uncommon row migrations (which might be mitigated with more appropriate settings of PCTFREE).
That said, I’ll discuss a few other areas of potential concern associated with this change of behaviour in my next post…
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…
Merry Christmas and Happy New Year!! (“The Jean Genie”) December 22, 2022
Posted by Richard Foote in Christmas, David Bowie, Richard's Blog, Richard's Musings, The Jean Genie.1 comment so far
I would like to take this opportunity to wish all my readers who celebrate the festive season a very Merry Christmas and a most happy, peaceful and prosperous New Year.
2023 promises to be a big year for me, in that I’ll be officially retiring from work early in the year, after close to 40 years working in IT. I’ve had an absolute blast and met and worked with some truly amazing people, but it’s time to move on and enjoy other things that life has to offer.
But I’ll hopefully keep my hand in enough to perhaps attend the odd Oracle conference during the year and say my farewells and have a quiet drink with as many of my many Oracle-related friends and colleagues as I can. So you haven’t perhaps heard the last from me quite yet.
My Christmas gift this year is of course David Bowie related. 50 years ago, Bowie had a huge hit in the UK (and elsewhere) with the glam rock classic that is “The Jean Genie”. 50 years ago to the day, it was sitting in the UK 1972 Christmas charts at No. 16, but would reach as far as No. 2 in the coming weeks.
This is the official video, directed by the late, great Mick Rock. Enjoy !!
When Does A ROWID Change? Part IV (“Mass Production”) December 21, 2022
Posted by Richard Foote in Attribute Clustering, Autonomous Data Warehouse, Autonomous Database, Autonomous Transaction Processing, Changing ROWID, Clustering Factor, Data Clustering, Flashback, Move Partitions, Oracle, Oracle Blog, Oracle Cloud, Oracle General, Oracle Indexes, Partitioning, Richard's Blog, ROWID.1 comment so far
In Part II in this series, I discussed how the update of the partitioned key column of a row that results in the row being moved to a different partition, will result in the ROWID of such rows changing.
However, there a quite a number of other user initiated actions in which ROWIDs can easily change (as indeed discussed in Connor McDonald’s video on this subject).
Some of these include:
- Moving a table or partition, as this results in the segment being reorganised, with all associated rows being physically relocated and their associated ROWIDs changing
- Altering a non-partitioned table such that it be now be partitioned, which again results in the physical relocation of all rows and their ROWIDs changing (which BTW, can potentially occur on a Autonomous Database without any user intervention)
- Altering the partitioning strategy of a partitioned table, again changes the physical location of all rows
- Hybrid Columnar Compression (HCC), which by packing rows more tightly, can more likely result in the physical relocation of a row during subsequent DML statements
- Altering a table to Shrink Space, which attempts to move rows between table blocks to pack rows more tightly, again potentially resulting in rows physically moving and the changing of their associated ROWIDs
- Flashback of a table, which results in rows being deleted and inserted and hence the change of their associated ROWIDs
I’ll illustrate an example of all this, with one of the key reasons why you may want to re-organise a table (and implicitly change all the ROWIDs of a table).
I’ll start by creating and populating a simple little table, with a CODE column that has very poorly clustered data:
SQL> create table bowie (id number, code number, name varchar2(42)); Table created. SQL> insert into bowie select rownum, mod(rownum, 500), 'DAVID BOWIE' from dual connect by level <=1000000; 1000000 rows created. SQL> commit; Commit complete. SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'BOWIE'); PL/SQL procedure successfully completed.
Let’s now create an index on this CODE column:
SQL> create index bowie_code_i on bowie(code); Index created.
We take note of the ROWIDs of a few random rows:
SQL> select id, rowid from bowie where id in (42, 4242, 424242) order by id;
ID ROWID
---------- ------------------
42 AAASn1AAMAAAgB2AAp
4242 AAASn1AAMAAAgCHACL
424242 AAASn1AAMAAAgbtAAJ
If we run a simple query with a predicate based on the CODE column:
SQL> select * from bowie where code=42;
2000 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1845943507
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2000 | 42000 | 1004 (2)| 00:00:01 |
| * 1 | TABLE ACCESS FULL | BOWIE | 2000 | 42000 | 1004 (2)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("CODE"=42)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3596 consistent gets
0 physical reads
0 redo size
20757 bytes sent via SQL*Net to client
52 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2000 rows processed
We notice the CBO has chosen to ignore the index and use a FTS instead, even though only 2000 rows in a 1M row table (just 0.2%) are returned.
Why?
Because the clustering of the CODE data is terrible, with the required values littered throughout the table. If we look at the Clustering Factor of the index:
SQL> select index_name, leaf_blocks, clustering_factor from user_indexes where index_name='BOWIE_CODE_I'; INDEX_NAME LEAF_BLOCKS CLUSTERING_FACTOR ------------ ----------- ----------------- BOWIE_CODE_I 2063 1000000
We notice the index has the worst possible Clustering Factor value of 1000000.
So to improve the performance of this (say critical) query, we can add a Clustering Attribute to this table based on the CODE column and then reorganise the table:
SQL> alter table bowie add clustering by linear order (code); Table altered. SQL> alter table bowie move online; Table altered.
If we now look at the Clustering Factor of the index:
SQL> select index_name, leaf_blocks, clustering_factor from user_indexes where index_name='BOWIE_CODE_I'; INDEX_NAME LEAF_BLOCKS CLUSTERING_FACTOR ------------ ----------- ----------------- BOWIE_CODE_I 2063 3568
We can see it has substantially improved, down to just 3568 from the previous 1000000 value, as the data is now perfectly clustered based on the CODE column.
If we now re-run the query:
SQL> select * from bowie where code=42;
2000 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 853003755
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2000 | 42000 | 15 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED | BOWIE | 2000 | 42000 | 15 (0)| 00:00:01 |
| * 2 | INDEX RANGE SCAN | BOWIE_CODE_I | 2000 | | 7 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("CODE"=42)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
17 consistent gets
0 physical reads
0 redo size
50735 bytes sent via SQL*Net to client
52 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2000 rows processed
The CBO now choses to use the index and the query is much more efficient as a result (consistent gets down to just 17 from the previous 3596).
So all is now much better, except for any application that was reliant on using ROWIDs to fetch the data, as all ROWIDs have now changed:
SQL> select id, rowid from bowie where id in (42, 4242, 424242) order by id;
ID ROWID
---------- ------------------
42 AAASn6AAMAAAACvAEf
4242 AAASn6AAMAAAiRaAA4
424242 AAASn6AAMAAAiRWAEQ
So there are many ways in which the ROWID of a row can potentially change.
And now there’s another key manner in which a ROWID can very easily change in Oracle Autonomous Database environments, as I’ll next discuss…
When Does A ROWID Change? Part III (“Arriving Somewhere But Not Here”) December 13, 2022
Posted by Richard Foote in Autonomous Database, Changing ROWID, Index Internals, Local Indexes, Oracle, Oracle Blog, Oracle Cloud, Oracle General, Oracle Indexes, Oracle Indexing Internals Webinar, Partitioning, Performance Tuning, Richard's Blog, ROWID, Secondary Indexes.2 comments
In Part II of this series, I discussed how updating the Partitioned Key of a row from a Partitioned table will result in the row physically moving and the associated ROWID changing.
One of the reasons why changing the ROWID has historically has not been the default behaviour and requires the explicit setting of the ENABLE ROW MOVEMENT clause for Partitioned tables is because changing a ROWID comes at a cost. The cost being not only having to delete and re-insert the row within the table, but also delete and re-insert the associated index entry for each corresponding index on the table.
To illustrate, I’m going to create and populate another simple little Partitioned Table:
SQL> CREATE TABLE big_bowie2(id number, code1 number, code2 number, code3 number, code4 number, code5 number, code6 number, code7 number, code8 number, code9 number, code10 number, release_date date, name varchar2(42)) PARTITION BY RANGE (release_date)
(PARTITION ALBUMS_2021 VALUES LESS THAN (TO_DATE('01-JAN-2022', 'DD-MON-YYYY')),
PARTITION ALBUMS_2022 VALUES LESS THAN (MAXVALUE)) ENABLE ROW MOVEMENT;
Table created.
SQL> INSERT INTO big_bowie2 SELECT rownum, mod(rownum,1000), mod(rownum,1000), mod(rownum,1000), mod(rownum,100
ownum,1000), mod(rownum,1000), mod(rownum,1000), mod(rownum,1000), mod(rownum,1000), mod(rownum,1000), sysdate-
m,500), 'DAVID BOWIE' FROM dual CONNECT BY LEVEL <= 10000;
10000 rows created.
SQL> commit;
Commit complete.
SQL> exec dbms_stats.gather_table_stats(ownname=> null, tabname=> 'BIG_BOWIE2');
PL/SQL procedure successfully completed.
I’m now going to create a number of basic Global indexes on this table:
SQL> create index big_bowie2_id_i on big_bowie2(id); Index created. SQL> create index big_bowie2_code1_i on big_bowie2(code1); Index created. SQL> create index big_bowie2_code2_i on big_bowie2(code2); Index created. SQL> create index big_bowie2_code3_i on big_bowie2(code3); Index created. SQL> create index big_bowie2_code4_i on big_bowie2(code4); Index created. SQL> create index big_bowie2_code5_i on big_bowie2(code5); Index created. SQL> create index big_bowie2_code6_i on big_bowie2(code6); Index created. SQL> create index big_bowie2_code7_i on big_bowie2(code7); Index created. SQL> create index big_bowie2_code8_i on big_bowie2(code8); Index created. SQL> create index big_bowie2_code9_i on big_bowie2(code9); Index created. SQL> create index big_bowie2_code10_i on big_bowie2(code10); Index created.
If I run a simple single row UPDATE that updates a non-indexed, non-partitioned key column:
SQL> update big_bowie2 set name='ZIGGY STARDUST' where id=424;
1 row updated.
Execution Plan
----------------------------------------------------------
Plan hash value: 3590621923
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
-------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 1 | 16 | 2 (0) | 00:00:01 |
| 1 | UPDATE | BIG_BOWIE2 | | | | |
|* 2 | INDEX RANGE SCAN | BIG_BOWIE2_ID_I | 1 | 16 | 1 (0) | 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=424)
Statistics
----------------------------------------------------------
1 recursive calls
1 db block gets
2 consistent gets
0 physical reads
328 redo size
204 bytes sent via SQL*Net to client
41 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
We can see that the number of db block gets is just 1 and consistent gets just 2, as only the one table block needs to be updated and easily accessed via the index on the ID column.
If we now run a single row update of an indexed column:
SQL> update big_bowie2 set code1=42 where id = 424;
1 row updated.
Execution Plan
----------------------------------------------------------
Plan hash value: 3590621923
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
-------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 1 | 8 | 2 (0) | 00:00:01 |
| 1 | UPDATE | BIG_BOWIE2 | | | | |
|* 2 | INDEX RANGE SCAN | BIG_BOWIE2_ID_I | 1 | 8 | 1 (0) | 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=424)
Statistics
----------------------------------------------------------
1 recursive calls
5 db block gets
2 consistent gets
1 physical reads
948 redo size
204 bytes sent via SQL*Net to client
41 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
We can see that the number of db block gets increases to 5, as not only does the table block have to be updated but so also do the associated index blocks.
If we now finally run a single row update on the partitioned table’s partition key column that results in the row having to physically move to another partition:
SQL> update big_bowie2 set release_date='06-DEC-22' where id = 424;
1 row updated.
Execution Plan
----------------------------------------------------------
Plan hash value: 3590621923
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
-------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 1 | 64 | 2 (0) | 00:00:01 |
| 1 | UPDATE | BIG_BOWIE2 | | | | |
|* 2 | INDEX RANGE SCAN | BIG_BOWIE2_ID_I | 1 | 64 | 1 (0) | 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=424)
Statistics
----------------------------------------------------------
1 recursive calls
49 db block gets
3 consistent gets
0 physical reads
5996 redo size
204 bytes sent via SQL*Net to client
41 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
11 sorts (memory)
0 sorts (disk)
1 rows processed
We see that the number of db block gets jumps significantly to 49, as all the corresponding indexes require their associated index entries to be likewise deleted/re-inserted in order to change all their ROWIDs.
So this additional cost of updating the indexes has been a cost that Oracle has traditionally attempted to avoid, by generally not changing the ROWID when performing an update of a row.
Of course, the update of a Partitioned Key column is not the only manner in which ROWIDs have previously easily changed as we’ll see in Part IV…
UPDATE: As my buddie Martin Widlake makes in this comment. it’s also well worth mentioning the increase in associated redo (as redo is an excellent measurement of “work” the Oracle Database has to perform), if Oracle has to change the ROWID of a row and make the necessary changes to all its corresponding indexes. In the example above, the redo increases significantly from 328 bytes to 5996 bytes, when Oracle has to move the row to another partition and so update the ROWID on the 11 indexes. More on all this when I discuss the changes implemented with the current Autonomous Databases…
When Does A ROWID Change? Part II (“You’ve Got A Habit Of Leaving”) December 9, 2022
Posted by Richard Foote in Autonomous Database, CBO, Changing ROWID, Global Indexes, Multiple Indexes, Oracle, Oracle 21c, Oracle Blog, Oracle Cloud, Oracle General, Partitioning, Performance Tuning, Richard's Blog, ROWID.3 comments
In my previous post, I discussed how a row is generally “migrated”, but the ROWID remains unchanged, when a row is updated such that it can no longer fit into its current block. Hence the general rule has always been that the ROWID of a row does not change.
However, even before the changes now present with Oracle Autonomous Databases (to be discussed in future posts), there has always been (since Oracle 8) one classic scenario when this “ROWID never changes after an update” rule has not been true.
To illustrate, I’m going to create and populate a basic little Range-based Partitioned table, with the RELEASE_DATE column being the partitioned column:
SQL> CREATE TABLE big_bowie(id number, release_date date, name varchar2(42)) PARTITION BY RANGE (release_date)
(PARTITION ALBUMS_2021 VALUES LESS THAN (TO_DATE('01-JAN-2022', 'DD-MON-YYYY')),
PARTITION ALBUMS_2022 VALUES LESS THAN (MAXVALUE));
Table created.
SQL> INSERT INTO big_bowie SELECT rownum, sysdate-mod(rownum,500), 'DAVID BOWIE' FROM dual CONNECT BY LEVEL <= 10000;
10000 rows created.
SQL> commit;
Commit complete.
SQL> exec dbms_stats.gather_table_stats(ownname=> null, tabname=> 'BIG_BOWIE');
PL/SQL procedure successfully completed.
Let’s look at the current ROWIDs of a few random rows:
SQL> select id, release_date, rowid from big_bowie where id in (424, 444, 482) order by id;
ID RELEASE_D ROWID
---------- --------- ------------------
424 08-OCT-21 AAASe9AAMAAAAj7ABU
444 18-SEP-21 AAASe9AAMAAAAj7ABo
482 11-AUG-21 AAASe9AAMAAAAj7ACO
I’m now going to try and update for these rows, the partitioned column value, such that they would now logically belong in the other partition:
SQL> update big_bowie set release_date='06-DEC-22' where id in (424, 444, 482); update big_bowie set release_date='06-DEC-22' where id in (424, 444, 482) * ERROR at line 1: ORA-14402: updating partition key column would cause a partition change
I now get a very key and important error. By default, Oracle does not allow you to update a row if it results in the row having to physically move to a different partition.
I suspect there are at least 3 good reasons for this default restriction.
One is to protect the business integrity of the data, where it might just not make any business sense for a row to be updated in this manner.
The second is that it protects any applications out there that explicitly uses ROWIDs and relies on the ROWIDs not suddenly changing behind the scenes.
And finally, it protects perhaps valuable database resources and ensures that the database does not have to incur any additional workloads, that would be necessary if such an operation were to proceed.
But we have the ability and control to override this default behaviour in the following manner with the ENABLE ROW MOVEMENT clause:
SQL> alter table big_bowie enable row movement; Table altered.
If we now try and update these rows again:
SQL> update big_bowie set release_date='06-DEC-22' where id in (424, 444, 482); 3 rows updated. SQL> commit; Commit complete.
The updates are now successful.
As these rows no longer logically belong in the previous partition, they have to be physically moved to its new partition. This is effectively implemented by deleting the rows from the previous partition and then re-inserting them in the new partition segment.
If we now look the ROWIDs of these updated rows:
SQL> select id, release_date, rowid from big_bowie where id in (424, 444, 482) order by id;
ID RELEASE_D ROWID
---------- --------- ------------------
424 06-DEC-22 AAASe+AAMAAAATQABR
444 06-DEC-22 AAASe+AAMAAAATQABS
482 06-DEC-22 AAASe+AAMAAAATQABT
We notice that they now all have different ROWIDs, because they indeed now all exist in a different physical location.
In my next post, I’ll highlight but one obvious disadvantage and consequence of allowing rows to be physically moved in this manner…
When Does A ROWID Change? Part I (“Fearless”) December 7, 2022
Posted by Richard Foote in Autonomous Database, Block Dumps, Changing ROWID, Oracle, Oracle Blog, Oracle Cloud, Oracle General, Oracle Indexes, Oracle Table Internals, Oracle19c, Pink Floyd, Richard's Blog, ROWID.7 comments
Recently, my mate Connor McDonald caused a tad of a storm when he disclosed that the once sacred, (almost) unchangeable ROWID can now indeed potentially easily change, without the DBA doing a thing.
You can watch his excellent video on the subject here.
As the humble ROWID is a critical component of any index, I thought it worthwhile to have a deep dive discussion on when a ROWID can and can’t change and some of the key changes that have been introduced within Oracle’s Autonomous database environments.
If you’re a developer who explicitly uses the ROWID in your applications, you might want to pay extra attention to these changes.
I thought I’ll begin first though by discussing how the ROWID currently doesn’t generally change…
The ROWID is basically just a pointer stored in indexes that effectively points to the physical location of a row within a table that’s associated with the specific indexed key. It consists of the Data Object ID (if it’s a Global Index associated to a Partitioned Table, as the Relative File ID is no longer a unique value and so needs this to determine the appropriate tablespace), a Relative File ID, a Data Block ID within the Relative File and the Row Location ID within the Data Block.
Now although it has never been a completely risk free approach to manually store and directly use these ROWIDs to fetch a row from a table, it’s a technique that has been frequently used by developers for 3 very good reasons.
The first reason is that it’s one of the most efficient ways to fetch a required row, because the database can go directly to the physical location and directly access the required row, without having to even read a single index block.
The second reason is because it has always been fully supported by Oracle to do so, with the required syntax well documented. Indeed, Oracle APEX includes the base functionality to store and access rows directly via their associated ROWIDs.
The third reason is because it’s generally well understood that the ROWID doesn’t change, except for a very few (generally) well known scenarios, and so using the ROWID to access data within an application is viewed as being reasonable safe.
When a row is inserted into a table, each corresponding index on the table (generally) has a new index entry also inserted, including its associated ROWID. Now, if the row never moves from its current physical location, there is no need to ever worry about the ROWID subsequently changing.
So the big question is, when can a row physically move AND the associated ROWID change?
Logically, a scenario that springs to mind is when a row is updated and made bigger and there’s no longer room within the current data block to store the larger row. Does the row move to another block with sufficient free space and result in the ROWID to change?
To check out this scenario, I’ll create and populate a basic table, with PCTFREE set to 0, so once the blocks within the table are filled, there is precious little space for rows to subsequently grow:
SQL> create table bowie (id number, name varchar2(142)) pctfree 0 enable row movement; Table created. SQL> insert into bowie select rownum, 'BOWIE' from dual connect by level <=10000; 10000 rows created. SQL> commit; Commit complete. SQL> create index bowie_id_i on bowie(id); Index created. SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'BOWIE'); PL/SQL procedure successfully completed.
For good measure, I’ve also included the ENABLE ROW MOVEMENT clause, which is usually associated with Partitioned Tables (as I’ll discuss in Part II in this series).
Let’s have a look at the ROWIDs of a few random rows:
SQL> select id, rowid from bowie where id in (42, 424, 4242) order by id; ID ROWID ---------- ------------------ 42 AAASe4AAMAAAACHAAp 424 AAASe4AAMAAAACHAGn 4242 AAASe4AAMAAAACNAHV
If we look at a partial block dump of one of the table blocks:
tab 0, row 0, @0x1312 tl: 12 fb: --H-FL-- lb: 0x1 cc: 2 col 0: [ 2] c1 02 col 1: [ 5] 42 4f 57 49 45 tab 0, row 1, @0x131e tl: 12 fb: --H-FL-- lb: 0x1 cc: 2 col 0: [ 2] c1 03 col 1: [ 5] 42 4f 57 49 45 tab 0, row 2, @0x132a tl: 12 fb: --H-FL-- lb: 0x1 cc: 2 col 0: [ 2] c1 04 col 1: [ 5] 42 4f 57 49 45 tab 0, row 3, @0x1336 tl: 12 fb: --H-FL-- lb: 0x1 cc: 2 col 0: [ 2] c1 05 col 1: [ 5] 42 4f 57 49 45 tab 0, row 4, @0x1342 tl: 12 fb: --H-FL-- lb: 0x1 cc: 2 col 0: [ 2] c1 06 col 1: [ 5] 42 4f 57 49 45
The above partial block dump shows the first 5 rows within the block, with the contents of the 2 table columns listed (in hex format).
If we access a row via an index:
SQL> select * from bowie where id=42;
ID NAME
---------- --------------------------------------------------------------
42 THE RISE AND FALL OF ZIGGY STARDUST AND THE SPIDERS FROM MARS
Execution Plan
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 66 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED | BOWIE | 1 | 66 | 2 (0)| 00:00:01 |
| * 2 | INDEX RANGE SCAN | BOWIE_ID_I | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=42)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
702 bytes sent via SQL*Net to client
52 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
We can see in this example that Oracle requires 4 consistent gets, 3 of which are accesses to the index.
If we access this table via its ROWID:
SQL> select * from bowie where rowid='AAASe4AAMAAAACHAAp';
ID NAME
---------- --------------------------------------------------------------
42 THE RISE AND FALL OF ZIGGY STARDUST AND THE SPIDERS FROM MARS
Execution Plan
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 10 | 1 (0) | 00:00:01 |
| 1 | TABLE ACCESS BY USER ROWID | BOWIE | 1 | 10 | 1 (0) | 00:00:01 |
------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1 consistent gets
0 physical reads
0 redo size
698 bytes sent via SQL*Net to client
52 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
We can see that this only requires just the 1 consistent get (vs. 4 when using the index) to access the row.
So we can see the appeal of using the ROWID to access a row.
If we now update the rows within the table and make them substantially larger so they can no longer fit within the currently filled blocks:
SQL> update bowie set name='THE RISE AND FALL OF ZIGGY STARDUST AND THE SPIDERS FROM MARS'; 10000 rows updated. SQL> commit; Commit complete.
And now look again at the ROWIDs of these selected rows:
SQL> select id, rowid from bowie where id in (42, 424, 4242) order by id; ID ROWID ---------- ------------------ 42 AAASe4AAMAAAACHAAp 424 AAASe4AAMAAAACHAGn 4242 AAASe4AAMAAAACNAHV
We notice that the ROWIDs all remain the same.
This has always historically been the behaviour here. If we update a row and the updated row can no longer fit within the current block, the row “migrates” to another table block with sufficient free space. BUT the associated ROWIDs do NOT change and the associated indexes are NOT updated.
Rather, the row data within the updated table is replaced with a “pointer”, that points to the new physical location of the migrated row. The advantage here being that Oracle only has to update the table with this new pointer, rather having to update the associated ROWIDs of all the (possibly many) associated indexes (noting that such an update would actually result in a delete followed by a re-insert of each index entry).
The disadvantage of course is that to now access this migrated row via an index requires an extra hop, to first read the initial table block and then to follow the pointer and access the actual block that now contains the row. Note if this row is forced to be migrated again because it grows again and can’t be housed in the current block, this pointer in the initial block is updated to reflect the newer location, so at least there is only ever the one extra hop.
If we look at a new partial block dump of the previously accessed block:
tab 0, row 0, @0x1f8f tl: 9 fb: --H----- lb: 0x2 cc: 0 nrid: 0x030000bf.4b tab 0, row 1, @0x1f86 tl: 9 fb: --H----- lb: 0x2 cc: 0 nrid: 0x030000bf.4c tab 0, row 2, @0x1f7d tl: 9 fb: --H----- lb: 0x2 cc: 0 nrid: 0x030000bf.4d tab 0, row 3, @0x1f74 tl: 9 fb: --H----- lb: 0x2 cc: 0 nrid: 0x030000bf.4e tab 0, row 4, @0x1f6b tl: 9 fb: --H----- lb: 0x2 cc: 0 nrid: 0x030000b8.0
We notice that the rows with their 2 columns have been replaced with a logical nrid pointer (consisting of a relative block address and row location within the block), that effectively points to the new physical location of the row.
Note we can still use the same, unchanged ROWID to access the same table rows:
SQL> select * from bowie where rowid='AAASe4AAMAAAACHAAp';
ID NAME
---------- --------------------------------------------------------------
42 THE RISE AND FALL OF ZIGGY STARDUST AND THE SPIDERS FROM MARS
Execution Plan
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 10 | 1 (0) | 00:00:01 |
| 1 | TABLE ACCESS BY USER ROWID | BOWIE | 1 | 10 | 1 (0) | 00:00:01 |
------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
698 bytes sent via SQL*Net to client
52 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
This ROWID access still works fine, except it has now increased to 2 consistent gets, one to access the initial block referenced by the ROWID and the extra consistent get to follow the pointer and access the new physical location of the row.
So historically, we haven’t had to worry about updates changing the ROWID of a row (except perhaps at looking at reducing the number of these migrated rows).
Well, except for one clear example as I’ll discuss in Part II…
Costing Concatenated Indexes With Range Scan Predicates Part I (Nothing To Be Desired) July 22, 2022
Posted by Richard Foote in BLEVEL, CBO, Clustering Factor, Concatenated Indexes, Index Access Path, Index Column Order, Index Column Reorder, Leaf Blocks, Non-Equality Predicates, Oracle, Oracle Cost Based Optimizer, Oracle General, Oracle Indexes, Performance Tuning, Richard Foote Consulting, Richard Foote Training, Richard's Blog.1 comment so far
In my previous post, I discussed how Automatic Indexing ordered columns when derived from SQLs containing both equality and non-equality predicates.
I’ve since had offline questions asking why indexes are more effective with leading columns addressing the equality predicates rather than the leading columns addressing non-equality predicates. Based on the theory that for everyone who asks a question, there are likely numerous others wondering the same thing, I thought I’ll try to explain things with these posts.
I’ll start by creating the following simple table that has two columns (ID and CODE) that are both highly selective (they both have 10,000 distinct values in a 100,000 rows table, so 10 rows approximately per value):
SQL> CREATE TABLE radiohead (id NUMBER, code NUMBER, name VARCHAR2(42)); Table created. SQL> INSERT INTO radiohead SELECT mod(rownum,10000)+1, ceil(dbms_random.value(0,10000)), 'RADIOHEAD' FROM dual CONNECT BY LEVEL <= 100000; 100000 rows created. SQL> commit; Commit complete.
I’ll next create an index based on the ID, CODE columns, with importantly the ID column as the leading column:
SQL> CREATE INDEX radiohead_id_code_i ON radiohead(id, code); Index created. SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'RADIOHEAD', estimate_percent=> null, method_opt=> 'FOR ALL COLUMNS SIZE 1'); PL/SQL procedure successfully completed.
When it comes to costing index accesses, some of the crucial statistics including the Blevel, Leaf_Blocks and often most crucial of all, the Clustering_Factor:
SQL> SELECT index_name, blevel, leaf_blocks, clustering_factor FROM user_indexes WHERE index_name = 'RADIOHEAD_ID_CODE_I'; INDEX_NAME BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR -------------------- ---------- ----------- ----------------- RADIOHEAD_ID_CODE_I 1 265 99034
We begin by running the following query, with an equality predicate on the ID column and a relatively large, non-selective range predicate on the CODE column:
SQL> SELECT * FROM radiohead WHERE id = 42 AND CODE BETWEEN 1000 AND 5000;
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_ID_CODE_I | 4 | | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
8 consistent gets
0 physical reads
0 redo size
824 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)
5 rows processed
As (perhaps) expected, the CBO uses the index to retrieve the small number of rows (just 5 rows).
However, if we run the following query which also returns a small number of rows (just 4 rows) BUT with the relatively unselective, non-equality predicate based on the leading indexed ID column:
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 | 105 (11)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| RADIOHEAD | 4 | 72 | 105 (11)| 00:00:01 |
-------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
363 consistent gets
0 physical reads
0 redo size
770 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 (perhaps unexpectedly) that the CBO now ignores the index and uses a Full Table Scan, even though only 4 rows are returned from a 100,000 row table.
This is a common area of confusion. Why does Oracle not use the index when both columns in the index are referenced in the SQL predicates and only a tiny number of rows are returned?
The answer comes down to the very unselective non-equality predicate (ID BETWEEN 1000 AND 5000) being serviced by the leading column (ID) of the index.
The “ID BETWEEN 1000 AND 5000” predicate basically covers 40% of all known ID values, which means Oracle must now read 40% of all Leaf Blocks within the index (one leaf block at a time), starting with ID =1000 and ending with ID = 5000. Although there are very few rows that then subsequently match up with the other (CODE = 140) predicate based on the second column (CODE) of the index, these relatively few values could exist anywhere within the 40% ID range.
Therefore, when costing the reading of the actual index, the CBO basically stops its calculations after the non-equality predicate on this leading ID column and indeed estimates that a full 40% of the index itself must be scanned.
If we force the CBO into a range scan via a basic index hint:
SQL> SELECT /*+ index(r) */ * FROM radiohead r WHERE id BETWEEN 1000 AND 5000 AND CODE = 140;
Execution Plan
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 72 | 116 (4)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| RADIOHEAD | 4 | 72 | 116 (4)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | RADIOHEAD_ID_CODE_I | 4 | | 112 (4)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
114 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 that the overall cost of this index based plan is 116, greater than the 105 cost of the Full Table Scan (and hence why the Full Table Scan was selected). We also notice that the vast majority of this 116 cost can be attributed to the index scan itself in the plan, which has a cost of 112.
If you have a calculator handy, this is basically how these costs are derived.
Range Selectivity = (Max Range Value–Min Range Value)/(Max Column Value–Min Column Value)
Effective Index Selectivity = Range Selectivity + 2 x ID density (as a BETWEEN clause was used which is inclusive of Min/Max range)
= (5000-1000)/(10000-1) + 2 x (1/10000)
= 0.40004 + 0.0002
= 0.40024
Effective Table Selectivity = ID selectivity (as above) x CODE selectivity
= 0.40024 x (1/10000)
= 0.40024 x 0.0001
= 0.000040024
These selectivities are then inserted into the following index costing formula:
Index IO Cost = blevel +
ceil(effective index selectivity x leaf_blocks) +
ceil(effective table selectivity x clustering_factor)
Index IO Cost = 1 + ceil(0.40024 x 265) + ceil(0.000040024 x 99034)
= 1 + 107 + 4
= 108 + 4 = 112.
Index Access Cost = IO Costs + CPU Costs (in this plan, 4% of total costs)
= (108 + (112 x 0.04)) + (4 + (4 x 0.04))
= (108 + 4) + (4 + 0)
= 112 + 4
= 116
So we can clearly see how the CBO has made its calculations, come up with its costs and has decided that the Full Table Scan is indeed the cheaper alternative with the current index in place.
So Automatic Indexing is doing the right thing, by creating an index with the leading column based on the equality predicate and the second indexed column based on the unselective non-equality predicate.
I’ll expand on this point in an upcoming Part II post.
Automatic Indexing 21c: Non-Equality Predicate Anomaly (“Strangers When We Meet”) July 14, 2022
Posted by Richard Foote in 21c New Features, Automatic Indexing, Autonomous Database, Autonomous Transaction Processing, CBO, Exadata, Exadata X8, Full Table Scans, Index Column Order, Invisible Indexes, Non-Equality Predicates, Oracle, Oracle 21c, Oracle Blog, Oracle Cloud, Oracle Cost Based Optimizer, Oracle Indexes, Performance Tuning, Richard Foote Training, Richard's Blog.3 comments
I’m currently putting together some Exadata related training for a couple of customers and came across a rather strange anomaly with regard the status of Automatic Indexes, when created in part on unselective, non-equality predicates.
As discussed previously, Oracle Database 21c now allows the creation of Automatic Indexes based on non-equality predicates (previously, Automatic Indexes were only created on equality-based predicates).
But one appears to get rather odd resultant Automatic Indexes in the scenario where the non-equality predicate is not particularly selective but other predicates are highly selective.
To illustrate, I’ll create a basic table that has two columns (ID and CODE) that are both highly selective:
SQL> create table ziggy_new (id number, code number, name varchar2(42)); Table created. SQL> insert into ziggy_new select rownum, mod(rownum, 1000000)+1, 'David Bowie' from dual connect by level <= 10000000; 10000000 rows created. SQL> commit; Commit complete. SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'ZIGGY_NEW'); PL/SQL procedure successfully completed.
So there are currently no indexes on this table.
I’ll next run the following SQL (and others similar) a number of times:
SQL> select * from ziggy_new where code=42 and id between 1 and 100000;
Execution Plan
----------------------------------------------------------
Plan hash value: 3165184525
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 23 | 6738 (2) | 00:00:01 |
| * 1 | TABLE ACCESS STORAGE FULL | ZIGGY_NEW | 1 | 23 | 6738 (2) | 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - storage("CODE"=42 AND "ID"<=100000 AND "ID">=1)
filter("CODE"=42 AND "ID"<=100000 AND "ID">=1)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
38605 consistent gets
38600 physical reads
0 redo size
725 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
Without any indexes, the CBO currently has no choice but to use a Full Table Scan.
But only 1 row is returned. The first equality predicate on the CODE column is highly selective and on its own would only return 10 rows out of the 10M row table. The second, non-equality range-based predicate on the ID column is nowhere near as selective and offers limited additional filtering.
The CBO stops calculating index related costs after a non-equality predicate column (as subsequent column values could exist anywhere within the preceding range), and so the more effective index here is one based on (CODE, ID) with the non-equality predicate column second, or potentially just on the CODE column only, as the ID range offers minimal filtering benefits.
So what does Automatic Indexing make of things?
If we look at the subsequent Automatic Indexing report:
SUMMARY (AUTO INDEXES) ------------------------------------------------------------------------------- Index candidates : 3 Indexes created (visible / invisible) : 1 (0 / 1) Space used (visible / invisible) : 209.72 MB (0 B / 209.72 MB) Indexes dropped : 0 SQL statements verified : 44 SQL statements improved (improvement factor) : 12 (64.7x) SQL plan baselines created : 0 Overall improvement factor : 1.6x ------------------------------------------------------------------------------- SUMMARY (MANUAL INDEXES) ------------------------------------------------------------------------------- Unused indexes : 0 Space used : 0 B Unusable indexes : 0 ------------------------------------------------------------------------------- INDEX DETAILS ------------------------------------------------------------------------------- The following indexes were created: ------------------------------------------------------------------------------- ---------------------------------------------------------------------------- | Owner | Table | Index | Key | Type | Properties | ---------------------------------------------------------------------------- | BOWIE | ZIGGY_NEW | SYS_AI_75j16xff1ag3j | CODE,ID | B-TREE | NONE | ----------------------------------------------------------------------------
So Automatic Indexing has indeed created an index based on CODE,ID (a common Automatic Indexing trait appears to be to create an index based on all available predicates).
BUT the index is created as an INVISIBLE Index and so can not generally be used by database sessions.
SQL> select index_name, auto, visibility, status, num_rows, leaf_blocks, clustering_factor
from user_indexes where table_name='ZIGGY_NEW';
INDEX_NAME AUT VISIBILIT STATUS NUM_ROWS LEAF_BLOCKS CLUSTERING_FACTOR
------------------------------ --- --------- -------- ---------- ----------- -----------------
SYS_AI_75j16xff1ag3j YES INVISIBLE VALID 10000000 25123 10000000
SQL> select index_name, column_name, column_position
from user_ind_columns where table_name='ZIGGY_NEW';
INDEX_NAME COLUMN_NAME COLUMN_POSITION
------------------------------ ------------ ---------------
SYS_AI_75j16xff1ag3j CODE 1
SYS_AI_75j16xff1ag3j ID 2
So re-running the previous SQL statements continues to use a Full Table Scan:
SQL> select * from ziggy_new where code=42 and id between 1 and 100000;
Execution Plan
----------------------------------------------------------
Plan hash value: 3165184525
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 23 | 6738 (2) | 00:00:01 |
| * 1 | TABLE ACCESS STORAGE FULL | ZIGGY_NEW | 1 | 23 | 6738 (2) | 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - storage("CODE"=42 AND "ID"<=100000 AND "ID">=1)
filter("CODE"=42 AND "ID"<=100000 AND "ID">=1)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
38605 consistent gets
38600 physical reads
0 redo size
725 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
Automatic Indexing appears to only create Invisible indexes when there is an inefficient non-equality predicate present. It won’t create the index as a Visible index, even though it would significantly benefit these SQL statements that caused its creation. And Automatic Indexing won’t create an index on just the highly selective CODE equality predicate, which would also be of much benefit to these SQL statements.
If we now run similar queries, but with much more selective non-equality predicates, such as:
SQL> select * from ziggy_new where code=1 and id between 1 and 10;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 3165184525
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 23 | 6738 (2) | 00:00:01 |
| * 1 | TABLE ACCESS STORAGE FULL | ZIGGY_NEW | 1 | 23 | 6738 (2) | 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - storage("CODE"=1 AND "ID"<=10 AND "ID">=1)
filter("CODE"=1 AND "ID"<=10 AND "ID">=1)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
38604 consistent gets
38600 physical reads
0 redo size
503 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
Again, with no (Visible) index present, the CBO currently has no choice but to use the Full Table Scan.
But during the next cycle, after Automatic Indexing kicks in again:
SUMMARY (AUTO INDEXES)
-------------------------------------------------------------------------------
Index candidates : 5
Indexes created (visible / invisible) : 1 (1 / 0)
Space used (visible / invisible) : 209.72 MB (209.72 MB / 0 B)
Indexes dropped : 0
SQL statements verified : 89
SQL statements improved (improvement factor) : 31 (71.9x)
SQL plan baselines created : 0
Overall improvement factor : 1.7x
-------------------------------------------------------------------------------
SUMMARY (MANUAL INDEXES)
-------------------------------------------------------------------------------
Unused indexes : 0
Space used : 0 B
Unusable indexes : 0
-------------------------------------------------------------------------------
INDEX DETAILS
-------------------------------------------------------------------------------
The following indexes were created:
-------------------------------------------------------------------------------
----------------------------------------------------------------------------
| Owner | Table | Index | Key | Type | Properties |
----------------------------------------------------------------------------
| BOWIE | ZIGGY_NEW | SYS_AI_75j16xff1ag3j | CODE,ID | B-TREE | NONE |
----------------------------------------------------------------------------
-------------------------------------------------------------------------------
VERIFICATION DETAILS
-------------------------------------------------------------------------------
The performance of the following statements improved:
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
Parsing Schema Name : BOWIE
SQL ID : d4znwcu4h52ca
SQL Text : select * from ziggy_new where code=42 and id between 1 and 10
Improvement Factor : 38604x
Execution Statistics:
-----------------------------
Original Plan Auto Index Plan
---------------------------- ----------------------------
Elapsed Time (s): 3398605 68
CPU Time (s): 3166824 68
Buffer Gets: 463250 3
Optimizer Cost: 6738 4
Disk Reads: 463200 0
Direct Writes: 0 0
Rows Processed: 0 0
Executions: 12 1
PLANS SECTION
--------------------------------------------------------------------------------
-------------
- Original
-----------------------------
Plan Hash Value : 3165184525
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 6738 | |
| 1 | TABLE ACCESS STORAGE FULL | ZIGGY_NEW | 1 | 23 | 6738 | 00:00:01 |
--------------------------------------------------------------------------------
- With Auto Indexes
-----------------------------
Plan Hash Value : 1514586396
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 23 | 4 | 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED | ZIGGY_NEW | 1 | 23 | 4 | 00:00:01 |
| * 2 | INDEX RANGE SCAN | SYS_AI_75j16xff1ag3j | 1 | | 3 | 00:00:01 |
-------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 2 - access("CODE"=42 AND "ID">=1 AND "ID"<=10)
Notes
-----
- Dynamic sampling used for this statement ( level = 11 )
But this time, the index on the CODE,ID columns is created as a Visible index.
INDEX_NAME AUT VISIBILIT STATUS NUM_ROWS LEAF_BLOCKS CLUSTERING_FACTOR ------------------------------ --- --------- -------- ---------- ----------- ----------------- SYS_AI_75j16xff1ag3j YES VISIBLE VALID 10000000 25123 10000000 SQL> select index_name, column_name, column_position from user_ind_columns where table_name='ZIGGY_NEW'; INDEX_NAME COLUMN_NAME COLUMN_POSITION ------------------------------ ------------ --------------- SYS_AI_75j16xff1ag3j CODE 1 SYS_AI_75j16xff1ag3j ID 2
So this index can be generally used, both by the newer SQLs that generated the now Visible index:
SQL> select * from ziggy_new where code=42 and id between 1 and 10;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 1514586396
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 23 | 4 (0) | 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED | ZIGGY_NEW | 1 | 23 | 4 (0) | 00:00:01 |
|* 2 | INDEX RANGE SCAN | SYS_AI_75j16xff1ag3j | 1 | | 3 (0) | 00:00:01 |
------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("CODE"=42 AND "ID">=1 AND "ID"<=10)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
503 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
And also used by the SQLs with the unselective non-equality predicates, that Automatic Indexing would only create as Invisible indexes:
SQL> select * from ziggy_new where code=42 and id between 1 and 100000;
Execution Plan
----------------------------------------------------------
Plan hash value: 1514586396
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 23 | 4 (0) | 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED | ZIGGY_NEW | 1 | 23 | 4 (0) | 00:00:01 |
|* 2 | INDEX RANGE SCAN | SYS_AI_75j16xff1ag3j | 1 | | 3 (0) | 00:00:01 |
------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("CODE"=42 AND "ID">=1 AND "ID"<=100000)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
729 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
Automatic Indexing appears to currently not quite do the right thing with SQL statements that have unselective non-equality predicates, by creating such indexes as only Invisible Indexes, inclusive of the unselective columns.
Although an edge case, I would recommend looking through the list of created Automatic Indexes to see if any such Invisible/Valid indexes exists, as it can suggest there are current inefficient SQL statements that could benefit from such indexes being Visible.
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…
London March 2020: “Oracle Indexing Internals and Best Practices” and “Oracle Performance Diagnostics and Tuning” Seminars January 21, 2020
Posted by Richard Foote in Richard Foote Consulting, Richard Foote Seminars, Richard's Blog.add a comment
Time is fast running out to enroll for one of my acclaimed seminars I’ll be running in London, UK in March 2020. The dates and registration links are as follows:
23-24 March 2020: “Oracle Indexing Internals and Best Practices” Seminar – Tickets and Registration Link
25-26 March 2020: “Oracle Performance Diagnostics and Tuning” Seminar – Tickets and Registration Link
You can also purchase tickets to both seminars at a special 20% combo discount (all enrollments so far have gone with this option, so it’s obviously the way to go):
23-26 March 2020: Both “Oracle Indexing Internals and Best Practices” and “Oracle Performance Diagnostics and Tuning” Seminars – Tickets and Registration Link
The cost for each individual seminar is:
- Early Bird Rate (enrollments prior to 31 January 2020) £990.00 (+ VAT)
- General Rate (enrollments post 31 January 2020) £1190 (+VAT)
The cost for the seminar combo is:
- Early Bird Rate (enrollments prior to 31 January 2020) £1550.00 (+ VAT)
- General Rate (enrollments post 31 January 2020) £1900 (+VAT)
The venue is the rather nice Hilton London Kensington.
Prices include attendance to the seminar, both soft and hard copy of the extensive seminar materials, lunch and morning/afternoon tea/coffee.
Both seminars are very highly acclaimed, with past attendees universally applauding the quality and educational outcomes of the training. They’re both aimed at Oracle Professionals (DBAs and Developers) who are interested in Performance Tuning and how to maximise the performance of both Oracle Databases and associated applications.
All the details of the Oracle Indexing Internals and Best Practices Seminar.
All the details of the Oracle Performance Diagnostic and Tuning Seminar.
Both seminars have strictly limited places to ensure a quality event for all attendees with venues booked with only small classes in mind. So I recommend booking early (as it’s cheaper) and to avoid possible disappointment. I don’t get to run these kind of events in the UK very often (it would be over 2 years since I last run seminars in London) so do take advantage of attending what will be a unique training opportunity while you can.
If you have any questions, please leave a comment or contact me at richard@richardfooteconsulting.com.
Hope to see you at one or both of these seminars next year !!
Richard Foote's Oracle Blog 