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