You Asked
Hi all：
I got a mission this days, I want to identify which tables have 'fragment'or row chains. after search a few days, I have some questions,hope you can help me.
1. select owner,table_name,round((blocks*8),2) "size (kb)" ,
round((num_rows*avg_row_len/1024),2) "actual_data (kb)",
(round((blocks*8),2) - round((num_rows*avg_row_len/1024),2)) "wasted_space (kb)"
from dba_tables
where (round((blocks*8),2) > round((num_rows*avg_row_len/1024),2))
order by 3 desc
the statement above shows the waste space ,but it need to analyze the table first, that's right?
2. after I get the tables , I have two methods to wipe the fragment,one is use move or imp(impdp)。 I want to know which one is better and why?
3. The Segment Space management of my tablespace is manual , Should I change it to AUTO? Can It help to reduce the fragment?
4. I wonder if Oracle have the concept of 'fragment', or It maybe call row chains, can you give me some docs?
5.please give me some advice about how to identify the table that have 'fragment' , and how often should handle it(use move or shrink or imp)
At last, my English is not good ,hope you can understand it, waiting for you reply.
and Connor said...
OK, we've got some mixed up terminology there - we'll sort that out first.
1) chaining/migration
This is when a row is updated and is too large to fit where it was originally stored. Normally not a large concern - you want to check the "table fetch continued row" statistic. If this is *large*, then you might want to consider a reorganisation of affected tables. You would do this (for example) with:
alter table T move
(and then rebuild any indexes for T)
in an available maintenance window. There are ways of doing it online as well.
2) fragmentation
This is when you've deleted rows in a table, and you now have pockets of free space littered throughout the table blocks. Once again, normally not a large cause for concern. We will automatically re-use that space when more rows are inserted. It's really only worth looking at more carefully when
a) the table is now static (ie, no more rows expected). In this case, you might do: alter table T move compress, to make it as dense and as compact a spossible.
b) you did something 'abnormal', for example, you deleted millions of rows, so you've got huge amounts of free space that you may never re-use. In this case, you might do: alter table T move
But you typically need only worry about it in these cases, ie, an activity that it not an ordinary event. For just standard insert, delete, update etc - we will take care of re-using that free space for you.
Rating
(3 ratings)
Is this answer out of date? If it is, please let us know via a Comment