First, with things like ASSM you no longer have to worry about that.
Second, why would you want to reorganize tables? Just because some
artificially determined number is not what your consultant says it
should be? Do you have any chained rows? Did you analyze table for
chained rows? What particular problem rears its ugly head if the tables
are not re-organized? Just keep your buffer cache hit ratio (BCHR) close
to 100 and you're fine (sorry folks, I couldn't resist).
The first rule of tuning is "if it ain't broken it doesn't need fixing".
Do you have any problems, like application working unacceptably slow or
you just want to do the right thing and reorganize some tables? One of
the things that my favorite DBA authors (Cary Millsap, Jonathan Lewis,
Marlene T., Rachel Carmichael and Gaja V., Chris Lawson and Tom Kyte )
say is that one should tune the applications, not the instance parameters.
Tables that have 100 blocks more then some consultant think they should,
are not candidates for a reorg. I've had empty tables which have had several
gigabytes of allocated space and were completely empty, in preparation of a
data load. Then the tapes arrived, load was done in minutes. Your consultant
would have shrunk my carefully allocated tables and thus cause load to go on
for hours because of the dynamic space management. Can't you do us all a favor
and just shoot that consultant? Oh yes, and get yourself a real DBA, or you
can always contract hotsos (http://www.hotsos.com). They will tune your system
so fast that the sparks will be flying. Ask them to fix your BCHR.
On 01/07/2004 03:49:33 PM, "Shrake, Jolene" 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
> > ----------------------------------------------------------------------
> > --
> > 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: William.Thater_at_carrier.utc.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).
> >
>
> --
> Mladen Gogala
> Oracle DBA
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Mladen Gogala
> INET: mladen_at_wangtrading.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).
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Shrake, Jolene
> INET: JEShrake_at_Pella.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).
>
--
Mladen Gogala
Oracle DBA
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Mladen Gogala
INET: mladen_at_wangtrading.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).
Received on Wed Jan 07 2004 - 15:29:26 CST
