FW: Tablespace and Table storage options

>Now, I was going to say that one thing that may be affecting your
timings is the (relatively small) size of your extents.

Yes, I think so too. However my initial (very raw) tests indicate that having adjacent extents (by setting some higher initial/next values) helps even for relatively small extents. As for mbrc then it is set to 8. Well, it is somewhat smaller than usual but this is not my fault. On the other hand, it shows things like this (note apparent seek on the third read):

WAIT #1: nam='db file scattered read' ela= 474 p1=13 p2=35889 p3=8 WAIT #1: nam='db file scattered read' ela= 445 p1=13 p2=35897 p3=8 WAIT #1: nam='db file scattered read' ela= 2744 p1=13 p2=35905 p3=4

>What plaform and O/S? What storage platform? Is this raw, or
filesystem? If filesystem, is it buffered or direct I/O?

Platform: 9i, AIX 5.3, SAN storage, jsf2 file system with options=cio, oracle filesystemio_option=async.
I was told that stripe size is 64K. RAID is 5 but for read only mode it can do.

Here are results of tests I made:

create table
tablespace data
storage (initial 160K next 160K)
as select * from
/
Elapsed: 00:01:31.12

Statistics

        327  recursive calls
          0  db block gets
      95513  consistent gets
      95010  physical reads
          0  redo size
        200  bytes sent via SQL*Net to client
       3285  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          1  rows processed

create table
tablespace data
storage (initial 2M next 2M)
as select * from
/

Elapsed: 00:00:42.98

Statistics

        327  recursive calls
          0  db block gets
      95488  consistent gets
      94984  physical reads
          0  redo size
        200  bytes sent via SQL*Net to client
       3287  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          1  rows processed

Calculations show about 2x better throughput for 2Mb adjacent extents, about 17 and 8 mb/sec respectively.
As for the original tests then the table I used is a real production table (tests were made on the read only standby however :-)) This table can have much more randomly allocated 160K extents and it can also have all kinds of row chaining/migration which may have the effect that throughput on that table is just 2mb/sec.

Fyrirvari/Disclaimer
http://www.landsbanki.is/disclaimer

--
http://www.freelists.org/webpage/oracle-l