Validation of Sort Sizes in an Oracle10g Database

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:

1. 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.

1. Methodology:

1. Establish a large enough table that will ensure all test sorts will spill to disk, thus ensuring the maximum sort area is utilized
2. 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.
3. Values to be checked:
4. Pga_aggregate_target of 1 gigabyte with default values for all undocumented parameters with a serial sort.
5. Pga_aggregate_target of 4 gigabytes with default values for all undocumented parameters with a serial sort.
6. 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.
7. 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.
8. 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

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.

Appendix A: All Initialization Parameters

In this appendix I list all of the initialization parameters, both documented and undocumented for the TEST environment.

Documented Initialization Parameters

Mon Sep 05 page 1

INIT.ORA PARAMETER LISTING

NUM TYPE NAME VALUE

------

660 1 O7_DICTIONARY_ACCESSIBILITY FALSE

317 6 __db_cache_size 717225984

96 6 __java_pool_size 8388608

94 6 __large_pool_size 4194304

92 6 __shared_pool_size 234881024

208 3 active_instance_count

#### 3 aq_tm_processes 0

450 3 archive_lag_target 0

#### 2 asm_diskgroups

#### 2 asm_diskstring

#### 3 asm_power_limit 1

761 2 audit_file_dest /home/oracle/DBHome1/rdbms/aud

it

666 1 audit_sys_operations FALSE

779 2 audit_trail TRUE

750 2 background_core_dump partial

751 2 background_dump_dest /home/oracle/admin/test/bdump

167 1 backup_tape_io_slaves FALSE

712 3 bitmap_merge_area_size 1048576

802 1 blank_trimming FALSE

349 2 buffer_pool_keep

350 2 buffer_pool_recycle

679 3 circuits

469 1 cluster_database FALSE

472 3 cluster_database_instances 1

180 2 cluster_interconnects

672 3 commit_point_strength 1

389 2 compatible 10.1.0.2.0

303 3 control_file_record_keep_time 7

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

42 3 cpu_count 2

711 3 create_bitmap_area_size 8388608

653 2 create_stored_outlines

714 2 cursor_sharing EXACT

690 1 cursor_space_for_time FALSE

323 6 db_16k_cache_size 0

320 6 db_2k_cache_size 0

324 6 db_32k_cache_size 0

321 6 db_4k_cache_size 0

322 6 db_8k_cache_size 0

304 3 db_block_buffers 0

630 1 db_block_checking FALSE

308 1 db_block_checksum TRUE

Mon Sep 05 page 2

INIT.ORA PARAMETER LISTING

NUM TYPE NAME VALUE

------

309 3 db_block_size 8192

378 2 db_cache_advice ON

318 6 db_cache_size 0

473 2 db_create_file_dest

474 2 db_create_online_log_dest_1

475 2 db_create_online_log_dest_2

476 2 db_create_online_log_dest_3

477 2 db_create_online_log_dest_4

478 2 db_create_online_log_dest_5

668 2 db_domain

465 3 db_file_multiblock_read_count 32

301 2 db_file_name_convert

464 3 db_files 200

557 3 db_flashback_retention_target 1440

325 6 db_keep_cache_size 0

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

326 6 db_recycle_cache_size 0

789 2 db_unique_name test

331 3 db_writer_processes 7

163 3 dbwr_io_slaves 0

576 1 ddl_wait_for_locks FALSE

#### 2 dg_broker_config_file1 /home/oracle/DBHome1/dbs/dr1hf

dwh.dat

#### 2 dg_broker_config_file2 /home/oracle/DBHome1/dbs/dr2hf

dwh.dat

#### 1 dg_broker_start FALSE

160 1 disk_asynch_io TRUE

675 2 dispatchers (PROTOCOL=TCP) (SERVICE=testX

DB)

670 3 distributed_lock_timeout 60

575 3 dml_locks 2924

#### 1 drs_start FALSE

119 3 enqueue_resources 3000

45 2 event

440 2 fal_client

441 2 fal_server

522 3 fast_start_io_target 0

523 3 fast_start_mttr_target 0

625 2 fast_start_parallel_rollback LOW

185 1 file_mapping FALSE

152 2 fileio_network_adapters

154 2 filesystemio_options none

778 2 fixed_date

Mon Sep 05 page 3

INIT.ORA PARAMETER LISTING

NUM TYPE NAME VALUE

------

486 2 gc_files_to_locks

197 3 gcs_server_processes 0

667 2 global_context_pool_size

669 1 global_names FALSE

739 3 hash_area_size 131072

58 3 hi_shared_memory_address 0

#### 1 hs_autoregister TRUE

791 4 ifile

44 2 instance_groups

673 2 instance_name test

585 3 instance_number 0

113 2 instance_type RDBMS

104 3 java_max_sessionspace_size 0

97 6 java_pool_size 0

103 3 java_soft_sessionspace_limit 0

706 3 job_queue_processes 10

95 6 large_pool_size 0

654 2 ldap_directory_access NONE

25 3 license_max_sessions 0

663 3 license_max_users 0

26 3 license_sessions_warning 0

681 2 local_listener

19 2 lock_name_space

61 1 lock_sga FALSE

397 2 log_archive_config

405 2 log_archive_dest

407 2 log_archive_dest_1

416 2 log_archive_dest_10

408 2 log_archive_dest_2

409 2 log_archive_dest_3

410 2 log_archive_dest_4

411 2 log_archive_dest_5

412 2 log_archive_dest_6

413 2 log_archive_dest_7

414 2 log_archive_dest_8

415 2 log_archive_dest_9

417 2 log_archive_dest_state_1 enable

426 2 log_archive_dest_state_10 enable

418 2 log_archive_dest_state_2 enable

419 2 log_archive_dest_state_3 enable

420 2 log_archive_dest_state_4 enable

421 2 log_archive_dest_state_5 enable

422 2 log_archive_dest_state_6 enable

423 2 log_archive_dest_state_7 enable

424 2 log_archive_dest_state_8 enable

425 2 log_archive_dest_state_9 enable

406 2 log_archive_duplex_dest

434 2 log_archive_format %t_%s_%r.dbf

433 1 log_archive_local_first TRUE

427 3 log_archive_max_processes 2

Mon Sep 05 page 4

INIT.ORA PARAMETER LISTING

NUM TYPE NAME VALUE

------

430 3 log_archive_min_succeed_dest 1

399 1 log_archive_start FALSE

432 3 log_archive_trace 0

445 3 log_buffer 524288

447 3 log_checkpoint_interval 0

448 3 log_checkpoint_timeout 1800

528 1 log_checkpoints_to_alert FALSE

302 2 log_file_name_convert

538 3 logmnr_max_persistent_sessions 1

387 3 max_commit_propagation_delay 700

678 3 max_dispatchers

753 2 max_dump_file_size UNLIMITED

657 3 max_enabled_roles 150

677 3 max_shared_servers

140 2 nls_calendar

146 2 nls_comp

137 2 nls_currency

136 2 nls_date_format

135 2 nls_date_language

145 2 nls_dual_currency

139 2 nls_iso_currency

132 2 nls_language AMERICAN

147 2 nls_length_semantics BYTE

148 2 nls_nchar_conv_excp FALSE

138 2 nls_numeric_characters

134 2 nls_sort

133 2 nls_territory AMERICA

141 2 nls_time_format

143 2 nls_time_tz_format

142 2 nls_timestamp_format

144 2 nls_timestamp_tz_format

764 3 object_cache_max_size_percent 10

763 3 object_cache_optimal_size 102400

#### 6 olap_page_pool_size 0

790 3 open_cursors 300

771 3 open_links 4

772 3 open_links_per_instance 4

960 3 optimizer_dynamic_sampling 2

777 2 optimizer_features_enable 10.1.0.3

872 3 optimizer_index_caching 0

871 3 optimizer_index_cost_adj 100

795 2 optimizer_mode ALL_ROWS

793 2 os_authent_prefix ops$

655 1 os_roles FALSE

847 1 parallel_adaptive_multi_user TRUE

849 1 parallel_automatic_tuning FALSE

729 3 parallel_execution_message_size 2148

726 2 parallel_instance_group

721 3 parallel_max_servers 40

708 3 parallel_min_percent 0

Mon Sep 05 page 5

INIT.ORA PARAMETER LISTING

NUM TYPE NAME VALUE

------

720 3 parallel_min_servers 0

470 1 parallel_server FALSE

471 3 parallel_server_instances 1

848 3 parallel_threads_per_cpu 2

939 6 pga_aggregate_target 1073741824

702 2 plsql_code_type INTERPRETED

698 2 plsql_compiler_flags INTERPRETED, NON_DEBUG

704 1 plsql_debug FALSE

699 2 plsql_native_library_dir

700 3 plsql_native_library_subdir_count 0

705 3 plsql_optimize_level 2

697 1 plsql_v2_compatibility FALSE

701 2 plsql_warnings DISABLE:ALL

56 1 pre_page_sga FALSE

20 3 processes 600

881 2 query_rewrite_enabled TRUE

882 2 query_rewrite_integrity enforced

656 2 rdbms_server_dn

467 1 read_only_open_delayed FALSE

529 3 recovery_parallelism 0

396 2 remote_archive_enable true

693 2 remote_dependencies_mode TIMESTAMP

682 2 remote_listener

662 2 remote_login_passwordfile EXCLUSIVE

658 1 remote_os_authent FALSE

659 1 remote_os_roles FALSE

579 1 replication_dependency_tracking TRUE

24 1 resource_limit FALSE

171 2 resource_manager_plan

627 3 resumable_timeout 0

600 2 rollback_segments

689 2 serial_reuse disable

674 2 service_names test

691 3 session_cached_cursors 0

766 3 session_max_open_files 10

21 3 sessions 665

52 6 sga_max_size 1073741824

285 6 sga_target 1073741824

749 2 shadow_core_dump partial

57 3 shared_memory_address 0

100 6 shared_pool_reserved_size 11744051

93 6 shared_pool_size 0

680 3 shared_server_sessions

676 3 shared_servers 1

#### 1 skip_unusable_indexes TRUE

695 2 smtp_out_server

781 3 sort_area_retained_size 0

780 3 sort_area_size 65536

788 2 sp_name test

112 2 spfile /home/oracle/DBHome1/dbs/spfil

Mon Sep 05 page 6

INIT.ORA PARAMETER LISTING

NUM TYPE NAME VALUE

------

etest.ora

800 1 sql92_security FALSE

792 1 sql_trace FALSE

913 2 sql_version NATIVE

#### 2 sqltune_category DEFAULT

431 2 standby_archive_dest ?/dbs/arch

482 2 standby_file_management MANUAL

807 2 star_transformation_enabled TRUE

981 2 statistics_level TYPICAL

98 6 streams_pool_size 104857600

161 1 tape_asynch_io TRUE

517 3 thread 0

23 3 timed_os_statistics 0

22 1 timed_statistics TRUE

124 1 trace_enabled TRUE

2 2 tracefile_identifier

597 3 transactions 731

598 3 transactions_per_rollback_segment 5

608 2 undo_management AUTO

621 3 undo_retention 900

609 2 undo_tablespace UNDOTBSP2

59 1 use_indirect_data_buffers FALSE

752 2 user_dump_dest /home/oracle/admin/test/udump

694 2 utl_file_dir

941 2 workarea_size_policy AUTO

259 rows selected.

Undocumented Initialization Parameters

In the interest of full disclosure, here is the complete list of undocumented parameters at the start of the test.

Mon Sep 05 page 1

Undocumented Oracle Parameters TEST Database

Parameter Description Session Value Instance Value

------

_4031_dump_bitvec bitvec to specify 20479 20479

dumps prior to 4031

error

_4031_dump_interval Dump 4031 error once 300 300

for each n-second

interval

_4031_max_dumps Maximum number of 100 100

4031 dumps for this

process

_4031_sga_dump_interval Dump 4031 SGA 3600 3600

heapdump error once

for each n-second

interval

_4031_sga_max_dumps Maximum number of 10 10

SGA heapdumps

_NUMA_instance_mapping Set of nodes that Not specified Not specified

this instance should

run on

_NUMA_pool_size aggregate size in Not specified Not specified

bytes of NUMA pool

_PX_use_large_pool Use Large Pool as FALSE FALSE

source of PX buffers

__db_cache_size Actual size of 717225984 717225984

DEFAULT buffer pool

for standard block

size buffers

__java_pool_size Actual size in bytes 8388608 8388608

of java pool

__large_pool_size Actual size in bytes 4194304 4194304

of large pool

__shared_pool_size Actual size in bytes 234881024 234881024

of shared pool

_abort_recovery_on_join if TRUE, abort FALSE FALSE

recovery on join

reconfigurations

_active_standby_fast_reconfiguration if TRUE optimize dlm TRUE TRUE

reconfiguration for

Mon Sep 05 page 2

Undocumented Oracle Parameters TEST Database

Parameter Description Session Value Instance Value

------

active/standby OPS

_adaptive_direct_read Adaptive Direct Read TRUE TRUE

_adaptive_fetch_enabled enable/disable TRUE TRUE

adaptive fetch in

parallel group by

_add_stale_mv_to_dependency_list add stale mv to TRUE TRUE

dependency list

_addm_auto_enable governs whether ADDM TRUE TRUE

gets run

automatically after

every AWR snapshot

_addm_skiprules comma-separated list

of ADDM nodes to

skip

_affinity_on enable/disable TRUE TRUE

affinity at run time

_aiowait_timeouts Number of aiowait 100 100

timeouts before

error is reported

_alert_expiration seconds before an 604800 604800

alert message is

moved to exception

queue

_all_shared_dblinks treat all dblinks as

shared

_allocate_creation_order should files be FALSE FALSE

examined in creation

order during

allocation

_allocation_update_interval interval at which 3 3

successful search in

L1 should be updated

_allow_commutativity allow for TRUE TRUE

commutativity of +,

* when comparing

expressions

_allow_error_simulation Allow error FALSE FALSE

simulation for

Mon Sep 05 page 3

Undocumented Oracle Parameters TEST Database

Parameter Description Session Value Instance Value

------

testing

_allow_level_without_connect_by allow level without FALSE FALSE

connect by

_allow_read_only_corruption allow read-only open FALSE FALSE

even if database is

corrupt

_allow_resetlogs_corruption allow resetlogs even FALSE FALSE

if it will cause

corruption

_allow_terminal_recovery_corruption Finish terminal FALSE FALSE

recovery even if it

may cause corruption

_always_anti_join always use this CHOOSE CHOOSE

method for anti-join

when possible

_always_semi_join always use this CHOOSE CHOOSE

method for semi-join

when possible

_always_star_transformation always favor use of FALSE FALSE

star transformation

_app_ctx_vers enable app ctx FALSE FALSE

versioning

_aq_tm_scanlimit scan limit for Time 0 0

Managers to clean up

IOT

_arch_io_slaves ARCH I/O slaves 0 0

_array_update_vector_read_enabled Enable array update FALSE FALSE

vector read

_ash_disk_filter_ratio Ratio of the number 10 10

of in-memory samples

to the number of

samples actually

written to disk

_ash_disk_write_enable To enable or disable TRUE TRUE

Active Session

History flushing

_ash_dummy_test_param Oracle internal 0 0

Mon Sep 05 page 4

Undocumented Oracle Parameters TEST Database

Parameter Description Session Value Instance Value

------

dummy ASH parameter

used ONLY for

testing!

_ash_eflush_trigger The percentage above 66 66

which if the

in-memory ASH is

full the emergency

flusher will be

triggered

_ash_enable To enable or disable TRUE TRUE

Active Session

sampling and

flushing

_ash_sample_all To enable or disable FALSE FALSE

sampling every

connected session

including ones

waiting for idle

waits

_ash_sampling_interval Time interval 1000 1000

between two

successive Active

Session samples in

millisecs

_ash_size To set the size of 1048618 1048618

the in-memory Active

Session History

buffers

_asm_acd_chunks initial ACD chunks 1 1

created

_asm_allow_only_raw_disks Discovery only raw TRUE TRUE

devices

_asm_ausize allocation unit size 1048576 1048576

_asm_blksize metadata block size 4096 4096

_asm_disk_repair_time seconds to wait 14400 14400

before dropping a

failing disk

_asm_libraries library search order ufs ufs

for discovery

_asm_maxio Maximum size of 1048576 1048576

Mon Sep 05 page 5

Undocumented Oracle Parameters TEST Database

Parameter Description Session Value Instance Value

------

individual I/O

request

_asm_stripesize ASM file stripe size 131072 131072

_asm_stripewidth ASM file stripe 8 8

width

_asm_wait_time Max/imum time to 18 18

wait before asmb

exits

_asmlib_test Osmlib test event 0 0

_asmsid ASM instance id asm asm

_async_recovery_claims if TRUE, issue TRUE TRUE

recovery claims

asynchronously (DFS)

_async_recovery_reads if TRUE, issue TRUE TRUE

recovery reads

asynchronously (DFS)

_avoid_prepare if TRUE, do not TRUE TRUE

prepare a buffer

when the master is

local (DFS)

_awr_corrupt_mode AWR Corrupt Mode FALSE FALSE

_awr_restrict_mode AWR Restrict Mode FALSE FALSE

_b_tree_bitmap_plans enable the use of TRUE TRUE

bitmap plans for

tables w. only

B-tree indexes

_backup_disk_io_slaves BACKUP Disk I/O 0 0

slaves

_backup_io_pool_size memory to reserve 1048576 1048576

from the large pool

_backup_kgc_bufsz specifies buffer 0 0

size to be used for

kgc compression

_backup_kgc_niters specifies number of 0 0

iterations used for

kgc compression

_backup_kgc_type specifies 0 0

compression type

used for kgc

Mon Sep 05 page 6

Undocumented Oracle Parameters TEST Database

Parameter Description Session Value Instance Value

------

compression

_backup_ksfq_bufcnt number of the ksfq 0 0

buffers used for

backup/restore

_backup_ksfq_bufsz size of the ksfq 0 0

buffer used for

backup/restore

_bct_bitmaps_per_file number of bitmaps to 8 8

store for each

datafile

_bct_buffer_allocation_max maximum size of all 104857600 104857600

change tracking

buffer allocations,

in bytes

_bct_buffer_allocation_min_extents mininum number of 1 1

extents to allocate

per buffer

allocation

_bct_buffer_allocation_size size of one change 2097152 2097152

tracking buffer

allocation, in bytes

_bct_chunk_size change tracking 0 0

datafile chunk size,

in bytes

_bct_crash_reserve_size change tracking 262144 262144

reserved crash

recovery SGA space,

in bytes

_bct_file_block_size block size of change 0 0

tracking file, in

bytes

_bct_file_extent_size extent size of 0 0

change tracking

file, in bytes

_bct_initial_private_dba_buffer_size initial number of 0 0

entries in the

private change

tracking dba buffers

Mon Sep 05 page 7

Undocumented Oracle Parameters TEST Database

Parameter Description Session Value Instance Value

------

_bct_public_dba_buffer_size total size of all 0 0

public change

tracking dba

buffers, in bytes

_bitmap_or_improvement_enabled controls extensions TRUE TRUE

to partition pruning

for general

predicates

_block_change_tracking change tracking TRUE TRUE

possible

_blocks_per_cache_server number of 128 128

consecutive blocks

per global cache

server

_bt_mmv_query_rewrite_enabled allow rewrites with TRUE TRUE

multiple MVs and

base tables

_buffer_busy_wait_timeout buffer busy wait 100 100

time in centiseconds

_bufq_stop_flow_control Stop enforcing flow FALSE FALSE

control for buffered

queues

_bump_highwater_mark_count how many blocks 0 0

should we allocate

per free list on

advancing HWM

_bwr_for_flushed_pi if TRUE, generate a TRUE TRUE

BWR for a flushed PI

(DFS)

_cache_stats_monitor if TRUE, enable TRUE TRUE

cache stats

monitoring

_cgs_send_timeout CGS send timeout 300 300

value

_check_block_after_checksum perform block check TRUE TRUE

after checksum if

both are turned on

_check_ts_threshold check tablespace 0 0

Mon Sep 05 page 8

Undocumented Oracle Parameters TEST Database

Parameter Description Session Value Instance Value

------

thresholds

_cleanup_rollback_entries no. of undo entries 100 100

to apply per

transaction cleanup

_close_cached_open_cursors close cursors cached FALSE FALSE

by PL/SQL at each

commit

_cluster_library cluster library clss clss

selection

_collapse_wait_history collapse wait FALSE FALSE

history

_collect_undo_stats Collect Statistics TRUE TRUE

v$undostat

_column_compression_factor Column compression 0 0

ratio

_column_elimination_off turn off FALSE FALSE

predicate-only

column elimination

_column_tracking_level column usage 1 1

tracking

_complex_view_merging enable complex view TRUE TRUE

merging

_controlfile_block_size control file block 0 0

size in bytes

_controlfile_enqueue_dump dump the system FALSE FALSE

states after

controlfile enqueue

timeout

_controlfile_enqueue_timeout control file enqueue 900 900

timeout in seconds

_controlfile_update_check controlfile update OFF OFF

sanity check

_convert_set_to_join enables conversion FALSE FALSE

of set operator to

join

Mon Sep 05 page 9

Undocumented Oracle Parameters TEST Database

Parameter Description Session Value Instance Value

------

_coord_message_buffer parallel recovery 0 0

coordinator side

extra message buffer

size

_corrupted_rollback_segments corrupted undo

segment list

_cost_equality_semi_join enables costing of TRUE TRUE

equality semi-join

_cpu_to_io divisor for 0 0

converting CPU cost

to I/O cost

_cr_grant_global_role if TRUE, grant lock TRUE TRUE

for CR requests when

block is in global

role

_cr_grant_local_role if TRUE, grant lock FALSE FALSE

for CR using 3way

ping when block in

local role

_cr_server_log_flush if TRUE, flush redo TRUE TRUE

log before serving a

CR buffer (DFS)

_cursor_bind_capture_area_size maximum size of the 400 400

cursor bind capture

area

_cursor_bind_capture_interval interval (in 900 900

seconds) between two