> I'm surprised at these responses. I'm asking what sql statement most > people use to identify tables that need reorganization because of > "holes". > > We had an Oracle consultant here and he uses > > Select table_name, > blocks-((num_rows*avg_row_len/<block_size>)*(1+(pct_free/100))) blkdiff > From dba_tables > Where blkdiff > 100; > > To determine reorganization need. >
Hi Jolene You already received a number of replies why there are issues with using a general formula as above. IF a table is commonly accessed via a FTS AND, IF sufficient deletes without subsequent re-inserts (permanent table shrinkage, ouch, it's a male thing ;) or IF you've set a shocking PCTUSED which prevents inserts reclaiming deleted space, or IF you've set a shocking PCTFREE with no subsequent row size increase (etc) AND FTS access performance causes notable performance issues, you might have a case for a table re-org. The above conditions are not particularly common (perhaps a table containing future bookings for sleepovers at Michael Jackson's place ? ;) but if they do, consider the clustering factor of your most significant index access while you're at it, assuming there is one. The point I'll like to make are a couple of issues with your formula above. Firstly, it doesn't consider general block overhead details which means for largish tables with a sum of 100 block or more of overhead, the (rather expensive) re-org would achieve nothing. Secondly, it doesn't consider blocks above the HWM which could quite easily exceed the 100 mark depending on extent size. Again the re-org would result in a somewhat disappointing outcome. The formula above will potentially call for the re-org of *all* your larger tables for absolutely no benefit. Glad you asked the question .... Cheers Richard -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Richard Foote INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
