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…
Updates and Indexes Part II (Down Is The New Up) February 9, 2009
Posted by Richard Foote in Index Delete Operations, Oracle Myths, Update Indexes.7 comments
In Updates and Indexes Part I, I described how there’s no such things as an “update” operation as such on a index and that an update is effectively a delete followed by an insert operation.
I also showed how Oracle only marks index entries as deleted and doesn’t physically delete the index entry at the time of the transaction.
This leads some folk into (incorrectly) thinking indexes that experience lots of update (or delete) operations need to be frequently rebuilt as the delete space might accumulate and waste space within the index structure over time .
However I also showed how deleted space is actually generally automatically reused by Oracle. For example, all it takes is one subsequent tiny little insert operation into a leaf block for all deleted entries within the leaf block to be automatically cleaned out by Oracle.
Therefore just because an index experiences lots of update activity doesn’t necessarily mean the index needs to be rebuilt. Generally, all deleted space is reused and is effectively nothing but free and available space within the index.
To emphasise this key point and attempt to really get the message across, I thought it might be worth going through a little demo of an index that does indeed experience lots of update operations and see what impact it actually has on the index.
Let’s first just create a simple little table that will have two numeric columns:
SQL> create table bowie (id number, value number);
Table created.
Now let’s populate the first column with a monotonically increasing unique identifier and the second column with a random number:
SQL> insert into bowie select rownum, ceil(dbms_random.value(0,50000))
from dual connect by level <=50000;
50000 rows created.
SQL> commit;
Commit complete.
Now we’ll create an index on the value column that is populated with a bunch of random numbers:
SQL> create index bowie_idx on bowie(value);
Index created.
Let’s collect some statistics and see how much space is being used by this “freshly” created index:
SQL> analyze index bowie_idx validate structure;
Index analyzed.
SQL> select blocks, lf_blks, btree_space, pct_used, del_lf_rows from index_stats;
BLOCKS LF_BLKS BTREE_S PCT_USED DEL_LF_ROWS ------ ------- ------- -------- ----------- 128 110 888032 90 0
Specifically note the number of leaf blocks and the overall btree space usage of the index. Also note that as the index has only just been created, we currently have no deleted index entries.
OK, next we’re going to update the index column with a new random number, one row at a time, for effectively 50% of all rows in the entire table. The table currently has 50,000 rows and we will update every other row (that’s 25,000 rows for those mathematically challenged) with a new random value. Now that will be effectively 25,000 separate delete operations and 25,000 separate insert operations.
There are some folk who would think such an exercise would result in 25,000 deleted index entries which need to be cleaned up at some point via an index rebuild.
There are some folk who would think updating 50% of all index column values would result in so much wasted space that the index would grow in an inefficient manner in order to store all these deleted index entries, likely causing performance issues.
There are some folk who think that such a regular and high proportion of updates on an index column in a table is a clear indication that such an index should be rebuilt on a regular basis.
Well, let’s see what happens. First, let’s update every other row in the entire table with a new random number for the value column:
SQL> begin
2 for i in 1..25000 loop
3 update bowie set value = ceil(dbms_random.value(0,50000)) where id = i*2;
4 commit;
5 end loop;
6 end;
7 /
PL/SQL procedure successfully completed.
Let’s see how badly the index has been impacted:
SQL> analyze index bowie_idx validate structure;
Index analyzed.
SQL> select blocks, lf_blks, btree_space, pct_used, del_lf_rows from index_stats;
BLOCKS LF_BLKS BTREE_S PCT_USED DEL_LF_ROWS ------ ------- ------- -------- ----------- 128 110 888032 90 60
How interest !!
The first thing to note is that there aren’t actually 25,000 deleted index entries at all, even though we’ve just updated 25,000 index entries. There are just 60 deleted entries currently in the index. Just 60, that’s it !!
Why ?
Because the index is effectively just a random based index and while we may update (and hence effectively delete) an index entry from a specific leaf block, at some later time we’re likely to insert another index entry into this same leaf block, thereby cleaning out any deleted entries it may contain. Effectively, almost all the deleted index entries are being automatically cleaned out by subsequent random inserts throughout the index structure.
The relative handful of currently marked deleted index entries (60) happen to exist in leaf blocks that have not had a subsequent insert since the last delete operation in the specific leaf block. But even these deleted entries will eventually be cleaned out and the space reused by any other subsequent inserts in the specific leaf blocks.
The deleted space is simply not an issue, the vast majority of it has been cleaned out and reused and those entries that haven’t yet been cleaned out will likely be reused by subsequent insert operations anyways.
If we look at the actual space used by the index, after 50% of all index entries have been updated and we note that the index has not changed at all. It has exactly the same number of leaf blocks and is using exactly the same amount of btree space. (Note: because the index values are random, it’s likely that the new values will not be exactly distributed as it was previously and there might be the possibility that the odd index leaf block could fill and split as it contains more associated values than previously. Regardless, as the index grows with more index entries, this is not going to be an issue anyways).
There is nothing “wrong” or inefficient or fragmented with this index, even though we’ve just updated (and hence deleted) 50% of all its index entries.
With deleted index entries being trivial and likely to be reused at some later time anyways and with the index having the same leaf blocks and btree space as it did when the index was newly created, rebuilding such an index would be a total and utter waste of time and resources.
Again, just because an index column is frequently updated, it doesn’t necessarily mean the index is a candidate for a periodic index rebuilds. Any such suggestions are simply misguided …
Updates and Indexes Part I (Fashion) January 17, 2009
Posted by Richard Foote in Index Delete Operations, Oracle Indexes, Oracle Myths, Update Indexes.27 comments
Let’s start with a few basic concepts.
Simplistically, Oracle doesn’t generally care where or in what order data is stored in heap tables, unless the table is partitioned or clustered. Therefore, when an update operation is performed on a column value, Oracle can basically make the change “in place” within the table block. If the value of a name column changes from say “BOWIE” to a new value of “FOOTE”, Oracle can simply just make the necessary change to the specific table block. Only if the new value is larger and there’s not sufficient space within the current table block for the new value does the picture get a little more complicated, with Oracle having to migrate the row.
However, the story isn’t quite so simple for corresponding changes to index column values. The key difference (no pun intended) is that unlike most heap tables, the location and order of the data within the index is very much an issue. All index entries must be and must always be in the order of the indexed columns. Otherwise, it would be impossible for Oracle to efficiently navigate down the index structure to find within the index all the necessary indexed values required for an index range scan. Imagine for one moment how difficult it would be to find someones phone details if the telephone book wasn’t ordered on names or to use an index in the back of a reference book if the index wasn’t ordered.
The order of an index is crucial and must always be in the order of the indexed column values. Therefore, if a specific indexed value of say “BOWIE” is updated to a new value of “FOOTE”, Oracle can’t simply make the change “in place” within the leaf block as this would result in the index order no longer being maintained. The new value of “FOOTE” would be surrounded by index values starting with “B” and there would be no easy way to subsequently find it within the index. It would likely not even be in the same index leaf block as those other index entries beginning with “F” where the new value should actually reside.
Therefore, Oracle doesn’t actually “update” an index value. The old index entry is marked as deleted and the a new index entry is inserted in the appropriate location within the index to ensure the index order is maintained. So the update of an index value is effectively a delete operation followed by an insert.
Simple example to demonstrate. Let’s first create a little table and index.
SQL> create table test_update (id number, name varchar2(10));
Table created.
SQL> create index test_update_idx on test_update (name);
Index created.
Now, let’s insert one row.
SQL> insert into test_update values (1, ‘BOWIE’);
1 row created.
SQL> commit;
Commit complete.
Let’s have a quick look at a block dump of our little index. As the index only contains the one index entry, the index will only consist of a single leaf block. This specific block is located next to the segment header of the index.
SQL> select header_file, header_block from dba_segments where segment_name = ‘TEST_UPDATE_IDX’;
HEADER_FILE HEADER_BLOCK ----------- ------------ 7 118537
We just need to add 1 to the header_block to get us the specific block id we need to dump.
SQL> alter system dump datafile 7 block 118538;
System altered.
Following is the portion of interest from the index block dump:
Leaf block dump
===============
header address 425361500=0x195a805c
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 8021=0x1f55
kdxcoavs 7983
kdxlespl 0
kdxlende 0
kdxlenxt 0=0x0
kdxleprv 0=0x0
kdxledsz 0
kdxlebksz 8036
row#0[8021] flag: ——, lock: 2, len=15
col 0; len 5; (5): 42 4f 57 49 45
col 1; len 6; (6): 01 c1 ce 8a 00 00
—– end of leaf block dump —–
We note for now that the index indeed just has the one index entry (with a row# 0).
Let’s now update this indexed column and see what impact this has in the index.
SQL> update test_update set name = ‘ZIGGY’ where id = 1;
1 row updated.
SQL> commit;
Commit complete.
SQL> alter system dump datafile 7 block 118538;
System altered.
New block dump follows:
Leaf block dump
===============
header address 425361500=0x195a805c
kdxcolev 0
KDXCOLEV Flags = – – –
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=— is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 2
kdxcofbo 40=0x28
kdxcofeo 8006=0x1f46
kdxcoavs 7966
kdxlespl 0
kdxlende 1
kdxlenxt 0=0x0
kdxleprv 0=0x0
kdxledsz 0
kdxlebksz 8036
row#0[8021] flag: —D–, lock: 2, len=15
col 0; len 5; (5): 42 4f 57 49 45
col 1; len 6; (6): 01 c1 ce 8a 00 00
row#1[8006] flag: ——, lock: 2, len=15
col 0; len 5; (5): 5a 49 47 47 59
col 1; len 6; (6): 01 c1 ce 8a 00 00
—– end of leaf block dump —–
We notice of few interesting things. Firstly, even though we’ve only ever inserted the one row in the table, the index now actually has two index entries (note the index row count kdxconro value has increased from 1 to 2 and we can see two actual index entries, row#0 and row#1).
Another interesting point is that the initial index entry has been marked as deleted (via the “D” flag highlighted in blue) and that the deleted row count kdxlende value has gone up to 1.
So yes indeed, an update index operation actually consists of a delete operation followed by an insert, even if the new index value were to reside in the same index leaf block as the previous value. Note the deleted index entry isn’t actually physically deleted, it’s only marked as deleted and so continues to consume space within the index leaf block.
If we look at the index_stats for this index:
SQL> analyze index test_update_idx validate structure;
Index analyzed.
SQL> select del_lf_rows from index_stats …
DEL_LF_ROWS ----------- 1
We notice that we do indeed have a deleted index entry listed in the statistics.
Now it’s at this point of the story when some people go “Aaah haa !! So if we have lots of updates, we effectively have lots deletes and we therefore have lots of deleted space that wastes space within the index. Therefore we would need to periodically rebuild such an index as the deleted space will just continue to grow, making the index larger and less efficient over time”.
Not necessarily.
The point that many don’t quite understand is that this “wasted” delete space can be subsequently reused by Oracle. It’s just free space that in the vast majority of indexes is automatically cleaned out and reused by Oracle.
To illustrate, let’s now insert a second row into this table. Notice the new row has an indexed value that is completely different to the previous values but because we still have available space in our current index leaf block, Oracle will simply insert the new value in this leaf block as well.
SQL> insert into test_update values (2, ‘PINK FLOYD’);
1 row created.
SQL> commit;
Commit complete.
SQL> alter system dump datafile 7 block 118538;
System altered.
Let’s have a look at our block dump now …
Leaf block dump
===============
header address 425361500=0x195a805c
kdxcolev 0
KDXCOLEV Flags = – – –
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=— is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 2
kdxcofbo 40=0x28
kdxcofeo 7986=0x1f32
kdxcoavs 7961
kdxlespl 0
kdxlende 0
kdxlenxt 0=0x0
kdxleprv 0=0x0
kdxledsz 0
kdxlebksz 8036
row#0[7986] flag: ——, lock: 2, len=20
col 0; len 10; (10): 50 49 4e 4b 20 46 4c 4f 59 44
col 1; len 6; (6): 01 c1 ce 8a 00 01
row#1[8006] flag: ——, lock: 0, len=15
col 0; len 5; (5): 5a 49 47 47 59
col 1; len 6; (6): 01 c1 ce 8a 00 00
—– end of leaf block dump —–
Now isn’t that interesting. The previously marked deleted index entry has disappeared. It’s been automatically cleaned out by Oracle and the deleted row count value kdxlende is now back to 0.
We only have an index row count kdxconro value of 2 for the two actual index entries corresponding to the two rows in the table. The previously deleted index entry is no longer recorded or stored in any way within the index.
When the new index entry was inserted, Oracle basically needed to reorganise the index leaf block to accommodate for the new index entry and automatically cleaned out any deleted index entries there may have been in the leaf block in the process. That’s all it takes to clean out deleted index entries from an index leaf block, just one subsequent insert in the leaf block.
If we now look at the index_stats …
SQL> analyze index test_update_idx validate structure;
Index analyzed.
SQL> select del_lf_rows from index_stats;
DEL_LF_ROWS ----------- 0
We note that indeed, no deleted index entries are now stored within the index. Periodically rebuilding such an index may not be necessary after all …
Regular readers will recall I’ve previously discussed how deleted index entries are generally cleaned out by Oracle automatically. Sometimes, it’s worth hammering the point a few times 😉
More on the topic of update operations and indexes to come …
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 …
Deleted Index Entries Part V (Trouble) July 1, 2008
Posted by Richard Foote in Index Delete Operations, Oracle General, Oracle Indexes.Tags: Add new tag
14 comments
As discussed in Part IV, index blocks that contain nothing but deleted index entries are placed on the index freelist and can be recycled in subsequent index block split operations. This of course is a good thing as it helps to keep the index as small and efficient as possible without having to perform relatively expensive index maintenance activities such as index rebuilds, coalesces or shrinks.
However, an important issue is that although the effectively empty index block is free and available to be reused, it remains in place in it’s current logical location within the index structure until it’s been recycled. It’s only logically unlinked and re-positioned within the index structure at the time of the block being recycled.
This simple demo highlights this issue.
First create a table and associated index and populate it with a 10000 rows:
SQL> CREATE TABLE test_1 (id NUMBER, name VARCHAR2(30));
Table created.
SQL> INSERT INTO test_1 SELECT rownum, ‘BOWIE’ FROM dual CONNECT BY level <= 10000;
10000 rows created.
SQL> COMMIT;
Commit complete.
SQL> CREATE INDEX test_i ON test_1(id);
Index created.
Next delete most of the rows to so that we have a number of index blocks that contain nothing but deleted index entries which can potentially be subsequently recycled:
SQL> DELETE test_1 WHERE id between 1 and 9990;
9990 rows deleted.
SQL> COMMIT;
Commit complete.
SQL> ANALYZE INDEX test_i VALIDATE STRUCTURE;
Index analyzed.
SQL> SELECT lf_blks, del_lf_rows FROM index_stats;
LF_BLKS DEL_LF_ROWS ------- ----------- 21 9990
If we now run a select statement that forces the use of the index and which only returns the 10 remaining rows, the high number of logical I/Os highlights how Oracle still accesses the now emptied index blocks during the index range scan operation:
SQL> SELECT /*+ index (test_1) */ * FROM test_1
WHERE id BETWEEN 1 and 10000;
Execution Plan -------------------------------------------- | Id| Operation | Name | -------------------------------------------- | 0| SELECT STATEMENT | | | 1| TABLE ACCESS BY INDEX ROWID| TEST_1 | |* 2| INDEX RANGE SCAN | TEST_I | -------------------------------------------- Statistics ---------------------------------------------- 0 recursive calls 0 db block gets 25 consistent gets 0 physical reads 0 redo size 577 bytes sent via SQL*Net to client 396 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 10 rows processed
If there’s a significant time lag between index blocks being emptied and subsequently recycled, there could be some scenarios where accessing many of these effectively empty index blocks can be problematic.
Classic case is when we’re interested in the minimum value of a specific column. Oracle can potentially use an index to navigate to the first index leaf block in the index structure to quickly find the minimum value of an indexed column. However, if the first or left-most index leaf block contains no current index entries, Oracle will be forced to navigate to the next leaf block and if it’s also empty, to the next leaf block and so on until it finally comes across the first non-deleted index entry.
Similar demo, but this time lets make the table somewhat bigger for effect:
SQL> CREATE TABLE ziggy (id NUMBER, value VARCHAR2(30)) ;
Table created.
SQL> INSERT INTO ziggy SELECT rownum, ‘BOWIE’ FROM dual CONNECT BY level <=1000000;
1000000 rows created.
SQL> COMMIT;
Commit complete.
SQL> CREATE INDEX ziggy_i ON ziggy(id);
Index created.
Let’s now deleted 1/2 the index entries, all those on the left-hand side of the index structure:
SQL> DELETE ziggy WHERE id <=500000;
500000 rows deleted.
SQL> COMMIT;
Commit complete.
SQL> exec dbms_stats.gather_table_stats(ownname=>’BOWIE’, tabname=>’ZIGGY’, estimate_percent=> null, cascade=> true, method_opt=>’FOR ALL COLUMNS SIZE 1′);
PL/SQL procedure successfully completed.
We now run the following simply little SELECT statement featuring the MIN function:
SQL> SELECT MIN(id) FROM ziggy;
MIN(ID) ------- 500001 Execution Plan -------------------------------------------- | Id| Operation | Name | -------------------------------------------- | 0| SELECT STATEMENT | | | 1| SORT AGGREGATE | | | 2| INDEX FULL SCAN (MIN/MAX)| ZIGGY_I | -------------------------------------------- Statistics 0 recursive calls 0 db block gets 1115 consistent gets 0 physical reads 0 redo size 412 bytes sent via SQL*Net to client 396 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
Notice the high numbers of consistent gets, 1115 !! Wow.
Until the empty index blocks get recycled, all those effectively empty index blocks will make this MIN select statement run poorly. Classic example where an index rebuild (or coalesce or shrink) would be benefical to improve the performance of this specific query.
SQL> ALTER INDEX ziggy_i REBUILD ONLINE;
Index altered.
SQL> SELECT MIN(id) FROM ziggy;
MIN(ID) ------- 500001 Execution Plan ------------------------------------------- | Id| Operation | Name | ------------------------------------------- | 0| SELECT STATEMENT | | | 1| SORT AGGREGATE | | | 2| INDEX FULL SCAN (MIN/MAX)| ZIGGY_I| ------------------------------------------- Statistics 0 recursive calls 0 db block gets 3 consistent gets 0 physical reads 0 redo size 412 bytes sent via SQL*Net to client 396 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
Now the consistent gets have dramatically reduced from 1115 to just 3 making the select statement run noticeably faster.
Yes, index blocks that contain nothing but deleted index entries are placed on the index freelist and can be recycled by Oracle but until they’ve been reused, they remain linked in place in the index structure. In some scenarios, these empty index blocks can be problematic and is a classic example of when an index rebuild might be beneficial.
Deleted Index Entries Part IV (Breaking Glass) June 25, 2008
Posted by Richard Foote in Index Delete Operations, Oracle General, Oracle Indexes, Oracle Myths.6 comments
Yet another method of cleaning out deleted space Oracle has up its sleeve is the recycling of index blocks that contain nothing but deleted index entries.
In some cases, it’s possible for an index block to contain no current index entries with all the corresponding index entries within the index block having been deleted. The index block may be totally empty of index entries or it may contain just deleted index entries.
Once an index block has no current index entries, Oracle places the block on the segment freelist and is now a candidate block to be recycled and reused elsewhere within the index structure after a subsequent index block split operation.
When recycled, the index block becomes “unattached” from its current location within the logical index structure and is reallocated elsewhere within the logical index structure as the new index block in an index block split operation.
Any previously deleted index entries are removed and the contents of the index block are replaced with new index entries associated with its new logical location within the index structure.
A simple little demo to illustrate this process.
First, I create a simple table and associated index and populate it with a 10000 rows:
SQL> CREATE TABLE test_empty_block (id NUMBER, name VARCHAR2(30));
Table created.
SQL> INSERT INTO test_empty_block SELECT rownum, ‘BOWIE’ FROM dual
CONNECT BY level <= 10000;
10000 rows created.
SQL> COMMIT;
SQL> CREATE INDEX test_empty_block_idx ON test_empty_block(id);
Index created.
I next delete the vast majority of the rows, leaving only a handful behind that are likely only found in the last one or maybe two leaf blocks within the index. All the other index leaf blocks therefore only contain nothing but deleted index entries:
SQL> DELETE test_empty_block WHERE id between 1 and 9990;
9990 rows deleted.
SQL> COMMIT;
Commit complete.
If we look at some statistics, we’ll find we have lots of deleted row entries that are all found in leaf blocks that are totally empty, except perhaps the right most leaf block within the index:
SQL> ANALYZE INDEX test_empty_block_idx VALIDATE STRUCTURE;
Index analyzed.
SQL> SELECT lf_blks, del_lf_rows FROM index_stats;
LF_BLKS DEL_LF_ROWS ------- ----------- 21 9990
We next insert a bunch of new rows into the table, but importantly, all these new rows have index entry values that are greater than the previous values. Therefore, all these new index entries will be inserted into the right most side of the index structure and not into the index where we have nothing but the previously deleted index entries.
Oracle will need to allocate new index leaf blocks to accommodate these new index entries, but from where will Oracle get these new index blocks ?
SQL> INSERT INTO test_empty_block SELECT rownum+20000, ‘ZIGGY’
FROM dual CONNECT BY level <= 10000;
10000 rows created.
SQL> COMMIT;
Commit complete.
If we now look at the index statistics, we notice something very interesting:
SQL> ANALYZE INDEX test_empty_block_idx VALIDATE STRUCTURE;
Index analyzed.
SQL> SELECT lf_blks, del_lf_rows FROM index_stats;
LF_BLKS DEL_LF_ROWS ------- ----------- 21 0
The number of deleted leaf entries has disappeared back to zero and the number of allocated leaf blocks has remained the same at 21.
Oracle has both removed the previously deleted index entries and has also recycled all the previously empty leaf blocks and reused them again to accommodate the new index entries. The index is effectively the same size as it was previously even though we’ve added new values that were greater than the previously deleted values.
So index blocks that are totally empty or contain nothing but deleted index entries become “free” again, are placed on the freelist within the index segment and can be reused or recycled again somewhere else within the logical index structure at some later point in time.
Again, yet another example of Oracle cleaning out these unwanted deleted index entries for us.
However, these empty index blocks can potentially be problematic and can cause performance issues until eventually they actually get reused and recycled.
But that’s a topic for another day.
Deleted Index Entries Part II (V-2 Schneider) June 18, 2008
Posted by Richard Foote in Index Delete Operations, Oracle Indexes.17 comments
I’m back !!
In Part I, we looked at how index entries are only marked as deleted during delete/update operations. This means the DML operation and resultant transaction doesn’t have to concern itself with physically cleaning out the deleted entries.
The question is therefore, are these deleted index entries “deadwood”, wasted space that makes the index less compact and less efficient over time requiring a potential periodic index rebuild, or can this deleted space be subsequently reused by Oracle ?
There’s a common perception that deleted space within an index is indeed “deadwood” or can only be reused in very specific circumstances, such as when an identical index value is subsequently re-inserted.
However, this is just another of Oracle’s infamous urban myths. In the vast majority of cases, deleted space within an index can indeed be recycled and can indeed be subsequently reused. Today, I’m just going to begin by focusing on the most common method by which deleted index entries are generally cleaned up.
All it takes to clean out all the deleted index entries within a specific index leaf block is a subsequent insert in the block. That’s it, that’s all it takes. Just one new index entry in a leaf block will automatically clean out all associated deleted index entries that may currently exist within the block. Even if there are hundreds of deleted entries with the leaf block, just the one new index entry will clean them all out. Note the new index value doesn’t have to be the same as any of the deleted index entries, it doesn’t even have to be within the range of any of the deleted index entries within the block. Any new index entry within the leaf block will do the job quite nicely.
A very simple demonstration I use to highlight this point. First, create a simple table and associated index with 10 rows.
SQL> CREATE TABLE del_stuff (id NUMBER, name VARCHAR2(30));
Table created.
SQL> CREATE INDEX del_stuff_i ON del_stuff(id);
Index created.
SQL> INSERT INTO del_stuff SELECT rownum, ‘Bowie’ FROM dual CONNECT BY level <=10;
10 rows created.
SQL> COMMIT;
Commit complete.
Next, deleted say 4 of the rows from the table.
SQL> DELETE del_stuff WHERE id in (2,4,6,8);
4 rows deleted.
SQL> COMMIT;
Commit complete.
A view of INDEX_STATS after a ANALYZE … VALIDATE STRUCTURE command will show the following:
LF_ROWS DEL_LF_ROWS DEL_LF_ROWS_LEN U_SPACE ------- ----------- --------------- ------- 10 4 56 140
Note: It clearly shows 4 index entries are indeed currently marked as deleted.
A treedump will show the following:
—– begin tree dump
leaf: 0x1402e3a 20983354 (0: nrow: 10 rrow: 6)
—– end tree dump
Note: It only shows 6 rrow but 10 nrow values, clearly suggesting there are currently 4 deleted index entries.
A partial index block dump will show the following:
kdxlende 4
row#1[7928] flag: —D–,lock: 2, len=12
col 0; len 2; (2): c1 03
col 1; len 6; (6): 01 40 2e 32 00 01
That indeed there are currently 4 index entries marked as deleted with the ‘D’ flag within the index block.
However, just a single subsequent insert will clean out all 4 of these deleted index entries. Note the new value (100) is not the same value as any of the previously deleted entries and is not even within the range of previously deleted index entries:
SQL> INSERT INTO del_stuff VALUES (100, ‘New Row’);
1 row created.
SQL> COMMIT;
Commit complete.
Now, if we look at the same leaf block, we find:
LF_ROWS DEL_LF_ROWS DEL_LF_ROWS_LEN U_SPACE ------- ----------- --------------- ------- 7 0 0 98
That indeed, INDEX_STATS no longer has any statistics of deleted index entries.
—– begin tree dump
leaf: 0x1402e3a 20983354 (0: nrow: 7 rrow: 7)
—– end tree dump
That the Index Tree dump no longer has any record of the deleted index entries.
That the index block dump no longer has any record of the deleted index entries and that:
kdxlende 0
the count of deleted index entries within the block has been reset to 0.
For most randomly inserted indexes, this means deleted entries will be eventually automatically cleaned out and the freed space reused by subsequent insert operations. Worrying about deleted space and rebuilding such indexes is typically unnecessarily.
I’ll next discuss another method by which Oracle will clean out and remove deleted index entries from its indexes …
Deleted Index Entries – Part I (Let It Be) June 8, 2008
Posted by Richard Foote in Index Delete Operations, Oracle General, Oracle Indexes, Oracle Myths.9 comments
Just before I hop on a plane to do some training in Europe, thought I might begin a little series on deleted space within an index. I’ll begin with a short piece on how we can determine what deleted space an index may currently have before beginning a discussion on whether this deleted space can indeed be reused by Oracle.
Generally speaking, when an index entry is deleted, Oracle doesn’t physically remove the index entry, it’s simply marked as deleted. It’s another case of Oracle putting off what could be an expensive operation for the current transaction and leaving any potential clean up operations to future processes. However, there’s often some confusion whether these deleted index entries remain “deadwood” within the index structure or whether they are somehow cleaned out later and the space potentially reused by subsequent inserts in the relevant index block.
To set the scene, we begin by creating a very simple scenario. Here we create a little table and associated index, insert a single row, commit it and then delete and commit the row afterwards. We can then have a bit of a look around to see how this deleted index entry is recorded by Oracle.
SQL> CREATE TABLE test_delete (id NUMBER, name VARCHAR2(10));
Table created.
SQL> CREATE INDEX test_delete_idx ON test_delete (name);
Index created.
SQL> INSERT INTO test_delete VALUES (1, ‘BOWIE’);
1 row created.
SQL> COMMIT;
Commit complete.
SQL> DELETE test_delete WHERE id = 1;
1 row deleted.
SQL> COMMIT;
Commit complete.
We begin by looking at statistics related to the deleted index entries within the INDEX_STATS view.
SQL> ANALYZE INDEX test_delete_idx VALIDATE STRUCTURE;
Index analyzed.
SQL> SELECT lf_rows, del_lf_rows, del_lf_rows_len FROM index_stats;
LF_ROWS DEL_LF_ROWS DEL_LF_ROWS_LEN ---------- ----------- --------------- 1 1 17
So yes, the one and only index entry is a deleted index entry.
We can also see how many current deleted entries we have by looking at an index tree dump of the index.
SQL> SELECT object_id FROM dba_objects WHERE object_name = ‘TEST_DELETE_IDX’;
OBJECT_ID ---------- 61198
SQL> ALTER SESSION SET EVENTS ‘immediate trace name treedump level 61198‘;
Session altered.
Following is the index tree dump generated by the above operation.
—– begin tree dump
leaf: 0x14008d2 20973778 (0: nrow: 1 rrow: 0)
—– end tree dump
We notice that the rrow count which is the number of non-deleted index row entries is 0 but the nrow count which is the total index row entries, including deleted entries is 1. Therefore, yes the index currently consists of just the one deleted index row entry.
We can also view the deleted index details by performing a dump of the associated index block.
SQL> SELECT file_id,block_id FROM dba_extents WHERE segment_name=’TEST_DELETE_IDX’;
FILE_ID BLOCK_ID ---------- ---------- 5 2257
SQL> ALTER SYSTEM DUMP DATAFILE 5 BLOCK 2257;
System altered.
Below is an extract from the above index block dump:
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0000.000.00000000 0x00000000.0000.00 —- 0 fsc 0x0000.00000000
0x02 0x0008.024.0000075b 0x00804e29.0078.0b –U- 1 fsc 0x0011.00000000
……
kdxlende 1
kdxlenxt 0=0x0
kdxleprv 0=0x0
kdxledsz 0
kdxlebksz 8036
row#0[8021] flag: —D–, lock: 2, len=15
col 0; len 5; (5): 42 4f 57 49 45
col 1; len 6; (6): 01 40 10 0a 00 00
From the above, kdxlende 1 is a count of the deleted index entries. The index entry has a D flag set, signifying that the index entry has been deleted. Also note that the index entry was locked and deleted by the ITL entry associated with ITL number 2.
So yes, when we perform a delete that results in the deletion of an index row entry, the deleted index entry is marked as deleted but is not physically cleaned out at the time of the delete. All the above checks confirm this current state of the index.
The key question is therefore, are these deleted index entries ever reused/removed, or are they forever “deadwood” that would require a periodic rebuild of the indexes to clean out ?
Answer coming soon …
Richard Foote's Oracle Blog 