| Improve Sort Perf. Through SORT_AREA_SIZE [message #51967] |
Mon, 24 June 2002 07:24  |
Oracle/SAP Consultant
Messages: 1
Registered: June 2002
|
Junior Member |
|
|
Hello,
I am monitoring an SAP R/3 system, which is 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, paging, in which case there is no use increasing SORT_AREA_SIZE (As Oracle rarely does this 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, there may be a very low disk sort to memory sort area, but I think that the sort's improvement can be improved by using a larger SORT_AREA_SIZE?
Similarly, if there are no virtually no disk sorts, can it still be useful to set SORT_DIRECT_WRITES to TRUE.
Thanks,
SAP BASIS consultant
|
|
|
|
|
|
| Re: Improve Sort Perf. Through SORT_AREA_SIZE [message #52128 is a reply to message #51967] |
Wed, 03 July 2002 23:02  |
Peter
Messages: 62
Registered: August 2000
|
Member |
|
|
On most large site you will always have a certain percentage of your sorts being done on disk. As with all memory parameters there is a diminished return for increasing your parameters. So there may be little real benefit by increasing this parameter.
The best thing to do once you have tuned this memory is to make sure your sort_area_size is a multiple of your temp segments initial and next extents.
You may also have some queried that you can avoid sort with such and union/union all.
If you application has a specific reporting(high sorting ) user you could alter the application so when they log in an alter session set sort_area_size=XXXX is done.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
