Re: table reorganizations

Or in yet other words, is it worth spending two or three days (preparation + actual reorg - preferably on a sunday morning between 2 and 4am) on an inherently risky operation to shave 0.01% off response times ? Nobody will notice, or hardly. There is certainly much more to be gained checking queries which are run. Now, if you can identify with certainty that a critical query would significantly benefit from a reorg, do it.

HTH, SF

Rachel Carmichael wrote:
>
> The point of these questions is... why do you think you have to
> reorganize the tables?
>
> Define a "hole". How does it get created? Is it ever filled in (as in,
> do you ever insert rows)? Do you ever delete or update?
>
> Figure out WHY you want to do something before you try to solve it.
>
> or, in the same vein as the prior posts "if it ain't broke, don't fix
> it"
>
> --- "Shrake, Jolene" <JEShrake_at_Pella.com> wrote:
> > 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.
> >
> > 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
> > > william.thater_at_carrier.utc.com
> > >

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Stephane Faroult
  INET: sfaroult_at_oriole.com

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: ListGuru_at_fatcity.com (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).