Re: Oracle Myths

Thanks for the info on the tests. You've shown more motivation than me. A couple of things(at least from my perspective)

I am not sure that the tablespace you created would be contiguous on disk. Especially seeing I am not Micro$oft literate I have no idea.

If I get a box I will probably do something like the following to test.

1. Create raw devices, or do something to make sure the space I was getting was contiguous.
2. Contiguous extents might as well be one extent as far as my reasoning goes, so I would just create a table with a small extent size, and have an attached index with a small extent size.

Procedure:
create a table with a small extents size, and an index on a couple of columns with small extents on that table.

Insert all your records.

I am pretty sure this will cause the index and table extents to be interleaved?

Do this for a few tables.

Create a few tables with large extents and detached indexes in a different tablspace with large extents

Run select on 1 table
run select on 2 tables concurrently
run select on 3 tables concurrently
...
Until disks are saturated.

Repeat with index scan

Keep your disk throughput, and timings.

Note 1: You can't use the same table, or the fact it's cached would be bad.
Note 2: Make sure the data isn't cached in O/S or DBMS, if you keep testing until disks are saturated though, this shouldn't be an issue Note 3: if tables being scanned concurrently are in different tablspaces, this would be ideal.

Will

<SNIP>
>
> 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.
> 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 - 11:05:53 CDT