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

From: SAP BASIS Consultant <basis_consultant_at_hotmail.com>
Date: 25 Jun 2002 12:35:15 -0700
Message-ID: <dd2036f3.0206251135.10c73a2c@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 - 14:35:15 CDT