RE: Best bet for table defragmentation

From: Powell, Mark D <mark.powell_at_eds.com>
Date: Fri, 17 Aug 2007 13:19:45 -0400
Message-ID: <D1DC33E67722D54A93F05F702C99E2A90136F93A@usahm208.amer.corp.eds.com>

 

Rather than "assume" the table is highly fragmented why not use the table statistics and the row count to calculate the necessary table size and then compare that to what the table is using. It would be a shame to waste a lot of time reorganizing the table only to have it take only a couple of megabytes less than what it is using now.  

Here is something I have posted a couple of times in the past  

Oracle Table Sizing Estimation Formula  

Abbreviations  

AVIL = Available space in block to hold rows
OBS  = Oracle block size
RS   = Row size
Ovhd = Fixed plus variable block overhead
TBR  = Total blocks required
 

Expected size = (( RS * number of rows) / AVIL ) * OBS) / K or M where K = 1024 and M = 1048576  

Figure RS as
 for varchar2 expected number of characters for column  for number where p = number of digits and s = 0 for positive and 1 for negative

     round((( length((p) + s) / 2)) + 1
 for date use 7
 + 1 byte per column in row
 + 3 byte row overhead per row
[Or use the dba_tables.avg_row_len value]
 

Figure number of bytes for block as
pctfree = decimal value of pctfree parameter * OBS  

The variable area is mostly made up of 23 bytes per initran area and 2 bytes per row for the row table entry. For 1 to 4 initrans I have calculated row overhead of 86 to 156 bytes so I just use a constant for this value. Try 113 to start.  

Figure AVIL as OBS - ovhd - pctfree  

Total bytes = number of expected rows * RS TBR = Total Bytes / AVIL
Expected Size = TBR * OBS / 1024 [for K]  

This is one way and it is fairly quick and works pretty well. The formula can be improved by adjusting the variable area size for the number of initrans and for the number of expected rows in the block, but using a constant works well for us.

• Mark D Powell -- Phone (313) 592-5148

  ---

        From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Khan, Muhammad S

	Sent: Friday, August 17, 2007 11:56 AM
	To: oracle-l_at_freelists.org
	Subject: Best bet for table defragmentation
	
	

	Its Oracle 9.2.0.7 on Solaris, we have a transaction table with

approximately 68 million rows. It is consisting of about 100GB space in the tablespace and it is assumed that it is highly fragmented. We definitely need some space at that tablespace and for that we were discussing the methods for defragmenting it and get some space back. One suggested method was export/import but that would take atleast 6 hours while another one was moving the table to another tablespace and rebuild the indexes which would not require any downtime.          

        Does any guru have any other suggestion regarding that? Or your input about the above methods as per knowledge and experience???          

        Thanks,          

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Aug 17 2007 - 12:19:45 CDT