| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: RE: Autoallocate vs Uniform extent performance
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.
SQL> set timing on
SQL> create table lots_of_extents (x number)
2 storage (initial 1450m)
3 tablespace biggish;
Table created.
Elapsed: 00:01:48.02
SQL> select count(*) from user_extents where segment_name = 'LOTS_OF_EXTENTS'; COUNT(*)
61887
Elapsed: 00:00:08.01
SQL> drop table lots_of_extents;
Table dropped.
Elapsed: 00:00:16.09
SQL> create table lots_of_extents1 (x number) 2 tablespace biggish;
Table created.
Elapsed: 00:00:00.01
SQL> create table lots_of_extents2 (x number)
2 tablespace biggish;
Table created.
Elapsed: 00:00:00.00
SQL> begin
2 for i in 1..61886 loop
3 execute immediate 'alter table lots_of_extents1 allocate extent'; 4 execute immediate 'alter table lots_of_extents2 allocate extent';5 end loop;
PL/SQL procedure successfully completed.
Elapsed: 00:19:29.07
SQL> select count(*) from user_extents where segment_name='LOTS_OF_EXTENTS1';
COUNT(*)
61887
Elapsed: 00:00:04.09
SQL> select count(*) from user_extents where
segment_name='LOTS_OF_EXTENTS2';
COUNT(*)
61887
Elapsed: 00:00:04.08
SQL> drop table lots_of_extents1;
Table dropped.
Elapsed: 00:00:06.05
SQL> drop table lots_of_extents2;
Table dropped.
Elapsed: 00:00:05.07
It's actually somewhat faster but in case caching and the such has had an effect ...
SQL> create table lots_of_extents (x number)
2 storage (initial 1450m)
3 tablespace biggish;
Table created.
Elapsed: 00:01:13.00
SQL> drop table lots_of_extents;
Table dropped.
Elapsed: 00:00:05.06
So it's all about the same....
I have a little experiment in mind that could cause me to reconsider heaps of extents but it might have to wait a day or two.
Cheers
Richard
>
> 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
>
>
> ----- Original Message -----
> To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
> Sent: 04 April 2003 09:23
>
>
> > 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).
>
>
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Richard Foote INET: richard.foote_at_bigpond.com 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 Sat Apr 05 2003 - 06:43:36 CST
 |
 |