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 Oracledatabase. 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.

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:

dbms_space.space_usage works only for ASM segments.

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).

The above code and procedures are examples only. Use them at your own risk, and tailor them as you wish. And,

Feel free to test my assumptions as extensively as you wish. And I welcome your feedback.

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.

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.

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…

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.”

PYTHIAN®, LOVE YOUR DATA®, and ADMINISCOPE® are trademarks and registered trademarks owned by Pythian in North America and certain other countries, and are valuable assets of our company. Other brands, product and company names on this website may be trademarks or registered trademarks of Pythian or of third parties. Use of trademarks without permission is strictly prohibited.