| 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: Sort_multiblock_read_count
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
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: ListGuru_at_fatcity.com (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).
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: ListGuru_at_fatcity.com (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).
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: ListGuru_at_fatcity.com (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). Received on Fri Jun 15 2001 - 09:44:20 CDT
 |
 |