Re: Increasing Value of SORT_AREA_SIZE Even if Few Disk Sorts in v8.0.6

You almost answered your question yourself. If a sort does not fit in SORT_AREA_SIZE, it is written to TEMP. Then when SORT_AREA_SIZE is filled up again, it writes to TEMP again. At the end the parts have to be merged (and written to TEMP).

It's obvious writing to TEMP slows down the sort. By regular querying V$SORT_USAGE you can see how much TEMP is used by each session currently using TEMP.
That should give an impression how much disk sorts can be prevented by a larger SORT_AREA_SIZE.

Can't give any advice on SORT_DIRECT_WRITES setting. I thought TEMP was already bypassed from the buffer cache but the description of this parameter in the Reference Guide makes me doubt that. In V8.1 this parametes is obsolete, maby because it did not worked so good (just a guess).

SAP BASIS Consultant <basis_consultant_at_hotmail.com> schreef in berichtnieuws dd2036f3.0206251135.10c73a2c_at_posting.google.com...
| Hello,
|
| I am monitoring an SAP R/3 system using Oracle v8.0.6 on NTv4.0.
| (No insults please !..it will be upgraded soon), which is mainly OLTP but
| executes, for an SAP system, a great many SQL statements such as
| 'ORDER BY', 'GROUP BY', 'MIN', etc..
|
| The ratio of disk sorts to memory sorts is very low (Less than .1%).
| In that case, is there any advantage to increasing the size of
| SORT_AREA_SIZE (I am not severly constrained for RAM)?
|
| If Oracle needs more space to sort than given by SORT_AREA_SIZE, will
| it start sorting on disk and paging, in which case there is no use
| increasing SORT_AREA_SIZE (As Oracle rarely sorts on disk now), or
| will Oracle sort a subset of the data in a temp. segment in RAM, then
| sort another such subset, then merge the subsets, etc..? If so, it may
| make sense to increase the size of SORT_AREA_SIZE.
|
| Similarly, if there are no virtually no disk sorts, can it still be
| useful to set SORT_DIRECT_WRITES to TRUE (It is set to AUTO now)?
|
|
| Thanks,
| SAP BASIS consultant
Received on Tue Jun 25 2002 - 16:59:03 CDT