Interesting results. In the test data I saw it was doing export/import
activity to a prebuilt table with a large number of extents. I will
see if I can find the numbers.
Mike
"Niall Litchfield" <niall.litchfield_at_dial.pipex.com> wrote in message news:<3cf604dc$0$8511$cc9e4d1f_at_news.dial.pipex.com>...
> "Mike Ault" <mikerault_at_earthlink.net> wrote in message
> news:37fab3ab.0205291028.79e0b197_at_posting.google.com...
> > While I have no doubt that SELECT and DELETE may have little
> > difference, what about UPDATE? What about DROP TABLE? Was the DELETE a
> > conditional DELETE or a DELETE of all rows? Again, give us the full
> > story. Also, where the extents contiguous (i.e. created all at the
> > same time with no other segments) or did you make sure and distribute
> > them randomly across the tablespace datafiles involved?
>
>
> have a start for this, shown below. Things missing.
>
> select statement timing. (doesn't seem to be a point of discussion).
> distributing extents around the tablespace. done to some *ahem* extent with
> the table small_exts since large_exts existed.
>
> system single athlon 512mb ram, pair of striped 40gb ide disks as a single
> volume, winxp, Oracle 9.0.1.0.1, archive log mode.
>
> my summary. extent size doesn't seem to make any noticeable difference in
> these tests, with the possible exception of the update on the dictionary
> managed table with a small extent size - which is 20% or so longer.
>
> Things I can think of that could be done to build on this.
>
> Repeat the test on a system where the oracle files are correctly spread
> across multiple spindles.
> as mike says do this on a system with pre-existing segments.
> do this in larger volumes - say 10 million rows not 1 million.
> do this on indexed tables.
> obviously repeat on earlier versions of Oracle.
>
> anyway.
>
> SQL> @c:\scripts\create_tablespaces
> SQL> set echo on;
> SQL>
> SQL> create tablespace dict
> 2 datafile 'c:\oracle\oradata\dict.dbf' size 1025m;
>
> Tablespace created.
>
> SQL>
> SQL> create tablespace lmt_128
> 2 datafile 'c:\oracle\oradata\lmt_128.dbf' size 1025m
> 3 extent management local uniform size 128k;
>
> Tablespace created.
>
> SQL> spool off
>
> SQL> connect niall/niall
> Connected.
> SQL>
> SQL> set timing on
> SQL>
> SQL> create table large_exts
> 2 (id number,
> 3 description varchar2(255))
> 4 tablespace dict
> 5 storage (initial 100m next 100m pctincrease 0);
>
> Table created.
>
> Elapsed: 00:00:00.03
> SQL>
> SQL> create table small_exts
> 2 (id number,
> 3 description varchar2(255))
> 4 tablespace dict
> 5 storage (initial 128k next 128k pctincrease 0);
>
> Table created.
>
> Elapsed: 00:00:00.00
> SQL>
> SQL>
> SQL> create table lmt
> 2 (id number,
> 3 description varchar2(255))
> 4 tablespace lmt_128;
>
> Table created.
>
> Elapsed: 00:00:00.00
> SQL>
> SQL> spool off
>
> SQL> /*
> DOC>
> DOC>do 1m inserts first
> DOC>
> DOC>*/
> SQL>
> SQL> begin
> 2 for i in 1..1000000 loop
> 3 insert into large_exts values(i,rpad(to_char(i),255,'0'));
> 4 end loop;
> 5 commit;
> 6 end;
> 7 /
>
> PL/SQL procedure successfully completed.
>
> Elapsed: 00:02:18.03
> SQL>
> SQL> begin
> 2 for i in 1..1000000 loop
> 3 insert into small_exts values(i,rpad(to_char(i),255,'0'));
> 4 end loop;
> 5 commit;
> 6 end;
> 7 /
>
> PL/SQL procedure successfully completed.
>
> Elapsed: 00:02:15.00
> SQL>
> SQL>
> SQL> begin
> 2 for i in 1..1000000 loop
> 3 insert into lmt values(i,rpad(to_char(i),255,'0'));
> 4 end loop;
> 5 commit;
> 6 end;
> 7 /
>
> PL/SQL procedure successfully completed.
>
> Elapsed: 00:02:13.04
> SQL>
> SQL> /*
> DOC>
> DOC>do some updates
> DOC>
> DOC>*/
> SQL>
> SQL> update large_exts set id = id*2 where mod(id,5) = 0;
>
> 200000 rows updated.
>
> Elapsed: 00:00:36.00
> SQL>
> SQL> update small_exts set id = id*2 where mod(id,5) = 0;
>
> 200000 rows updated.
>
> Elapsed: 00:00:44.00
> SQL>
> SQL> update lmt set id = id*2 where mod(id,5) = 0;
>
> 200000 rows updated.
>
> Elapsed: 00:00:35.07
> SQL>
> SQL> /*
> DOC>
> DOC>now some deletes
> DOC>
> DOC>*/
> SQL>
> SQL>
> SQL> delete large_exts where mod(id,7) = 1;
>
> 142857 rows deleted.
>
> Elapsed: 00:00:47.03
> SQL> commit;
>
> Commit complete.
>
> Elapsed: 00:00:00.00
> SQL>
> SQL> delete small_exts where mod(id,7) = 1;
>
> 142857 rows deleted.
>
> Elapsed: 00:00:46.03
> SQL> commit;
>
> Commit complete.
>
> Elapsed: 00:00:00.00
> SQL>
> SQL> delete lmt where mod(id,7) = 1;
>
> 142857 rows deleted.
>
> Elapsed: 00:00:44.02
> SQL> commit;
>
> Commit complete.
>
> Elapsed: 00:00:00.00
> SQL>
> SQL> /*
> DOC>
> DOC>now drop the tables
> DOC>
> DOC>*/
> SQL>
> SQL> drop table large_exts;
>
> Table dropped.
>
> Elapsed: 00:00:00.06
> SQL> drop table small_exts;
>
> Table dropped.
>
> Elapsed: 00:00:00.01
> SQL> drop table lmt;
>
> Table dropped.
>
> Elapsed: 00:00:00.01
> SQL>
> SQL>
> SQL>
> SQL> spool off
Received on Thu May 30 2002 - 09:10:29 CDT
