USE_MERGE Hint

[ligang]

To tune the SQL statement , I use USE_MERGE hint and
found the performance is dramatically getting better
(around 50% better) .
Before doing that , I change the sort_area_size to 2MB
(the default is 64K in Oracle73) , I have some doubts on that
actually :
1. I think sort area size is additional to SGA and it's created
for each session if the sort is required . So is there any problem if many session request sorts and the server memory
would be used up . ( the server has 1GB memory and oracle SGA is around 300 MB now ) .
2. How to manage the sort_area_retain_size ? in my case ,
should I set it to a very small value like 0 or just keep the
default value 64K .

Thanks

[ligang]

correct my typing mistake is previous thread , the sort_area_size is changed to 20MB not 2 MB

[julian]

The default value is not always 0. If you do not explicitly set sort_area_retained_size, Oracle uses the value of the sort_area_size parameter.

20M is very big sort_area_size. Some people consider 1M being huge.

To reduce I/O operations set sort_multiblock_read_count to sort_area_size/sort_area_retained_size.

We have very badly written application, with lots of groups by and havings, etc. I have tried to set sort_area_size to 10M and to 1M, I noticed there was no big need for 10M sort_area_size. Even 1M is a lot. What for do you need 20M?