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

On 25 Jun 2002 12:35:15 -0700, basis_consultant_at_hotmail.com (SAP BASIS Consultant) wrote:

>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 less than 0.1% (one tenth of one percent) of your sorts are going to disk, I'd guess you have bigger fish to fry.

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

Not sure I understand what you're saying here. As you say below, Oracle will sort in memory until it exhausts SORT_AREA_SIZE, at which point it will start writing to disk, in the designated temp tablespace. If you are NOT getting disk sorts, your SORT_AREA_SIZE is already large enough and may even be larger than needed. If you ARE getting disk sorts, increasing SORT_AREA_SIZE *will* reduce them. However, if increasing SORT_AREA_SIZE pushes the total system memory requirements over some threshold, it would result in OS pageing. I don't know if OS paging is a bigger or lesser hit than Oracle "paging" sort segments and suspect there is not a simple answer to that one.

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

--
Ed Stevens
(Opinions expressed do not necessarily represent those of my employer.)