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.
[EMAIL PROTECTED]
-----Original Message-----
Sent: Monday, March 24, 2003 2:30 PM
To: [EMAIL PROTECTED]
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 <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
03/24/2003 09:38 AM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
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.
- Do you do regular table reorgs? What benefit does that give you?
- Are there any indicators of when a table reorg would be beneficial?
- What sort of test would verify whether a table reorg changed
performance?
Dennis Williams
DBA, 40%OCP, 100% DBA
Lifetouch, Inc.
[EMAIL PROTECTED]
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: DENNIS WILLIAMS
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
