RE: multiple extents are OK, dagnabbit!

From: Jack C. Applewhite <japplewhite_at_inetprofit.com>
Date: Thu, 17 Jan 2002 16:16:10 -0800
Message-ID: <F001.003F35E2.20020117155539@fatcity.com>

<SPAN

class=890442623-17012002>Jerry,
<SPAN

class=890442623-17012002>
If
they want to pay you to reduce their extents, then let 'em! ;-) "A fool and his money are soon
parted."
<SPAN

class=890442623-17012002>
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.
<SPAN

class=890442623-17012002>
If my
anecdotal situation could be of any help, here it is.
<SPAN

class=890442623-17012002>
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).
<SPAN

class=890442623-17012002>
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.
<SPAN

class=890442623-17012002>
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.
<SPAN

class=890442623-17012002>
<SPAN

class=890442623-17012002>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.
<SPAN

class=890442623-17012002>
Hope
my experience helps convince your boneheaded clients. ;-)
<SPAN

class=890442623-17012002>
<SPAN

class=890442623-17012002>Jack

--------------------------------Jack C.

ApplewhiteDatabase Administrator/DeveloperOCP Oracle8 DBAiNetProfit, Inc.Austin,
Texaswww.iNetProfit.comjapplewhite_at_inetprofit.com(512)327-9068

-----Original Message-----From: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of Cunningham, GeraldSent: Thursday, January 17, 2002 3:46 PMTo: Multiple recipients of list ORACLE-LSubject: 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 Received on Thu Jan 17 2002 - 18:16:10 CST