RE: multiple extents are OK, dagnabbit!

Hello,

My 2 cents:

It does make a difference to reorg, esp. when done thoughtfully, with a specific goal in mind. For example, if you have a order_log table that started with first extent 1MB and next extent 1MB, and this table has grown in size to say, 10 million rows (business is good), you would have hundreds of extents, and each of those new extents took some time to extend that would have been avoided if you had started with 100MB first and 25MB next. Indexes take a far worse performance hit. You also expose yourself to other issues (fragmentation, full table scans (yuck) run slower, table drops run slower, more extent overhead, recovery time runs slower, risk of failure increases).

Thank you,

Paul Sherman
DBA
voice - 781-501-4143 (office)
fax - 781-278-8341 (office)
email - psherman_at_elcom.com

-----Original Message-----
Sent: Friday, January 18, 2002 8:41 AM
To: Multiple recipients of list ORACLE-L

Jerry,

Tell the client that you will be HAPPY to reorg the tables and indexes over 10 extents. It will cost X dollars and take Y hours of downtime/slowdown. Insert inappropriately huge numbers into X and Y. It's amazing how quickly people will change their minds when you talk hours and dollars.

Some people don't see the light until they are on fire.

Jerry Whittle
ACIFICS DBA
NCI Information Systems Inc.
jerome.whittle_at_scott.af.mil
618-622-4145

> -----Original Message-----
> From: Cunningham, Gerald [SMTP:Gerald.Cunningham_at_usi.net]
>
> Hi there -
>
> I'm trying to convince a client that multiple extents for a table will
> not hurt their performance. It's a PeopleSoft app, and PeopleSoft is
> telling them that they need to reorg any object with greater than 10
> extents (even indexes). This Oracle 8.1.6.
>
> I've referenced the "How to Stop Defragmenting and Start Living: The
> Definitive Word on Fragmentation" white paper by Bhaskar Himatsingka
> and Juan Loaiza of Oracle. That didn't convince them. I tried to
> explain that Oracle reads BUFFERS and not extents, etc., but that
> didn't work.
>
> I'm about to open a vein.
>
> Does anybody have any references that they can point me to? (Something
> from PeopleSoft would be ideal, though I would be suprised if it
> existed.) I read a rant on somebody's web site a while back that was
> really good, but alas I cannot remember his name or URL. (I blame my
> kids for my failing memory).
>
>
> Thanks!
>
> - Jerry

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Whittle Jerome Contr NCI
  INET: Jerome.Whittle_at_scott.af.mil

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
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.com
-- 
Author: Sherman, Paul R.
  INET: PSherman_at_elcom.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
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).