Jerry,
If
they want to pay you to reduce their extents, then let 'em!
;-) "A fool and his money are soon
parted."
If
they employ you and want you to work weekends on this, then it's worth the
effort to educate them. I'm surprised an official Oracle white paper
didn't convince them. You may just be out of luck - adamant, entrenched
misinformation is sometimes difficult to dislodge.
If my
anecdotal situation could be of any help, here it is.
We
just moved our production 8.1.6.0.0 database to 8.1.7.2.5 on a new, but almost
identical server.
Old
server's OS was Windows 2000 Server with Service Pack 2 - new server, the
same.
Old
server had dual 550MHz Xeon CPUs - new server, the same.
Old
server had 2GB RAM - new server has 4GB RAM (of which Oracle can only use 2GB
anyway).
Old
server had eighteen 36GB drives - new server has twenty 36GB drives. In
both cases configured as JBOD (Just a Bunch Of Drives - no RAID, no mirroring,
no striping of any kind).
Our 6
documents tables each had (and has) its own drive
and each had (and has) about 2 million rows. The out-of-line
CLOB documents take up about 20-30GB for each table. Each of those
segments had between 20,000 and 30,000 1MB extents. For the year we
operated that way, we never had a problem with performance, even with a full
interMedia Text index on the CLOB column.
When
we moved the DB to 8.1.7.2.5, I pre-created those tables with 100MB
extents for the CLOB segments before I imported the documents. So, now
we're down to a few hundred extents per segment, instead of tens of
thousands. It hasn't made any noticeable difference on performance.
If numbers of extents really mattered, a 100 to 1 reduction would have made an
impact - it didn't.
What did make a difference was spreading the main
token table (DR$...$I) of the interMedia Text index across 3 drives, instead of
one. Distributing I/O has significant impact. Number of extents per
segment has close to zero impact. The Oracle white paper is dead-on
accurate.
Hope
my experience helps convince your boneheaded clients.
;-)
Jack
--------------------------------
Jack C.
Applewhite
Database Administrator/Developer
OCP Oracle8 DBA
iNetProfit,
Inc.
Austin,
Texas
www.iNetProfit.com
[EMAIL PROTECTED]
(512)327-9068
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Cunningham, Gerald
Sent: Thursday, January 17, 2002 3:46 PM
To: Multiple recipients of list ORACLE-L
Subject: multiple extents are OK, dagnabbit!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
