Fragmentation? Too many extents?

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!!!

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

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.

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

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 .

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

2. How can one repair OS fragmentation?

TIA

This answer is specific to NT platforms!!!

Defrag may be an option

On unix you use veritas to defrag also!!

Thanks for the response.

Which Veritas tool?

Volume manager!

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?

To be precise, more than 1 indicates fragmentation. But a value of 10 or 20 hardly matters since this is quite possible in tablespaces which have heavy DML operations like delete, insert. Your aim would then be to keep the chunks at the minimum.

Hi.

You can set pct_increase above 0 in tablespace default storage, this will force SMON to auto colesce adjoining free chunks into one group of freespace blocks.
I keep pctincrease at 1 and ensure minimum extent is set to make all extents inside the tablespace a multiple of it.

I usually investigate more when the amount of freepace extents reaches into hundreds. You can use export/import to export data and then import (compress = y) into one big extent.

You can also consider converting tablespaces to LMT.

If you have Oracle Enterprise Manager there is a way of reorganizing tablespace to defrag. What it basically does is move all objects inside the tablespace to another tablespace created specifically for that purpose. It then moves the objects back to the original tablespace defragged.

You can rebuild indexes to eliminate fragmentation in indexes.

I have seen statements that more than 5 extents is bad, others that more than 20 is bad.

In recent years, I have changed my mind about fragmentation. It depends on what you are doing.

If you are doing OLTP (transaction processing) then your extent size should be the size of your OS read/write buffer. If that means hundreds (or even thousands) of extents, so be it. Actually, you want to balance the minimum read/write time to the hardware with the buffer pool in the SGA.

If you are doing OLAP (report writing) then you want the minimum number of extents because will probably be doing a lot of full table scans, and again want to minimize the io.

Just to confuse matters more, a lot can also depend on your IO channels (disk drives/drive controllers) and whether you have multiple cpu's or not. Multiple extents faciliates parallel processing, making statement processing more efficient.

Generally speaking, fewer extents are better, but fewer than what is something you need to determine for each instance.

I suggest you switch to LMTs and forget about DMTs.