| Reorganization of table [message #563328] |
Mon, 13 August 2012 00:46  |
lakshmis
Messages: 102
Registered: November 2008
Location: India
|
Senior Member |
|
|
Hi All,
We have re-orged a table as it was highly fragmented.
we calculated fragmented size of the table as:
ROUND((a.blocks*8/1024),2)FRAGMENTED_SIZE_MB
and actual size of the table as:
ROUND((num_rows*avg_row_len/1024/1024),2) ACTUAL_SIZE_MB
Fragmented size of the table was 15GB and actual size was 10GB.
We asked our DBAs to re-org the tables. They did the re-org by export/import method and analyzed the tables.
But even after re-org, the fragmented size and actual size of the table remained the same.
So, we changed the Storage parameter of the table and again re-orged the tables:
INITIAL EXTENT: 268435456 to 1048576
NEXT EXTEND: 268435456 to 1048576
Even after this, the size of the table did not change.
Please help me understand the reason for not change in the fragmented size of the table.
Is there anything which I can try to reclaim the fragmented space.
Regards,
Lakshmi.
|
|
|
|
|
|
|
|
|
|
| Re: Reorganization of table [message #563339 is a reply to message #563328] |
Mon, 13 August 2012 01:28  |
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
|
You should ask your DBAs to study the command ALTER TABLE....SHRINK SPACE CASCADE they will find this a much more efficient (efficient by any criteria I can think of) techinique for doing this sort of thing than export/import.
|
|
|
|
|
|
|
|
|
|
| Re: Reorganization of table [message #563357 is a reply to message #563343] |
Mon, 13 August 2012 03:20 |
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
I think all these discussions (it is a very common topic) are a waste of time. In the vast majority of cases, this "reorganizing" business is not only a waste of energy, it will often cause more problems than it solves. But for those who insist in doing it, a blog article explaining why these calculations are always wrong is long overdue. Here is a start:(possibly wrong, too):
Every column is prefixed with a byte that states how long the column is, and every row is prefixed with a three byte row header. Gather ing stats with dbms_stats includes the row column header in avg_row_len, but not the row header. Gathering stats with ANALYZE includes both:orcl>
orcl> create table rowsize(c1 varchar2(10));
Table created.
orcl> insert into rowsize values('1234567890');
1 row created.
orcl> select vsize(c1) from rowsize;
VSIZE(C1)
----------
10
orcl> exec dbms_stats.gather_table_stats(user,'rowsize')
PL/SQL procedure successfully completed.
orcl> select avg_row_len from user_tables where table_name='ROWSIZE';
AVG_ROW_LEN
-----------
11
orcl> analyze table rowsize compute statistics;
Table analyzed.
orcl> select avg_row_len from user_tables where table_name='ROWSIZE';
AVG_ROW_LEN
-----------
14
orcl>
orcl>
Then there is the block header, according to the docs http://docs.oracle.com/cd/B28359_01/server.111/b28318/logical.htm#CIHEIFJC that is typially 84 to 107 bytes.
So that simple calculation used above needs to be modified to add 3 bytes per row plus 84 bytes per block and then add another 10% to take account of PERCENT_FREE.
Corrections welcome - if anyone thinks this sort of discussion has any purpose. I don't.
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
