Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: RE: Autoallocate vs Uniform extent performance

Re: RE: Autoallocate vs Uniform extent performance

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Fri, 04 Apr 2003 11:04:05 -0800
Message-ID: <F001.0057AAA2.20030404110405@fatcity.com>

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US