Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: db_file_multiblock_read_count causing full scans to takelonger?

RE: db_file_multiblock_read_count causing full scans to takelonger?

From: Allen, Brandon <Brandon.Allen_at_OneNeck.com>
Date: Tue, 19 Dec 2006 13:43:58 -0700
Message-ID: <04DDF147ED3A0D42B48A48A18D574C45059E2674@NT15.oneneck.corp>


According to the docs, if you set it higher than your OS' max io size, Oracle will automatically scale it down anyway:

http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14237/init params047.htm

"The maximum value is the operating system's maximum I/O size expressed as Oracle blocks ((max I/O size)/DB_BLOCK_SIZE). If you set this parameter to a value greater than the maximum, Oracle uses the maximum."

Just run a trace with WAITs and you will be able to see clearly how many blocks it is in fact requesting in a single read, e.g.:

WAIT #6: nam='db file scattered read' ela= 42076 file#=14 block#=1035659 blocks=126 obj#=461389 tim=23362933581533 WAIT #6: nam='db file scattered read' ela= 16616 file#=15 block#=976523 blocks=126 obj#=461389 tim=23362947739977 WAIT #6: nam='db file scattered read' ela= 27336 file#=16 block#=954123 blocks=126 obj#=461389 tim=23362964451801

 
^^^^^^^^^^




-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Kevin Lidh

My assumption is that if I set it higher, there is an attempt to make the multiple I/O requests and assemble them into a single I/O response and the overhead is the additional time.

Privileged/Confidential Information may be contained in this message or attachments hereto. Please advise immediately if you or your employer do not consent to Internet email for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of this company shall be understood as neither given nor endorsed by it.

--
http://www.freelists.org/webpage/oracle-l
Received on Tue Dec 19 2006 - 14:43:58 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US