Oracle Scratchpad

December 17, 2013

dbms_space usage

Filed under: ASSM,Indexing,Infrastructure,Oracle — Jonathan Lewis @ 6:43 pm BST 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 a measure of the available space in the blocks in a data segment, reporting 6 possible states of the blocks below the high high water 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 when you look at indexes what does it mean, in the context of this package, to talk about index blocks having free space ? On one hand you might want to get an idea of how many blocks you would need if you rebuilt the index with pctfree set either to zero or the current value; on the other hand there are only two (or maybe 3) interesting states for index blocks – in use or completely empty (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 free space in the index leaf blocks, it only reports three possible states .

The sample output above comes from an index where I’ve been inserting and deleting rows singly 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.

5 Comments »

  1. Jonathan,

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

    -- 11.2.0.1
    -- 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);
    
    begin
    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;
    
    commit;
    
    end;
    /   
    
    declare
      unf number;
      unfb number;
      fs1 number;
      fs1b number;
      fs2 number;
      fs2b number;
      fs3 number;
      fs3b number;
      fs4 number;
      fs4b number;
      full number;
      fullb number;
    begin
      dbms_space.space_usage(user,'T_IDX','INDEX',unf,unfb,fs1,fs1b,fs2,fs2b,fs3,fs3b,fs4,fs4b,full,fullb);
      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);
    end;
    / 
    
    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;
    
        HEIGHT     BLOCKS    LF_BLKS    BR_BLKS BTREE_SPACE   PCT_USED DEL_LF_ROWS
    ---------- ---------- ---------- ---------- ----------- ---------- -----------
             2        256        199          1     1600032        100       99000
    
    -- create a tree dump
    select object_id from user_objects where object_name = 'T_IDX';
    
     OBJECT_ID
    ----------
         89087
    
    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.

    Martin

    Comment by Martin Preiss — December 18, 2013 @ 7:55 am BST 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';
      
      HEADER_FILE HEADER_BLOCK
      ----------- ------------
                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    
                         Unlocked
        --------------------------------------------------------
        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 BST 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 BST 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 BST 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 BST Dec 22,2013


RSS feed for comments on this post. TrackBack URI

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

The Rubric Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 4,014 other followers