Yep, I agree that reorganizing table in order to get it more compact is pointless if you have more data coming in all the time anyway (most of big tables tend to grow).
But there is one (not very likely) case where rebuild might help to reuse "hidden" space - it's with freelist managed tables where you usually have quite small rows, but have inserted a bunch of very large rows for some reason - when a block is let say 50% full, PCTUSED is 40 and you try to insert a row, which *would* fill this block over PCTFREE limit, then this block is unlinked from free list and insert is attempted to next block, causing the space from previous block being lost even for small rows (how many unsuitable blocks are unlinked in that way, depends on few hidden parameters and number of configured freelists for the segment). But in practice, I haven't faced such kind of problem yet, but in tables with greatly varying row sizes, it can cause inefficient space usage problems. ASSM relieves this problem greatly, because it knows in general level how full blocks are and doesn't even try to insert a large row to a block with low free space. Tanel. ----- Original Message ----- To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Wednesday, January 07, 2004 11:19 PM > I guess some of the folks on the list are in a "playful" > mood today... > > The need for a table "reorganization" depends on how it is > used. > > The query you cite might be illuminating if the table in > question is mostly accessed by "full table scans", as it > seems to identify tables with large "gaps" due to deletions. > During an FTS, these gaps would still be "traversed", > resulting in what might turn out to be excessive I/O > required to accomplish the task. > > However, if the table in question is commonly accessed via > indexed lookups or scans, then reorganizing these tables to > "close these gaps" might well be a complete waste of time. > The reason being that the "table access by ROWID" action > that is the last step of table access via indexes does not > scan emptied blocks. It directly addresses populated blocks > in the table only. Thus, accessing rows in a table that is > 99.99% empty takes no longer than accessing rows in a table > that is 100% full, using this access method. Of course, if > someone wants to throw clustering factor in, then that > assertion starts to get a little squishy, but the fact > remains that the effort expended in reorging the table > clearly does not provide anything near an adequate "return > on investment". > > So, the knowledge of how the table is accessed is clearly > part of the answer. This puts the equation beyond the scope > of a simple query on the data dictionary, although I'm > pretty sure that the V$SEGMENT_STATISTICS view in Oracle9i > could provide some of the insight into the usage of the > table. > > So, if the table in question is typically accessed via full > table scan, the query you cited is useful. If the table in > question is rarely (if ever) accessed via full table scan > (or shouldn't be), then the query you cited should at least > be changed to indicate a much much much larger "blkdiff" > constant. Indeed, table reorgs in such circumstances wouuld > help so rarely that they aren't really worth worrying about. > > Hope this helps... > > -Tim > > > > 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/1 > > 00))) blkdiff From dba_tables > > Where blkdiff > 100; > > > > To determine reorganization need. > > > > What sql statement is used by others? > > > > Jolene > > > > -----Original Message----- > > Sent: Wednesday, January 07, 2004 2:25 PM > > To: Multiple recipients of list ORACLE-L > > > > > > I usually recommend Gospel by Jonathan for its > > completeness and a wide range of subjects. The book you > > mentioned is great for beginner as well. As for the > > number 42, I'll continue using it until this Saturday > > (1/10/2004) when it will become 43. Inflation is not as > > big as you think. PS: > > --- > > I was born on 1/10/1961, and that makes January 10th so > > special. I don't have to work on that great day, mostly > > because it's Saturday. > > > > On 01/07/2004 03:09:53 PM, "Thater, William" wrote: > > > Mladen Gogala scribbled on the wall in glitter crayon: > > > > > > > Lemme guess: you just started on your new job as a > > > > DBA? You are another person to which can only > > wholeheartedly recommend Jonathan's > > > > book. As for your questions, the answer is "42". > > > > > > actually, if she's just starting out, i'd recommend > > > Marlene, Rachel and Jim's book first, then Jonathan's. > > > > > > and are you sure it's not "57" now due to inflation? > > > > > > -- > > > Bill "Shrek" Thater ORACLE DBA > > > "I'm going to work my ticket if I can..." -- Gilwell > > > song [EMAIL PROTECTED] > > > > > ---------------------------------------------------------- > > > ------------ -- > > > Perfection of means and confusion of ends seem to > > characterize our age. - > > > Albert Einstein > > > -- > > > Please see the official ORACLE-L FAQ: > > > http://www.orafaq.net -- > > > Author: Thater, William > > > 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). > > > > -- > > Mladen Gogala > > Oracle DBA > > -- > > Please see the official ORACLE-L FAQ: > > http://www.orafaq.net -- > > Author: Mladen Gogala > > 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). -- > > Please see the official ORACLE-L FAQ: > > http://www.orafaq.net -- > > Author: Shrake, Jolene > > 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). > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Tim Gorman > 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). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder 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).
