Oracle Scratchpad

August 27, 2012

Fragmentation ?

Filed under: fragmentation,Infrastructure,LOBs,Oracle — Jonathan Lewis @ 5:15 pm BST Aug 27,2012

Here’s a simple piece of SQL that could, in theory, compare the current size of  a table with the size it might be after a call to “alter table move” – and it’s followed by the results for a table that’s current in the database that I’m looking at:

        blocks, num_rows, avg_row_len, pct_free,
        ceil(num_rows * avg_row_len / (8000 * ((100 - pct_free)/100))) blocks_needed
        table_name = 'T1'

---------- ---------- ----------- ---------- -------------
        25       1000          22         10             4

The total space required (allowing, possibly, for odd details of row and column overheads) is the row length multiplied by the number of rows in the table. The code assumes the table is in a tablespace with a block size of 8KB (and allows about 200 bytes for general block overhead). Finally we allow for the space that will be left by the current setting of PCTFREE if we rebuild the table at the current setting. It’s only intended to be a rough guideline, and in the case of this tiny table, we can see the arithmetic seems to be quite reasonable – we need about 22,000 bytes (probably with some small errors) so 4 blocks of 8KB seems to be in the right ballpark.

The table is currently reporting 25 blocks below the high water mark, though, so we might assume that this indicated some degree of “fragmentation”, and if we were suffering from a compulsion to minimise “wasted” space this excess 21 blocks (or 525%, to give it a different perspective) might incline us to rush into rebuilding. (Note: this is only intended as a tiny example, but the principle it’s going to describe could apply on a much larger scale, making the discrepancy look as if it’s worth some action.)

Before rushing into action, though, you would probably do a few checks – so here’s some extra information: the tablespace is locally managed with uniform extents of 1MB and an 8KB blocksize, but isn’t using ASSM; I have only just created the table, inserted the rows, and run dbms_stats.gather_table_stats() to collect the statistics; the Oracle version is (which I picked because this posting is about a recent question on OTN from someone who is using 8i and may get the urge to rebuild a 60GB table). So here’s the kicker:

SQL> set feedback off;
SQL> alter table t1 move;
SQL> execute dbms_stats.gather_table_stats(user,'t1');
SQL> select
  2     blocks, num_rows, avg_row_len, pct_free,
  3     ceil(num_rows * avg_row_len / (8000 * ((100 - pct_free)/100))) blocks_needed
  4  from
  5     user_tables
  6  where
  7     table_name = 'T1'
  8  ;

---------- ---------- ----------- ---------- -------------
        23       1000          22         10             4

No change! So what’s the missing ingredient?

The table has a CLOB column, with storage enabled in row, and dbms_stats (for this version of Oracle) hasn’t included the length of the CLOB (which I happen to have used to store just 100 bytes of data per row) in its calculation of row length. Switch to the (now deprecated) analyze command and Oracle gets the answer right – reporting the avg_row_len as 162 bytes.

If you want to play about with the example, here’s the code to generate the data:

rem     Script:         avg_row_len_long.sql
rem     Author:         Jonathan Lewis
rem     Dated:          July 2012

create table t1(
        v1      varchar2(10),
--      l       long,
        l       clob,
        v2      varchar2(10)

insert into t1
        rownum <= 1000 -- > comment to avoid wordpress format issue

You’ll notice that I’ve included the option for using a LONG instead of a CLOB – the OP was, after all, running so if they had any large character columns they were much more likely to be LONGs than LOBs – and the side effects of changing column type and version of Oracle are worth investigating. You might, for example try repeating the exercise in, which still does it wrong, but differently.

In, the avg_row_len on my system came out as 225 (when the actual space used per row was 162 bytes), because the code Oracle uses to determine the length of the LOB column seems to include an assumption about the size of the LOB locator that is a few dozen bytes larger than it ought to be. If you’re trying to use things like avg_col_len and avg_row_len to work out whether or not you have a lot of available space below the HWM, then LOBs make life difficult- it looks like the only way to find out how much space your rows occupy in the table segment is still to use the (deprecated) analyze command (and that doesn’t tell you about the LOBs that have ended up in the LOB segment) but remember to use analuze again to delete the stats afterwards before using dbms_stats to recreate the stats the optimizer.

In passing, if you still have LONGs in the latest versions of Oracle then dbms_stats still loses sight of them completely when calculating the row and column length.


  1. I’ve found good old dbms_space.space_usage useful, at least since 10g.
    It doesn’t handle LOBs gracefully either, but at least gives me a good picture of how much space I can get back and how/why/where:

    SQL> @gdstst
    Enter owner name=>prd_wljms
    Enter tname=>JBM_MSG
    PL/SQL procedure successfully completed.
    Press Enter to continue...
    Script follows:
    WPPRD$cat gdstst.sql
    accept towner char prompt 'Enter owner name=>'
    accept tname char prompt 'Enter tname=>'
    variable unf number;
    variable unfb number;
    variable fs1 number;
    variable fs1b number;
    variable fs2 number;
    variable fs2b number;
    variable fs3 number;
    variable fs3b number;
    variable fs4 number;
    variable fs4b number;
    variable full number;
    variable fullb number;
                            :unf, :unfb,
                            :fs1, :fs1b,
                            :fs2, :fs2b,
                            :fs3, :fs3b,
                            :fs4, :fs4b,
                            :full, :fullb);
    print unfb ;
    print fs4b;
    print fs3b;
    print fs2b;
    print fs1b;
    print fullb;
    undefine tname
    undefine towner

    Comment by Noons — August 28, 2012 @ 12:16 am BST Aug 28,2012 | Reply

    • Noons,

      Thanks for the example.

      I keep a copy of something similar on my laptop for those occasions when I need the extra detail. I can never remember whether fs1 means one quarter full or one quarter free – until I check my notes, that is.

      Comment by Jonathan Lewis — August 29, 2012 @ 8:59 am BST Aug 29,2012 | Reply

  2. Thank you Jonathan for the above example.

    Although this is not a place to ask a question, but … I am in a situation wherein there are LOB objects in a tablespace of 8KB block size. There are some tables whose physical size is like 32GB but the used space is only 40MB! Any ways you can suggest to reduce the space wasted .. like increasing block size? Note: I have tried with exp/imp but with no luck. DB Version, front end is PeopleSoft 8.8.


    Comment by Suddhasatwa Bhaumik — August 28, 2012 @ 4:28 pm BST Aug 28,2012 | Reply

    • 1. If LMT tablespace, check if it is in extent alloc is system managed. If there are lot of extents in the tablespace then there could be really large extents. Check if few extents hold lot of space: select sum(bytes)/1024/1024,count(1) extents from dba_extents where segment_name=’tablename’ and owner=’owner’; If so, try export/import with compress option or alter table shrink space. Can also try switching to uniform size extent alloc. for a long term solution.
      2. If the issue is the LOB with IN ROW disabled, then check if the lob segment is large. LOB are separate segments and to check their size lookup dba_lobs.segment_name and compare the size of the lobs using dbms_lob package. If there is a big difference then it may be better to store them IN ROW.

      Comment by rajesh — August 28, 2012 @ 8:12 pm BST Aug 28,2012 | Reply

      • Rajesh,

        Thank you for trying to help, but before making any suggestions it would be sensible to find out where Suddhasatwa Bhaumik is getting that measure of 32GB from, and where he’s seeing the 40MB. He hasn’t said anything about table segments or LOB segments or high water marks, or space management views, or stats collection. Given that “exp/imp” didn’t help he is either missing something very simple, or has found a bug – you need to know what he’s looking at before making any suggestions about fixing a problem that simply may not exist.

        Comment by Jonathan Lewis — August 29, 2012 @ 9:49 am BST Aug 29,2012 | Reply

    • Suddhasatwa Bhaumik,

      You are right – this is not a forum.

      Your description is sufficiently vague that the first problem is finding out what you are looking at and how you have determined that something is wrong. Your comment that you had ” no luck with exp/imp” doesn’t help since you don’t tell us how you used exp/imp – but it does suggest that either you’ve hit a bug or you’re not looking at the right information.

      I suggest you take this question to the OTN database forum:

      Comment by Jonathan Lewis — August 29, 2012 @ 9:24 am BST Aug 29,2012 | Reply

  3. […] A little warning about LOBs and commonly used space calculations […]

    Pingback by Fragmentation 1 | Oracle Scratchpad — March 2, 2021 @ 6:35 pm GMT Mar 2,2021 | 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: