-
Reducing database fragmentation
I read this on another site. Its a sql command to find out if the DB is fragmented.
select * from dba_segments where extents > 10;
After I run this almost all of my tables are above 10, which the author of this article says "In general, if a table or index has more than 10 extents then rebuild it to fit into one extent". This is a pretty new DB and hasn't been used that much. Is this good advice?
Thanks Paul
-
Don't trust everything you read out there.
Here in my shop we have several tables holding in excess of one billion rows apiece, how could them fit in a single extent? BS!
Everything depends on volume.
For your five more critical tables please post...
1- Quantity of rows
2- Average row lenght
3- Initial/Next extent size
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
I disagree, it is a sql query that will tell you what has more than 10 extents. A table or index having more than 10 extents is not a sign of fragmentation. If you are using locally managed tablespaces than you have have tables with extents in the thousands and not see an issue.
If you want to avoid fragmentation use locally managed tablespaces with either automatic storage of reasonably sized uniform extents.
-
Thanks
Thanks for the replies.. We are using a RAC setup with ASM.. Does ASM account for the fragmentation?
-
Originally Posted by PAVB
For your five more critical tables please post...
1- Quantity of rows
2- Average row lenght
3- Initial/Next extent size
What does that have to do with fragmentation?
-
Originally Posted by ixion
What does that have to do with fragmentation?
have you seen what OP thinks fragmentation is? Just trying to use the same language of the user does, otherwise no conversation is possible.
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
Originally Posted by pam61
Does ASM account for the fragmentation?
No, it doesn't.
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
There used to be a concern with dictionary managed tablespaces over segments with extremely ghigh numbers of extents, and in fact the number of extents used be be severely limityed in early versions of Oracle.
With locally managed tablespaces there should be a very much lower level of concern, certainly not in the range the author suggests.
-
This seems to be the definitive word on the subject of fragmentation, http://technet.oracle.com/deploy/ava...pdf/defrag.pdf
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
|