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.
Printable View
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.