This is from Metalink
-----
The sort_multiblock_read_count parameter was introduced in Oracle8i. The
parameter is similiar to db_file_multiblock_read_count in that it specifies
the number of blocks to read at one time, in this case from a temporary
segment, when it is necessary to merge information previously written out to
a temporary segment. If the information needing to be sorted does not fit
into the memory allocated to SORT_AREA_SIZE, sections of data are written to
temporary
segments in the form of sorted runs. Once all the data has been partially
sorted to these runs, the runs are merged by reading pieces of them (i.e.
based on sort_multiblock_read_count) from the temporary segment into memory
to produce the final sorted output. If
SORT_AREA_SIZE is not large enough to merge all the runs at once (which is
likely to be the case as it was not large enough to perform the entire sort
in memory to begin with), subsets of the runs are merged in a number of
merge passes.
Increasing SORT_MULTIBLOCK_READ_COUNT forces a larger section of each run to
be read into memory during a merge pass. Needless to say, this must be
balanced with SORT_AREA_SIZE. This reduces the merge width, or number of
runs that can be merged in one merge pass, and may increase the number of
merge passes. Each merge pass produces an intermediate run on disk, a run
that contains all the data that was part of the runs that were just merged.
Any increase in I/O throughput obtained by increasing
SORT_MULTIBLOCK_READ_COUNT needs to be balanced with a possible
increase in total amount of I/O performed due to an increase in the number
of merge passes. So, you would not necessarily set
sort_multiblock_read_count to the number of blocks that comprise an extent.
----------
As usual - many words and no answer how to select the value of this
parameter. What memory is used inmerge phase? How it depends on
SORT_AREA_SIZE? How much memory really lost when we increase this parameter
from 1 to 2 let say etc. Aybody can clarify it? Oracle performance tuning
101 recommend using 1 or 2 whithout explanation (lost during development as
too complicated as also discussion of new aging algorithm for buffer cache
:-) )
Alex Hillman
-----Original Message-----
Sent: Friday, June 15, 2001 8:36 AM
To: Multiple recipients of list ORACLE-L
Because it is very possible and likely to have a sort area size much larger
than the max io of the OS. Most os can only do 64-128k in a single io, not
10-20m.
"Walking on water and developing software from a specification are easy if
both are frozen."
Christopher R. Spence
Oracle DBA
Fuelspot
-----Original Message-----
Sent: Thursday, June 14, 2001 4:57 PM
To: Multiple recipients of list ORACLE-L
Hi All,
Reading about the SORT_MULTIBLOCK_READ_COUNT init.ora
parameter, I am wondering why it should ever be
anything smaller or larger than the SORT_AREA_SIZE.
Has anyone changed the default setting of this
parameter for the better?
Johnson Job
__________________________________________________
Do You Yahoo!?
Get personalized email addresses from Yahoo! Mail - only $35
a year! http://personal.mail.yahoo.com/
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Johnson Poovathummoottil
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Christopher Spence
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Hillman, Alex
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
