| 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: Oracle Myths
"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
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
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
Table created.
Elapsed: 00:00:00.00
SQL>
SQL> spool off
SQL> /* DOC> DOC>do 1m inserts first DOC> DOC>*/ SQL> SQL> begin
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
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
-- Niall Litchfield Oracle DBA Audit Commission UKReceived on Thu May 30 2002 - 05:54:20 CDT
 |
 |