Reporting Space-Wasting Objects in Oracle

May 1, 2007 / By Marc Billette

Tags: , ,

Woohoo, I finally got a bit of spare time to blog — my first blog post ever!

I chose to talk about a technique I used at a client’s site to report the topmost space-wasting objects in an Oracle database. I was looking for a way to detect these objects without having to run some expensive analyze statements or dbms_stats jobs. I found out that I can use the dbms_space package to do this. It worked very well for me and I’m sure lots of DBAs could use this technique too. It is not perfect in all situations as it has some prerequisites that I will list later, but still it does the trick for me.

The dbms_space package has procedures that are very useful for determining how much space is used within db blocks. As far as I know, this information is determined by looking at the tablespace bitmap pages. I haven’t confirmed this yet, but that would explain why it is so much faster than running an analyze or dbms_stats.gather_%_stats.

So, the idea is to generate the space-usage information using dbms_space, store that information into a table, and then run queries on it to report the topmost space-wasters.

Here are simplified versions of the scripts I use:

  1. Create a table to store the statistics generated by dbms_space.
    connect &your_account/&your_account_password
    
    create table segment_space_stats
           (owner                     varchar2(32),
            segment_name              varchar2(32),
            segment_type              varchar2(32),
            tablespace_name           varchar2(32),
            segment_space_management  varchar2(32),
            unformatted_blocks        number,
            unformatted_bytes         number,
            fs1_blocks                number,
            fs1_bytes                 number,
            fs2_blocks                number,
            fs2_bytes                 number,
            fs3_blocks                number,
            fs3_bytes                 number,
            fs4_blocks                number,
            fs4_bytes                 number,
            full_blocks               number,
            full_bytes                number,
            total_blocks              number,
            total_bytes               number,
            unused_blocks             number,
            unused_bytes              number,
            last_used_extent_file_id  number,
            last_used_extent_block_id number,
            last_used_block           number,
            timestamp                 date)
    ;
    
  2. Create a procedure and job to run the dbms_space procedures (space_usage and unused_space) on all applicable objects.
    -- You may need to grant these to your_account.
    --
    -- connect sys as sysdba
    -- grant select any tables, select any dictionary, analyze any to &your_account;
    -- grant execute on dbms_space to &your_account;
    -- connect &your_account/&your_account_password
    
    CREATE OR REPLACE procedure GEN_SEGMENT_SPACE_STATS
          as
            index_does_not_exist EXCEPTION;
            table_does_not_exist EXCEPTION;
            PRAGMA EXCEPTION_INIT(index_does_not_exist, -1418);
            PRAGMA EXCEPTION_INIT(table_does_not_exist, -942);
    
            v_unformatted_blocks number;
            v_unformatted_bytes  number;
            v_fs1_blocks         number;
            v_fs1_bytes          number;
            v_fs2_blocks         number;
            v_fs2_bytes          number;
            v_fs3_blocks         number;
            v_fs3_bytes          number;
            v_fs4_blocks         number;
            v_fs4_bytes          number;
            v_full_blocks        number;
            v_full_bytes         number;
    
            v_total_blocks number;
            v_total_bytes number;
            v_unused_blocks number;
            v_unused_bytes number;
            v_last_used_extent_file_id number;
            v_last_used_extent_block_id number;
            v_last_used_block number;
    
          begin
            for s in (select owner, segment_name, segment_type, seg.tablespace_name , segment_space_management
                        from dba_tablespaces ts, dba_segments seg
                       where ts.tablespace_name not in ('SYSTEM', 'SYSAUX')
                         and ts.tablespace_name = seg.tablespace_name
                         and segment_type in ('TABLE', 'INDEX','CLUSTER','LOB')
                         and (owner, segment_name, segment_type, seg.tablespace_name ) in (
                              select owner, segment_name, segment_type, tablespace_name from segment_space_stats))
            loop
              begin
    
                if s.segment_space_management = 'AUTO' then
                   dbms_space.space_usage(s.owner, s.segment_name, s.segment_type, v_unformatted_blocks, v_unformatted_bytes,
                                          v_fs1_blocks, v_fs1_bytes, v_fs2_blocks, v_fs2_bytes , v_fs3_blocks, v_fs3_bytes ,
                                          v_fs4_blocks, v_fs4_bytes , v_full_blocks, v_full_bytes , NULL);
                end if;
                dbms_space.unused_space(s.owner, s.segment_name, s.segment_type, v_total_blocks, v_total_bytes,
                                        v_unused_blocks, v_unused_bytes, v_last_used_extent_file_id,
                                        v_last_used_extent_block_id, v_last_used_block, NULL);
                update SEGMENT_SPACE_STATS
                   set tablespace_name=s.tablespace_name,
                       unformatted_blocks=v_unformatted_blocks,
                       unformatted_bytes=v_unformatted_bytes,
                       fs1_blocks =v_fs1_blocks,
                       fs1_bytes  =v_fs1_bytes,
                       fs2_blocks =v_fs2_blocks,
                       fs2_bytes  =v_fs2_bytes,
                       fs3_blocks =v_fs3_blocks,
                       fs3_bytes  =v_fs3_bytes,
                       fs4_blocks =v_fs4_blocks,
                       fs4_bytes  =v_fs4_bytes,
                       full_blocks=v_full_blocks,
                       full_bytes =v_full_bytes,
                       timestamp  =sysdate,
                       total_blocks=v_total_blocks,
                       total_bytes =v_total_bytes,
                       unused_blocks=v_unused_blocks,
                       unused_bytes =v_unused_bytes,
                       last_used_extent_file_id =v_last_used_extent_file_id,
                       last_used_extent_block_id=v_last_used_extent_block_id,
                       last_used_block          =v_last_used_block
                 where owner=s.owner
                   and segment_name=s.segment_name
                   and segment_type=s.segment_type;
              exception
                when index_does_not_exist then
                  null; -- ignore these errors
                when table_does_not_exist then
                  null; -- ignore these errors
                when others then
                  raise;
              end;
            end loop;
            commit;
    
            for s in (select owner, segment_name, segment_type, seg.tablespace_name, segment_space_management
                        from dba_tablespaces ts, dba_segments seg
                       where ts.tablespace_name not in ('SYSTEM', 'SYSAUX')
                         and ts.tablespace_name = seg.tablespace_name
                         and segment_type in ('TABLE', 'INDEX','CLUSTER','LOB')
                       minus
                      select owner, segment_name, segment_type, tablespace_name, segment_space_management
                        from segment_space_stats)
            loop
              begin
                if s.segment_space_management = 'AUTO' then
                   dbms_space.space_usage(s.owner, s.segment_name, s.segment_type, v_unformatted_blocks, v_unformatted_bytes,
                                          v_fs1_blocks, v_fs1_bytes, v_fs2_blocks, v_fs2_bytes , v_fs3_blocks, v_fs3_bytes ,
                                          v_fs4_blocks, v_fs4_bytes , v_full_blocks, v_full_bytes , NULL);
                end if;
                dbms_space.unused_space(s.owner, s.segment_name, s.segment_type, v_total_blocks, v_total_bytes, v_unused_blocks,
                                        v_unused_bytes, v_last_used_extent_file_id, v_last_used_extent_block_id, v_last_used_block, NULL);
                insert into SEGMENT_SPACE_STATS
                values(s.owner, s.segment_name, s.segment_type, s.tablespace_name, s.segment_space_management,v_unformatted_blocks,
                       v_unformatted_bytes, v_fs1_blocks, v_fs1_bytes, v_fs2_blocks, v_fs2_bytes, v_fs3_blocks, v_fs3_bytes,
                       v_fs4_blocks, v_fs4_bytes, v_full_blocks, v_full_bytes, v_total_blocks, v_total_bytes,
                       v_unused_blocks, v_unused_bytes, v_last_used_extent_file_id, v_last_used_extent_block_id,
                       v_last_used_block,sysdate);
              exception
                when index_does_not_exist then
                   null; -- ignore these errors
                when table_does_not_exist then
                   null; -- ignore these errors
                when others then
                   raise;
              end;
            end loop;
    
            commit;
    
            delete SEGMENT_SPACE_STATS where (owner, segment_name, segment_type, tablespace_name) not in
            (select owner, segment_name, segment_type, tablespace_name from dba_segments
              where tablespace_name not in ('SYSTEM', 'SYSAUX')
                and segment_type in ('TABLE', 'INDEX','CLUSTER','LOB'));
            commit;
          end;
    /
    
    var v_job_id number;
    begin
      -- schedule the job to run daily at 4AM...
      dbms_job.submit(:v_job_id,'GEN_SEGMENT_SPACE_STATS;',to_date(trunc(sysdate+1)+4/24),'trunc(sysdate+1)+4/24');
      commit;
    end;
    /
    
    exec dbms_job.run(:v_job_id)
    
  3. Run queries on the table’s data.
    TTITLE center underline "Top Ten Largest Empty Segments"
    set linesize 140 pagesize 100
    col owner for a20
    col total_MB for 999999
    clear breaks
    clear computes
    break on report dup
    compute sum of total_MB on report
    select a.*
      from (select owner, segment_name, segment_type, tablespace_name, total_bytes/1048576 total_MB
              from segment_space_stats
             where full_bytes = 0
            order by total_bytes desc) a
    where rownum<11
    ;
    
                                                           Top Ten Largest Empty Segments
                                                           ______________________________
    
    OWNER                SEGMENT_NAME                     SEGMENT_TYPE                     TABLESPACE_NAME                  TOTAL_MB
    -------------------- -------------------------------- -------------------------------- -------------------------------- --------
    PERFSTAT             STATS$SQL_PLAN                   TABLE                            PERFSTAT_DATA                           5
    PERFSTAT             STATS$SQL_PLAN_USAGE             TABLE                            PERFSTAT_DATA                           5
    PERFSTAT             STATS$SEG_STAT                   TABLE                            PERFSTAT_DATA                           3
    BP_PROD_JOBS         JB_SEARCH_RESULT_ALERT           TABLE                            BP_PROD_JOBS_DATA                       2
    PERFSTAT             STATS$BUFFERED_QUEUES            TABLE                            PERFSTAT_DATA                           1
    PERFSTAT             STATS$BUFFERED_QUEUES_PK         INDEX                            PERFSTAT_DATA                           1
    PERFSTAT             STATS$BUFFERED_SUBSCRIBERS       TABLE                            PERFSTAT_DATA                           1
    PERFSTAT             STATS$CR_BLOCK_SERVER_PK         INDEX                            PERFSTAT_DATA                           1
    PERFSTAT             STATS$CR_BLOCK_SERVER            TABLE                            PERFSTAT_DATA                           1
    PERFSTAT             STATS$BUFFERED_SUBSCRIBERS_PK    INDEX                            PERFSTAT_DATA                           1
                                                                                                                            --------
    sum                                                                                                                           21
    
    10 rows selected.
    
    TTITLE center underline "Top Ten Largest Space Wasters"
    set linesize 140 pagesize 20
    col owner for a20
    col segment_type for a12
    col total_MB for 999999
    col unformatted_MB for 999999
    col unused_MB for 999999
    col pct_unused for 999 heading "Pct Unused"
    col estmtd_pssbl_svngs for 99999 heading "Estimated|Possible Savings|w Index Rebuild"
    clear breaks
    clear computes
    break on report dup
    compute sum of total_MB on report
    compute sum of unused_MB on report
    
    select a.*
      from (
            select owner, segment_name, segment_type,
                   total_bytes/1048576 total_MB,
                   unformatted_bytes/1048576 unformatted_MB,
                   unused_bytes/1048576 unused_MB,
                   trunc(unused_bytes / total_bytes * 100) pct_unused,
                   case when segment_type = 'INDEX' and total_blocks >= 256 and fs2_blocks >= 64
                             then unused_blocks + fs2_blocks
                        else 0
                   end * b.block_size/1048576 estmtd_pssbl_svngs
              from segment_space_stats a, dba_tablespaces b
             where unused_bytes <> 0
               and a.tablespace_name = b.tablespace_name
             order by unused_bytes desc) a
    where rownum<11
    ;
    
                                                            Top Ten Largest Space Wasters
                                                            _____________________________
    
                                                                                                                           Estimated
                                                                                                                    Possible Savings
    OWNER                SEGMENT_NAME                     SEGMENT_TYPE TOTAL_MB UNFORMATTED_MB UNUSED_MB Pct Unused  w Index Rebuild
    -------------------- -------------------------------- ------------ -------- -------------- --------- ---------- ----------------
    BP_PROD_JOBS         JB_SEARCH_RESULT_IDX             INDEX            2229              4        56          2             2179
    BP_PROD_MVIEWS       USER_INFO_BASIC                  TABLE            4412              0        48          1                0
    BP_PROD_JOBS         JB_SEARCH_RESULT                 TABLE            1216              6        40          3                0
    BP_PROD_JOBS         JB_SEARCH_RESULT_USJ_IDX         INDEX            2109              4        32          1             2041
    BP_PROD_MVIEWS       USER_INFO                        TABLE            6039              0        31          0                0
    BP_PROD_GW_SUPPORT   GWS_ACCESS_LOG_SUM_PK            INDEX            1761              1        16          0              323
    BP_PROD_JOBS         JB_MONSTER_XMIT_LOG              TABLE            3108              0         8          0                0
    BP_PROD_JOBS         DR$JM_SEARCH_DNRM_XML_IDX_2$I    TABLE             144              0         8          5                0
    BP_PROD_JOBS         DR$JM_SEARCH_DNRM_XML_IDX_1$I    TABLE             144              0         8          5                0
    BP_PROD_GW_SUPPORT   GWS_DLY_LOG_20070423_UK          INDEX              88              0         6          6                7
                                                                       --------                ---------
    sum                                                                   21250                      252
    
    10 rows selected.
    
    TTITLE center underline "Top Ten Indexes with non-full blocks"
    clear breaks
    clear computes
    set linesize 145 pagesize 100
    col owner for a20
    col fs1_blocks for 9999999 heading "< 25%|blocks free"
    col fs2_blocks for 9999999 heading "25-50%|blocks free"
    col fs3_blocks for 9999999 heading "50-75%|blocks free"
    col fs4_blocks for 9999999 heading "75-100%|blocks free"
    col full_blocks for 9999999 heading "blocks|full"
    col total_blocks for 9999999 heading "total|blocks"
    col estmtd_pssbl_svngs for 99999 heading "Estimated|Possible Savings|w Index Rebuild"
    col block_size noprint
    break on report dup
    compute sum of estmtd_pssbl_svngs on report
    select a.*
      from (
            select owner, segment_name,
                   fs1_blocks ,
                   fs2_blocks ,
                   fs3_blocks ,
                   fs4_blocks ,
                   full_blocks, total_blocks,
                   case when segment_type = 'INDEX' and total_blocks >= 256 and fs2_blocks >= 64
                             then unused_blocks + fs2_blocks
                        else 0
                   end * b.block_size/1048576 estmtd_pssbl_svngs,
                   b.block_size
              from segment_space_stats a, dba_tablespaces b
             where segment_type = 'INDEX'
               and a.tablespace_name = b.tablespace_name
             order by fs4_blocks desc, fs3_blocks desc, fs2_blocks desc, fs1_blocks desc) a
    where rownum<11
    ;
    
                                                           Top Ten Indexes with non-full blocks
                                                           ____________________________________
    
                                                                                                                                   Estimated
                                                                < 25%      25-50%      50-75%     75-100%   blocks    total Possible Savings
    OWNER                SEGMENT_NAME                     blocks free blocks free blocks free blocks free     full   blocks  w Index Rebuild
    -------------------- -------------------------------- ----------- ----------- ----------- ----------- -------- -------- ----------------
    BP_PROD_JOBS         JB_SEARCH_RESULT_IDX                       0      271793           0           0     5035   285312             2179
    BP_PROD_JOBS         JB_SEARCH_RESULT_USJ_IDX                   0      257204           0           0     7402   269952             2041
    BP_PROD_JOBS         JB_SEARCH_RESULT_SCORE_IDX                 0      122435           0           0     4443   127488              957
    BP_PROD_GW_SUPPORT   GWS_ACCESS_LOG_SUM_PK                      0       39342           0           0   183215   225408              323
    PERFSTAT             STATS$SQL_SUMMARY_PK                       0        2015           0           0     9354    12288               22
    AVAIL                STAT_TABLE                                 0        1556           0           0    20269    22528               16
    BP_PROD_INVITE       REL_RELATIONSHIP_QUERY_IDX                 0         489           0           0   155335   156544                4
    PERFSTAT             STATS$EVENT_HISTOGRAM_PK                   0         245           0           0     6299     6656                2
    BP_PROD_JOBS         JB_ACTIVITY_LOG_IDX                        0         245           0           0    49501    50816                8
    BP_PROD_MVIEWS       USER_INFO_BASIC_LWR_USRNAM_IDX             0         229           0           0    91468    92160                2
                                                                                                                            ----------------
    sum                                                                                                                                 5554
    
    10 rows selected.
    

Voila! Isn’t that pretty? I know, I need to explain these reports a bit…

The first report is straightforward. It lists the ten largest empty segments. As you can see, there aren’t any large empty objects in this database. This is mostly due to the work of Oracle’s Automatic Storage Management (ASM).

The second report lists the objects with the most unused space within their currently allocated extents.

The column that probably gets you going “huh?” is the “Estimated Possible Savings w Index Rebuild”. This column is derived based on my own observations of the data generated by dbms_space.space_usage.

I noticed that for indexes, there are never any blocks that are totally empty. That got me curious. It seems that all blocks allocated to the indexes automatically get a usage estimate of at least 25%. I also noticed, based on my analysis of the indexes, that these large indexes with lots of blocks in the 25-50% range are indexes that get updated very frequently, and in fact, get lots of their rows deleted (i.e. rows older than, say, 30 days are deleted).

I was of the impression that lots of old blocks would be reported in the 0-25% utilization range. So I figured, what the heck, let’s rebuild some of these and see if I’m right in suspecting that most of these blocks in the 25-50% range are empty. And, bingo! The space usage went down to about 10% more than the “blocks full” figure. Nice! I might be able to use this to estimate how much space I can save by rebuilding an index. Yes, I know — the problem is an application-design issue, and indexes shouldn’t need to be rebuilt over time. Hey, if we lived in a perfect world, there wouldn’t be any administrators… but that’s another story.

I also noticed that this was not necessarily true for small indexes, hence the case statement I have in the query. Only indexes larger than 256 blocks and with at least 64 blocks in the 25-50% range are considered for space saving. You can use whatever value you like here. These are the ones that work for me. And no, I didn’t do any extensive research or testing with these figures.

A few things that you should be aware of:

  1. dbms_space.space_usage works only for ASM segments.
  2. dbms_space‘s unused_space, space_usage and free_space do not require a special license to use. The other procedures in that package are linked to the AWR licensing (at least they were the last time I checked).
  3. The above code and procedures are examples only. Use them at your own risk, and tailor them as you wish. And,
  4. Feel free to test my assumptions as extensively as you wish. And I welcome your feedback.

I hope you find this useful.

Enjoy!
Marc

12 Responses to “Reporting Space-Wasting Objects in Oracle”

  • anon says:

    Isn’t that what the segment/space advisor is for?

    It identifies heavily used segments with lots of wasted space in them that would potentially benefit from a reorg/shrink?

  • Marc says:

    Good point! Correct me if I’m wrong but as far as I know, this is not an Oracle Standard Edition feature. I believe it requires an EM Diagnostic Pack license, or some other EM pack, and therefore you are not allowed to use it with a SE license alone.

  • Raj Jamadagni says:

    Nice Script Marc,

    but what unit you had in mind for values in “estimated savings” (the rightmost) column?
    blocks? kb? mb?

    BTW I had put dbms_space call inside a pipelined function, so it becomes easy to put it in a subquery if needed.

    Raj

  • Marc says:

    I’m glad you like it Raj,

    The unit is MB, same as the other columns.

    Enjoy!
    Marc

  • Don Seiler says:

    I finally got a chance to read this, and the links to your scripts both give me a 404!

  • Thanks for the heads-up, Don. Fixed now.

  • Marcio says:

    I already knew the dbms_space package but, your script is very useful to us and also very didactic.
    Great work!

    Regards,

    Marcio Lima.

  • comfeng says:

    Thank you for your article!

  • Catherine says:

    Hi Marc. Could you give same explain about the difference between unformatted_blocks and unused_blocks? I’m confused. I don’t know which blocks had free space because of deleting, so are under HWM and which blocks had never data and are above HWM.

    thx

  • Marc says:

    Hi Catherine,

    As I understand it, unused_blocks (generated with the unused_space procedure) show the total blocks in the segment that are allocated to segment extents but have NEVER been used. Data deletion will not add blocks to the unused_blocks nor the unformatted_blocks.

    You can look at this post from Tom Kyte for his demonstration. I just had a look at that post and he basically created a similar procedure to the one I posted here, but he had done so way before me…

    http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:231414051079

    Also, in his post he has this explanation about the unformatted blocks:

    “unformatted blocks are blocks that are available for immediate use (below the true high water mark) but haven’t yet had any data. when the table says “i’m full”, we pull a bunch of blocks down into the table from above the HWM and they would all be unformatted until you use them.”

    Hope this helps.
    Marc

  • Catherine says:

    Hi Marc
    Thank you very much. Your response make it clearly for me :-)

  • Paul says:

    Hi Marc,

    Nice procedure btw.

    You might find this useful. Sometimes tables aren’t full of empty blocks but mostly empty blocks (fs4_blocks in dbms_space terms).

    select owner,segment_name,round(fs4_blocks/total_blocks,2)*100 as “Blocks75to100free” from segment_space_stats where fs4_blocks/total_blocks > 0.5 and total_bytes > 100*1024*1024;

Leave a Reply

  • (will not be published)

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>