Oracle Scratchpad

December 17, 2013

dbms_space usage

Filed under: ASSM,Indexing,Infrastructure,Oracle — Jonathan Lewis @ 6:43 pm GMT Dec 17,2013

Just throwing out a brief comment (one of my many draft notes that I don’t have time to complete) about the dbms_space package. You’re probably familiar with this package and how, for ASSM segments, it can give you an indication of the available space in the blocks in a data segment, reporting 6 possible states of the blocks below the “high highwater mark” (HHWM) e.g.:

Unformatted                   :       12
Freespace 1 (  0 -  25% free) :        0
Freespace 2 ( 25 -  50% free) :       14
Freespace 3 ( 50 -  75% free) :        0
Freespace 4 ( 75 - 100% free) :        0
Full                          :       34

The above is the output from a little procedure I wrote to wrap around a call to the procedure dbms_space.space_usage().

But, in the context of this package, what does it mean when you look at indexes to talk about blocks having “free space”? On one hand you might be trying to get a rough idea of how many blocks would be used if you were to rebuild the index. On the other hand there are only two (arguably 3) interesting current states for index blocks: “has current index entries” or “has no current index entries” (the third state would be unformatted). The implications of this came up in a conversation on Oracle-L : Oracle doesn’t actually use this procedure to tell you about the percentage of free space in the index blocks, it reports just those two (three) states.

The sample output above comes from an index where I’ve been inserting and deleting rows both individually and in batches from a couple of different sessions – carefully counting index entries in blocks – after rebuilding the index once. As you can see the package reports only 3 possible states: unformatted, FS2, and Full.

Following the conversation on Oracle-l I had thought that FS2 probably meant “empty and available to be used elsewhere in the index” but this turned out to be wrong *** – at present I have no idea when Oracle decides that a block has become full, or when it decides that it’s no longer full; all I can say from the above report is that this index has 48 index leaf blocks below the HHWM, and at some time 34 of them have been “full”.

When I generated this report 19 of the blocks were in their original state (from when I had done an index rebuild – and that’s a case where index blocks do become full), some were completely empty, some were at 30% of the notional “Full” state, some were at 66% of “Full”.

Further investigations are left as an exercise for enthusiastic readers.

*** Footnote (Aug 2022)

I am now reasonably certain that the “Full / FS2 / unformatted” flags are the only states available to index blocks under normal (insert / update / delete) procssesing. It’s likely that there were three factors involved in my tests of 2013 that fooled me: first that I didn’t allow for concurrency and delayed logging block cleanout effects, secondly that I simply misread the results, and thirdly that I believe there have been tweaks over time regarding the point at which the space management bitmap blocks were updated.

I should also add that FS2 doesn’t just mean “all the entries have been deleted”, it also covers “formatted but not yet used”. Remember that ASSM tends to format batches of blocks (often 16 or multiples thereof on 16th-block “boundaries”) below the high highwater mark, so you can have lots of empty formatted blocks, as well as lots of unformatted blocks below the HHWM. There is no obvious way to decide whether an FS2 block is simply “newly formatted” or “has been used and is now empty” (in the older versions of Oracle at least).

I’ve updated this note because I’ve now found index leaf blocks at FS4 (nominally 75% to 100% free) after “alter index shrink space” – I’m still looking into it, but it may offer a partial answer to the question of whether this an empty block that is (a) newly formatted, (b) in the index structure or (c) previously used but taken out of the index structure.



  1. Jonathan,

    maybe the following steps don’t add much, but perhaps the next enthusiastic reader can reuse parts of the test setup:

    -- ASSM
    -- creation of a table with a (non-unique) index wasting a lot of space
    -- by creating rows and deleting most of them (but not all) immediately
    drop table t;
    create table t (a number);
    create index t_idx on t(a);
    for i in 1..1000 loop
    insert into t(a) 
    select i * 100 + rownum
      from dual
    connect by level <= 100  
    delete from t
     where a > i * 100
       and mod(a, 100) <> 1;
    end loop;
      unf number;
      unfb number;
      fs1 number;
      fs1b number;
      fs2 number;
      fs2b number;
      fs3 number;
      fs3b number;
      fs4 number;
      fs4b number;
      full number;
      fullb number;
      dbms_output.put_line('unformatted blocks/bytes: ' || unf || ' / ' || unfb);
      dbms_output.put_line('fs1 blocks/bytes: ' || fs1 || ' / ' || fs1b);
      dbms_output.put_line('fs2 blocks/bytes: ' || fs2 || ' / ' || fs2b);
      dbms_output.put_line('fs3 blocks/bytes: ' || fs3 || ' / ' || fs3b);
      dbms_output.put_line('fs4 blocks/bytes: ' || fs4 || ' / ' || fs4b);
      dbms_output.put_line('full blocks/bytes: ' || full || ' / ' || fullb);
    unformatted blocks/bytes: 0 / 0
    fs1 blocks/bytes: 0 / 0
    fs2 blocks/bytes: 45 / 368640
    fs3 blocks/bytes: 0 / 0
    fs4 blocks/bytes: 0 / 0
    full blocks/bytes: 199 / 1630208
    analyze index t_idx validate structure;
    select height, blocks, lf_blks, br_blks, btree_space, pct_used, del_lf_rows
      from index_stats;
    ---------- ---------- ---------- ---------- ----------- ---------- -----------
             2        256        199          1     1600032        100       99000
    -- create a tree dump
    select object_id from user_objects where object_name = 'T_IDX';
    ALTER SESSION SET EVENTS 'immediate trace name treedump level 89087';
    -- snippet from the tree dump
    ----- begin tree dump
    *** 2013-12-18 08:23:11.242
    branch: 0x1007a73 16808563 (0: nrow: 199, level: 1)
       leaf: 0x1007a75 16808565 (-1: nrow: 533 rrow: 6)
       leaf: 0x1007a76 16808566 (0: nrow: 533 rrow: 5)
       leaf: 0x1007a77 16808567 (1: nrow: 533 rrow: 5)
       leaf: 0x1007a74 16808564 (2: nrow: 533 rrow: 6)
       leaf: 0x1007a7d 16808573 (3: nrow: 533 rrow: 5)
       leaf: 0x1007a7e 16808574 (4: nrow: 533 rrow: 5)
       leaf: 0x1007a7f 16808575 (5: nrow: 533 rrow: 6)
       leaf: 0x1007e8c 16809612 (193: nrow: 500 rrow: 5)
       leaf: 0x1007e90 16809616 (194: nrow: 500 rrow: 5)
       leaf: 0x1007e94 16809620 (195: nrow: 500 rrow: 5)
       leaf: 0x1007e98 16809624 (196: nrow: 500 rrow: 5)
       leaf: 0x1007e9c 16809628 (197: nrow: 387 rrow: 3)
    ----- end tree dump

    So the procedure tells me that there are 199 full blocks and 45 blocks in fs2 state – but the analyze command only reports 199 leaf blocks and the tree dump agrees with this (also showing that the blocks are almost empty – as expected – with ~ 500 index entries and ~ 5 entries that are not deleted).

    Regarding my load and delete pattern I would assume that all the generated index entries should have been made in the 199 (full) blocks. So what I can say about the fs2 blocks? Nothing. They are not unformatted but obviously also not used.


    Comment by Martin Preiss — December 18, 2013 @ 7:55 am GMT Dec 18,2013 | Reply

    • in addition some information from the segment header:

      -- dump creation
      select header_file, header_block from dba_segments where segment_name = 'T_IDX';
      ----------- ------------
                4        31346
      alter system dump datafile 4 block 31346;
      -- and some information from the trace file
        Extent Control Header
        Extent Header:: spare1: 0      spare2: 0      #extents: 17     #blocks: 256   
                        last map  0x00000000  #maps: 0      offset: 2716  
            Highwater::  0x01007f00  ext#: 16     blk#: 128    ext size: 128   
        #blocks in seg. hdr's freelists: 0     
        #blocks below: 244   
        mapblk  0x00000000  offset: 16    
        Low HighWater Mark : 
            Highwater::  0x01007e90  ext#: 16     blk#: 16     ext size: 128   
        #blocks in seg. hdr's freelists: 0     
        #blocks below: 132   
        mapblk  0x00000000  offset: 16    
        Level 1 BMB for High HWM block: 0x01007e81
        Level 1 BMB for Low HWM block: 0x01007e80

      So we have 244 blocks below the HWM and that corresponds with the information from dbms_space.space_usage. So in this case fs2 seems to mean formatted but unused blocks in the index structure.

      Comment by Martin Preiss — December 18, 2013 @ 2:14 pm GMT Dec 18,2013 | Reply

      • Martin,

        Thanks for the contribution –

        “So in this case fs2 seems to mean formatted but unused blocks in the index structure.”

        This was the initial conclusion I came to after the discussion on Oracle-l, and doing some single-session tests – but the hypothesis failed when I started doing some concurrency tests, perhaps due to transient simultaneous uncommitted transactions on the same index leaf blocks.

        Comment by Jonathan Lewis — December 19, 2013 @ 7:49 am GMT Dec 19,2013 | Reply

        • concurrency makes everything so much more complicated. I advocate using databases only in single user mode …

          Comment by Martin Preiss — December 19, 2013 @ 9:20 am GMT Dec 19,2013

        • Somewhere I’ve got my list of rules on how to test a feature – starting with small single user to large highly concurrent. Not only does it get harder to understand what’s happening, it gets harder to deign suitable tests.

          Comment by Jonathan Lewis — December 22, 2013 @ 9:18 am GMT Dec 22,2013

  2. […] a fairly well-known package called dbms_space that can give you a fairly good idea of the space used by a segment stored in a tablespace […]

    Pingback by LOB Space | Oracle Scratchpad — January 21, 2015 @ 1:27 pm GMT Jan 21,2015 | Reply

  3. […] Hakan Factor: A procedure – owned by SYS – that reports the Hakan factor for an object. Segment Space: A script to report on space usage within generai segment types Securefile Space: A variant on the […]

    Pingback by Simple scripts | Oracle Scratchpad — September 15, 2016 @ 9:18 am BST Sep 15,2016 | Reply

  4. […] As you can see Oracle has now set leaf_blocks = 1555. This is actually the number of blocks below the highwater mark in segment. A further check with the dbms_space and dbms_space_usage packages showed that the number of blocks in the index structure was actually 1,523 with a further 32 blocks that we could infer were space management blocks. Of the 1,523 blocks in the index structure 157 were reported as “FULL” while 1364 were reported as FS2 which possibly ought to mean  “available for re-use” (though still in the structure), although this didn’t quite seem to be the case a few years ago. […]

    Pingback by Index FFS Cost 2 | Oracle Scratchpad — October 6, 2020 @ 12:58 pm BST Oct 6,2020 | Reply

  5. […] get a complete picture we’ll have to do some work with the dbms_space package, the index treedump command, dumping redo, and we also ought to take a look at v$rollstat […]

    Pingback by drop partition | Oracle Scratchpad — August 5, 2022 @ 8:24 pm BST Aug 5,2022 | Reply

  6. […] I published a few years back; the relevance of the numbers when applied to indexes is desribed in this blog note and shows the […]

    Pingback by Shrinking indexes | Oracle Scratchpad — September 2, 2022 @ 7:22 pm BST Sep 2,2022 | Reply

RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Website Powered by

%d bloggers like this: