Home » RDBMS Server » Server Administration » Reorganization of table (Oracle 10.2.0.5)
Reorganization of table [message #563328] Mon, 13 August 2012 00:46 Go to next message
lakshmis
Messages: 101
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 #563331 is a reply to message #563328] Mon, 13 August 2012 00:49 Go to previous messageGo to next message
Michel Cadot
Messages: 58913
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As your formala are wrong, your action and conclusion are too.

Regards
Michel
Re: Reorganization of table [message #563333 is a reply to message #563331] Mon, 13 August 2012 00:55 Go to previous messageGo to next message
lakshmis
Messages: 101
Registered: November 2008
Location: India
Senior Member
Hi Michel,
I got the details from the below link:
http://www.orafaq.com/node/1936

Please help me in correcting the formula if they are wrong.

Regards,
Lakshmi.
Re: Reorganization of table [message #563337 is a reply to message #563333] Mon, 13 August 2012 01:24 Go to previous messageGo to next message
Michel Cadot
Messages: 58913
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use dbms_space.space_usage to know how the space is used in your table.

Regards
Michel
Re: Reorganization of table [message #563339 is a reply to message #563328] Mon, 13 August 2012 01:28 Go to previous messageGo to next message
John Watson
Messages: 4508
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 #563340 is a reply to message #563328] Mon, 13 August 2012 01:31 Go to previous messageGo to next message
John Watson
Messages: 4508
Registered: January 2010
Location: Global Village
Senior Member
Also, what is DBA_TABLES.PCT_FREE set to for the table?
Re: Reorganization of table [message #563343 is a reply to message #563339] Mon, 13 August 2012 01:41 Go to previous messageGo to next message
lakshmis
Messages: 101
Registered: November 2008
Location: India
Senior Member
Hi,

PCTFREE is 10 and PCTUSED is 70 for this table.
I will also read about dbms_space.space_usage. Thanks for your suggestion.

Regards,
Lakshmi.
Re: Reorganization of table [message #563345 is a reply to message #563343] Mon, 13 August 2012 02:00 Go to previous messageGo to next message
Michel Cadot
Messages: 58913
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Have a look at the following for an example of this procedure:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:231414051079#11326100924186

Regards
Michel
Re: Reorganization of table [message #563357 is a reply to message #563343] Mon, 13 August 2012 03:20 Go to previous messageGo to next message
John Watson
Messages: 4508
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.
Re: Reorganization of table [message #563360 is a reply to message #563357] Mon, 13 August 2012 03:32 Go to previous messageGo to next message
Michel Cadot
Messages: 58913
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
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.


This is why I pointed to T. Kyte's show_space function and topic.
This function is a far better way to know table "fragmentation" than any other computation based on avg_row_len and so on (at least with ASSM).

Regards
Michel
Re: Reorganization of table [message #563380 is a reply to message #563360] Mon, 13 August 2012 06:34 Go to previous message
lakshmis
Messages: 101
Registered: November 2008
Location: India
Senior Member
Hi Michel and Watson,

Thanks for your suggestions. I will go through the link provided.

Regards,
Lakshmi.
Previous Topic: Session_privs
Next Topic: vktm and dia0
Goto Forum:
  


Current Time: Wed Aug 27 22:18:32 CDT 2014

Total time taken to generate the page: 0.19984 seconds