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 ...


>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.

>