Fragmentation? Too many extents?

[yanert98]

hello,

After searching the old postings, I am still confused. Can someone clarify?

Is a table fragmented if it has double digit extents?

If I run a query like:

select segment_name, extents
from dba_segments
where extents > 10;

Should I reorg these tables based on this information?

(I understand the difference between row chaining and migration and I understand that this is a separate issue from multiple extents.)

Thanks for the insight!!!

[roobaron]

The table is not fragmented when it has double digit extents.

There are some issues with very high numbers of extents, but that is more to do with the dictionary tables and how the default cluster key size is not set up to handle very high no. of extents in many tables.

A good followup on this issue would be to look at the asktom.oracle.com site and search on the topic or
http://www.ixora.com.au where Steve Adams also talks about the issue.

Both basically debunk the idea that tables should be in one extent.

Have Fun

[Raminder]

Fragmentation can occur if there are too many deletes in the table/index or if there are different types of objects, all with different storage requirements stored in the same tablespace.

Extents will keep on adding to the initial segment even if there is only one, growing table in a tablespace. So, high number of extents, per se does not mean high fragmentation.

[Sureshy]

Fragmentation is not based upon the amount of extents per segment, it is based on the amount of freespace pieces in a tablespace.

here is a quick way of finding how many free pieces in each tablespace :

select tablespace_name, count(*) from dba_free_space
group by tablespace_name

[vinit]

One has to look at the OS fragmentation as well. Even though the oracle blocks are not fragmented, the actual underlying Os blocks could be fragmented .

[dknight]

1. How can one find the degree of OS fragmentation?

2. How can one repair OS fragmentation?

TIA

[netbar]

This answer is specific to NT platforms!!!

Defrag may be an option

On unix you use veritas to defrag also!!

[dknight]

Thanks for the response.

Which Veritas tool?

[netbar]

Volume manager!

[yanert98]

Sureshy,

What should I be looking for with the query:

select tablespace_name, count(*) from dba_free_space
group by tablespace_name

What results would indicate fragmentation? More than 10, More than 20?

And what steps should I take to correct this fragmentation?