RE: Full table scan difference

I cannot have a theory which make sense because I don't have all the information so let me try with the other kind, one that doesn't make sense. Instance is the same, machine is the same, the new table is organized probably
more optimal then the old one and, yet, a full table scan on the old one is faster. Aside from the Anjo's and Cary's suggestions, let me try doing some guesswork:

• You execute the initial select /*+ full(o) */ count(*) from original o; before creating the new table, the CTAS flushes everything from the disk controller cache and you end up with slower response.
• CTAS is internally doing insert, thereby locking the individual rows and you experience the slowness because of the delayed cleanup of the lock entries from the blocks of the new table.
• Not all disks are equivalent. Your new table may be placed in a bad neighborhood, so called "hotspot".

To prove or disprove the problems with the disk, controllers and alike, you can look into gv$filestat (timed_statistics must be on), do iostat, sar -d, sar -b or something of that nature. To prove/disprove the delayed cleanup you
should really dial M for 10046 or (ab)use Anjo's monitor.
>From the practical side, it doesn't matter. You were right, your boss is
wrong, you don't have to do table reorgs every Saturday, he has to change his hairdo into a pointy one.

-----Original Message-----
Sent: Friday, April 04, 2003 3:49 PM
To: Multiple recipients of list ORACLE-L

List - As I mentioned earlier, a new manager came from a site that regularly rebuilt tables. I protested the value of this, but his response was "well, at least you could test it." So on a test instance I've been doing a CTAS to create a reorganized copy of the table in the same tablespace (LMT with uniform extents, autoextend on). As expected, the number of blocks is very close.

   Then I've been doing a FTS (select count(*)) on both tables. Usually the time is very close. However, on one large table, the original is much faster -- 18-sec. vs. 27-sec. 21,349 blocks vs. 21179. Same plan. Does anyone have a theory for this discrepancy?

Dennis Williams
DBA, 40%OCP, 100% DBA
Lifetouch, Inc.
dwilliams_at_lifetouch.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: DENNIS WILLIAMS
  INET: DWILLIAMS_at_LIFETOUCH.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: Gogala, Mladen
  INET: MGogala_at_oxhp.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).