How to measure Oracle index fragmentation

At Oracle Open World 2014, or rather the Oaktable World, Chris Antognini has presented ‘Indexes: Structure, Splits and Free Space Management Internals’. It’s not something new, but it’s still something that is not always well understood: how index space is managed, block splits, fragmentation, coalesce and rebuilds. Kyle Hailey has made a video of it available here. For me, it is the occasion to share the script I use to see if an index is fragmented or not.

First, forget about those ‘analyze index validate structure’ which locks the table, and the DEL_LEAF_ROWS that counts only the deletion flags that are transient. The problem is not the amount of free space. The problem is where is that free space. Because if you will insert again in the same range of values, then that space will be reused. Wasted space occurs only when lot of rows were deleted in a range where you will not insert again. For exemple, when you purge old ORDERS, then the index on the ORDER_DATE – or on the ORDER_ID coming from a sequence – will be affected. Note that the problem occurs only for sparse purges because full blocks are reclaimed when needed and can get rows from an other range of value.

I have a script that shows the number of rows per block, as well as used and free space per block, and aggregates that by range of values.

First, let’s create a table with a date and an index on it:

drop table DEMOTABLE;
create table DEMOTABLE as select sysdate-900+rownum/1000 order_date,decode(mod(rownum,100),0,'N','Y') delivered , dbms_random.string('U',16) cust_id  from (select * from dual connect by level <= 1e4 );
create index DEMOINDEX on DEMOTABLE(ORDER_DATE) pctfree 90;

My script shows 10 buckets with begin and end value and for each of them the averge number of rows per block and the free space:

SQL> @index_fragmentation

ORDER_DAT -> ORDER_DAT rows/block bytes/block %free space     blocks free
--------- -- --------- ---------- ----------- ----------- ---------- -----
24-APR-12 -> 02-AUG-12        377        7163          11        266
03-AUG-12 -> 11-NOV-12        377        7163          11        266
11-NOV-12 -> 19-FEB-13        377        7163          11        266
19-FEB-13 -> 30-MAY-13        377        7163          11        265
30-MAY-13 -> 07-SEP-13        377        7163          11        265
07-SEP-13 -> 16-DEC-13        377        7163          11        265
16-DEC-13 -> 26-MAR-14        377        7163          11        265
26-MAR-14 -> 03-JUL-14        377        7163          11        265
04-JUL-14 -> 11-OCT-14        377        7163          11        265
12-OCT-14 -> 19-JAN-15        376        7150          11        265

Note that the script reads all the table (it can do a sample but here it is 100%). Not exactly the table but only the index. It counts the index leaf blocks with the undocumented function sys_op_lbid() which is used by oracle to estimate the clustering factor.

So here I have no fragmentation. All blocks have about 377 rows and no free space. This is because I inserted them in increasing order and the so colled ’90-10′ block split occured.

Let’s see what I get if I delete most of the rows before the 01-JAN-2014:

SQL> delete from DEMOTABLE where order_dateSQL> @index_fragmentation

ORDER_DAT -> ORDER_DAT rows/block bytes/block %free space     blocks free
--------- -- --------- ---------- ----------- ----------- ---------- -----
25-APR-12 -> 02-AUG-12          4          72          99        266 oooo
03-AUG-12 -> 11-NOV-12          4          72          99        266 oooo
11-NOV-12 -> 19-FEB-13          4          72          99        266 oooo
19-FEB-13 -> 30-MAY-13          4          72          99        265 oooo
30-MAY-13 -> 07-SEP-13          4          72          99        265 oooo
07-SEP-13 -> 16-DEC-13          4          72          99        265 oooo
16-DEC-13 -> 26-MAR-14          4          72          99        265 oooo
26-MAR-14 -> 03-JUL-14          4          72          99        265 oooo
04-JUL-14 -> 11-OCT-14         46         870          89        265 oooo
12-OCT-14 -> 19-JAN-15        376        7150          11        265

I have the same buckets, and same number of blocks. But blocks which are in the range below 01-JAN-2014 have only 4 rows and a lot of free space. This is exactly what I want to detect: I can check if that free space will be reused.

Here I know I will not enter any orders with a date in the past, so those blocks will never have an insert into them. I can reclaim that free space with a COALESCE:

SQL> alter index DEMOINDEX coalesce;
Index altered.
SQL> @index_fragmentation
ORDER_DAT to ORDER_DAT rows/block bytes/block %free space blocks free
 --------- -- --------- ---------- ----------- ----------- ---------- -----
 25-APR-12 -> 03-OCT-14 358 6809 15 32
 03-OCT-14 -> 15-OCT-14 377 7163 11 32
 15-OCT-14 -> 27-OCT-14 377 7163 11 32
 27-OCT-14 -> 08-NOV-14 377 7163 11 32
 08-NOV-14 -> 20-NOV-14 377 7163 11 32
 20-NOV-14 -> 02-DEC-14 377 7163 11 32
 02-DEC-14 -> 14-DEC-14 377 7163 11 32
 14-DEC-14 -> 26-DEC-14 377 7163 11 32
 27-DEC-14 -> 07-JAN-15 377 7163 11 32
 08-JAN-15 -> 19-JAN-15 371 7056 12 32

I still have 10 buckets because this is defined in my script, but each bucket noew has less rows. I’ve defragmented the blocks and reclaimed the free blocks.

Time to share the script now. Here it is: index_fragmentation.zip

The script is quite ugly. It’s SQL generated by PL/SQL. It’s generated because it selects the index columns. And as I don’t want to have it too large it is not indented nor commented. However, if you run it with set servertoutput on you will see the generated query.

How to use it? Just change the owner, table_name, and index name. It reads the whole index so if you have a very large index you may want to change the sample size.