Defragmentation Can Degrade Query Performance

As data in tables is updated and / or deleted, pockets of empty space can be created, leading to fragmented free space. This free space is wasted space as it is insufficient to hold new data. Moreover, it can impact performance of the database as a higher number of sparsely-populated blocks need to be visited. Oracle 11g introduced online segment

As data in tables is updated and / or deleted, pockets of empty space can be created, leading to fragmented free space. This free space is wasted space as it is insufficient to hold new data. Moreover, it can impact performance of the database as a higher number of sparsely-populated blocks need to be visited. Oracle 11g introduced online segment shrink functionality to defragment and reclaim this space. During this operation, the database:

  • Compacts the segment: Fragmented free space is consolidated by moving the rows to new locations to create empty blocks near the High Water Mark (HWM – a measurement of the maximum number of database blocks a segment has used so far).
  • Adjusts the high water mark so that new free space is available above the HWM. That free space is then deallocated and is made available for use by other segments.
  • Maintains the indexes so that they remain usable after the operation is complete.

Shrinking a sparsely-populated segment improves the performance of Full Table Scans because there are fewer blocks below the HWM. But performance of queries doing Index scans might degrade since the clustering factor of the index can increase as a result of row movement in the table. It is a misconception that rebuilding of index can improve its clustering factor. To improve the index clustering factor, data in the table needs to be reorganized so that rows in the table are in the same order on disk as the index keys.

In this article, I will demonstrate:

  • Compacting of a fragmented table / index results in:
    • Movement rows across data blocks
    • Increased clustering factor of the index
  • Shrinking of a table causes:
    • Adjustment of HWM of table
    • Release of free space above the HWM
  • Shrinking of the index coalesces the space freed on deleting the rows
  • Increased clustering factor of the index causes degradation of queries performing Index Full Scan
  • Rebuilding of the index:
    • Does not affect its clustering factor
    • Improves performance of index access
  • Reorganizing the data in table improves:
    • The clustering factor of the index
    • Performance of table access via the index

Demonstration

  • Create and populate a table called organized, with 400 distinct IDs and 7 rows per ID.

  • Verify that all seven rows for each ID are located in the same block and that each block contains records belonging to one ID only.

  • Create an index on the ID column and gather statistics for the table and index.

  • Find out the HWM of the table:

  • From index statistics verify that:
    • Clustering factor = Number of distinct IDs (400), as all the records for an ID are placed in the same block. Hence while accessing all the records of the table via index, blocks have to be switched 400 times.
    • Information about 2800 rows having 400 distinct keys is spread across 6 leaf blocks.

  • Verify that there are no deleted rows in leaf blocks:

  • Delete one row for every ID to introduce fragmentation:

  • Verify that after one record for every ID has been deleted, each block now contains 6 records for each key, thereby leaving free space for one record.

  • Gather statistics for the table and index:

  • Verify that the HWM for the table remains unchanged:

  • From index statistics verify that:
    • Clustering factor (= number of distinct keys (400)) remains same as earlier as all 6 rows for an ID are still placed in the same block. Hence while accessing all the records of the table via the index, blocks have to switch 400 times.
    • Information about 2400 (instead of 2800 earlier) rows having 400 distinct keys is spread across 6 leaf blocks.

  • Analyze the index and verify that:
    • There are 400 deleted rows in leaf blocks leading to around 14% space wastage.
    • There are 2800 leaf rows as earlier (400 deleted rows are not reflected as they are still occupying space).

  • Execute a query which performs Index Full Scan. Note that:
    • There is a cost of 7 for using the index for the ORGANIZED table, i.e. the query will hit one root block (1) and the 6 leaf blocks (6).
    • The query will be doing 400 more IOs against the table (equal to the clustering factor), because the rows needed are all next to each other on 400 database blocks.
    • Total cost of query = Cost of Index access (7) + Cost of Table access (400) = 407.

  • Let’s compact only the table and index by specifying the SHRINK SPACE COMPACT clause.

  • Let us see how the data has been reorganized in the table after compaction. It can be seen that the rows have been moved in order to fill up the vacant space for one record in each block, so that each block now contains 6 records for one ID and one record of another ID.

  • As a result, records for 57 IDs have been scattered over 6 blocks, with each block having one row of that ID whereas all the 6 rows of the remaining 343 IDs are contained within the same block.

  • Gather statistics for the table and index:

  • Verify that HWM for the table remains the same, since only compaction has taken place:

  • From index statistics, verify that:
    • Clustering factor has increased from 400 to 685
      • All 6 rows for each of the 343 IDs are in the same block – 343 table block switches are needed to access 343 IDs
      • Rows for each of the 57 IDs are spread across 6 blocks, i.e. 57*6 = 342 table block switches are needed to access these 57 IDs
      • To access all the 400 IDs total block switches = 343 + 342 = 685
    • Index has been maintained (status = valid)

  • Analyze the index and verify that after compacting the index, the space freed on deleting the rows has been coalesced and entries for deleted rows have been removed:
    • DEL_LF_ROWS = 0 (400 earlier)
    • LF_ROWS = 2400 (2800 earlier)
    • WASTAGE = 0 (14% earlier)

  • Let us execute the query again and check execution statistics. Note that:
    • IOs to index remain same as earlier, i.e. 7
    • IOs to table have increased from 400 to 685 (equaling the clustering factor of the index)
    • Total cost has increased from 407 to 692

  • Hence it can be deduced that compacting the table and index:
    • Does not move the HWM of the table
    • Coalesces the space freed by deleted rows in the index
    • Can increase CF of the index
    • Can increase the cost of accessing the table via the index
  • Now let’s shrink the table so that the HWM of the table moves from 496 blocks to 343 blocks:

  • Note that index status remains valid and that there is no change to clustering factor or other index statistics:

  • Let’s execute the query again and check execution statistics. Note that:
    • IOs to index remain the same as earlier, i.e. 7
    • IOs to table remain the same as earlier, i.e. 685 (= clustering factor of the index)
    • Total cost remain the same as earlier, i.e. 692

  • Let’s rebuild the index and verify that :
    • Clustering factor remains same as earlier, i.e. 685
    • Number of leaf blocks have dropped from 6 to 5

  • Let’s execute the query again and check execution statistics. Note that:
    • IOs to index have dropped from 7 to 6 as the number of leaf blocks has decreased by 1
    • IOs to table remain same as earlier, i.e. 685 (= clustering factor of the index)
    • Total cost drops by 1, i.e. 692 to 691

Hence we can say that rebuilding the index:

  • Does not alter the clustering factor of the index.
  • May decrease the number of leaf blocks in the index, thereby reducing the cost of index access.
  • In order to improve clustering factor of the index and hence cost of table access, let us sort the data in the in the same order on disk as the index keys. Note that it causes clustering factor for the index to improve from 685 to 343.

  • Let’s execute the query again and check execution statistics. Note that:
    • IOs to index remain the same as earlier, i.e. 6
    • IOs to table drop from 685 to 343 (equalling improved clustering factor of the index)
    • Total cost significantly drops from 692 to 349

Summary

  1. Compacting of a fragmented table / index:
    • Does not move the HWM of the table
    • Causes deleted rows to be removed from the index
    • Can increase CF of the index and hence the cost of accessing the table via the index may increase
  2. Shrinking of a table causes:
    • Adjustment of the HWM of table
    • Release of free space above the HWM
  3. Shrinking of an index coalesces the space freed upon deleting the rows
  4. Rebuilding of index:
    • Does not alter its clustering factor
    • May decrease the number of leaf blocks in the index thereby reducing cost of index access.
  5. After shrinking the table and indexes, to take advantage in terms of performance, you should:
    • Rebuild the indexes
    • Sort the data in the table in the order of the index key to improve the index clustering factor.

References

http://www.oracle.com/technetwork/issue-archive/2012/12-sep/o52asktom-1735913.html

https://richardfoote.wordpress.com/2011/09/25/rebuilding-indexes-and-the-clustering-factor-solution-move-on/