I ran test in my own environment to compare sort_area_size with PGA and how PGA is being used. Results are as follows:
Table Size: 5.125
Index Size (One that I used for testing with DOP 4): 4.3942
Size in MB
PGA memory and Temporary table space usage (Output from v$sql_workarea_active):
Case 1:
SID TIME WORK_AREA_SIZE MAX_MEM_USED PASSES TEMPSEG_SIZE
81 42:11 2.3 512 1 1050
83 42:11 2.3 512 1 1041
86 42:11 2.3 512 1 1034
92 42:11 3.3 512.1 1 1558
Case 2:
SID TIME WORK_AREA_SIZE MAX_MEM_USED PASSES TEMPSEG_SIZE
87 05:02 1.1 1024 1 1034
88 05:02 1.1 1024 1 1041
89 05:02 1.1 1024 1 1050
90 05:02 1.1 1024 1 1558
Case 3:
SID TIME WORK_AREA_SIZE MAX_MEM_USED PASSES TEMPSEG_SIZE
86 17:47 1103.8 1103.8 0 0
87 17:47 1086.5 1086.5 0 0
88 17:47 1092.8 1092.8 0 0
89 17:47 1.1 1536 1 1558
Case 4 (sort_area_size) :
SID TIME WORK_AREA_SIZE MAX_MEM_USED PASSES TEMPSEG_SIZE
82 39:10 0 .1 4099 1219
88 39:10 0 .1 4102 1795
89 39:10 0 .1 3710 1248
90 39:10 0 .1 3947 1258
Case 1: This is default setting. MAX_MEM_USED used for each parallel process was 512 MB ie _smm_px_max_size/4.
Case 2: I increased all (_) parameter to a higher value. There was some gain in performance but not much as it still has to spill sorts to temporary tablespace. See MAX_MEM_USED used for each parallel process was 1024 MB ie _smm_px_max_size/4
Case 3: As per Case 1 and Case 2 max it has to spill to temporary tablespace was 1558 MB. So I increased PGA to 6 GB _smm_px_max_size to 6GB so that I can accomodate most of the sorting in memory. I could manage to do it with 3 slaves (see PASSES = 0 for 3 slaves) where as fourth slave needed 1558 MB but Oracle will allocate least of _smm_max_size= 1536 MB and _smm_px_max_size/4 = 1536 MB. Response time improved from 8 mins 27 seconds to 6 mins 57 seconds.
Case 4: I used old way of sorting with sort_area_size but it took lot of time. Also I observed SORT/MERGE after SORT operation which took most of the time.
SID % Complete Time Now ELAPSED_SECONDS MESSAGE
--------------- ---------- --------------- --------------- ----------------------------------------------
90.22 .51 090506 12:36:38 111 Sort/Merge: : 5039 out of 995618 Blocks done
82.9 .49 090506 12:36:38 111 Sort/Merge: : 5025 out of 1027136 Blocks done
89.164 .54 090506 12:36:38 111 Sort/Merge: : 5038 out of 934140 Blocks done
88.33 .32 090506 12:36:38 111 Sort/Merge: : 5015 out of 1581604 Blocks done
Conclude: On large Warehouse systems where PGA is set in range of 10G-32G, changing PGA (_) parameters for load and batch processes for sorting, PL/SQL collections will help speed up operations that use PGA memory. Also it can help in multiple node RAC environments where one node is dedicated for Warehouse type of operation.
