Rachel,
At a recent past job, under 8.1.6 on Win2k we had tables with out-of-line
CLOB segments of 30,000 extents (1MB each). Every month we dropped one to
make room for another (6 months of CLOB documents online). It always just
took a few seconds for the drop. These were in DMTs.
Later we switched servers and I changed to LMTs of 100MB Uniform Extents
for the CLOB segments. Going from 30,000 to 300 extents for those hulks
made no noticeable difference in query or interMedia indexing performance,
nor did it noticeably change the time it took to drop the tables.
Here at AISD, our student information database (SASI, for those in
Education who know this 3rd party app) has over 47,000 tables and 70,000
indexes (typical abysmal design for a 3rd party app, eh?), many of them
empty or with very few rows. A few months ago I rebuilt it under 8.1.7.4.6
(Win2k - it was previously at 8.1.7.0.0) with LMTs of 8KB Uniform Extents
to save space. Surprisingly, only 40 or so segments have over 1000
extents. One, a consolidated Student table, has a little over 10,000
extents. We've noticed no problem at all with performance, etc.
I've not been concerned about extent counts for several years now, and I've
seen nothing convincing that I should be. Maybe I've just not hit the
situation where it matters. That is not to say that extents don't matter,
but it's only if they obey the stupid directives of uninformed duhvelopers,
such as those of our 3rd party Financials system, where they used
PctIncrease of 50. Like children and dogs, there are no bad extents, just
bad designers. ;-)
Jack C. Applewhite
Database Administrator
Austin Independent School District
Austin, Texas
512.414.9715 (wk)
512.935.5929 (pager)
[EMAIL PROTECTED]
Rachel Carmichael
<[EMAIL PROTECTED] To: Multiple recipients of list
ORACLE-L
o.com> <[EMAIL PROTECTED]>
Sent by: cc:
[EMAIL PROTECTED] Subject: Re: Autoallocate vs Uniform
extent performance
04/04/2003 07:01
AM
Please respond to
ORACLE-L
rumor hath it (as I've never actually had an object hit that high a
number) that when you exceed 4K extents it's time to resize. This came
from one of the instructors in Oracle University, one who is well-known
to actually have more than a clue. He said this at the Data Internals
class, before 9i was released.
I have not seen his test results but.... I do know that tests done with
DMTs have shown that large numbers of extents (I believe Kevin Loney
tested with 60K extents, and I vaguely remember a conversation with
Cary where he said he had also tested large numbers) are a problem
during operations that empty a lot of extents (think large deletes)
because of thrashing on FET$ and UET$. Since an LMT doesn't access
those tables by design, I would think that that problem goes away.
--
Author: Rachel Carmichael
INET: [EMAIL PROTECTED]
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author:
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).
