Validation of Sort Sizes in an Oracle10g Database
Table 1: Predicted Verses Actual Data Chart
Download 3.53 Mb.
|
Table 1: Predicted Verses Actual Data ChartWhile 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 BehaviorParallel 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.
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. Download 3.53 Mb. Share with your friends:
|