Reporting Space-Wasting Objects in Oracle

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