Oct 31, 2012 11:00:00 PM by Steve Hilker
The tablespace map uses a simplified fragmentation formula. The key points in question:
SELECT sum(blocks) FROM dba_extents WHERE tablespace_name = :tblspace AND (segment_name = :segName)
Sums all the blocks - by segment name - for the segments in a tablespace. Of course this query is not run explicity, it would be far too inefficient. The query is executed without the segment_name distinction; the rest is done in memory.
Run the query. Look down through the values returned in the "BLOCKS" column. Find the biggest value. That is the largest block for that segment. We've had a number of these things from users coming through support saying, "Gee, is this really true? Is my tablespace fragmented, really???" and then I end up sending out an email like this.
It's not by partition. Or table. Nowhere does partition or table appear in the map blocks. Its by segment name.
In Oracle, there are many different kinds of fragmentation - and just as many opinions on which ones really matter or how they should be calculated. One type of fragmentation, which has many names but I'll refer to as internal fragmentation attempts to measure the relative fragmentation of a segment within a tablespace. That is often call internal fragmentation due to its measure of the fragmentation of the object within the tablespace - which is what we are providing. With locally managed tablespaces, uniform extents and no disadvantage to zillions of extents as in past days - this might not be as relevant a measurement as it once was.
Another type of fragmentation attempts to measure how fragmented the tablespace itself is (sort of like how fragmented is my hard disk on my PC). This is often referred to as external fragmentation - and is NOT what we are trying to show on the tablespace map screen (although lots of people assume or prefer that we did so). While this may sound like a good measurement - there is a wide and varied opinion on how to best calculate this value. Plus for large tablespaces with tons of objects in them, it could take inordinate amounts of time to return.
We are providing feedback on the segment and not the tablespace. So the short answer is: There are several kinds of fragmentations that can be calculated and we're doing one that is probably not the one you were expecting.
Written by Steve Hilker
Steve Hilker was a Product Manager for Quest Software. Steve has over 35 years technical experience spanning application development, system administration, database management and various management positions at several software companies. Steve was the founder of RevealNet, best known for its desktop knowledge bases and unique database tools such as PL/Formatter. RevealNet was acquired by Quest Software in 2001. He's had the pleasure of being the product manager for many of Quest's database tools.
In his private life, Steve enjoys oil painting and living on a small farm in a pre-Civil war manor home in Fredericksburg, Virginia.