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

Home -> Community -> Usenet -> c.d.o.server -> Re: Dropping Tables - Slow Performance

Re: Dropping Tables - Slow Performance

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Sat, 30 Jan 1999 16:52:19 -0000
Message-ID: <917715117.17370.0.nnrp-06.9e984b29@news.demon.co.uk>


Interesting,
but what happens if you have say 10 tables which start with a few extents each, then randomly grow so that extents are not contiguous ?

Jonathan Lewis
Yet another Oracle-related web site: www.jlcomp.demon.co.uk

Thomas Kyte wrote in message <36b6242e.5315933_at_192.86.155.100>...
>A copy of this was sent to Sybrand Bakker <postbus_at_sybrandb.demon.nl>
>(if that email address didn't require changing)
>On Sat, 30 Jan 1999 07:47:18 +0100, you wrote:
>
>>Hi Jeanne,
>>
>>One possible factor is the tables have been stored scattered in the
database,
>>ie consist of multiple extents. This will force Oracle to execute extent
>>management during the drop table command. Extent management has always
been
>>very expensive in terms of resource.
>
>thats correct (and thanks for "has been" as in past tense :)
>
>In Oracle8i, space management has been rewritten. There are 2 ways to
manage
>object space requests:
>
>- dictionary (the current way)
>- locally (the new way)
>
>locally managed tablespaces manage their own space (no recursive sql) in a
>bitmap. the results can be amazing, consider:
>
>SQL> create tablespace locally_managed
> 2 datafile 'c:\oracle\oradata\orcl\locally_managed.dbf' size 5m reuse
> 3 extent management local
> 4 /
>Tablespace created.
> real: 2914
>
>SQL> create tablespace dictionary_managed
> 2 datafile 'c:\oracle\oradata\orcl\dictionary_managed.dbf' size 5m reuse
> 3 extent management dictionary
> 4 /
>Tablespace created.
> real: 2884
>
>
>so, we have 2 tablespaces -- one managed locally and one managed in the
>dictionary. now we create tables with lots and lots of extents
>
>
>SQL> create table locally_managed_table ( x int )
> 2 storage ( initial 1k next 1k pctincrease 0 maxextents unlimited
> minextents 1000 )
> 3 tablespace locally_managed
> 4 /
>Table created.
> real: 130
>
>so, it took 1/10 of a second to creat a table with 1,000 extents the new
way...
>
>SQL> create table dictionary_managed_table ( x int )
> 2 storage ( initial 1k next 1k pctincrease 0 maxextents unlimited
> minextents 1000 )
> 3 tablespace dictionary_managed
> 4 /
>Table created.
> real: 33659
>
>
>but over 33 seconds the 'old' way....
>
>
>SQL> drop table locally_managed_table
> 2 /
>Table dropped.
> real: 521
>
>
>SQL> drop table dictionary_managed_table
> 2 /
>Table dropped.
> real: 12818
>
>
>Now look at dropping, 24 times longer to drop a dictionary managed object
then a
>locally managed object...
>
>
>
Received on Sat Jan 30 1999 - 10:52:19 CST

Original text of this message

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