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?
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
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.
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.
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.
Reply With Quote
Originally Posted by PAVB
Just trying to use the same language of the user does, otherwise no conversation is possible.
Bookmarks