max_IO_size equiv to scsi_maxphys???

[Todd McDaniel_1]

Okay I have a question, I want to know if the line in my subject is correct...

I saw this posting which referenced it...
http://forums1.itrc.hp.com/service/forums/questionanswer.do?threadId=440871

My dba was asking what my max_io_size was. Below is related to his question....


My current setting is:
# kmtune -q scsi_maxphys
Parameter ........... Current Dyn Planned ........... Module Version
===============================================
scsi_maxphys ........... 1048576 - 1048576



=======================
Here is a reference of what I am interested to know.

Description:
~~~~~~~~~~~~
DB_FILE_DIRECT_IO_COUNT is used to specify the number of blocks to be used
for IO operations done by backup, restore or direct path read and write
functions. The IO buffer size is a product of DB_FILE_DIRECT_IO_COUNT and
DB_BLOCK_SIZE. The IO buffer size cannot exceed max_IO_size for your
platform.

Assigning a high value to this parameter results in greater use of PGA or
SGA memory.
========================

[TwoProc · Accepted solution]

Usually, You've got to give a call to HP and ask them - b/c what the DBA wants to know is what is the size of one chunk of I/O going to be going to the storage system. On the XP1024 and XP512 we were told that that this was 128k so this meant that with a block size of 8k, I should set the db_file_multiblock_read_count in init.ora file to be 16 (8x16=128). On the FC-60's it is supposed to be 64k as per HP from way back when I put one of those in - so this setting was only 8. Now, with all that in mind - the only *supported* setting for Oracle APPS 11i is 8.

So, I'm using a setting that's based on 128K for HP's large arrays, and only 64 for the lesser things that HP sells.

Is this actually correct and right? While I've never got a differing opinion, I've gotten lots and lots of blank-stares and quiet phone time with support and other techs.

Oracle mentions this in the tuning guides as if it is an easy to obtain number, and HP will really not know what you're asking b/c asking "what is the maximum size of a single transfer of data" really *is* asking a lot. This is because of the obvious "what do you *mean* by a single transfer of data to the storage system" isn't that obvious and it really begs the question - "in what context" and "at what level"? Which is really hard to describe.

I think the engineers at Oracle should asking the engineers at HP (and vice-versa) and talk amongst themselves, and just put it in the recommended tuning guide- and settle it once and for all.

The problem is that along with a number of other Tuning DBA's from Oracle - we've arrived at a number that we believe *may* be correct, but don't know how to be really sure that we are correct.

Of course, I'm also always amused at the whole exercise ( which I also double check and play with and therefore must be propogating) b/c how do we *know* if we're even on a block boundary w/in the file system? It's highly conceivable that the 8k blocks that I have on the file system are off by 256 bytes due to the overhead of a file system, lvm, etc. So that everyone of "my" single block gets may actually be two - I just hope that next chunk I need came from the last oversplash of chunk that I unintentially just got.

Let's face it - it's not like on the old IBM days when you allocated a cylinder to a job and you *KNEW* where the boundaries of read were....

Anyways, I think what your DBA is asking is - "what is the size of largest single storage transmission if all data is requested sequentially? With that information he/she can properly set the db_file_multiblock_read_count at what they can only assume is optimal.

[Andrei Lica_1]

---
I think the engineers at Oracle should asking the engineers at HP (and vice-versa) and talk amongst themselves, and just put it in the recommended tuning guide- and settle it once and for all.
---

I subscribe to that - HP talk with Oracle.

See also :
http://docs.hp.com/en/B3921-90010/scsi_maxphys.5.html
http://www.ixora.com.au/q+a/io.htm
http://docs.hp.com/en/B3921-90010/st_large_recs.5.html
It seems on HP-UX 11.23 the I/O max size is 1MB - 1024*1024 ( scsi_maxphys )
It depends also on the type of storage : LVM, VxVM, raw or filesystem, etc

Usualy this matter ( I/O max size, stripe size ) is used by Oracle/DBA or application consultants to blame for low performance of their applications.