Re: FW: Tablespace and Table storage options

  • From: "Alex Gorbachev" <gorbyx@xxxxxxxxx>
  • To: Laimutis.Nedzinskas@xxxxxxxxxxxxx
  • Date: Thu, 12 Oct 2006 22:27:32 -0400

Having mbrc 8 limits your IO to 8 blocks max anyway. With 20 blocks extent
size you get 8+8+4 blocks (4 due to extent boundary). Sometimes you get less
(like 3) when block is in the cache.
Make your mbrc 128 and get big extents (Oracle will do lower if 128 is not
available). For big tables 16-64 MB wouldn't hurt. No idea what's the limit
on AIX+JFS2 but probably more than 64K.
With your 64K stripe size RAID5, chances are you were reading from one
spindle (or two) at a time. I.e. for single thread you are under-utilizing
you IO subsystem a lot.
Let us know your results if you get to test it with high mbrc and large
extent. you can use alter session to set your mbrc temporary.

On 10/11/06, Laimutis Nedzinskas <Laimutis.Nedzinskas@xxxxxxxxxxxxx> wrote:



>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 -- //www.freelists.org/webpage/oracle-l





--
Best regards,
Alex Gorbachev

The Pythian Group
Sr. Oracle DBA

http://www.pythian.com/blogs/author/alex/
http://blog.oracloid.com

Other related posts: