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. (Note: due to considerations of page space, this example and the next are linked to as text files.)

   oracle-space-waste-2.txt

3. Run queries on the table’s data.

   oracle-space-waste-3.txt

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

You can follow any responses to this post through the feed. You can leave a response, or trackback from your own site.