Multi-Block read count
Norman Dunbar
my first question of 2002 ...
Quote from Guy Harrison in Oracle SQL High-Performance Tuning :
"On Windows NT/Win2k the maximum number of blocks that can be read
cannot exceed 128K for 32 bit versions,
On most versions of Unix when the I/O is through a filesystem, no more
than 8K can be read in one physical I/O,
On Unix when the datafiles are locvated on raw devices, the maximum I/O
rate varies from 64K (earlier versions of Solaris) to 1MB (latest
version of HP-UX."
So the question I have is, how do I find out what the maximum number of
Oracle blocks that I can read in one single OS I/O read is ?
Regards,
Norman.
------------------------------------------------------------------------
-----
Norman Dunbar EMail: Norman...@LFS.co.uk
Database/Unix administrator Phone: 0113 289 6265
Fax: 0113 289 3146
Lynx Financial Systems Ltd. URL: http://www.Lynx-FS.com
------------------------------------------------------------------------
-----
Nuno Souto
>
>So the question I have is, how do I find out what the maximum number of
>Oracle blocks that I can read in one single OS I/O read is ?
>
you ask the OS maker. if you're lucky, you get the real info. if
not, you get a load of marketing hype...
an alternative is to ask around in the NGs and see if you can bounce
into someone who has actually seen the source code.
another way is to pour through the doco and help files and get access
to developer information. usually that sort of stuff is available at
that level.
Cheers
Nuno Souto
nso...@optushome.com.au.nospam
Norman Dunbar
I didn't know of a Unix command to get the info for me, so it loooks
from your reply that there isn't one. Damn.
Not to worry.
Hope you are well away from the fires, my Auntie in Harbord couldn't see
the beach last week for smoke and she's on the cliff above it and 20 odd
miles away from the fires. Hope the b*st*rds who started it get strung
up by the sore bits.
Regrads,
Norman.
------------------------------------------------------------------------
-----
Norman Dunbar EMail: Norman...@LFS.co.uk
Database/Unix administrator Phone: 0113 289 6265
Fax: 0113 289 3146
Lynx Financial Systems Ltd. URL: http://www.Lynx-FS.com
------------------------------------------------------------------------
-----
-----Original Message-----
From: nso...@optushome.com.au.nospam (Nuno Souto)
[mailto:nso...@optushome.com.au.nospam]
Posted At: Wednesday, January 09, 2002 12:15 PM
Posted To: server
Conversation: Multi-Block read count
Subject: Re: Multi-Block read count
Norman Dunbar doodled thusly:
>
>So the question I have is, how do I find out what the maximum number of
>Oracle blocks that I can read in one single OS I/O read is ?
>
you ask the OS maker. if you're lucky, you get the real info. if
not, you get a load of marketing hype...
<SNIP>
Herman de Boer
it is platform dependent. The method I use in order to determine the
maximum in a database is simple, using sqlplus:
* alter session set db_file_multiblock_read_count = 10000;
* show parameters db_file_multiblock_read_count
On my NT laptop, with RDBMS 9.0.1, db_file_multiblock_read_count can
be set to 64, so with 8 Kb block size, at most 512 Kbyte can be read.
On our Linux server (still linux 2.2, RDBMS 8.1.6), also with 8 Kb
blocks, db_file_multiblock_read_count can be set to 128.
The largest figure I have seen was 512 with a block size of 8Kbyte, on
SGI, RDBMS 7.3. We double-checked that one with wait-events set on,
and truss/trace/strace (can't remember which one), and it really read
4 Mbyte per I/O.
Note that with full table/index/partition scans, extent boundaries are
never crossed. Strange enough, physically contiguous extents are not
coalesced.
Kind Regards,
Herman de Boer
sr. consultant
IT Consultancy Group bv
Mark D Powell
> Morning all,
>
> my first question of 2002 ...
>
> Quote from Guy Harrison in Oracle SQL High-Performance Tuning :
>
> "On Windows NT/Win2k the maximum number of blocks that can be read
> cannot exceed 128K for 32 bit versions,
> On most versions of Unix when the I/O is through a filesystem, no more
> than 8K can be read in one physical I/O,
> On Unix when the datafiles are locvated on raw devices, the maximum I/O
> rate varies from 64K (earlier versions of Solaris) to 1MB (latest
> version of HP-UX."
>
> So the question I have is, how do I find out what the maximum number of
> Oracle blocks that I can read in one single OS I/O read is ?
>
> Regards,
> Norman.
>
> ---------------------------------------
Norman, hopefully someone who has already sought this information out
will reply but if not part of the installation process usually
references you to an online document that contains platform specific
information. That might be a good place to try looking along with any
platform specific documentation delivered with the install disk for
your system. After that I think you could try the metalink forum
followed by an iTAR.
I be interested to see the response so hopefully someone will post it
here.
-- Mark D Powell --
Niall Litchfield
news:bde5777e.02010...@posting.google.com...
> hello Norman,
>
> it is platform dependent. The method I use in order to determine the
> maximum in a database is simple, using sqlplus:
> * alter session set db_file_multiblock_read_count = 10000;
> * show parameters db_file_multiblock_read_count
>
> On my NT laptop, with RDBMS 9.0.1, db_file_multiblock_read_count can
> be set to 64, so with 8 Kb block size, at most 512 Kbyte can be read.
snip
from a compaq server we have
SQL> alter session set db_file_multiblock_read_count = 10000;
Session altered.
SQL> show parameter multiblock;
NAME TYPE VALUE
------------------------------------ ------- --------------------
db_file_multiblock_read_count integer 128
hash_multiblock_io_count integer 0
sort_multiblock_read_count integer 2
suggesting that it might be hardware specific as well - this is an nt4
server. Nice trick though.
--
Niall Litchfield
Oracle DBA
Audit Commission UK
*****************************************
Please include version and platform
and SQL where applicable
It makes life easier and increases the
likelihood of a good answer
******************************************
Norman Dunbar
many thanks for that info, I shall go off and try it out and see what
happens here. I have Win2k and HP-UX both 10:20 and 11:00 to play with.
I'll also have a look in the docs (again !) and see what I may have
missed.
Regards,
Norman.
-------------------------------------
Norman Dunbar
Database/Unix administrator
Lynx Financial Systems Ltd.
mailto:Norman...@LFS.co.uk
Tel: 0113 289 6265
Fax: 0113 289 3146
URL: http://www.Lynx-FS.com
-------------------------------------
-----Original Message-----
From: h.de...@itcg.nl (Herman de Boer) [mailto:h.de...@itcg.nl]
Posted At: Wednesday, January 09, 2002 2:29 PM
Posted To: server
Conversation: Multi-Block read count
Subject: Re: Multi-Block read count
hello Norman,
it is platform dependent. The method I use in order to determine the
maximum in a database is simple, using sqlplus:
* alter session set db_file_multiblock_read_count = 10000;
* show parameters db_file_multiblock_read_count
<SNIP>
Norman Dunbar
following results (if anyone is interested !). The following results are
after 'ALTER SESSION SET DB_FILE_MULTIBLOCK_READ_COUNT = 10000;' :
Win2K Professional NTFS and Personal Oracle 8.1.7
Block size 8K
DB_FILE_MULTIBLOCK_READ_COUNT = 128
HP-UX 11:00 (32 bit) Oracle 8.1.7
Block size 8K
DB_FILE_MULTIBLOCK_READ_COUNT = 128
HP-UX 10:20 Oracle 7.3.4
Block size 4K
DB_FILE_MULTIBLOCK_READ_COUNT = 64
NT4 Oracle 7.3.4
Block size 8k
DB_FILE_MULTIBLOCK_READ_COUNT = 7
Digital Unix 4.0d Oracle 8.0.6
Block size 8k
DB_FILE_MULTIBLOCK_READ_COUNT = 128
Interestingly enough, it appears that Oracle has a different idea to how
many blocks can be read in one go from that mentioned in Guy Harrison's
book. In the meantime, Herman's tip is going in my file of very useful
information.
Hope the above is useful to someone other than me.
Connor McDonald
Its also worth nothing that the ceiling on multiblock read count is what
*Oracle* thinks that it will be able to achieve. Its quite possible
that you will not be able to get that due to other restrictions...
For example, Oracle on Solaris can get up to 1m, but unless you set
maxphys kernel parameter you won't get near that.
To see what you can actually get, set a 10046 trace at level 8 and run a
full scan on a big table. The p3 values in the trace file for the
scattered reads shows you what Oracle could achieve in reality.
hth
Connor
--
==============================
Connor McDonald
"Some days you're the pigeon, some days you're the statue..."
Bass Chorng
>
> For example, Oracle on Solaris can get up to 1m, but unless you set
> maxphys kernel parameter you won't get near that.
>
I think there is another important layer in between which also restricts
your milti block count - that is your Filesystem cache, unless you use
raw or Veritas quick IO.
I believe Solaris UFS has limitation of 8k cache IO size, for VxFS
the default is 256K but you can uptune it to 1M.
My production box has maxphys set to 8m, ( I guess for Solaris 2.7,
it only goes up to 1m regardless what you set it to ), but my
block_size * multi-block will not exceed 256K regardless what I set
my multi-block to. We are running VxFS with default setting.
I may be wrong on these theories because I obtain these thru my
own test and investigation + some readings. Your corrections welcome.
Herman de Boer
two additional related issues:
1. while the RDBMS requests to the OS kernel to read db_block_size
times
db_file_multiblock_read_count from some file/device at some offset,
the kernel can split this one I/O into multiples, depending on
file system settings etc. On a lower level (logical volumes) these
read calls can be split again, spreading over multiple devices.
At a physical level, I/O's can be split up inside the I/O
subsystem.
E.g. inside an EMC box, the I/O size is always 32 kbyte.
Bottom line: changing db_file_multiblock_read_count should always
be considered with the I/O confuration in mind.
2. When performing a full table scan, needed blocks in the buffer
cache can cause splits in the reading.
For instance, if the RDBMS needs blocks 11 to 20, each of the block
in the range is checked whether it exists in the buffer cache.
If block 15 is cached (the only one in the range), there will
be 2 reads: one for block 11 till 14, and one read for 16 - 20.
Nuno Souto
>> >
>> > it is platform dependent. The method I use in order to determine the
>> > maximum in a database is simple, using sqlplus:
>> > * alter session set db_file_multiblock_read_count = 10000;
>> > * show parameters db_file_multiblock_read_count
this may be accurate in some cases but it assumes that ORACLE and the
OS have been tuned to fully exploit the max IO size. that is not
necessarily always true, particularly the combination of both
products.
>
>Its also worth nothing that the ceiling on multiblock read count is what
>*Oracle* thinks that it will be able to achieve. Its quite possible
>that you will not be able to get that due to other restrictions...
exactly.
>
>For example, Oracle on Solaris can get up to 1m, but unless you set
>maxphys kernel parameter you won't get near that.
precisely. but you'll still see the "right" value in the parameter
list, if you use the method above of setting dfmrc=10K.
>
>To see what you can actually get, set a 10046 trace at level 8 and run a
>full scan on a big table. The p3 values in the trace file for the
>scattered reads shows you what Oracle could achieve in reality.
>
yup, that's the way to go to find out exactly what IO size can be
achieved.
And even with this I've had some surprise diffs between raw and cooked
file access...
Cheers
Nuno Souto
nso...@optushome.com.au.nospam
Svend Jensen
The trick with show parameter is nice, but I cant believe the figures I
get. At work on a IBM e-server 250 Win2K Oracle 8.1.7EE with hardware
4M raid controler and seven mirrors striped, I've set dfmrc to 32384,
set event 10046, level 12 and ran a large full scans. Acording to the
trace file 'scattered read' p3 (blocks) never got any higher than 128.
At 8k block that makes 1M.
Tried the show parameter at my home system, win2K, Oracle 8.1.7EE and I
got 256. Havent tried the trace yet.
But same OS, oracle version and different results - better on ide disk
than 160scsi. Hard to believe.
A way to make sure, restart database, set high dfmrc, run some large
full scans and then check v$filestat against some disk io OS monitor.
If the figures are close to one another, then the requested block reads
from oracle to the os-layer is OK. If it is in the nabourhood of 1:n,
then one oracle request for x blocks is turned into n times x OS disk io's.
I tried that on Sun solaris 6 some time ago. Solaris read only 7 blocks
of 8k in one io cycle. With default setup, no fiddling.
Somebody got a tool for win2k to measure OS disk io's like iostat?
/Svend Jensen
Jonathan Lewis
The second issue which you mention is extremely
important, as it can make a dramatic difference
to performance.
Oracle has taken note of this in Oracle 9 by
allowing you to collect "system statistics"
over a typical time period so that it can incorporate
I/O rates into its costing calculations. The interesting
thing to note is that it records three things for I/O
Average time for a single block read over the period.
Average time for a multi block read over the period.
Average ACTUAL size requested for a multiblock read
over the period.
--
Jonathan Lewis
http://www.jlcomp.demon.co.uk
Now running 3-day intensive seminars
http://www.jlcomp.demon.co.uk/seminar.html
Host to The Co-Operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
Author of:
Practical Oracle 8i: Building Efficient Databases
Herman de Boer wrote in message ...
>There is much to say about this topic. I think it's worth mentioning
>two additional related issues:
>
Herman de Boer
do you have - regarding this - pointers to relevant
v$-views, parameters or oracle doc?
> Oracle has taken note of this in Oracle 9 by
> allowing you to collect "system statistics"
> over a typical time period so that it can incorporate
> I/O rates into its costing calculations. The interesting
> thing to note is that it records three things for I/O
>
> Average time for a single block read over the period.
>
> Average time for a multi block read over the period.
>
> Average ACTUAL size requested for a multiblock read
> over the period.
Kind Regards,
Herman de Boer.
Jonathan Lewis
Look at the package dbms_stats, which
includes gather_, import_, export_, get_
and even set_, system_stats.
The data ends up in sys.aux_stats$
There are then two undocumented hints:
cpu_costing
tells Oracle to use estimates of CPU costing
if no system stats are set
nocpu_costing
tell oracle to ignore any system stats that
are set
--
Jonathan Lewis
http://www.jlcomp.demon.co.uk
Now running 3-day intensive seminars
http://www.jlcomp.demon.co.uk/seminar.html
Host to The Co-Operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
Author of:
Practical Oracle 8i: Building Efficient Databases
Herman de Boer wrote in message ...
Mark D Powell
> Norman Dunbar wrote:
>
> > Morning all,
> >
> > my first question of 2002 ...
> >
> > Quote from Guy Harrison in Oracle SQL High-Performance Tuning :
> >
> > "On Windows NT/Win2k the maximum number of blocks that can be read
> > cannot exceed 128K for 32 bit versions,
> > On most versions of Unix when the I/O is through a filesystem, no more
> > than 8K can be read in one physical I/O,
> > On Unix when the datafiles are locvated on raw devices, the maximum I/O
> > rate varies from 64K (earlier versions of Solaris) to 1MB (latest
> > version of HP-UX."
> >
> > So the question I have is, how do I find out what the maximum number of
> > Oracle blocks that I can read in one single OS I/O read is ?
> >
> > Regards,
> > Norman.
> >
> >
>
> get. At work on a IBM e-server 250 Win2K Oracle 8.1.7EE with hardware
> 4M raid controler and seven mirrors striped, I've set dfmrc to 32384,
> set event 10046, level 12 and ran a large full scans. Acording to the
> trace file 'scattered read' p3 (blocks) never got any higher than 128.
> At 8k block that makes 1M.
> Tried the show parameter at my home system, win2K, Oracle 8.1.7EE and I
> got 256. Havent tried the trace yet.
> But same OS, oracle version and different results - better on ide disk
> than 160scsi. Hard to believe.
>
> A way to make sure, restart database, set high dfmrc, run some large
> full scans and then check v$filestat against some disk io OS monitor.
> If the figures are close to one another, then the requested block reads
> from oracle to the os-layer is OK. If it is in the nabourhood of 1:n,
> then one oracle request for x blocks is turned into n times x OS disk io's.
>
> I tried that on Sun solaris 6 some time ago. Solaris read only 7 blocks
> of 8k in one io cycle. With default setup, no fiddling.
>
>
> Somebody got a tool for win2k to measure OS disk io's like iostat?
>
> /Svend Jensen
Svend, the 56K IO you found for Solaris is exactly what Ahmed Alomari
said was the largest IO Solaris performs in his book Oracle & UNIX
Performance Tuning from Prentice Hall. I bring this up only because I
though it was a pretty good book and it spent a lot of time discussing
IO options. But with version 9 it may well be a little dated.
Considering how hard it is to get this information from your System
Administrator, the Hardware vendor, or Oracle I think that if you have
an OLTP that because the size of the MBRC affects the CBO that it is
best to choose 64K for this setting. Larger settings cause the CBO to
underweigh indexes in favor of full table scans; this comment was
based on information in the student manual for Oracle Performance and
Tuning for version 8. Obviously a bigger setting would be in order
for a warehouse.
I find it amazing how complex it can be to determine the right value
for a simple setting with Oracle.
Connor McDonald
Oracle also has an internal maximum SSTIOMAX which varies from platform
to platform, but on most 8i+, the limitation is 1m.
hth
connor
Joel Garry
Idly wondering if a bunch of 128's through a 4M raid controller might
be pushed faster than a 256 through an IDE.
But with the mystery of Win2K, nothing would surprise me.
And with a system doing a bunch of things besides benchmarking a scan
of one table, it may not make a difference even if the ide can scan
bigger chunks. It may be very easy to design a benchmark that works
against a piece of hardware specialized for grabbing a bunch of
different things off an array of disks in the quickest order.
Parallelizing a bunch of slow things can be faster than having a
serial fast thing.
This is still all very interesting. Perhaps it might be even more so
if in these heterogenous tests the time to scan the table is posted.
And the time to update the table.
>
> A way to make sure, restart database, set high dfmrc, run some large
> full scans and then check v$filestat against some disk io OS monitor.
> If the figures are close to one another, then the requested block reads
> from oracle to the os-layer is OK. If it is in the nabourhood of 1:n,
> then one oracle request for x blocks is turned into n times x OS disk io's.
>
> I tried that on Sun solaris 6 some time ago. Solaris read only 7 blocks
> of 8k in one io cycle. With default setup, no fiddling.
>
>
> Somebody got a tool for win2k to measure OS disk io's like iostat?
>
> /Svend Jensen
jg
--