Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Mailing Lists -> Oracle-L -> Re: RE: Autoallocate vs Uniform extent performance
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 scare you off, by the way,
so you don't have to do it. Check with
Connor which version of Oracle introduced
the modification that updates tsq$ just
once one the drop, rather than once
per extent as this does make a difference.
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk
The educated person is not the person
who can answer the questions, but the
person who can question the answers -- T. Schick Jr
One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html
____UK_______April 8th ____UK_______April 22nd ____Denmark__May 21-23rd ____USA_(FL)_May 2nd
Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html
____UK_(Manchester)_May ____Estonia___June (provisional) ____USA_(CA, TX)_August
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
> 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.
>
> SQL> set timing on
> SQL> create table lots_of_extents (x number)
> 2 storage (initial 1990M)
> 3 tablespace biggish;
>
> Table created.
>
> Elapsed: 00:00:49.06
>
> SQL> select count(*) from user_extents where segment_name
> = 'LOTS_OF_EXTENTS';
>
> COUNT(*)
> ----------
> 127423
>
> Elapsed: 00:00:04.01
>
> SQL> drop table lots_of_extents;
>
> Table dropped.
>
> Elapsed: 00:00:06.08
>
> Based on the above, I could drop a table with 1,000,000 extents in
> under 1 minute.
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis INET: jonathan_at_jlcomp.demon.co.uk 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: 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).Received on Fri Apr 04 2003 - 13:04:05 CST