Validation of Sort Sizes in an Oracle10g Database


Table 1: Predicted Verses Actual Data Chart



Download 3.53 Mb.
Page2/17
Date28.05.2018
Size3.53 Mb.
#50899
1   2   3   4   5   6   7   8   9   ...   17

Table 1: Predicted Verses Actual Data Chart

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 Behavior


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.

Download 3.53 Mb.

Share with your friends:
1   2   3   4   5   6   7   8   9   ...   17




The database is protected by copyright ©ininet.org 2022
send message

    Main page