1. Installation Guide
  2. Preparing for BRM Installation
  3. Planning Your Database Configuration

2 Planning Your Database Configuration

Learn about guidelines for configuring your Oracle Communications Billing and Revenue Management (BRM) database.

Topics in this document:

Configuring Development or Demonstration System Databases

The following information will assist you in configuring your database for a development or demonstration system.

Tablespace Mapping

In general, you do not need to map tablespaces for development or demonstration systems manually. You can use the default configuration. However, see "Mapping Tablespaces to Logical Devices" for information if you are using more than one tablespace for either tables or indexes.

For information about managing tablespaces, see "Managing Tablespaces" in Database Administrator’s Guide in the Oracle database documentation.

Creating Tablespaces

Create the actual tablespaces before running the pin_setup configuration script since they require existing tablespaces. Only the two default tablespaces must be created if you have not edited the pin_tables.values file to assign tables to nondefault tablespaces.

Configuring init.ora Parameters for Development or Demonstration Systems

Table 2-1 provides guidelines for some database and operating system configuration options specific to BRM. For information on other parameters, see the Oracle database documentation.

For information about memory configuration, see "Tuning Database Memory " in Database Performance Tuning Guide and "Managing Memory" in Database Administrator’s Guide in the Oracle database documentation.

Table 2-1 Configuration Options for Development or Demonstration Systems

Configuration Options Guidelines

log_buffer

Set the log_buffer option to approximately 2 MB.

Sample value setting for the log_buffer parameter: 2621440.

open_cursors

A minimum number for open_cursors is 1080. There is no penalty, however, for having this parameter set to a high value.

Set open_cursors to match the value of the stmt_cache_entries entry in the DM Oracle configuration (pin.conf) file. If using a statement cache size of 1080, for example, the stmt_cache_entries entry appears as follows in the DM pin.conf file: 

- dm stmt_cache_entries -1080

session_cached_cursors

Set the session_cached_cursors option to a nonzero value to enable the database to maintain cursors used in the repeated parsing of SQL statements. Use 150 as a starting point.

db_files

Sample value setting for the db_files parameter: 300 to 1020.

processes

Sample value setting for the processes parameter: 320.

db_block_size

Sample value setting for the db_block_size parameter: 8192.

Configuring Production System Databases

For information about configuring the Oracle database, see the Oracle documentation. In particular, consult the following:

  • For information about memory configuration, see "Tuning Database Memory " in Database Performance Tuning Guide and "Managing Memory" in Database Administrator’s Guide in the Oracle database documentation.

    The benchmark testing uses Automatic Shared Memory Management.

  • For information about managing tablespaces, see "Managing Tablespaces" in Database Administrator’s Guide in the Oracle database documentation.

  • For information about configuring a high-availability system for best performance, see "Oracle Database High Availability Best Practices" in High Availability Overview and Best Practices in the Oracle database documentation.

The following sections contain information about configuring the database for a production system:

Storage Considerations, Redo, and Undo

To determine the storage device type to select, and for other storage considerations, see "I/O Configuration and Design" in Database Performance Tuning Guide in the Oracle database documentation.

For information about the redo log buffer, see "Configuring the Redo Log Buffer" in Database Performance Tuning Guide in the Oracle database documentation.

Oracle strongly recommends that you run your database in automatic undo management mode instead of using rollback segments. For more information, see "Managing Undo" in Database Administrator’s Guide in the Oracle database documentation.

Temporary storage should be three to four times the size of the largest table (usually EVENT_T).

Estimating the Database Size

The objects that require the most storage space in a default BRM installation are accounts, bills, and events. The tables that correspond to these objects are also the ones with the most activity:

  • ACCOUNT_T
  • BAL_GRP_T
  • BAL_GRP_BALS_T
  • BAL_GRP_SUB_BALS_T
  • PURCHASED_DISCOUNT_T
  • PURCHASED_PRODUCT_T
  • BILL_T
  • BILLINFO_T
  • ITEM_T
  • INVOICE_FORMATS_T
  • SERVICE_T

For most customers, the following event tables will be very large:

  • EVENT_BAL_IMPACTS_T
  • EVENT_BILLING_PRODUCT_T
  • EVENT_ESSENTIALS_T
  • EVENT_T

For Telco service customers, the following tables will be very large:

  • EVENT_DLAY_SESS_TLCS_T
  • EVENT_DLYD_SESSION_TLCO_GPRS_T
  • EVENT_DLYD_SESSION_TLCO_GSM_T

Your customizations might create additional large tables. For example, if you store a lot of account profile data, the following tables will also be large:

  • PROFILE_T
  • PROFILE_SERV_EXTRATING_DA_T
  • PROFILE_SERV_EXTRATING_T

Other tables may be large depending on your implementation. Your estimate should not include space gained by archiving.

International Version Sizing Considerations

English databases store VARCHARs as one byte per character. Localized versions of BRM can store these strings in AL32UTF8 or UTF8 format. BRM uses the AL32UTF8 character set. Character strings such as names, addresses, descriptions, and notes that can be manipulated by BRM GUI tools can vary in size. To size the database, you must determine roughly what percentage of the database consists of strings that can vary in size.

Selecting the Storage Model

Choose a storage model based on the total size of your database, which you can determine by summing your data, index, rollback, and temporary tablespaces.

  • Use Test or Small for test or demonstration databases.

  • Use Large for production databases.

You can set the storage option during installation. See "Installing BRM".

During installation, BRM tables are created using one of the storage models. Therefore, the default storage clause is not used at installation. (The default storage clause specifies the storage parameters to use if no storage clause is provided.) However, if you create custom tables, you must specify a storage clause, or else the default storage clause is used.

Creating Tablespace Storage

For information about managing tablespaces, see "Managing Tablespaces" in Database Administrator’s Guide in the Oracle database documentation.

In our benchmark implementations, we use locally managed tablespaces with the bigfile option and Automatic Space Management (ASM). This is much easier than managing them manually. If you use the bigfile option, make sure that you monitor the file space usage so that disks don't become full. The following is a sample command to create a data file this way.

create bigfile tablespace tbsName DATAFILE '+DATA' SIZE tbsSizeM AUTOEXTEND ON NEXT extentSizeM MAXSIZE UNLIMITED;

where tbsName is the name of your tablespace, tbsSize is the initial size of the tablespace, and extentSize is the size of the extents.

For more information about locally managed tablespaces, see "Locally Managed Tablespaces" in Database Administrator’s Guide in the Oracle database documentation.

For more information about ASM, see "Introducing Oracle Automatic Storage Management" in Administrator's Guide in the Oracle database documentation.

If you are using Oracle RAC, for more information about managing storage, see "Storage Considerations for Oracle Grid Infrastructure and Oracle RAC" in Grid Infrastructure Installation and Upgrade Guide for Linux in the Oracle database documentation.

If you use Automatic Storage Management, you generally do not need to map tables to tablespaces.

However, if you want to manage tables to tablespaces, after BRM installation, you can use the pin_tables.values file to determine which tables map to which tablespaces. Then sum the storage required for the tablespace. This can be done for the larger tables and tablespaces. The smaller tablespaces do not need more than a few Gigabytes for their initial storage. See "Tablespace Priorities".

The size of the database, in turn, determines the minimum number of disks required for the database. Remember to consider disk space required for other purposes, for example, the operating system, BRM, log files, temporary storage, and swap files.

You should have enough disks to avoid performance bottlenecks. In addition, you can increase performance by spreading the most-used tables over multiple disks.

Note:

You can add disks and logical devices at any time after BRM has been installed.

After determining the number of disks available for the database, divide the tablespaces among those disks.

Half the remaining space will be used for mirroring. Put mirrors on different disks than their corresponding tables and indexes. The number of mirror logical devices will be equal, in number and size, to their corresponding table and index logical devices.

After allocating disk space for mirrors, divide the remaining disk space using the ratio of 2:1 between tables and indexes. That is, two-thirds of the space will be used for table logical devices and one-third of the space will be used for index logical devices.

Note:

The logical devices may not be the same size, because some tablespaces will be more active than others. See "Tablespace Priorities".

Create the logical devices over the remaining physical disks using the above guidelines. The next step is mapping tablespaces to logical devices.

Mapping Tablespaces to Logical Devices

In our benchmark implementations, we use locally managed tablespaces. If you use locally managed tablespaces, you do not have to manually map tablespaces to logical devices.

If you want to use it, logical-to-physical table and index mapping is defined in the BRM_home/setup/scripts/pin_tables.values file. This file lists 36 logical tablespaces for indexes and 36 logical tablespaces for data.

In the default BRM installation, all 72 BRM logical tablespaces map to two separate physical tablespaces: one for all the tables ($PIN_CONF_DM_DB_TABLES_GROUP) and one for all the indexes ($PIN_CONF_DM_DB_INDEX_GROUP). When you install BRM, the physical tablespace names PIN00 and PINX00 are substituted for the configuration variable names in the file. Following is a sample line from the pin_tables.values file: 

$PIN_CONF_TBLSPACE0="$PIN_CONF_TABLESPACE_ID $PIN_CONF_DM_DB_TABLES_GROUP";

To map logical and physical devices, you modify the entries in the right-hand column. You can edit the file before or after creating the logical devices, however, you must create logical and physical tablespaces before running the pin_setup script, because it uses the values in the pin_tables.values file.

Tablespace Priorities

BRM tablespaces are ranked according to highest level of activity. Table 2-2 shows the default priority.

Table 2-2 Tablespace Priorities

Priority Table Index

1

tablespace5

tablespacex15

2

tablespace0

tablespacex14

3

tablespace15

tablespacex1

4

tablespace1

tablespacex0

5

tablespace6

tablespacex7

6

tablespace2

tablespacex11

7

tablespace3

tablespacex5

8

tablespace7

tablespacex12

9

tablespace4

tablespacex9

10

tablespace8

tablespacex6

11

tablespace14

tablespacex13

12

tablespace13

tablespacex2

13

tablespace12

tablespacex3

14

tablespace10

tablespacex10

15

tablespace11

tablespacex4

16

tablespace9

tablespacex8

In most cases you should not change table to tablespace mappings. Instead, change the tablespace priorities, the number of logical priorities, or both. Your goal should be to maintain the best performance based on your customizations and customer usage patterns.

To change table-to-tablespace mapping, use the same tablespace for tables whose sizes result from different conditions. In this way, tables grow at different rates, resulting in more efficient space allocation within one tablespace. If tablespaces are not large enough, they fill up and an error is returned to BRM. This usually happens with the event tables first.

For best results, 36 tablespaces have been provided for data and another 36 tablespaces for indexes in the pin_tables.values file. Depending on your installation size, available hardware, and the functionality you are implementing, such as using the Rated Event (RE) Loader, you may or may not use all 36 of these tablespaces for the database. BRM recommends the use of at least 18 tablespaces for data and another 16 for indexes.

Note:

If you are using RE Loader, you may need additional tablespaces for related tables.

If there are fewer than 72 logical devices, you can choose your own method to map the logical tablespaces to the smaller number of physical tablespaces. For example, you can allocate a few physical tablespaces to the highest-priority logical tablespaces, and allocate the remaining logical tablespaces using round-robin. Or, you can simply map all tablespaces using round-robin.

For example, assume that you have 6 logical devices for tables, and 4 logical devices for indexes. These 10 logical devices are mapped to 10 physical tablespaces. The two highest priority logical tablespaces are mapped to their own physical tablespaces, using the priorities defined in the priority table. The remaining 30 logical tablespaces are then mapped to the remaining tablespaces using round-robin. To do this, first assign the physical tablespace to the physical tablespace configuration variable:

#=========================================================
# default tablespace or filegroup names
#=========================================================
$PIN_CONF_DM_DB_TABLES_GROUP = "pin00";
$PIN_CONF_DM_DB_TABLES_GROUP1 = "pin01";
$PIN_CONF_DM_DB_TABLES_GROUP2 = "pin02";
$PIN_CONF_DM_DB_TABLES_GROUP3 = "pin03";
$PIN_CONF_DM_DB_TABLES_GROUP4 = "pin04";
$PIN_CONF_DM_DB_TABLES_GROUP5 = "pin05";
$PIN_CONF_DM_DB_INDEX_GROUP = "pinx00";
$PIN_CONF_DM_DB_INDEX_GROUP1 = "pinx01"
$PIN_CONF_DM_DB_INDEX_GROUP2 = "pinx02"
$PIN_CONF_DM_DB_INDEX_GROUP3 = "pinx03"
  

Then change the default logical tablespace to physical tablespace mapping as follows, using the priority table:

Tablespace Mapping for Data
$PIN_CONF_TBLSPACE0 = "$PIN_CONF_TABLESPACE_ID $PIN_CONF_DM_DB_TABLES_GROUP1";
$PIN_CONF_TBLSPACE1 = "$PIN_CONF_TABLESPACE_ID $PIN_CONF_DM_DB_TABLES_GROUP3";
$PIN_CONF_TBLSPACE2 = "$PIN_CONF_TABLESPACE_ID $PIN_CONF_DM_DB_TABLES_GROUP5";
$PIN_CONF_TBLSPACE3 = "$PIN_CONF_TABLESPACE_ID $PIN_CONF_DM_DB_TABLES_GROUP2";
$PIN_CONF_TBLSPACE4 = "$PIN_CONF_TABLESPACE_ID $PIN_CONF_DM_DB_TABLES_GROUP4";
$PIN_CONF_TBLSPACE5 = "$PIN_CONF_TABLESPACE_ID $PIN_CONF_DM_DB_TABLES_GROUP";
$PIN_CONF_TBLSPACE6 = "$PIN_CONF_TABLESPACE_ID $PIN_CONF_DM_DB_TABLES_GROUP4";
$PIN_CONF_TBLSPACE7 = "$PIN_CONF_TABLESPACE_ID $PIN_CONF_DM_DB_TABLES_GROUP3";
$PIN_CONF_TBLSPACE8 = "$PIN_CONF_TABLESPACE_ID $PIN_CONF_DM_DB_TABLES_GROUP5";
$PIN_CONF_TBLSPACE9 = "$PIN_CONF_TABLESPACE_ID $PIN_CONF_DM_DB_TABLES_GROUP3";
$PIN_CONF_TBLSPACE10 = "$PIN_CONF_TABLESPACE_ID $PIN_CONF_DM_DB_TABLES_GROUP5";
$PIN_CONF_TBLSPACE11 = "$PIN_CONF_TABLESPACE_ID $PIN_CONF_DM_DB_TABLES_GROUP2";
$PIN_CONF_TBLSPACE12 = "$PIN_CONF_TABLESPACE_ID $PIN_CONF_DM_DB_TABLES_GROUP4";
$PIN_CONF_TBLSPACE13 = "$PIN_CONF_TABLESPACE_ID $PIN_CONF_DM_DB_TABLES_GROUP3";
$PIN_CONF_TBLSPACE14 = "$PIN_CONF_TABLESPACE_ID $PIN_CONF_DM_DB_TABLES_GROUP2";
$PIN_CONF_TBLSPACE15 = "$PIN_CONF_TABLESPACE_ID $PIN_CONF_DM_DB_TABLES_GROUP2";
...
...
Tablespace Mapping for Indexes
$PIN_CONF_TBLSPACEX0 = "$PIN_CONF_TABLESPACE_ID $PIN_CONF_DM_DB_INDEX_GROUP3";
$PIN_CONF_TBLSPACEX1 = "$PIN_CONF_TABLESPACE_ID $PIN_CONF_DM_DB_INDEX_GROUP2";
$PIN_CONF_TBLSPACEX2 = "$PIN_CONF_TABLESPACE_ID $PIN_CONF_DM_DB_INDEX_GROUP3";
$PIN_CONF_TBLSPACEX3 = "$PIN_CONF_TABLESPACE_ID $PIN_CONF_DM_DB_INDEX_GROUP";
$PIN_CONF_TBLSPACEX4 = "$PIN_CONF_TABLESPACE_ID $PIN_CONF_DM_DB_INDEX_GROUP2";
$PIN_CONF_TBLSPACEX5 = "$PIN_CONF_TABLESPACE_ID $PIN_CONF_DM_DB_INDEX_GROUP2";
$PIN_CONF_TBLSPACEX6 = "$PIN_CONF_TABLESPACE_ID $PIN_CONF_DM_DB_INDEX_GROUP1";
$PIN_CONF_TBLSPACEX7 = "$PIN_CONF_TABLESPACE_ID $PIN_CONF_DM_DB_INDEX_GROUP";
$PIN_CONF_TBLSPACEX8 = "$PIN_CONF_TABLESPACE_ID $PIN_CONF_DM_DB_INDEX_GROUP3";
$PIN_CONF_TBLSPACEX9 = "$PIN_CONF_TABLESPACE_ID $PIN_CONF_DM_DB_INDEX_GROUP";
$PIN_CONF_TBLSPACEX10 = "$PIN_CONF_TABLESPACE_ID $PIN_CONF_DM_DB_INDEX_GROUP1";
$PIN_CONF_TBLSPACEX11 = "$PIN_CONF_TABLESPACE_ID $PIN_CONF_DM_DB_INDEX_GROUP1";
$PIN_CONF_TBLSPACEX12 = "$PIN_CONF_TABLESPACE_ID $PIN_CONF_DM_DB_INDEX_GROUP3";
$PIN_CONF_TBLSPACEX13 = "$PIN_CONF_TABLESPACE_ID $PIN_CONF_DM_DB_INDEX_GROUP2";
$PIN_CONF_TBLSPACEX14 = "$PIN_CONF_TABLESPACE_ID $PIN_CONF_DM_DB_INDEX_GROUP1";
$PIN_CONF_TBLSPACEX15 = "$PIN_CONF_TABLESPACE_ID $PIN_CONF_DM_DB_INDEX_GROUP";
...
...
  

The details on changing configuration variables in the configuration scripts can be found in the comments in those scripts.

Be sure to assign custom tables (new tables created for new objects that are not part of the default BRM installation) to explicit tablespaces. These can be new tablespaces that you create or existing ones. If you do not make a tablespace assignment for these tables, the new tables will go into a default tablespace which may cause performance or storage problems as the new tables grow or are accessed.

Creating Tablespaces

Create the actual tablespaces before running the pin_setup configuration script since they require existing tablespaces. Only the two default tablespaces must be created if you have not edited the pin_tables.values file to assign tables to nondefault tablespaces.

Running Configuration Scripts to Create the BRM Database

See "BRM Preinstallation Tasks" for information about creating the BRM database.

Configuring init.ora Parameters

Table 2-3 provides guidelines for some database and operating system configuration options specific to BRM. For information on other parameters, see the Oracle database documentation.

For information about memory configuration, see "Tuning Database Memory" in Database Performance Tuning Guide and "Managing Memory" in Database Administrator’s Guide in the Oracle database documentation.

For information about database performance tuning, see "Oracle Database Configuration Best Practices" in High Availability Overview and Best Practices in the Oracle database documentation.

Table 2-3 Configuration Options for Production Systems

Configuration Options Guidelines

db_writer_processes

You can improve I/O performance by increasing the number of DB writer processes from the default, single process. Setting db_writer_processes between 5 and 10 (for the largest systems) can improve I/O throughput. If db_writer_processes is set, dbwr_io_slaves must not be specified.

DML locks

Use 5000 for DML locks for very heavy workloads.

Sample value setting for the dml_locks parameter: 5000.

freelist and pctfree

If you are using locally managed tablespaces, you do not need to set these parameters.

If you are not using locally managed tablespaces, consider creating tablespaces with additional room for inserting. The storage parameters are pctfree and freelist. Although using freelists requires more disk and memory, insert speed is greatly enhanced. The default is 1. The most active tables should be in tablespaces with at least 10 – 20 freelists, depending on the size of the installation.

open_cursors

A minimum number for open_cursors is 1080. There is no penalty, however, for having this parameter set to a high value.

Set open_cursors to match the value of the stmt_cache_entries entry in the DM Oracle configuration (pin.conf) file. If using a statement cache size of 1080, for example, the stmt_cache_entries entry appears as follows in the DM pin.conf file: 

- dm stmt_cache_entries -1080

The statement-handle caching performance feature requires a large number of open_cursors. Increase the open_cursors parameter to 4192 by adding the following line to the initSID.ora file. 

open_cursors = 4192 (minimum value: statement cache size + number of dm_backends)

session_cached_cursors

Set the session_cached_cursors option to a nonzero value to enable the database to maintain cursors used in the repeated parsing of SQL statements. Use 150 as a starting point.

processes

Sample value setting for the processes parameter: 800.

db_block_size

Sample value setting for the db_block_size parameter: 8192.

Using Optimization

For information about using the optimizer, see "Influencing the Optimizer " and "Optimizer Statistics Concepts" in SQL Tuning Guide in the Oracle database documentation.

In our benchmark implementations, we use the ALL_ROWS optimizer mode. For performance reasons, it is important to gather good optimizer statistics.

About Using Virtual Columns

Virtual columns are columns whose values are defined by an expression, are computed when you query the data, and are not physically stored in the database. Oracle Database supports virtual columns by default. You can use virtual columns in the BRM database.

Implementations of BRM have shown that a high percentage of the BRM database storage space can be used by the event tables. BRM can use virtual columns in a way that results in space savings for event records. To enable virtual columns in the BRM database, you convert event storable classes (/event and its subclasses) in the BRM schema. The savings in database storage applies to event data that the system creates after the virtual columns are generated (not to existing event data). Virtual column functionality is transparent to BRM.

For information about virtual columns in general, see the Oracle Database documentation.

For information about generating virtual columns on BRM event tables, see "Generating Virtual Columns on Event Tables" in BRM System Administrator's Guide.