Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: table reorganizations

Re: table reorganizations

From: Stephane Faroult <>
Date: Wed, 07 Jan 2004 12:14:25 -0800
Message-ID: <>

> "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


  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:
Author: Stephane Faroult

Fat City Network Services    -- 858-538-5051
San Diego, California        -- Mailing list and web hosting services
To REMOVE yourself from this mailing list, send an E-Mail message
to: (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 - 14:14:25 CST

Original text of this message