Re: Question: amount of data in 1 read constrained by os-limit?

From: Jeroen van Sluisdam <Jeroen.van.Sluisdam_at_vrijuit.nl>
Date: 28 Aug 1998 13:06:52 GMT
Message-ID: <35E6AB41.6D3F7E0A@vrijuit.nl>

Hi Jonathan,

Thanks for your reply. I have some questions concerning this:

1. can't find any docu about alter sesssion set events even on the web. What does it do and where can i find more info about using this
2. use_readv i did find and here is what they said at http://www.oreview.com/9704sys.htm :

The USE_READV parameter enables "scattered reads"; that is, it allows a list of blocks to be read into various memory locations in a single read call during full table scans. According to Oracle Support Link, testing on many platforms has shown that this method actually degrades performance because of the overhead caused by the kernel performing all of the memory pinning. In light of this, I recommend setting USE_READV to FALSE.

    What are you're experiences with this ?

3) Experimenting with your suggestion gives the following result on our

test db:
WAIT #1: nam='db file sequential read' ela= 2 p1=13 p2=13432 p3=1
WAIT #1: nam='db file scattered read' ela= 2 p1=13 p2=13433 p3=8
-    What does scattered read mean here ?
-     p3= 1 did lead me to lookup db_file_multi_block_read_count   in
v$parameter and to my suprise
      this said while the init file says 32. How this can be possible is
a very large question. So this could
     mean we're not reading 256K at all but only 64K

- Is it possbile that Oracle is undermining the initfile by adjusting it fitting to OS-limits ?

Thanks,

Jeroen

Jonathan Lewis wrote:

> With your setup you will probably be limited to 8 blocks,
> i.e. 64K. To prove this, try the following from SQL*Plus:
>
> alter session set events '10046 trace name context forever, level 8';
> select something from large_unindexed table;
> (to force a tablescan).
>
> In the resulting trace file you will find WAITs for
> db file sequential read
> with
> p1 = file number
> p2 = starting block number
> p3 = number of blocks requested by oracle in read.
>
> You should find p3 = 8 quite often.
>
> However, if you restart the database with the init.ora parameter:
>
> use_readv = true
>
> you may find that you can actually push your maximum
> up to 32 blocks per read.
>
>
>
> Jeroen van Sluisdam <Jeroen.van.Sluisdam_at_vrijuit.nl> wrote in article
> <35E66BC2.9E5913B_at_vrijuit.nl>...
> > Hi,
> >
> > Probably this question is more appropiate for a Unix-group, but
> > hopefully
> > somebody here knows the answer. I have an Oracle db with a 8K
> block-size
> >
> > and a multi_block_read_count of 32 so this would be 256K of data
> that
> > can be read in a single read. My problem is how do I determine if
> really
> >
> > 256K can be read or my OS constrains this to a certain amount and
> which
> > amount
> > this is (think about unix block sizes etc..)
> >
> > Data: HP-UX 10.01
> > Oracle 7.3.2.3
> > Multi-block_read_count 32
> > Block_size 8K
> >
> > Thanks,
> >
> > Jeroen
> >
> >
Received on Fri Aug 28 1998 - 08:06:52 CDT