| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: db_file_multiblock_read_count causing full scans to takelonger?
Well, that's what contributed to my confusion. In my trace files, which
are 9i so slightly different:
WAIT #1: nam='db file scattered read' ela= 21614 p1=6 p2=3979 p3=126
--> 126 blocks = 21614
WAIT #1: nam='db file scattered read' ela= 10724 p1=6 p2=4107 p3=126
WAIT #1: nam='db file scattered read' ela= 577 p1=6 p2=3979 p3=16 WAIT #1: nam='db file scattered read' ela= 1524 p1=6 p2=3995 p3=16 WAIT #1: nam='db file scattered read' ela= 916 p1=6 p2=4011 p3=16 WAIT #1: nam='db file scattered read' ela= 1022 p1=6 p2=4027 p3=16 WAIT #1: nam='db file scattered read' ela= 1095 p1=6 p2=4043 p3=16 WAIT #1: nam='db file scattered read' ela= 1026 p1=6 p2=4059 p3=16 WAIT #1: nam='db file scattered read' ela= 829 p1=6 p2=4075 p3=16 WAIT #1: nam='db file scattered read' ela= 826 p1=6 p2=4091 p3=14 -->126 blocks = 7815
Oracle requested more blocks in the single read (126) and got them. It just took longer than 8 requests for less blocks (16) per read. That's why I guessed that if the system constructed the larger response from smaller physical requests to the disk (SAN, whatever) then maybe that was the extra time. On my system, 128 is the max. But 16 is the fastest.
On Tue, 2006-12-19 at 13:43 -0700, Allen, Brandon wrote:
> 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
>
> ^^^^^^^^^^
-- http://www.freelists.org/webpage/oracle-lReceived on Tue Dec 19 2006 - 14:58:26 CST
 |
 |