Michael R. Ault
This test was constructed as a repeatable test case to show that the principles set forth in Don Burleson’s article Undocumented secrets for super-sizing your PGA are correct in most of the cases that I see as an Oracle tuning consultant.
Establish test parameters:
This test is designed to show the affects of varying the settings of pga_aggregate_target, “_pga_max_size” and “_smm_px_max_size” on a users sort area size for both serial and parallel queries.
Establish a large enough table that will ensure all test sorts will spill to disk, thus ensuring the maximum sort area is utilized
Using the table from (a.) run repeated tests with varying set points for the parameters in a 10g database and utilize the v$workarea tables to view the changes in sort areas for both serial and parallel sorts.
Values to be checked:
Pga_aggregate_target of 1 gigabyte with default values for all undocumented parameters with a serial sort.
Pga_aggregate_target of 4 gigabytes with default values for all undocumented parameters with a serial sort.
Pga_aggregate_target of 4 gigabytes with “_pga_max_size” set to 200 megabytes and default for all other undocumented parameters with a serial sort.
Pga_aggregate_target of 4 gigabytes with “_pga_max_size” set to 200 megabytes and “_smm_px_max_size” set to 333, varying the degree of parallel for the test query from 2 to 7.
Correlate the values and generate test documentation with findings.
The test is being accomplished utilizing a 3.0 gigahertz hyper threading P4 based system running the RedHat 3 kernel and utilizing the 10.1.0.3 release of Oracle. The test system runs against an 8 disk Nstore disk array that is in a RAID1 configuration with 1 128K stripe width; all disks participate in the stripe. The total available capacity is 120 gigabytes of external storage with two additional internal drives each with 110 gigabytes of formatted capacity. The internal drives are used for the operating system and programs as well as swap area. The external drive array is utilized strictly for Oracle data. ASM is not being utilized.
The test table consists of the columns shown in Figure 1 and contains simulated health record related data.
Figure 1: Test Table Columns
The table size and row count data are shown in Figure 2:
SQL> select table_name,num_rows,(blocks*8192)/(1024*1024) meg, avg_row_len from user_tables where table_name='TEST_PGA';
TABLE_NAME NUM_ROWS MEG AVG_ROW_LEN
TEST_PGA 4547752 624.78125 124
Figure 2: Test Table Size Data
With a size of over 624 megabytes this table should provide maximum sort segment sizing with the test SQL statement shown in Figure 3 for both serial and parallel sorts.
SQL for serial tests:
select * from test_pga
order by district_name,name
SQL for parallel tests:
select /*+ parallel(test_pga 7) */
The degree of parallel in the PARALLEL hint in the test SQL will be varied between 2 and 7 during the test.
Figure 4 shows the validation of the Oracle version and options used during the test.
SQL> select * from v$version;
NLSRTL Version 10.1.0.3.0 – Production
Figure 4: Test System Oracle Verification
Figure 5 shows the operating system version validate data.
[oracle@aultlinux3 test]$ uname -a
Linux aultlinux3 2.6.9-5.ELsmp #1 SMP Wed Jan 5 19:30:39 EST 2005 i686 i686 i386 GNU/Linux
Figure 5: OS Validation
Appendix A contains the full list of documented and undocumented parameters form the start of the test.
Figure 6 shows the baseline startup statistics from the test system.
ORACLE instance started.
Total System Global Area 1073741824 bytes
Fixed Size 782568 bytes
Variable Size 355208984 bytes
Database Buffers 717225984 bytes
Redo Buffers 524288 bytes
Figure 6: Startup Statistics
Figure 7 shows all non-default initialization parameters at the start of testing.
Mon Sep 05 page 1
The following procedure will be used to populate the test results table:
create or replace procedure get_results as
while x loop
insert into test_results
select count(*) into i from V$SQL_WORKAREA_ACTIVE
if i=0 then
The procedure selects results into the test results table every second while it still sees sorts occurring. This is a test environment dedicated to this test so no other users will be present on the system. In a busy system the procedure would have to be modified to allow for the proper SIDS to be captured. The TEST_RESULTS table will be truncated after each test run (the results will be moved to an excel spreadsheet for analysis).
This concludes the documentation of the test platform and start conditions for the test.
The results show close agreement with the predicted behavior. The two sets of tests involved serial sort behavior and parallel sort behavior.
Serial Sort Behavior
For the serial sort test Chart 1 shows the results in graphic form.
Chart 1: Predicted Verses Actual Sort Size (Serial Sorts)
The data used to compile chart one is shown in Table 1 and was derived by taking the maximum sort memory utilized from the results for a specific test. All serial test results are in Appendix B.