Recommendations for Oracle
This topic provides recommendations for the appropriate database configuration for Oracle in highly distributed environments. Please note that these recommendations are applicable to the Windows as well as Linux database servers. BMC recommends Oracle on Linux server for better performance.
Disk layout
When you run the Create_TS.oracle.sql script, which is provided for tablespace creation on your database, BMC recommends that you segregate the tablespaces on different disks, as shown in the following tables:
Tablespaces for tables
Small | Large | Static |
|---|---|---|
BCM_DATA | BCM_EL | BCM_VMCKB |
BCM_VM | BCM_INV | BCM_ESIDKB |
BCM_WQ | BCM_DELTAINV | BCM_PATCH |
— | BCM_VMINV | — |
— | BCM_ESIDDATA | — |
— | BCM_SCAP | — |
Tablespaces for indexes
Small | Large | Static |
|---|---|---|
BCM_INDEX | BCM_ELINDEX | BCM_VMCKBINDEX |
BCM_VMINDEX | BCM_INVINDEX | BCM_ESIDKBINDEX |
BCM_WQINDEX | BCM_DELTAINDEX | BCM_PATCHINDEX |
— | BCM_VMINVINDEX | — |
— | BCM_ESIDDATAINDEX | — |
— | BCM_SCAPINDEX | — |
Minimum requirement disk layout
Disk | Purpose |
|---|---|
Disk 1 | / OS-SYSTEM / ORACLE |
Disk 2 | / REDO LOG |
Disk 3 | / TABLESPACES FOR TABLES |
Disk 4 | / TABLESPACES FOR INDEXES |
Disk 5 | / TEMPORARY TABLESPACES |
Recommended best disk layout
Disk | Purpose |
|---|---|
Disk 1 | / OS-SYSTEM / ORACLE |
Disk 2 | / REDO LOG |
Disk 3 | / TABLESPACES FOR SMALL TABLES |
Disk 4 | / TABLESPACES FOR LARGE TABLES |
Disk 5 | / TABLESPACES FOR STATIC TABLES |
Disk 6 | / TABLESPACES FOR SMALL INDEXES |
Disk 7 | / TABLESPACES FOR LARGE INDEXES |
Disk 8 | / TABLESPACES FOR STATIC INDEXES |
Disk 9 | / TEMPORARY TABLESPACES |
Storage capacity sizing
When sizing storage capacity for an Oracle system, you need to consider the following Oracle database components:
- Database files
- Online REDO log files
- Backup/flash data
Database element | 5,000 nodes | 10,000 nodes | 20,000 nodes | 50,000 nodes | 100,000 nodes | 150,000 nodes | 200,000 nodes |
|---|---|---|---|---|---|---|---|
DB size | 5 GB | 10 GB | 20 GB | 50 GB | 100 GB | 150 GB | 200 GB |
Tablespaces for tables | 2 GB | 4 GB | 8 GB | 20 GB | 40 GB | 60 GB | 80 GB |
Tablespaces for index | 3 GB | 6 GB | 12 GB | 30 GB | 60 GB | 90 GB | 120 GB |
Temporary tablespace DB size * 0.2 | 1 GB | 2 GB | 4 GB | 10 GB | 20 GB | 30 GB | 40 GB |
System/undo tablespace DB size * 0.1 | 500 M | 1 GB | 2 GB | 5 GB | 10 GB | 15 GB | 20 GB |
Online redo logs DB size * 0.2 | 1 GB | 2 GB | 4 GB | 10 GB | 20 GB | 30 GB | 40 GB |
Archive logs DB size * 1.0 | 5 GB | 10 GB | 20 GB | 50 GB | 100 GB | 150 GB | 200 GB |
Backup/flash data DB size * 0.2 | 10 GB | 20 GB | 40 GB | 100 GB | 200 GB | 300 GB | 400 GB |
Total (GB) | 22,5 | 45 | 90 | 225 | 450 | 675 | 900 |
| |||||||
Memory management
Memory management is the most critical and complex part of tuning Oracle databases for performance. Our testing environment confirmed that using AMM provided better performance if the correct initial settings for MEMORY_TARGET are specified.
Number of nodes | MEMORY_TARGET |
|---|---|
5,000 | 4 GB |
10,000 | 8 GB |
20,000 | 16 GB |
50,000 | 24 GB |
100,000 | 48 GB |
150,000 | 72 GB |
200,000 | 96 GB |
Initialization parameters
For optimal performance with Client Management, the following Oracle database initialization parameters are recommended.
For Oracle 12c
Parameter | Value |
|---|---|
filesystemio_options | SETALL |
query_rewrite_enabled | FALSE |
optimizer_mode | CHOOSE |
optimizer_features_enable | 9.2.0 |
cursor_sharing | FORCE |
open_cursors | 800 |
session_cached_cursors | 2000 |
compatible | 11.2.0 |
commit_logging | BATCH |
commit_wait | NOWAIT |
optimizer_index_cost_adj | 20 |
For Oracle-18c and Oracle-19c
Parameter | Value |
|---|---|
filesystemio_options | SETALL |
query_rewrite_enabled | FALSE |
cursor_sharing | FORCE |
open_cursors | 800 |
session_cached_cursors | 2000 |
commit_logging | BATCH |
commit_wait | NOWAIT |
ddl_lock_timeout | 600 |
Only for Oracle-19c
Parameter | Value |
|---|---|
"_rowsets_enabled" | FALSE |
To check if the statistics are automatically being updated
To ensure that Oracle delivers optimum performance when working with BMC Client Management, you need to ensure that the database statistics and indexes are regularly updated.
To check if the statistics are automatically being updated, run the following script as sysdba in the Oracle SQL Developer tool.
Check that auto optimizer stats collection is activated.
To check the statistics update schedule
To find out when the statistics are automatically being updated, run the following script as sysdba in the Oracle SQL Developer tool.
To check the statistics update status
To find out when the status of the statistics is updated, run the following script as sysdba in the Oracle SQL Developer tool.
