Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: FW: Tablespace and Table storage options

Re: FW: Tablespace and Table storage options

From: Alex Gorbachev <gorbyx_at_gmail.com>
Date: Thu, 12 Oct 2006 22:27:32 -0400
Message-ID: <c2213f680610121927w1b68f7a8xeb4172f65929a28a@mail.gmail.com>


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_at_landsbanki.is> 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
> --
> http://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

--
http://www.freelists.org/webpage/oracle-l
Received on Thu Oct 12 2006 - 21:27:32 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US