Oracle recommendations

The database is an important part of BMC Client Management , especially in highly distributed environments. For each of the three basic types of database-Web Application (Web), Online Transaction Processing (OLTP), and Data Warehousing (DW)-the hardware component priorities are different. Client Management works like a web application with web services; it behaves like an OLTP application when integrating all information reported by devices; and it performs like a DW application for report generation, aggregating the results and displaying inventory views.

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.

If you need to refer to this page offline, you can export it as a PDF by clicking .

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	—

Recommended minimum disk layout

Required number of disks	Rpms/s	File system	Purpose
1	10 K / 15 K or SSD	ext3 (Linux)/NTFS (Windows)	/ SYSTEM - ORACLE
1	10 K / 15 K or SSD	ext3 (Linux)/NTFS (Windows)	/ REDO LOG
1	10 K / 15 K or SSD	ext3 (Linux)/NTFS (Windows)	/ TABLESPACES FOR TABLES
1	10 K / 15 K or SSD	ext3 (Linux)/NTFS (Windows)	/ TABLESPACES FOR INDEXES
1	10 K / 15 K or SSD	ext3 (Linux)/NTFS (Windows)	/ TEMPORARY TABLESPACES

Recommended best disk layout

Required number of disks	Rpms/s	File system	Purpose
1	10 K / 15 K or SSD	ext3 (Linux)/NTFS (Windows)	/ SYSTEM - ORACLE
2	10 K / 15 K or SSD	ext3 (Linux)/NTFS (Windows)	/ REDO LOG
1	10 K / 15 K or SSD	ext3 (Linux)/NTFS (Windows)	/ TABLESPACES FOR SMALL TABLES
1	10 K / 15 K or SSD	ext3 (Linux)/NTFS (Windows)	/ TABLESPACES FOR LARGE TABLES
1	10 K / 15 K or SSD	ext3 (Linux)/NTFS (Windows)	/ TABLESPACES FOR STATIC TABLES
1	10 K / 15 K or SSD	ext3 (Linux)/NTFS (Windows)	/ TABLESPACES FOR SMALL INDEXES
1	10 K / 15 K or SSD	ext3 (Linux)/NTFS (Windows)	/ TABLESPACES FOR LARGE INDEXES
1	10 K / 15 K or SSD	ext3 (Linux)/NTFS (Windows)	/ TABLESPACES FOR STATIC INDEXES
1	10 K / 15 K or SSD	ext3 (Linux)/NTFS (Windows)	/ TEMPORARY TABLESPACES

Recommended RAID implementation

Required number of disks	Rpms/s	RAID level	File system	Purpose
2	10 K / 15 K or SSD	10	ext3 (Linux)/NTFS (Windows)	/ SYSTEM
8	10 K / 15 K or SSD	10	ext3 (Linux)/NTFS (Windows)	/ ORACLE + TABLESPACES
2	10 K / 15 K or SSD	10	ext3 (Linux)/NTFS (Windows)	/ REDO LOG
2	10 K / 15 K or SSD	10	ext3 (Linux)/NTFS (Windows)	/ 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	Space requirement	20,000 nodes	50,000 nodes	100,000 nodes	150,000 nodes	200,000 nodes
Database files	DB size	20 GB	50 GB	100 GB	150 GB	200 GB
Temporary tablespace	DB size * 0.2	4 GB	10 GB	20 GB	30 GB	40 GB
System/undo tablespace	DB size * 0.1	2 GB	5 GB	10 GB	15 GB	20 GB
Online redo logs	DB size * 0.2	4 GB	10 GB	20 GB	30 GB	40 GB
Archive logs	DB size * 1.0	20 GB	50 GB	100 GB	150 GB	200 GB
Backup/flash data	DB size * 2.0	40 GB	100 GB	200 GB	300 GB	400 GB
Total		90 GB	225 GB	450 GB	675 GB	900 GB

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

Oracle configurations

To ensure that the Oracle delivers optimum performance when working with BMC Client Management, you need ensure that the database statistics and indexes are regularly updated.

To check if the statistics are automatically being updated

To check if the statistics are automatically being updates, run the following script as sysdba in the Oracle SQL Developer tool.

Select client_name, status, attributes from dba_autotask_client;

Check that automatic update of the statistics is activated.

To check the statistics update schedule

To find out when the statistics are automatically being updates, run the following script as sysdba in the Oracle SQL Developer tool.

Select * from dba_autotask_schedule;

This will provide you with complete information on the dates and times of the latest "maintenance window".

To check the statistics update status

To find out when the status of the statistics update, run the following script as sysdba in the Oracle SQL Developer tool.

select window_name, autotask_status, optimizer_stats from dba_autotask_window_clients;