This is true. This is one of the (sound) arguments in
favour of raw devices. On raw, its Oracle that
controls the buffering etc and doesn't cross extent
boundaries. On a file system, you can have all the
nifty trendy pre-fetch algorithms in the world, but it
won't know what an 'extent' is.
Cheers
Connor
- Ron Thomas <rthomas_at_hypercom.com> wrote: >
> It would appear that extent size does play a role in
> this.
>
> OS: HPUX 11.0
> Oracle: 8.1.7 32-bit
> DB Block size 8k
>
> Setting multiblock read count to 1000 (v$parameter
> shows 128), then selecting against a table in an
> LMT:
>
> Case 1:
> Extent size: 128k
> max p3: 16
>
> Case 2:
> Extent size 4M
> max p3: 128
>
> This would make since if oracle does not issue a
> read across extents.
>
> Ron Thomas
> Hypercom, Inc
> rthomas_at_hypercom.com
>
>
>
>
>
>
> dm32840_at_oregon.We
>
>
> st.Sun.COM To:
> ORACLE-L_at_fatcity.com
>
> Sent by: cc:
>
>
> root_at_fatcity.com
> Subject: Re: Solaris -
> db_file_multiblock_read_count
>
>
>
>
>
>
> 11/19/01 03:40 PM
>
>
> Please respond to
>
>
> ORACLE-L
>
>
>
>
>
>
>
>
>
>
>
>
> Hi Jeff,
>
> I suspect that the size of the reads is based on the
> extent size within the
> table you're reading. I don't believe Oracle will
> issue a read across extents
> even if multiblock_read_count is high enough.
>
> The maximum limit for a read within Oracle has
> varied a lot with releases on
> Solaris, but in your case it appears that it will
> allow 1 MB as the maximum
> read (128 * 8K). That has been the largest value
> allowed to date in any
> release on Solaris.
>
> In testing we've done, the larger read sizes are
> more efficient because the
> application can issue a single read. You proposed
> altering
> multiblock_read_count to 24. I suspect (based on
> the above) that you then
> would get a read of 24 blocks followed by a read of
> 1 block (if my extent
> assumption is correct). That would require twice as
> many reads issued by the
> application and your performance would suffer
> somewhat.
>
> As Jared mentioned if you want to set it lower than
> 128, then set it to 25.
> But leaving it higher should have no resource
> implications. It might affect
> how the optimizer handles some queries (by biasing
> more towards full table
> scans) but in your example you're forcing a FTS
> anyway, so it wouldn't make
> any difference.
>
> Dave Miller
> Sun Microsystems, Inc.
>
> >Date: Mon, 19 Nov 2001 13:20:18 -0800
> >To: Multiple recipients of list ORACLE-L
> <ORACLE-L_at_fatcity.com>
> >X-Comment: Oracle RDBMS Community Forum
> >X-Sender: "Jeff Wiegard"
> <JWIEGARD_at_ngwmail.des.state.mn.us>
> >From: "Jeff Wiegard"
> <JWIEGARD_at_ngwmail.des.state.mn.us>
> >Subject: Solaris - db_file_multiblock_read_count
> >X-ListServer: v1.0g, build 70; ListGuru (c)
> 1996-2001 Bruce A. Bergman
> >Mime-Version: 1.0
> >Content-Transfer-Encoding: 7bit
> >
> >Hi.
> >
> >I was hoping for some confirmation here.
> >
> >I'm running 8.0.5 on Solaris 2.7, with block size
> set to 8192.
> >There is no 'maxphys' parameter in /etc/system.
> >
> >After some testing, I've decided to set the
> >db_file_multiblock_read_count = 24, based on the
> following:
> >
> >1. SQL> alter session set
> db_file_multiblock_read_count = 1000;
> >
> >2. select value from v$parameter where name =
> >'db_file_multiblock_read_count';
> >
> > This gave me a value = 128
> >
> >3. alter session set events '10046 trace name
> context forever,
> >level 8';
> >
> >4. select /*+ FULL(t) */ count(*) from
> sys.source$ t;
> >
> >The trace file gave me the following:
> >
> >WAIT #1: nam='db file scattered read' ela= 0 p1=1
> p2=660 p3=25
> >WAIT #1: nam='db file scattered read' ela= 0 p1=1
> p2=1167 p3=25
> >WAIT #1: nam='db file scattered read' ela= 0 p1=1
> p2=1207 p3=25
> >WAIT #1: nam='db file scattered read' ela= 0 p1=1
> p2=1272 p3=25
> >WAIT #1: nam='db file scattered read' ela= 0 p1=1
> p2=1312 p3=25
> >
> >So then setting db_file_multiblock_read_count to 24
> would be about
> >right ... right?
> >
> >Thanks,
> >
> >Jeff
> >
> >
> >--
> >Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> >--
> >Author: Jeff Wiegard
> > INET: JWIEGARD_at_ngwmail.des.state.mn.us
>
>
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> --
> Author: David Miller
> INET: dm32840_at_oregon.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).
=== message truncated ===
Connor McDonald
http://www.oracledba.co.uk (mirrored at
http://www.oradba.freeserve.co.uk)
"Some days you're the pigeon, some days you're the statue"
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).
Received on Tue Nov 20 2001 - 04:39:41 CST