While we didn’t achieve the full 200 megabytes expected, we still increased available sort size by a factor of 3.8, very close to the predicted factor of 4.
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.
Chart 2: Actual Verses Predicted Sort Size for Various DOP Settings
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.
DOP
|
Predicted Sort Size
|
Actual Sort Size
|
DOP 2
|
166.5
|
142.8828125
|
DOP 3
|
111
|
97.578125
|
DOP 4
|
83.25
|
80.6328125
|
DOP 5
|
66.6
|
55.0625
|
DOP 6
|
55.5
|
55.0625
|
DOP 7
|
47.57143
|
42.4140625
|
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.
Share with your friends: |