Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: multiblock read count question
Oracle 8.1.7 does some predictive read-ahead, and has at least one new hidden parameter relating to multiblock. It is likely that with a mbrc of 2, Oracle is actually doing a much larger read anyway, and only using the value 2 for comparing optimizer paths.
Two checks when you repeat the test:
Examine v$sysstats for parameters like
'blocks fetched by predictive read'
'block prefetched and discarded before use'.
Set event 10046 to level 8, and check the trace file for the WAITS for 'db file scattered read' the p3 value will probably be the same for both the 2-block and 32-block run. (You could just check v$session_wait for this session to get event, p1,p2,p3 a few dozen times as a cheaper way of checking).
-- Jonathan Lewis 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 See http://www.jlcomp.demon.co.uk/book_rev.html Seminars on getting the best out of Oracle See http://www.jlcomp.demon.co.uk/seminar.html Rob K wrote in message ...Received on Thu Jun 21 2001 - 15:30:59 CDT
>Why would I be getting the same response time on full scans if the
>multiblock read count is set to 2 or 32?
>
>System:
>Oracle 8.1.7 block size 8k
>
>Sun 4500
>2-5200 drives
>Veritas
>stripe = 4 drives with 256k interleave size = 1M stripe size.
>max i/o = 1M
>
>I change the user sessions multiblock read count to 2 and do a full scan on
>a table and sum up a column.
>It takes aprox 9 min to scan 60,043,128 rows 430080 blocks.
>
>I change the user sessions multiblock read count to 32 run the same query
>and it takes about the same time.
>