Validation of Sort Sizes in an Oracle10g Database

Download 3.53 Mb.

Page	1/17
Date	28.05.2018
Size	3.53 Mb.
	#50899

1 2 3 4 5 6 7 8 9 ... 17

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:
1. Pga_aggregate_target of 1 gigabyte with default values for all undocumented parameters with a serial sort.
2. Pga_aggregate_target of 4 gigabytes with default values for all undocumented parameters with a serial sort.
3. 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.
4. 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

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

Download 3.53 Mb.

Share with your friends:

1 2 3 4 5 6 7 8 9 ... 17