Database Configuration
Review the following:
- Database I/O Calibration
- Resource Manager
- Database Parameters
- Parallel Degree Policy
- Oracle Automatic Stats Job
- Tablespace and Redo Log Recommendations
- ASM Recommendations
Parent topic: Configuration Guide for Non-Exadata
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 |
|
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.
- Parallel Degree Limit
- Adaptive Tuning
- Parallel Max Servers
- Parallel Min Servers
- Memory Settings
- Optimizer Features Enable
- Processes
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