Re: T3's and forcedirectio questions

Hi Anjo and all,

Setting multiblock_read_count really depends on what you're trying to do. If you have a lot of full table scans (that are really necessary) and are doing parallel query, then setting it as high as possible can be very important. In tests I've done the difference between 64K and 1M for readsize can be 4x or more in performance. It will use less cpu and perform better. The reason for this is that fewer I/O's are issued by the application meaning it has to cross the user/kernel interface fewer times and runs much more efficiently. Even the difference between 512K and 1M for total read size can be 30% or more.

Note I've run this on systems that tend to be very large and are configured to have a lot of available bandwidth. If you're bandwidth-limited by having too few controllers or disks, larger sizes may not help as much.

This is also fairly independent of the stripe size. Even if you have a relatively small stripe size, issuing larger I/O's still will give you the increased efficiency mentioned above. Since each stripe will read the next sequential block on the disks within the stripe, you may actually get reads "for free", since the data may be in the track buffer on the disk when you ask for it.

One caveat is that larger multiblock_read_count values will tend to make the optimizer prefer FTS as Dave Morgan mentioned below.

When using filesystems (I tend to use raw), it can also be very important to increase the maxcontig value so that the filesystem will issue larger I/O's as necessary. The command
tunefs -a 128 <raw_device> # Allow 1 MB atomic I/O (128*8K) will increase it so that the filesystem will do readahead of up to 128 * 8K when it detects sequential access, like in FTS. The default for this is 128K, I believe. See the tunefs man page for more details.

Also with filesystems, you can have a problem if the datafiles were created in parallel or created with autoextend of small extent size since they will be fragmented on the filesystem and each read will only get a fragment at a time, reducing efficiency. The only way to fix that if you are using ufs is to make sure the datafiles are created one at a time or to back them up and restore them (again one at a time). This is a big pain but can affect FTS performance by 4-8x again.

Dave Morgan, I suspect that the reason you are seeing differences with "forcedirectio" on and off is that the files you were looking at are smaller than your available memory. If so, the ufs buffer cache will cache the data the first time you read it and subsequent reads will not require a physical I/O. "forcedirectio" bypasses the buffer cache (which eliminates a copy and the necessity of creating a buffer) but requires a physical I/O. Given you're doing FTS of large tables that exceed memory, going through the buffer cache just adds work since by the time you're done, the cache has been completely gone through and you've gotten no reuse on it. The idea is that Oracle buffers more "smartly", meaning you want to give memory to the SGA buffer cache, not keep it in the filesystem buffer cache. There certainly are some workloads where using the ufs buffer cache can be a big win. Perhaps yours is one.

So you need to look at what your real workload is and set multiblock_read_count and other such parameters appropriately. I believe multiblock_read_count is a dynamic parameter in 8i and beyond (I don't remember the version that made it so).

Dave Miller

>
>From: "Anjo Kolk" <anjo_at_oraperf.com>
>Subject: Re: T3's and forcedirectio questions

>I think it is 2-4 * bs *mbr.
>
>Anjo.
>
>----- Original Message -----
>To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
>Sent: Friday, July 19, 2002 11:58 PM
>
>
>> Oracle (in class materials) recommends
>> stripe size = block_size * multiblock_read_count.
>> For example, if stripe size = 64k and block_size = 8k, then
>> multiblock_read_count should be 8.
>>
>> Dennis Williams
>> DBA
>> Lifetouch, Inc.
>> dwilliams_at_lifetouch.com
>>
>> -----Original Message-----
>> Sent: Friday, July 19, 2002 4:31 PM
>> To: Multiple recipients of list ORACLE-L
>>
>>
>> a) multi block read count - its good to set it high to
>> get the benefit from fts when they occur, but you also
>> have to give oracle 'good' information. eg look at
>> the optimizer_... parms to tell oracle more
>> information about how good index caching is, and the
>> difference between the cost of the multiblock read and
>> a single block read (which in 8i oracle assumes to be
>> equal). In 9i, using system level stats helps this
>> considerably. Also, ensure that you are actually
>> getting the full 1m (typically you wont on file
>> systems unless you've tweaked them on creation). Some
>> info at http://www.oracledba.co.uk/tips/mbrc.htm
>>
>> b) direct IO - if you turn this one, then you have
>> freed up a chunk of memory from the file cache - which
>> means you may need to then allocate this to oracle (by
>> increasing the size of the buffer cache accordingly).
>> The theory is that the oracle cache is 'smarter' than
>> the unix cache because it respects things like extent
>> boundaries etc etc
>>
>> hth
>> connor
>>
>> --- Dave Morgan <dvmrgn_at_telusplanet.net> wrote: > Hi
>> All,
>> > I am attempting to improve performance in a shop
>> > that
>> > uses alot of T3 Storedge arrays to hold the oracle
>> > datafiles.
>> > I have already moved the redo logs onto to JBOD and
>> > tweaked
>> > ac couple other settings to make everyone happy but
>> > I still
>> > have some questions.
>> >
>> > 1. SUN best practises says set
>> > db_multiblock_read_count to
>> > a value that with the db block size equals 1M. This
>> > forces
>> > many foolish full table scans. I have fixed this by
>> > knocking
>> > the parameter down so the db does reads of 64kK but
>> > does anyone
>> > know if I can set the optimizer_index_* parameters
>> > (and to what values)
>> > so that I can set db_multiblock_read_count to match
>> > the arrays
>> > logical I/O setting?
>> >
>> > 2 Both Oracle and SUN recommend mounting the arrays
>> > with
>> > the forcedirectio option and then setting the oracle
>> > _filesystemio_options to setall. This causes a 50%
>> > performance
>> > hit measured by timing representative jobs. Does
>> > anyone know
>> > why? Even Steve Adams recommends these settings yet
>> > the numbers don't
>> > lie.
>> >
>> > A grabbag of other stuff since I can't post to the
>> > list from work.
>> >
>> > Thanks Kirti, for the answer to my listener question
>> > a couple
>> > of weeks ago
>> >
>> > Rachel, I have been running Linux/Oracle in
>> > production since 8.1.6.
>> > I hate to be a paranoid command line hack again but
>> > the distribution
>> > is irrelevant, Either understand linux, ie compile
>> > your own kernels
>> > and understand the system libraries, or get a
>> > sysadmin who does. Trying
>> > to keep up to certified combinations is hard enough
>> > for commercial
>> > UNIXes,
>> > for linux it's easier just to roll your own. So what
>> > if Oracle doesn't
>> > formally support it. This list will ;)
>> >
>> > 40% higher throughput than the same box running
>> > Oracle under Windblows,
>> > 30% less administrative work.
>> >
>> > Upgrading the OCP. No-one cares what version of the
>> > OCP you have. I took
>> > mine at 7.3.4 and since then all anyone has asked is
>> > do you have it. The
>> > only
>> > real value I found is it is helpful when applying
>> > for a TN visa to work
>> > in
>> > the US.
>> >
>> > my $0.013, otherwise known as $CAN 0.02
>> >
>> >
>> > Dave
>> >
>> > --
>> > Dave Morgan
>> > dvmrgn_at_telusplanet.net
>> > 403 399 2442
>> > --
>> > Please see the official ORACLE-L FAQ:
>> > http://www.orafaq.com
>> > --
>> > Author: Dave Morgan
>> > INET: dvmrgn_at_telusplanet.net
>> >
>> > Fat City Network Services -- (858) 538-5051 FAX:
>> > (858) 538-5051
>> > San Diego, California -- Public Internet
>> > access / Mailing Lists
>> >
>> --------------------------------------------------------------------
>> > To REMOVE yourself from this mailing list, send an
>> > E-Mail message
>> > to: ListGuru_at_fatcity.com (note EXACT spelling of
>> > 'ListGuru') and in
>> > the message BODY, include a line containing: UNSUB
>> > ORACLE-L
>> > (or the name of mailing list you want to be removed
>> > from). You may
>> > also send the HELP command for other information
>> > (like subscribing).
>>
>> =====
>> Connor McDonald
>> http://www.oracledba.co.uk
>> http://www.oaktable.net
>>
>> "Remember amateurs built the ark - Professionals built the Titanic"
>>
>> __________________________________________________
>> Do You Yahoo!?
>> Everything you'll ever need on one web page
>> from News and Sport to Email and Music Charts
>> http://uk.my.yahoo.com
>> --
>> Please see the official ORACLE-L FAQ: http://www.orafaq.com
>> --
>> Author: =?iso-8859-1?q?Connor=20McDonald?=
>> INET: hamcdc_at_yahoo.co.uk
>>
>> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
>> San Diego, California -- Public Internet access / Mailing Lists
>> --------------------------------------------------------------------
>> To REMOVE yourself from this mailing list, send an E-Mail message
>> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
>> the message BODY, include a line containing: UNSUB ORACLE-L
>> (or the name of mailing list you want to be removed from). You may
>> also send the HELP command for other information (like subscribing).
>> --
>> Please see the official ORACLE-L FAQ: http://www.orafaq.com
>> --
>> Author: DENNIS WILLIAMS
>> INET: DWILLIAMS_at_LIFETOUCH.COM
>>
>> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
>> San Diego, California -- Public Internet access / Mailing Lists
>> --------------------------------------------------------------------
>> To REMOVE yourself from this mailing list, send an E-Mail message
>> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
>> the message BODY, include a line containing: UNSUB ORACLE-L
>> (or the name of mailing list you want to be removed from). You may
>> also send the HELP command for other information (like subscribing).
>>
>
>
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>--
>Author: Anjo Kolk
> INET: anjo_at_oraperf.com
>
>Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
>San Diego, California -- Public Internet access / Mailing Lists
>--------------------------------------------------------------------
>To REMOVE yourself from this mailing list, send an E-Mail message
>to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
>the message BODY, include a line containing: UNSUB ORACLE-L
>(or the name of mailing list you want to be removed from). You may
>also send the HELP command for other information (like subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: David Miller
  INET: dm32840_at_bachelor.West.Sun.COM

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).