Validation of Sort Sizes in an Oracle10g Database
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.
.
Test Plan:
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.
Methodology:
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.
Test System
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.
Test Table
The test table consists of the columns shown in Figure 1 and contains simulated health record related data.
desc test_pga
Name Null? Type
----------------------------------------- -------- ----------------------------
NAME VARCHAR2(128)
CODE1 VARCHAR2(4)
CODE2 VARCHAR2(4)
DISTRICT_NAME VARCHAR2(30)
MONTH VARCHAR2(6)
CODE3 VARCHAR2(9)
CODE_3_DESC VARCHAR2(30)
FIRST_DATE DATE
SEC_DATE DATE
COUNT1 NUMBER
COUNT2 NUMBER
COUNT3 NUMBER
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) */
* from test_pga
order by district_name,name
/
Figure 3: Test SQL Statements
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;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - Prod
PL/SQL Release 10.1.0.3.0 - Production
CORE 10.1.0.3.0 Production
TNS for Linux: Version 10.1.0.3.0 - Production
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.
SQL>
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
Database mounted.
Database opened.
Figure 6: Startup Statistics
Figure 7 shows all non-default initialization parameters at the start of testing.
Mon Sep 05 page 1
Non-Default Initialization Parameters
NUM TYPE NAME VALUE
---- ----- ------------------------------------- ----------------------
317 6 __db_cache_size 717225984
96 6 __java_pool_size 8388608
94 6 __large_pool_size 4194304
92 6 __shared_pool_size 234881024
779 2 audit_trail TRUE
751 2 background_dump_dest /home/oracle/admin/test/bdump
389 2 compatible 10.1.0.2.0
296 2 control_files /home/oracle/oradata/test/con
trol01.ctl, /home/oracle/orada
ta/test/control02.ctl, /home/
oracle/oradata/test/control03
.ctl
754 2 core_dump_dest /home/oracle/admin/test/cdump
309 3 db_block_size 8192
668 2 db_domain
465 3 db_file_multiblock_read_count 32
787 2 db_name test
479 2 db_recovery_file_dest /home/oracle/flash_recovery_ar
ea
480 6 db_recovery_file_dest_size 2147483648
331 3 db_writer_processes 7
675 2 dispatchers (PROTOCOL=TCP) (SERVICE=testX
DB)
119 3 enqueue_resources 3000
706 3 job_queue_processes 10
790 3 open_cursors 300
939 6 pga_aggregate_target 1073741824
20 3 processes 600
662 2 remote_login_passwordfile EXCLUSIVE
52 6 sga_max_size 1073741824
285 6 sga_target 1073741824
807 2 star_transformation_enabled TRUE
98 6 streams_pool_size 104857600
608 2 undo_management AUTO
609 2 undo_tablespace UNDOTBSP2
752 2 user_dump_dest /home/oracle/admin/test/udump
30 rows selected.
Figure 7: Non-Default Initialization Parameters
The test results will be captured by procedure and placed into the test_results table:
desc test_results
Name Null? Type
----------------------------------------- -------- -------------------
SID NUMBER
WORK_AREA_SIZE NUMBER
EXPECTED_SIZE NUMBER
ACTUAL_MEM_USED NUMBER
MAX_MEM_USED NUMBER
TEMPSEG_SIZE NUMBER
NOW VARCHAR2(12)
The following procedure will be used to populate the test results table:
create or replace procedure get_results as
x boolean;
i integer;
begin
x:=true;
while x loop
insert into test_results
SELECT
sid,
work_area_size,
expected_size,
actual_mem_used,
max_mem_used,
tempseg_size,
to_char(sysdate,'ddmmyyhh24miss')
FROM
V$SQL_WORKAREA_ACTIVE
WHERE
operation_type='SORT';
commit;
select count(*) into i from V$SQL_WORKAREA_ACTIVE
WHERE
operation_type='SORT';
if i=0 then
x:=false;
end if;
dbms_lock.sleep(1);
end loop;
end;
/
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.
Results
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.
PAT/PMS
|
Predicted
|
Actual
|
1g Default PMS
|
51.2
|
45.5
|
4g Default PMS
|
100
|
97.578125
|
4g 400M PMS
|
200
|
172.890625
|
Share with your friends: |