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…
Why A Brand New Index Might Benefit From An Immediate Coalesce (One Slip) July 6, 2015
Posted by Richard Foote in ASSM, Index Block Splits, Index Internals, Insert Append, Oracle Indexes, Tree Dumps, Truncate Indexes.2 comments
A recent question on the OTN Forums Reg: Index – Gathering Statistics vs. Rebuild got me thinking on a scenario not unlike the one raised in the question where a newly populated index might immediately benefit from a coalesce.
I’ve previously discussed some of the pertinent concepts such as how index rebuilds can make indexes bigger, not smaller and some of the issues around 90-10 block splits not occurring.
Let me show you a scenario where a newly populated index might benefit from an immediate coalesce.
As usual, I start with my little Bowie table and index on the ID column:
SQL> create table bowie (id number, name varchar2(42)); Table created. SQL> insert into bowie select rownum, 'DAVID BOWIE' from dual connect by level <= 1000000; 1000000 rows created. SQL> commit; Commit complete. SQL> create index bowie_id_i on bowie(id); Index created.
If we look at the current statistics on table and index:
SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'BOWIE'); PL/SQL procedure successfully completed. SQL> select num_rows, blocks, empty_blocks from dba_tables where table_name='BOWIE'; NUM_ROWS BLOCKS EMPTY_BLOCKS ---------- ---------- ------------ 1000000 3142 0 SQL> select blevel, leaf_blocks, num_rows from dba_indexes where index_name='BOWIE_ID_I'; BLEVEL LEAF_BLOCKS NUM_ROWS ---------- ----------- ---------- 2 2226 1000000
We note the index currently has 2226 leaf blocks. A look at a partial tree dump of the index:
—– begin tree dump
branch: 0x1834723 25380643 (0: nrow: 4, level: 2)
branch: 0x18340b6 25378998 (-1: nrow: 672, level: 1)
leaf: 0x1834724 25380644 (-1: row:485.485 avs:828)
leaf: 0x1834725 25380645 (0: row:479.479 avs:820)
leaf: 0x1834726 25380646 (1: row:479.479 avs:820)
leaf: 0x1834727 25380647 (2: row:479.479 avs:820)
leaf: 0x1834728 25380648 (3: row:479.479 avs:820)
leaf: 0x1834729 25380649 (4: row:479.479 avs:819)
leaf: 0x183472a 25380650 (5: row:479.479 avs:820)
leaf: 0x183472b 25380651 (6: row:479.479 avs:820)
leaf: 0x183472c 25380652 (7: row:479.479 avs:820)
leaf: 0x183472d 25380653 (8: row:479.479 avs:819)
leaf: 0x183472e 25380654 (9: row:479.479 avs:820)
leaf: 0x183472f 25380655 (10: row:479.479 avs:820)
…
Shows us that the index is fully populated with just the default 10% pctfree of free space (most leaf blocks have an avs of 819/820 free bytes).
The forum question mentions a scenario where 70% of the table is archived away. This is done by storing in a temporary table the 30% of required data, followed by a truncate of the original table and the 30% of data being re-inserted. Something like the following:
SQL> create table bowie_temp as select * from bowie where id > 700000; Table created. SQL> truncate table bowie; Table truncated.
So we store in the temp table all values with an ID > 700000. When a table is truncated, so are all the associated indexes. If we performed a tree dump of the index straight after the truncate:
—– begin tree dump
leaf: 0x1834723 25380643 (0: row:0.0 avs:8000)
—– end tree dump
We can see the index consists now of nothing but an empty leaf block.
If we now re-insert the 30% of data of interest and collect fresh statistics:
SQL> insert into bowie select * from bowie_temp; 300000 rows created. SQL> commit; Commit complete. SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'BOWIE'); PL/SQL procedure successfully completed. SQL> select num_rows, blocks, empty_blocks from dba_tables where table_name='BOWIE'; NUM_ROWS BLOCKS EMPTY_BLOCKS ---------- ---------- ------------ 300000 1000 0 SQL> select blevel, leaf_blocks, num_rows from dba_indexes where index_name='BOWIE_ID_I'; BLEVEL LEAF_BLOCKS NUM_ROWS ---------- ----------- ---------- 2 1112 300000
We note the number of leaf blocks has effectively halved to just 1112 leaf blocks down from 2226 leaf blocks.
But isn’t this index somewhat larger than it should be ? If we removed 70% of data, why has the index just reduced by 50% ?
If we look at a partial tree dump of the index now:
branch: 0x1834723 25380643 (0: nrow: 3, level: 2)
branch: 0x18335de 25376222 (-1: nrow: 332, level: 1)
leaf: 0x1834727 25380647 (-1: row:255.255 avs:3922)
leaf: 0x18004f6 25167094 (0: row:255.255 avs:3923)
leaf: 0x18004f2 25167090 (1: row:255.255 avs:3922)
leaf: 0x18004f3 25167091 (2: row:255.255 avs:3923)
leaf: 0x18004f4 25167092 (3: row:255.255 avs:3922)
leaf: 0x1800505 25167109 (4: row:449.449 avs:821)
leaf: 0x18004f5 25167093 (5: row:246.246 avs:4066)
leaf: 0x18004f1 25167089 (6: row:246.246 avs:4067)
leaf: 0x1834724 25380644 (7: row:500.500 avs:5)
leaf: 0x1834725 25380645 (8: row:500.500 avs:5)
leaf: 0x1834726 25380646 (9: row:500.500 avs:5)
leaf: 0x18004f7 25167095 (10: row:500.500 avs:5)
leaf: 0x18004f0 25167088 (11: row:255.255 avs:3922)
leaf: 0x1833d8c 25378188 (12: row:255.255 avs:3923)
leaf: 0x18331ed 25375213 (13: row:255.255 avs:3922)
leaf: 0x18331fd 25375229 (14: row:255.255 avs:3923)
leaf: 0x18331fa 25375226 (15: row:255.255 avs:3922)
leaf: 0x18331c8 25375176 (16: row:255.255 avs:3923)
leaf: 0x18331c9 25375177 (17: row:253.253 avs:3954)
leaf: 0x18331cd 25375181 (18: row:255.255 avs:3923)
leaf: 0x18331d4 25375188 (19: row:255.255 avs:3923)
leaf: 0x18331e0 25375200 (20: row:255.255 avs:3922)
…
We notice that vast areas of the index now has 50% of free space, not the default 10% it had previously.
The “problem” here is that when data was stored in the temp table, there was nothing to guarantee that the data be physically stored in the same order as the ID column. ASSM tablespaces will effectively pick random free blocks below the high water mark of the table so that although data might well be ordered within a block, the blocks within the table might not be logically ordered with respect to the data being inserted.
A simple select from the temp table displaying the “first” 20 rows of the table will illustrate this:
SQL> select * from bowie_temp where rownum <=20; ID NAME ---------- ------------------------------------------ 701717 DAVID BOWIE 701718 DAVID BOWIE 701719 DAVID BOWIE 701720 DAVID BOWIE 701721 DAVID BOWIE 701722 DAVID BOWIE 701723 DAVID BOWIE 701724 DAVID BOWIE 701725 DAVID BOWIE 701726 DAVID BOWIE 701727 DAVID BOWIE 701728 DAVID BOWIE 701729 DAVID BOWIE 701730 DAVID BOWIE 701731 DAVID BOWIE 701732 DAVID BOWIE 701733 DAVID BOWIE 701734 DAVID BOWIE 701735 DAVID BOWIE 701736 DAVID BOWIE
Note that the first selected range of values starts with 701717 and not 700001.
Therefore, when the data is loaded back within the table, the data is not necessarily ordered as it was previously and an insert into a full leaf block might not necessarily have the largest ID column currently within the table/index. That being the case, a 50-50 block split is performed rather than the 90-10 block splits that only occur when it’s the largest indexed value being inserted into a full leaf block. 90-10 block splits leaves behind nice full leaf blocks, but 50-50 block splits leaves behind 1/2 emptied blocks that don’t get filled if they don’t house subsequent inserts.
The 50-50 leaf block split is resulting in a larger index but most importantly, these areas of free space are not going to be used by subsequent monotonically increasing ID index values.
To reclaim this effectively unusable index storage, the index would benefit from an immediate coalesce.
Of course, the best cure would be to prevent this scenario from occurring in the first place.
One option would be to insert the data in a manner that ensures the effective ordering of the indexed data:
SQL> truncate table bowie; Table truncated. SQL> insert into bowie select * from bowie_temp order by id; 300000 rows created. SQL> commit; Commit complete. SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'BOWIE'); PL/SQL procedure successfully completed. SQL> select num_rows, blocks, empty_blocks from dba_tables where table_name='BOWIE'; NUM_ROWS BLOCKS EMPTY_BLOCKS ---------- ---------- ------------ 300000 1000 0 SQL> select blevel, leaf_blocks, num_rows from dba_indexes where index_name='BOWIE_ID_I'; BLEVEL LEAF_BLOCKS NUM_ROWS ---------- ----------- ---------- 1 600 300000
We notice by inserting the data in ID order within the table (via the “order by” clause), we guarantee that an insert into a full leaf block will indeed be as a result of the highest current index entry and that 90-10 block splits are performed. This leaves behind perfectly full leaf blocks and a nice, compact index structure. The index now only has 600 leaf blocks, significantly less than before with no “wasted” storage.
A partial tree dump of the index highlights this:
—– begin tree dump
branch: 0x1834723 25380643 (0: nrow: 600, level: 1)
leaf: 0x1834727 25380647 (-1: row:500.500 avs:5)
leaf: 0x1834724 25380644 (0: row:500.500 avs:5)
leaf: 0x1834725 25380645 (1: row:500.500 avs:5)
leaf: 0x1834726 25380646 (2: row:500.500 avs:5)
leaf: 0x18004f7 25167095 (3: row:500.500 avs:5)
leaf: 0x18004f0 25167088 (4: row:500.500 avs:5)
leaf: 0x18004f1 25167089 (5: row:500.500 avs:5)
leaf: 0x18004f5 25167093 (6: row:500.500 avs:5)
leaf: 0x18004f6 25167094 (7: row:500.500 avs:5)
leaf: 0x18004f2 25167090 (8: row:500.500 avs:5)
leaf: 0x18004f3 25167091 (9: row:500.500 avs:5)
leaf: 0x18004f4 25167092 (10: row:500.500 avs:5)
…
The index leaf blocks are chock-a-block full with just 5 bytes free, not enough space for another index entry.
Another alterative would of course be to make the indexes unusable before re-inserting data into the data and rebuild the index later with a pctfree of 0, a safe and appropriate value for monotonically increasing values. This has the added advantage of significantly improving the performance of the bulk insert operation.
If the data isn’t monotonically increasing, then 50-50 block splits are fine as the resultant free space would indeed be effectively used.
Update: 7 July 2015
And as Jonathan Lewis kindly reminded me, another method to avoid this issue is to simply use an insert Append. This will record all the key entries as it goes, sort them and populate the index much more efficiently in one step:
SQL> truncate table bowie; Table truncated. SQL> insert /*+ append */ into bowie select * from bowie_temp; 300000 rows created. SQL> commit; Commit complete. SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'BOWIE'); PL/SQL procedure successfully completed. SQL> select num_rows, blocks, empty_blocks from dba_tables where table_name='BOWIE'; NUM_ROWS BLOCKS EMPTY_BLOCKS ---------- ---------- ------------ 300000 936 0 SQL> select blevel, leaf_blocks, num_rows from dba_indexes where index_name='BOWIE_ID_I'; BLEVEL LEAF_BLOCKS NUM_ROWS ---------- ----------- ---------- 1 600 300000
So at 600 leaf blocks, the index is again populated within a fully compacted index structure.
Indexes And Small Tables Part IV (Treefingers) May 5, 2009
Posted by Richard Foote in Index Block Splits, Index Internals, Oracle Indexes, Root Index Block, Small Indexes.18 comments
As I asked in my previous post, the key question when comparing the associated costs of accessing a small table via a Full Table Scan (FTS) vs. an index scan is why does Oracle visit the segment header during a FTS but not during an index scan ?
The answer all comes down to understanding why Oracle must visit the table segment header during a FTS and how Oracle can avoid visiting the index segment header during an index scan.
Oracle must visit the table segment header during a FTS because it contains vital information necessary to perform the FTS, namely the extent map and the High Water Mark (HWM) associated with the table. Even with a table that only contains 1 data block worth of rows as in the SMALL table in my examples, Oracle has no way of automatically determining there’s actually only just the one block worth of data. It has to somehow look that up and determine exactly what table blocks Oracle needs to access during the FTS operation and the table segment header contains this necessary meta data. During “most” FTS operations, which are generally speaking larger, “expensive” operations, these accesses to the table segment header constitute a relatively small overhead. However, for FTS operations on “small” tables, accessing the table segment header can actually be a significant proportion of the overall associated costs.
During an index scan operation, there’s nothing of interest within the index segment header. The critical index block, the index block by which all index scans must start is the root block of the index (except Fast Full Index Scans which are basically the FTS equivalent for indexes). There’s no need to access the index segment header because it’s the root block that actually contains all the necessary information by which to start the index scan operation. The root blocks contains the pointers to subsequent index blocks (be it a branch or leaf blocks) that Oracle needs to follow in order to find the index entry of interest. The key to starting an index scan therefore is in determining the location of the index root block.
But how can Oracle determine the location of the index root block ?
Well Oracle implements a little “trick”, a golden rule with regard to indexes that doesn’t change regardless of the Oracle version, regardless of the O/S version, regardless of the type of tablespace or tablespace option of the index and regardless of how the index is created or grows and block splits over time.
The index root block is always, always, always the block immediately after the index segment header.
Always.
Therefore, when the Oracle code issues the associated function calls to perform an index scan, the first index block that Oracle assesses is the index segment header plus an offset of 1. Whereas a FTS accesses the table segment header, an index scan accesses the index segment block id plus 1.
With a tiny index that only has a level of 0 (or height of 1), note there is not “root” block as such as all the index entries can fit within one index leaf block. However, this block, this one and only leaf block within the index structure is also always the block immediately after the index segment header.
Always.
When we add more index entries into this one and only leaf block, we’ll eventually reach a point when it’s full and Oracle must perform an index block split operation. Oracle will then allocate 2 new blocks to the index. Assuming a 50-50 block split, one of these new blocks is assigned the lower 1/2 of all the current index entry values and the other new block is assigned the other upper 1/2 of the current index entry values. The original index leaf block content is then cleaned out and reassigned with just relative block address pointers and value boundaries associated with the 2 new leaf blocks.
The original index leaf block has been “reborn” as the root block of the index.
It’s quite easy to demonstrate how the original index block in a level 0 index or the root block of an index never changes and is always the block that follows the index segment header.
First, just create a little table and associated index:
SQL> CREATE TABLE same_root (id NUMBER, name VARCHAR2(30));
Table created.
SQL> INSERT INTO same_root VALUES (1, ‘The Thin White Duke’);
1 row created.
SQL> COMMIT;
Commit complete.
SQL> CREATE INDEX same_root_i ON same_root(name);
Index created.
If we dump the block immediately following the index segment header, we can confirm it’s our index block of interest, containing our one and only index entry:
SQL> select header_file, header_block from dba_segments where segment_name=’SAME_ROOT_I’;
HEADER_FILE HEADER_BLOCK ----------- ------------ 5 107441
SQL> alter system dump datafile 5 block 107442;
System altered.
Following is an extract of the index block dump:
Leaf block dump
===============
header address 98959964=0x5e6025c
kdxcolev 0
KDXCOLEV Flags = – – –
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=— is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 1
kdxcofbo 38=0x26
kdxcofeo 8007=0x1f47
kdxcoavs 7969
kdxlespl 0
kdxlende 0
kdxlenxt 0=0x0
kdxleprv 0=0x0
kdxledsz 0
kdxlebksz 8036
row#0[8007] flag: ——, lock: 0, len=29
col 0; len 19; (19): 54 68 65 20 54 68 69 6e 20 57 68 69 74 65 20 44 75 6b 65
col 1; len 6; (6): 01 41 a3 aa 00 00
—– end of leaf block dump —–
Note that it is indeed our one and only index leaf block.
If we now take a treedump of the index:
SQL> SELECT object_id FROM dba_objects where object_name = ‘SAME_ROOT_I’;
OBJECT_ID ---------- 67721
SQL> ALTER SESSION SET EVENTS ‘immediate trace name treedump level 67721’;
Session altered.
Following is the treedump output:
—– begin tree dump
leaf: 0x141a3b2 21078962(0: nrow: 1 rrow: 1)
—– end tree dump
We note that the relative block address of our one and only index leaf block is 21078962.
If we now add a whole bunch of new rows to the table so that the leaf block can no longer hold all the index entries, thereby forcing the index to block split and grow:
SQL> insert into same_root select rownum+1, ‘David Bowie’ from dual connect by level <=100000;
100000 rows created.
SQL> commit;
Commit complete.
And now take a block dump of the same index block:
SQL> alter system dump datafile 5 block 107442;
System altered.
Branch block dump
=================
header address 98959940=0x5e60244
kdxcolev 2
KDXCOLEV Flags = – – –
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=— is converted=Y
kdxconco 2
kdxcosdc 2
kdxconro 2
kdxcofbo 32=0x20
kdxcofeo 8016=0x1f50
kdxcoavs 7984
kdxbrlmc 21238606=0x144134e
kdxbrsno 1
kdxbrbksz 8060
kdxbr2urrc 0
row#0[8038] dba: 21238607=0x144134f
col 0; len 11; (11): 44 61 76 69 64 20 42 6f 77 69 65
col 1; len 5; (5): 01 41 be 5f 01
row#1[8016] dba: 21238769=0x14413f1
col 0; len 11; (11): 44 61 76 69 64 20 42 6f 77 69 65
col 1; len 5; (5): 01 44 12 ba 01
—– end of branch block dump —–
We notice that the block is no longer a leaf block but has changed itself into an index branch block.
But not just any branch block. If we now take a new treedump:
SQL> ALTER SESSION SET EVENTS ‘immediate trace name treedump level 67721’;
Session altered.
branch: 0x141a3b2 21078962(0: nrow: 3, level: 2)
branch: 0x144134e 21238606 (-1: nrow: 161, level: 1)
leaf: 0x141a3b3 21078963 (-1: nrow: 179 rrow: 179)
leaf: 0x141a3b4 21078964 (0: nrow: 179 rrow: 179)
leaf: 0x141a3b5 21078965 (1: nrow: 179 rrow: 179)
leaf: 0x141a3b6 21078966 (2: nrow: 179 rrow: 179)
leaf: 0x141a3b7 21078967 (3: nrow: 179 rrow: 179)
….
The above partial listing of the treedump clearly shows that the index has grown from a level 0 index to a level 2 index and that the root block is the very same index block as the original leaf block listed as it has the same relative block address as before (21078962).
Indeed the original leaf block is now the index root block which is still the same block that immediately follows the index segment header.
Because Oracle doesn’t have to visit the index segment header and can simply directly access the block following the index segment header as this block is always the first index block of interest when performing an index scan, the index scan has that little advantage over the FTS. And it’s this little advantage that can give the index scan the edge over a FTS, even if we’re potentially accessing data from a very small table.
And you can’t get much smaller than a table that has all it’s rows in the one table block.
So far though, the example I’ve shown has been a “normal”, everyday, non-unique index that has a 1 consistent get advantage over the FTS when accessing a row of interest. I’ll next discuss how indexes can having an even bigger edge and more significant advantage over a FTS of a tiny table, than just the 1 consistent get …
Empty Leaf Blocks and Statistics (Sense Of Doubt) July 8, 2008
Posted by Richard Foote in Index Access Path, Index Block Splits, Index Delete Operations, Index statistics, Oracle Cost Based Optimizer, Oracle Indexes, Performance Tuning.15 comments
I’ve recently been discussing how empty index blocks or those blocks that contain nothing but deleted index entries are placed on the index freelist and can potentially be recycled during subsequent index block split operations.
A point that’s not so well known about such empty index blocks is how Oracle considers them when calculating index related statistics and the possible implications this may have on the CBO.
Let’s set the scene with an example I’ve used previously where we load a table/index with 10000 entries and then subsequently delete the vast majority of them.
SQL> create table rich as select rownum id, ‘Bowie’ text from dual connect by level <= 10000;
Table created.
SQL> create index rich_i on rich(id);
Index created.
OK, so we now have an index with 10000 entries. Let’s just check to see how many leaf blocks we currently have:
SQL> analyze index rich_i validate structure;
Index analyzed.
SQL> select lf_rows, lf_blks, del_lf_rows from index_stats;
LF_ROWS LF_BLKS DEL_LF_ROWS ---------- ---------- ----------- 10000 21 0
So we currently have 10000 LF_ROWS and 21 LK_BLKS with no deleted index rows at this stage.
Let’s now deleted the vast majority of rows from the table and hence index row entries from the index:
SQL> delete rich where id <= 9990;
9990 rows deleted.
SQL> commit;
Commit complete.
OK, so now we have an index with the vast majority of the index entries having been deleted and with all but one index leaf block effectively empty.
Let’s start by looking at how the ANALYZE INDEX … VALIDATE STRUCTURE deals with empty leaf blocks and index entries:
SQL> analyze index rich_i validate structure;
Index analyzed.
SQL> select lf_rows, lf_blks, del_lf_rows from index_stats;
LF_ROWS LF_BLKS DEL_LF_ROWS ---------- ---------- ----------- 10000 21 9990
The first thing we notice is that the LF_ROWS statistics still has a value of 10000. It still counts index entries, even if they’ve been deleted.
We also notice that the LF_BLKS value is 21 so those leaf blocks that are effectively empty are still counted as well.
Let’s now collect statistics using DBMS_STATS as currently recommended by Oracle:
SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=> ‘RICH’, cascade => true, estimate_percent=> null, method_opt=> ‘FOR ALL COLUMNS SIZE 1’);
If we now look at the index statistics:
SQL> select index_name, num_rows, leaf_blocks from dba_indexes where index_name = ‘RICH_I’;
INDEX_NAME NUM_ROWS LEAF_BLOCKS ---------- -------- ----------- RICH_I 10 1
We notice a couple of important differences. Firstly, the NUM_ROWS value is 10, highlighting that only non-deleted index entries are counted. We also notice that the number of LEAF_BLOCKS is only 1, highlighting that only those index leaf blocks that contain non-deleted index entries are counted. Although there are 20 other leaf blocks within the index structure, these are not counted and considered by the CBO when statistics are calculated using DBMS_STATS.
If we run the following simple little query that effectively selects all remaining rows from the table, we notice the following execution plan:
SQL> select * from rich where id between 1 and 10000;
ID TEXT ---------- ----- 9991 Bowie 9992 Bowie 9993 Bowie 9994 Bowie 9995 Bowie 9996 Bowie 9997 Bowie 9998 Bowie 9999 Bowie 10000 Bowie Execution Plan -------------------------------------------- |Id | Operation | Name | -------------------------------------------- | 0 | SELECT STATEMENT | | | 1 | TABLE ACCESS BY INDEX ROWID| RICH | |*2 | INDEX RANGE SCAN | RICH_I | --------------------------------------------
The index is actually used to select all the remaining 10 rows, in part because the index related costs are so low.
Let’s see what would happens if we were to use the old, ANALYZE command to calculate the index statistics:
SQL> analyze index rich_i compute statistics;
Index analyzed.
First, let’s see if the index statistics are any different …
select index_name, num_rows, leaf_blocks from dba_indexes where index_name = ‘RICH_I’;
INDEX_NAME NUM_ROWS LEAF_BLOCKS ---------- -------- ----------- RICH_I 10 21
OK, a big big difference here. Where previously, DBMS_STATS didn’t include the empty leaf blocks in it’s statistics, we now notice that using the ANALYZE command does include such empty leaf blocks. The LEAF_BLOCKS value is now 21, not 1 as it was previously. Note though that the number of NUM_ROWS is still 10, so it still doesn’t count the deleted index entries themselves, just the empty leaf blocks.
But leaf blocks is one of the key statistics used by the CBO when calculating the cost of using an index related access path. Could this all make a difference in how our previous query is costed by the CBO ?
SQL> select * from rich where id between 1 and 10000;
ID TEXT ---------- ----- 9991 Bowie 9992 Bowie 9993 Bowie 9994 Bowie 9995 Bowie 9996 Bowie 9997 Bowie 9998 Bowie 9999 Bowie 10000 Bowie 10 rows selected. Execution Plan ---------------------------------- | Id | Operation | Name | ---------------------------------- | 0 | SELECT STATEMENT | | |* 1 | TABLE ACCESS FULL| RICH | ----------------------------------
Oh yes indeed. Now the CBO has decided to use a Full Table Scan, in large part because of the additional calculated costs associated with using the index.
Note these tests work the same on all supported versions of Oracle.
So empty leaf blocks can still have a large impact on not only how a query may perform but indeed on how the CBO calculates the associated costs, depending on how the statistics are generated.
Yes, there are differences between the ANALYZE command and DBMS_STATS. This is one of the more subtle differences …
Introduction To Reverse Key Indexes: Part III (A Space Oddity) January 18, 2008
Posted by Richard Foote in Index Block Splits, Index Internals, Oracle Indexes, Performance Tuning, Reverse Key Indexes.18 comments
A possibly significant difference between a Reverse and a Non-Reverse index is the manner in which space is used in each index and the type of block splitting that takes place.
Most Reverse Key Indexes are created to resolve contention issues as a result of monotonically increasing values. As monotonically increasing values get inserted, each value is greater than all previous values (providing there are no outlier values present) and so fill the “right-most” leaf block. If the “right-most” block is filled by the maximum current value in the index, Oracle performs 90-10 block splits meaning that full index blocks are left behind in the index structure. Assuming no deletes or updates, the index should have virtually 100% used space.
However, it’s equivalent Reverse Key index will have the values reversed and dispersed evenly throughout the index structure. As index blocks fill, there will be a very remote chance of it being due to the maximum indexed value and 50-50 block splits will result. The PCT_USED is likely therefore to be significantly less, averaging approximately 70-75% over time.
Therefore, for indexes with no deletions, a Reverse Key index is likely to be less efficient from a space usage point of view.
However, if there are deletions, the story may differ.
Deleted space can be reused if an insert is subsequently made into an index block with deleted entries or if a leaf block is totally emptied. However, if a leaf block contains any non-deleted entries and if subsequent inserts don’t hit the leaf block, then the deleted space can not reused. As monotonically increasing values in a non-reverse index only ever insert into the “right-most” leaf block, it won’t be able to reuse deleted space if leaf blocks are not totally emptied. Overtime, the number of such “almost but not quite empty” index leaf blocks may in some scenarios increase to significant levels and the index may continue to grow at a greater proportional rate than the table (where the reuse of space is set and controlled by the PCTUSED physical property).
However, Reverse Key indexes will be able to reuse any deleted space as they evenly distribute inserts throughout the index structure. Overtime, the index is likely to grow at a similar proportional rate as the table.
For indexes that have deletions resulting in many sparsely (but not totally emptied) leaf blocks, a Reverse Key index could be more efficient from a space usage point of view.
See this demo Differences in Space Usage Between a Reverse and a Non-Reverse Index for further details.
Do ROWID Index Row Entry Columns Impact Index Block Splits ? December 20, 2007
Posted by Richard Foote in Concatenated Indexes, Index Block Splits, Index Internals, Oracle Indexes, Richard's Musings, ROWID.11 comments
Based on a great question by Alberto Dell’Era in my “Differences Between Unique/Non-Unique” blog entry (comment 9), I thought it might be a useful exercise to show how I go about confirming my understanding of a specific concept by trying to develop a little test case or demo that can illustrate the concept. My “magic incarnation” if you like 😉
The basic question was does the ROWID that constitutes an additional column in a Non-Unique index determine whether a particular row entry is the maximum or equivalent entry or not. Because by implication, this can determine and influence whether Oracle performs the generally preferred 90-10 splits rather than 50-50 block splits for indexed column values that at least equal the maximum value.
The answer is yes because the ROWID column is just another column in the index row entry and is simply treated the same. But how to actually “illustrate” and show this ?
I needed a way therefore to insert a ROWID that was always going to be the maximum ROWID value for a Non-Unique index. Then insert a whole bunch of subsequent ROWIDs of a lesser value than the maximum and inspect via index statistics whether the type of block splits changed from 90-10 to 50-50 block splits. Remember with the Object Number being equal (if it’s there at all), the next significant portion of the ROWID is the Relative File Number.
The plan was (reasonably) simple. Create a tablespace with one data file and fill it with something. Then add a second data file and use this to store the start of my table of interest (and of course create the index). This will create a whole bunch of rows with ROWIDs of a higher Relative File Number than those in the first data file. Then drop the first table and ensure the second table uses the free space created in the first data file. That way, a whole bunch of ROWIDs can be created that are less than existing ROWIDs because it would be using ROWIDs from the first data file, which has a lesser Relative File Number.
It’s the usual process I go through with these things. Find something that’s of interest, have some idea on how I think things work, come up with plans or strategies that will illustrate whether or not what I think is true (ensuring that somewhere in the process I include at least one reference to David Bowie ;). I can then later take the initial strategies and expand them for all applicable database options and features. Then see if anything changes between database versions and platforms.
Hopefully this demo shows you how I went about proving this: Do ROWID Index Row Entry Columns Impact Index Block Splits Demo.
The benefit of then showing these demos is that others can see exactly how I came to a conclusion, potentially try them out for oneself and perhaps see holes or flaws or shortfalls in the strategy or expand or tailor them for individual requirements or environments.
Richard Foote's Oracle Blog 