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 .