Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re:RE: Reorganizing tables

Re:RE: Reorganizing tables

From: <dgoulet_at_vicr.com>
Date: Mon, 24 Mar 2003 16:29:00 -0500
Message-Id: <24765.322941@fatcity.com>


Dennis,

    I don't reorg tables for performance reasons. That strategy has been proved faulty more than once. Actually there is data out on the web that I've come across that points in the opposite direction. Rebuilding indexes many times improves performance by making the index more effective. A badly unbalanced index is nobodies friend. Now if your reorging for space management issues that's a whole new ballgame. Having multiple objects in a tablespace with different sized extents does make filling the datafile messy to say the least, therefore an occasional raking of the sandbox for this reason is reasonable. Just don't expect a performance gain.

Dick Goulet

____________________Reply Separator____________________
Subject: RE: Reorganizing tables
Author: DENNIS WILLIAMS <DWILLIAMS_at_LIFETOUCH.COM> Date: 3/24/2003 12:53 PM

Thanks Jared, Rachel, Tom, Dick, Prakash, Ron

Excellent points. Very much appreciated. Unfortunately at this point people are asking "but have you tested it?". So I need to construct some type of test that will demonstrate how much effect a reorg will have. After I've answered that question, then I can move on to some of the other issues that you mention. I have joked that if the results are strongly positive, they won't see me much after that because I'll be touring the world selling my performance solution that never occurred to anyone else.

   Our test system is cloned from an RMAN backup of production so the tables should be close to production. I'm thinking of creating a new table and copying the contents of a production table into it and then tracing full-table scans and comparing the results.  

Jared - is there a way to estimate block-level fragmentation? Comparing the average row length with the number of blocks used?

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

-----Original Message-----
Sent: Monday, March 24, 2003 2:30 PM
To: ORACLE-L_at_fatcity.com
Cc: DENNIS WILLIAMS
Importance: High

Dennis,

Table reorgs can be useful when there is block level fragmentation, and you
do a lot of FTS. eg. you have a table with 1000 blocks and all the data will actually
fit in 500 blocks.

It can also be useful for tables that change infrequently and are normally always
sorted on the same keys. You can load them in sorted order, though at the moment I
can't seem to recall how to do that.

Probably other reasons as well.

That said, I almost never do it myself, as I don't seem to have any problems that
warrant it.

Our previous SAP BASIS admin was *real* big on reorgs, though she wanted to do a whole tablespace at a time. I talked her out of it, or at least, she sensed
that I wasn't very cooperative on that subject. :)

Jared

DENNIS WILLIAMS <DWILLIAMS_at_LIFETOUCH.COM> Sent by: root_at_fatcity.com
 03/24/2003 09:38 AM
 Please respond to ORACLE-L  

        To:     Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
        cc: 
        Subject:        Reorganizing tables


We have a new manager, and at his last employer the DBAs reorganize Oracle tables on a regular basis. I don't reorg tables on a regular basis. He is lobbying us to investigate this and test whether it would or wouldn't increase performance. We are on Oracle 8.1.6, Compaq Tru64, all tables are LMT with uniform extents. This is an OLTP system, but the users continue to
add reports and the reports do quite a few full table scans. The reports are
probably the critical performance issue at this time.

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: 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 Received on Mon Mar 24 2003 - 15:29:00 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US