By Franck Pachot
.
I have recently published a script to check index fragmentation. But then, do you COALESCE or REBUILD? Well, there also is another option: ALTER INDEX SHRINK SPACE. Let’s compare all those index defragmentation operations.
Fragmented index
I have an index created when the table had 1 million rows. Then, I deleted 90% of the rows. Here is the index state from:
- dbms_space.space_usage
- index_stats after an analyze index validate structure
- my index fragmentation checking script with 4 buckets
and here are the results:
:UNF :FS1 :FS2 :FS3 :FS4 :FULL BLOCKS
---------- ---------- ---------- ---------- ---------- ---------- -------
0 0 1 0 0 2230 2304
HEIGHT BLOCKS LF_ROWS LF_BLKS BR_ROWS BR_BLKS DEL_LF_ROWS USED_SPACE PCT_USED
------- ------- ---------- -------- -------- -------- ----------- ---------- --------
3 2304 100404 2226 2225 5 404 1622013 10
N to N rows/block bytes/block %free space blocks free
---------- -- ---------- ---------- ----------- ----------- ------- -----
10 -> 250280 45 714 91 557 oooo
250730 -> 500370 45 714 91 557 oooo
500820 -> 750010 45 714 91 556 oooo
750460 -> 999660 45 714 91 556 oooo
I have 2226 leaf blocks, the index height is 3 with 5 branch blocks. The leaves are only 91.
However dbms_space shows only full blocks: the deleted entries are still there, just marked as deleted, and the blocks are still seen as full.
COALESCE
Now let’s COALESCE:
SQL> alter index DEMO_N coalesce;
And before checking the same values about the index space I measure the amount of work that has been done by the operation (from v$mystat):
NAME VALUE
---------------------------------------------------------------- ----------
redo entries 22067
db block gets 32818
session logical reads 32886
db block changes 40601
undo change vector size 35199264
redo size 47878800
:UNF :FS1 :FS2 :FS3 :FS4 :FULL BLOCKS
---------- ---------- ---------- ---------- ---------- ---------- -------
0 0 2004 0 0 227 2304
HEIGHT BLOCKS LF_ROWS LF_BLKS BR_ROWS BR_BLKS DEL_LF_ROWS USED_SPACE PCT_USED
------- ------- ---------- -------- -------- -------- ----------- ---------- --------
3 2304 100000 223 222 5 0 1591530 88
N to N rows/block bytes/block %free space blocks free
---------- -- ---------- ---------- ----------- ----------- ------- -----
10 -> 248690 452 7170 11 56
253200 -> 500800 450 7158 11 56
505310 -> 752020 449 7132 11 56
756530 -> 998730 443 7038 12 55
COALESCE is an online operation that defragments the leaf blocks. We have now only 223 leaf blocks that are 90% full (because my pctfree is the default 10%). But the index stil has the same height and still has 2300 blocks. Where are the reclaimed 2000 blocks? They are available when the index needs a new block (for a block split). They are seen as FS2 (at least 25 to 50% free space) by dbms_space because they still contain the deleted rows, and haven’t been touched but only unlinked from the B*Tree structure (parent branch + prev/next leaf), but space is available when needed by the index.
SHRINK SPACE COMPACT
Back with the same table, and doing a SHRINK SPACE COMPACT instead of COALESCE:
NAME VALUE
---------------------------------------------------------------- ----------
redo entries 28794
db block gets 40940
session logical reads 41527
db block changes 49387
undo change vector size 36990460
redo size 51848880
:UNF :FS1 :FS2 :FS3 :FS4 :FULL BLOCKS
---------- ---------- ---------- ---------- ---------- ---------- -------
0 0 1 0 2003 227 2304
HEIGHT BLOCKS LF_ROWS LF_BLKS BR_ROWS BR_BLKS DEL_LF_ROWS USED_SPACE PCT_USED
------- ------- ---------- -------- -------- -------- ----------- ---------- --------
3 2304 100000 223 222 5 0 1591530 88
N to N rows/block bytes/block %free space blocks free
---------- -- ---------- ---------- ----------- ----------- ------- -----
10 -> 248690 452 7170 11 56
253200 -> 500800 450 7158 11 56
505310 -> 752020 449 7132 11 56
756530 -> 998730 443 7038 12 55
So what is the difference? Slightly more work (about 20% more logical reads and block changes) for the same result. Except that now the reclaimed blocks are in FS4 (75 to 100% free space). Actually, what is different from COALESCE is that those blocks were not only unlinked from the B*Tree structure but also moved out of the end of the segment (to COMPACT it) with the goal to reduce the segment size with SHRINK SPACE. More info on Richard Foote blog.
SHRINK SPACE
What if we use SHRINK SPACE instead of SHRINK SPACE COMPACT?
NAME VALUE
---------------------------------------------------------------- ----------
redo entries 29352
db block gets 45496
session logical reads 46190
db block changes 50032
undo change vector size 36981524
redo size 51901500
:UNF :FS1 :FS2 :FS3 :FS4 :FULL BLOCKS
---------- ---------- ---------- ---------- ---------- ---------- -------
0 0 1 0 0 227 240
HEIGHT BLOCKS LF_ROWS LF_BLKS BR_ROWS BR_BLKS DEL_LF_ROWS USED_SPACE PCT_USED
------- ------- ---------- -------- -------- -------- ----------- ---------- --------
3 240 100000 223 222 5 0 1591530 88
N to N rows/block bytes/block %free space blocks free
---------- -- ---------- ---------- ----------- ----------- ------- -----
10 -> 248690 452 7170 11 56
253200 -> 500800 450 7158 11 56
505310 -> 752020 449 7132 11 56
756530 -> 998730 443 7038 12 55
With tables, theSHRINK SPACE lowers the high water mark. Here it is the same idea: in addition to the SHRINK SPACE COMPACT operation, the reclaimed blocks are removed from the end of the segment, not allocated anymore to the index, and can be used for other segments in the tablespace. We see that from dbms_space: the index is now 240 blocks only.
REBUILD
The previous requires a lock only for a short duration (according that we did the shrink space compact before). The rebuild needs a Share lock on the table during the whole operation, blocking concurrent DML.
NAME VALUE
---------------------------------------------------------------- ----------
db block gets 953
redo entries 1832
db block changes 1906
session logical reads 4019
undo change vector size 9152
redo size 173732
:UNF :FS1 :FS2 :FS3 :FS4 :FULL BLOCKS
---------- ---------- ---------- ---------- ---------- ---------- -------
0 0 1 0 0 222 256
HEIGHT BLOCKS LF_ROWS LF_BLKS BR_ROWS BR_BLKS DEL_LF_ROWS USED_SPACE PCT_USED
------- ------- ---------- -------- -------- -------- ----------- ---------- --------
2 256 100000 222 221 1 0 1591520 90
N to N rows/block bytes/block %free space blocks free
---------- -- ---------- ---------- ----------- ----------- ------- -----
10 -> 248690 452 7170 11 56
253200 -> 501250 451 7170 11 56
505760 -> 749300 451 7170 11 55
753810 -> 997350 448 7117 11 55
The result is the same as the previous operation (SHRINK SPACE) except that the index height has decreased. A rebuild is the right operation if the index blevel has become too high. And we did that offline but with much less work. Minimal undo and redo. And small blocks to read (when the index is still usable the rebuild can use the current index to rebuild the new segment).
REBUILD ONLINE
Last operation, possible only in Enterprise Edition, is the rebuild online which doesn’t need to lock the table.
NAME VALUE
---------------------------------------------------------------- ----------
redo entries 660
db block changes 876
db block gets 1419
session logical reads 4989
undo change vector size 24932
redo size 114924
:UNF :FS1 :FS2 :FS3 :FS4 :FULL BLOCKS
---------- ---------- ---------- ---------- ---------- ---------- -------
0 0 1 0 0 222 256
HEIGHT BLOCKS LF_ROWS LF_BLKS BR_ROWS BR_BLKS DEL_LF_ROWS USED_SPACE PCT_USED
------- ------- ---------- -------- -------- -------- ----------- ---------- --------
2 256 100000 222 221 1 0 1591520 90
N to N rows/block bytes/block %free space blocks free
---------- -- ---------- ---------- ----------- ----------- ------- -----
10 -> 248690 452 7170 11 56
253200 -> 501250 451 7170 11 56
505760 -> 749300 451 7170 11 55
753810 -> 997350 448 7117 11 55
Here we don’t see an overhead to do it online. This is because my table is small (my testcase has only one column which is the indexed one). On a real table you will probably see that the online rebuild takes longer than the offline one, because it cannot use the current index. But anyway, the fact that it is online means that the duration is not a big issue.
Conclusion
This is an example on a table that had a massive purge: all blocks were touched. In that case the REBUILD is the right solution. However if you are in Standard Edition and cannot do it online, then you will probably do a SHRINK SPACE COMPACT because it lets you do a SHRINK SPACE (need a quick locks but for a very short duration – to do on a period of low activity).
COALESCE will make sense here only if you know you will insert back a lot of rows, so that you don’t need to deallocate the blocks from the index.
Now, what to do if the free space to reclaim is only on small part of the index blocks? As in the following case:
N to N rows/block bytes/block %free space blocks free
---------- -- ---------- ---------- ----------- ----------- ------- -----
10 -> 50468 374 5974 26 112 o
50917 -> 100756 449 7179 11 112
101205 -> 151044 449 7179 11 112
151493 -> 201332 449 7179 11 112
201781 -> 251620 449 7179 11 112
252069 -> 301908 449 7179 11 112
302357 -> 351747 449 7179 11 111
352196 -> 401586 449 7179 11 111
402035 -> 451425 449 7179 11 111
451874 -> 501264 449 7179 11 111
501713 -> 551103 449 7179 11 111
In that case a COALESCE is probably the best because you keep the blocks allocated to the index for future growth. And the few blocks reclaimed will probably not change the index height anyway. However, if you did a small purge that concern only a small part of the index and want to reclaim the space for other segments, then the SHRINK SPACE is the right solution. But do you really need to reclaim space in that case?
Now you see the usage for my index fragmentation script: I don’t need only the average free space. I need to have a clear picture of the fragmentation in order to decide what to do and how.
Update Jan. 23th 2015
There is something else important in the INDEX SHRINK vs. COALESCE when there are concurrent DML activity. COALESCE just skip the blocks where some index entries are locked. SHRINK will wait on them.