-
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
Performance... Push the envelope!
-
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
Once you have eliminated all of the impossible,
whatever remains however improbable,
must be true.
-
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 .
Vinit
-
1. How can one find the degree of OS fragmentation?
2. How can one repair OS fragmentation?
TIA
David Knight
OCP DBA 8i, 9i, 10g
-
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?
David Knight
OCP DBA 8i, 9i, 10g
-
-
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?
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|