Parallel sort area size behavior under different settings of DOP and a constant 4 Gigabyte (actually 4g-1 byte) PGA_AGGREGATE_TARGET and 333 megabyte setting for “_smm_px_max_size” also tracked with predictions within expected overhead margins. Chart 2 shows the actual results verses predicted results for the parallel query testing.
Predicted sizes were calculated by dividing the “_smm_px_max_size” setting by the degree of parallel. Actual sizes were taken form the results data for the maximum sort memory size value for the parallel sessions.
Table 2 shows the numerical data for Chart 2.
Table 2: Actual Verses Predicted Values for Various DOP Settings
Notice that a DOP of 5 or 6 resulted in the same memory setting for sorts. Also notice that the actual size of 80.63 is very close to the predicted setting of 83.25 (84 if a ceiling function is used) for a DOP of 4.
The complete results for all DOP tests are in Appendix C.