Re: Dropping Tables - Slow Performance

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Sun, 31 Jan 1999 01:45:34 GMT
Message-ID: <36bab57e.14982483@192.86.155.100>

A copy of this was sent to "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> (if that email address didn't require changing) On Sat, 30 Jan 1999 16:52:19 -0000, you wrote:

>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 ?
>

Ok, ran a test. Here is the locally managed script i ran

SQL> drop tablespace locally_managed including contents   2 /
Tablespace dropped.

SQL> create tablespace locally_managed
  2 datafile 'c:\oracle\oradata\orcl\locally_managed.dbf' size 20m reuse   3 extent management local uniform size 1k   4 /
Tablespace created.

SQL> set timing on

SQL> create table locally_managed_table1 ( x char(2000) )   2 storage ( initial 1k next 1k pctincrease 0 maxextents unlimited minextents 1 )
  3 tablespace locally_managed
  4 /
Table created.
 real: 341  

SQL> create table locally_managed_table2 ( x char(2000) )   2 storage ( initial 1k next 1k pctincrease 0 maxextents unlimited minextents 1 )
  3 tablespace locally_managed
  4 /
Table created.
 real: 80

SQL> begin

  2  	     for i in 1 .. 999 loop
  3  		     execute immediate
  4  		     'alter table locally_managed_table1 allocate extent';
  5  		     execute immediate
  6  		     'alter table locally_managed_table2 allocate extent';
  7  	     end loop;

  8 end;
  9 /
PL/SQL procedure successfully completed.  real: 58124

SQL> select segment_name, extents
  2 from user_segments
  3 where segment_name like 'LOCALLY_MANAGED_TABLE_'   4 /

SEGMENT_NAME                 EXTENTS                                            
------------------------- ----------                                            
LOCALLY_MANAGED_TABLE1          1000                                            
LOCALLY_MANAGED_TABLE2          1000                                            

SQL> select * from
  2 (
  3 select segment_name, block_id
  4 from dba_extents
  5 where tablespace_name = 'LOCALLY_MANAGED'

  6   order by block_id                         <<<----- check that out as well 

  7 )
  8 where rownum < 11
  9 /

SEGMENT_NAME                BLOCK_ID                                            
------------------------- ----------                                            
LOCALLY_MANAGED_TABLE1            33                                            
LOCALLY_MANAGED_TABLE2            35                                            
LOCALLY_MANAGED_TABLE1            37                                            
LOCALLY_MANAGED_TABLE2            39                                            
LOCALLY_MANAGED_TABLE1            41                                            
LOCALLY_MANAGED_TABLE2            43                                            
LOCALLY_MANAGED_TABLE1            45                                            
LOCALLY_MANAGED_TABLE2            47                                            
LOCALLY_MANAGED_TABLE1            49                                            
LOCALLY_MANAGED_TABLE2            51                                            

10 rows selected.
 real: 4496

SQL> drop table locally_managed_table1
  2 /
Table dropped.
 real: 8372

SQL> drop table locally_managed_table2
  2 /
Table dropped.
 real: 8052

(how about that, order by in subqueries that work with rownum and yes, you can create a view with an order by as well..... also dynamic sql easy enough to use pl/sql as a good scripting language...)

to summarize:

• allocate 2*999 extents 58 seconds
• drop tables 8.3 and 8.0 seconds

for a dictionary managed tablespace (change local to dictionary in the script above to get the script i used to test)

• allocate 2*999 extents 107 seconds
• drop tables 14 and 19 seconds

so, not as dramatic but still much faster. also notice the allocations go faster as well.... ymmv of course.

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

[snip]  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

--
http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

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 - 19:45:34 CST