This guide details the Non-Exadata configuration for the Oracle Healthcare Foundation. It contains minimum parameter requirements for a medium deployment (about 3 million patients).
The contents of this guide apply only to the Oracle Healthcare Foundation ETL platform and does not consider any application stack.
This guide contains the following sections:
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 article IDs 727062.1 and 1269321.1 on My Oracle Support (https://support.oracle.com).
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 at http://www.oracle.com/technetwork/database/database-technologies/performance/resource-manager-twp-133705.pdf.
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 |
|
parallel_force_local |
TRUE |
|
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 |
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.
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.
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.
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.
Set the parallel_min_servers parameter to 32 to ensure the availability of a specific number of PX server processes at startup.
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.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.
Disable 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).
Table B-2 Tablespace Size Recommendations
| Tablespace | Recommended Size |
|---|---|
|
TEMP |
500 GB across all nodes. |
|
UNDO |
500 GB per instance. |
Note:
Use the BigFile tablespace for TEMP, UNDO, and user (HDI, HDM, and HCD) 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.
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 to use the default setting for the disk sequential reads.
Oracle Table or Schema Statistics:
Configure the scheduled jobs to collect the Oracle statistics using the following method. This should be configured after the initial load is complete.
exec dbms_stats.gather_schema_stats (ownname=> '<OWNER>', method_ opt=> 'for all columns size auto', estimate_percent=> dbms_ stats.auto_sample_ size, cascade=>true, block_sample => true, options => 'GATHER STALE')
Data files or flat files (for example, ODB result and reference data files) should be staged on Database File System (DBFS) as the Oracle DB provides much better security, availability, robustness, transactions, and scalability than the traditional file systems. For setting up DBFS, see article ID 1054431.1 on My Oracle Support (https://support.oracle.com).
Oracle Table or Schema Statistics:
HDI source tables should have proper table statistics before the initial load. See Oracle Healthcare Foundation Administrator's Guide for ETL specific recommendations regarding parallel options.
See Oracle Health Sciences Translational Research Center Installation Guide for Non-Exadata specific configurable partitioning and parallel options.
Run the result files in parallel to achieve maximum performance.
For the better storage savings, load more than 60 samples or 200 million rows per result table. Small sample sets may not compress up to 90%. Run the post processing script, load_exadata.sh, at the end of the day after loading minimum 60 samples or more than 200 million rows for high compression using HCC.
See Data Model Installation for Non-Exadata specific configurable partitioning and parallel options.