In article <36bab57e.14982483_at_192.86.155.100>,
tkyte_at_us.oracle.com wrote:
> 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.
>
You are obviously using 8i Beta. My suggestion is to take your comments to
Oracle Corp., so that they may fix it in production version.
Mladen Gogala
-----------== Posted via Deja News, The Discussion Network ==----------
http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
Received on Sun Jan 31 1999 - 02:46:32 CST
