Database Configuration

Database I/O Calibration

Run the I/O calibration tool for each database to set the automatic degree of parallelism.

For more information on I/O calibration and automatic degree of parallelism, see the I/O Calibration Inside the Database section in Oracle 12.2 Database Performance Tuning Guide as well as article IDs 727062.1 and 1269321.1 on My Oracle Support (https://support.oracle.com).

Parent topic: Database Configuration

Resource Manager

Use the Oracle Resource Manager to manage and prioritize the resource allocation for different applications, databases, instances, users, and so on.

For more information, see article ID 1339769.1 on My Oracle Support (https://support.oracle.com) and the White Paper Using Oracle Database Resource Manager.

Parent topic: Database Configuration

Database Parameters

Table B-1 Recommended Settings for Database Parameters

Database Parameter	Recommended Settings
processes	2000
parallel_max_servers	384
parallel_min_servers	32
parallel_degree_policy	LIMITED
parallel_adaptive_multi_user	TRUE
parallel_degree_limit	16
sga_target	20 GB
sga_max_size	20 GB
pga_aggregate_target	If HugePages is used, set this parameter to 20 GB. Else, set it to 10 GB.
memory_target	If HugePages is used, set this parameter to 0. Else, set it to 40 GB.
memory_max_target	If HugePages is used, set this parameter to 0. Else, set it to 60 GB.
db_block_size	8000
workarea_size_policy	AUTO
db_cache_size	2 GB
shared_pool_size	2 GB
optimizer_features_enable	See `Optimizer Features Enable`
optimizer_secure_view_merging	FALSE
_px_join_skew_handling	FALSE
result size max	0
_optimizer_dsdir_usage_control	0

Parent topic: Database Configuration

Parallel Degree Policy

Set the parallel_degree_policy parameter to LIMITED to use the automatic degree of parallelism.

Statement queuing is disabled to avoid any waits under heavy load where there are chances of draining all PX servers, thereby impacting response times.

Parent topic: Database Configuration

Parallel Degree Limit

Set the parallel_degree_limit parameter to 16 or below to improve the response time. A resource intensive query may request too many PX servers, draining available resources. Use the Resource Manager to limit the resource allocation to intensive or long running processes.

Parent topic: Parallel Degree Policy

Adaptive Tuning

Set the parallel_adaptive_multi_user parameter to TRUE, which lets Oracle downgrade PX requests under stress. You should not over-burden the database (DB) CPUs by allocating all requested PX servers, especially when the DB nodes are running over 80% CPU consumption.

Alternatively, you can use the Resource Manager to control resources.

Parent topic: Parallel Degree Policy

Parallel Max Servers

Set the parallel_max_servers parameter to 384 or less to make sure that there are enough PX server processes to allocate to concurrent user requests.

Parent topic: Parallel Degree Policy

Parallel Min Servers

Set the parallel_min_servers parameter to 32 to ensure the availability of a specific number of PX server processes at startup.

Parent topic: Parallel Degree Policy

Memory Settings

Set the Automatic Memory Management (AMM) using the memory_target parameter to 40 GB.

You should also set the Program Global Area (PGA) (pga_aggregate_target parameter) to a minimum of 20/10 GB depending on whether HugePages is used and the System Global Area (SGA) (sga_max_size parameter) to a minimum of 20 GB.

Note:

If you use HugePages, set all DB memory parameters appropriately as AMM does not work with HugePages. If you encounter any shared pool issues with HugePages, switch to alternate recommended settings.

Parent topic: Parallel Degree Policy

Optimizer Features Enable

Although Oracle Healthcare Foundation is benchmarked with 12.2.0.1, Oracle recommends you to upgrade to the latest version of the optimizer when it is available.

If the SQL execution plans are not optimized in the later version, consider using the 12.2.0.1 optimizer.

Parent topic: Parallel Degree Policy

Processes

Set the processes parameter to a sizeable number based on the expected concurrency. In internal benchmarks, it was set to 2000 based on concurrent loads.

Parent topic: Parallel Degree Policy

Oracle Automatic Stats Job

Disable the Oracle Automatic Stats Job if it is configured.

Automatic statistics scheduler jobs run under different maintenance windows (such as daily, weekly, and so on). This may potentially generate bad statistics due to data availability (for example, an application may have transient data or no data in stage tables).

Parent topic: Database Configuration

Tablespace and Redo Log Recommendations

Table B-2 Tablespace Size Recommendations

Tablespace	Recommended Size
TEMP	500 GB per RAC node
UNDO	500 GB per RAC node

Note:

Use the BigFile tablespace for TEMP, UNDO, and user (HDI, HDM, HCD, CDM, and ODB) tablespaces to ease tablespace maintenance.

Configure the redo logs to have at least five groups per instance with each member having a size of 500 MB or higher.

Parent topic: Database Configuration

ASM Recommendations

Redundancy - Oracle uses NORMAL redundancy for all benchmarks. You can use HIGH redundancy as per the data mirroring requirements. However, note that the usable storage is lower when using ASM with HIGH redundancy disk groups.

ASM AU Size - We recommend you use the default setting for the disk sequential reads.

Parent topic: Database Configuration