RE: table reorganizations

And to add to what Stephane said about the high water mark and chaining, another possible reason for reorging a table is for getting better clustering around a particular column(s) that are typically used as constraining criteria. By concentrating the like values in as few blocks as possible, you can dramatically reduce your IO, resulting in significant performance increases for queries constraining on those columns by which you ordered/clustered the data. Yes, this could also be done naturally without having to reorg by (1) using an IOT, or (2) have the table self clustered on that column, but in our case, neither was applicable or desired for various reasons. I think I've seen Kyte mention this as well, and the tuning guide may mention it.

But this is really a highly specific instance and not necessarily all that common, though I've worked on some DW's where data was loaded on a daily basis and this was a very common task that took place on more recent partitions every weekend. Parallel CTAS the data out, build the indexes in parallel, exchange the data back in, all taking very little time. You would literally drop queries going in by the index from minutes to seconds.

Regards,

Larry G. Elkins
elkinsl_at_flash.net
214.954.1781

> -----Original Message-----
> From: ml-errors_at_fatcity.com [mailto:ml-errors_at_fatcity.com]On Behalf Of
> Stephane Faroult
> Sent: Wednesday, January 07, 2004 2:14 PM
> To: Multiple recipients of list ORACLE-L
> Subject: Re: table reorganizations
>
>
> > "Shrake, Jolene" wrote:
> >
> > What SQL statement do you use to identify tables that need
> > reorganization?
> >
> > How do you identify tables that are used in full table scans? How
> > often do you run this query?
> >
> > Thanks,
> > Jolene
>
> Jolene,
>
> If your tables are reasonably sized initially, very few reasons may
> justify a reorganization (moreover, the mere size of some tables rules
> it out from the start ...). The only reasonable cases are substantial
> chaining, when there is no 'good' reason for that (ie if a row can fit
> into a block), which you will see if you collect statistics in
> DBA/USER_TABLES, and the other one is a high water mark in a table which
> is supposed to normally contain few rows. This one is harder to check,
> the easiest is probably to SET AUTOTRACE under SQL*Plus and run
> something like SELECT /*+ FULL */ COUNT(*) and check how many blocks
> (consistent gets + db block gets) were visited. If it's very high
> compared to what you would have normally expected, reorganizing may be
> necessary. But this only affects tables in which you can have massive
> deletes.
> Your second question gives the impression that you consider full table
> scans as a bad thing, which they are not necessarily. What is bad is
> what is much slower than it could be, and occurs too often for comfort.
> One of the places you can check is V$SQL; With Oracle 9.x, make sure
> that timed_statistics is set to TRUE et looks for statements with the
> highest elapsed_time. For older versions, buffer_gets is a good
> indicator.
>
> HTH,
>
> Stephane Faroult
> Oriole Software
> --
> 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).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Larry Elkins
  INET: elkinsl_at_flash.net

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).