Hi Jonathan,
OK let's give it a go (note that I'm now doing this at home on my not quite
so powerful PC with Best of Bowie playing on the CD so it's just me having a
play)
SQL create tablespace biggish
2 datafile 'c:\bowie\biggish01.dbf' size 3000m
3 uniform size 24k;
Tablespace created.
Hi Pete,
Stop using my favourite answer :)
I'm not suggesting this is particularly scientific but here's a quick
test on dropping a moderate number of extents (9.2 on XP):
SQL create tablespace biggish
2 datafile 'c:\bowie\biggish01.dbf' size 2000M
3 uniform size 16K;
Tablespace created.
I count that as 127 thousand extents dropped in 6seconds.
The CREATE TABLE + EXTENTS took 49seconds.
No, I am not trying to draw any conclusions, just making an
observation. I am comfortable with thousands of extents
but wouldn't look at a million extents.
Hemant
At 12:23 AM 04-04-03 -0800, you
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
as has been pointed out to me privately (and it's really okay to
correct me publicly!), it is not delete that would release blocks but
truncate or drop.
Resolution: do not post before at least two cups of coffee.
My apologies to anyone I might have confused.
--- Rachel Carmichael [EMAIL
You've also been to Scott Gosset's classes in NYC in the year 2001? He had
a whole series of those classes. It was a very rare event indeed, given that
he
lives in Peoria, Il. I was very, very impressed with him and his knowledge.
He warmly recommended Steve Adams book, which has really surprised
I think you ought to refine your test:
Create two tables at one extent each,
then alternately allocate one extent
to each table until you get to a very
large number of extents. THEN try
dropping one of them.
Remember to set tablespace quotas
for the user creating the table.
It still won't
Actually it was Bethesda a few years earlier but yes, Scott Gossett's
classes. He is indeed impressive.
I took Scott Heisey's class on 8i new features in Chicago in '96 or
'97. Also a great instructor.
I've never taken a class with Cary (I'm hoping to kinda change that at
the Hotsos Symposium
o.com[EMAIL PROTECTED]
Sent by: cc:
[EMAIL PROTECTED] Subject: Re: Autoallocate vs Uniform
extent
[EMAIL PROTECTED] wrote:
These guys are why I think I know nothing!
yes my goddess, what ever you say my goddess.;-)
you are the reason i think i don't know anything. so guess how far
behind those guys i am.;-)
--
--
Bill Shrek Thater ORACLE DBA
[EMAIL PROTECTED]
] Subject: Re:
Autoallocate vs Uniform extent performance
I don't believe that was the case. auto and uniform
in all of the (admittedly rudimentary and subjective)
tests I've done appear the same in terms of
performance.
I prefer uniform purely for the reasons of:
- more thorough elimination of fragmentation
- predictability of next extent sizes
hth
Totally agree with Connor. Just to add a comment to
his note.
A usage model recommended for UNIFORM vs. AUTOALLOCATE
follows:
If you know the data volume and growth of your
segments and they are predictable, then use UNIFORM.
If you are completely in the dark with:
1) How much data is going
I totally agree Gaja.
I support a SAP BW system and they create tables with a 100 of partitions
and only load 24 of them. With autoallocate, most of them are small (64k)
and space is not wasted. If they do decide to load them up, I'm still safe
because the extent size increase as the object
Just a general question to everyone (and one I've asked a few times
before in different forums).
If we're talking LMT, how many extents are too many ?
Assuming no quotas (which does introduce some known issues) at what
point do you say that your standard uniform size of 64K has generated
too
Title: RE: Autoallocate vs Uniform extent performance
I get the impression from what I've read on this list and elsewhere that in general one shouldn't worry about the number of extents in a table, at least for full table scans. (It can become a problem when dropping a table - at least
BD
Did you expect any other answer than it depends? :)
Seriously, we've seen a few thousand extents without any problems in later
releases. We've also heard of customers not noticing things had gone
horribly adrift from what they thought and end up with hundreds of thousands
of extents. No
17 matches
Mail list logo
