| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Dropping Tables - Slow Performance
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:
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...
>One command to verify this:
>select segment_name, bytes, extents
>from user_segments ;
>(assuming you log in as owner of the tables)
>If all the tables are in one tablespace, it would be possibly faster to
>export this user (this doesn't export the create user command, beware), drop
>the user and recreate it from scratch as this will defragment the tablespace
>at the same time.
>
>Hth,
>
>Sybrand Bakker, Oracle DBA
>
>Jeanne wrote:
>
>> I am running scripts that drop numerous tables in a database for a
>> software upgrade. It is taking up to a minute just to drop a table. Our
>> DBA just shrugs and goes and gets coffee. None of the tables has any
>> constraints, other than explicitly created indexes, and we don't maintain
>> histograms.
>> Anyone have any ideas on where I could look?
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA
--
http://govt.us.oracle.com/ -- downloadable utilities
Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Sat Jan 30 1999 - 09:31:17 CST
 |
 |