Improving Database Performance

29 Improving Database Performance

Learn how to improve database performance in Oracle Communications Billing and Revenue Management (BRM).

Topics in this document:

Improving Database Performance

A significant amount of time can be consumed in parsing SQL statements that read or write in the database. Adjusting an account, rating an event, and many other BRM activities require several steps to read and write data.

SQL statement-handle caching increases the speed at which statements are parsed.

How Statement-Handle Caching Works

An application sends an opcode through the CM to a DM, which maps each PCM operation to one or more dynamic SQL statements. For each such statement, the relational database management system (RDBMS)—such as Oracle—parses the statement, runs it, and fetches the results.

Oracle maintains a cache of the most frequent SQL queries. It uses soft parsing to shortcut its process of deciphering these statements, thus saving time in retrieving the requested data from the actual database. It then sends the data back through the DM to the application.

BRM generates and repeats a finite set of SQL statement forms. If the caching of statement handles is not enabled, the DM always parses the statement before each execution. With caching enabled, BRM maintains its most recently used statement handles within the DM, freeing the RDBMS from spending its time on soft parsing.

How to Use the Statement-Handle Cache

The stmt_cache_entries entry in the Oracle DM configuration file (BRM_home/sys/dm_oracle/pin.conf) controls the statement-handle cache. The entry can be one of these two values:

A value of 0 disables the cache.
The default value of 1 means that the DM maintains 32 entries in each statement-handle cache for each back-end thread or process.

See the configuration file for more information.

The statement-handle caching performance feature requires a large value for the open_cursors setting in the initSID.ora database configuration file. See "Configuring Oracle Databases" in BRM Installation Guide.

Note:

If your Oracle database and DM both reside on computers with extraordinarily large memory resources, you might be able to cache more statement handles. Consult with Oracle for advice before attempting to cache more statement handles. It can be dangerous to exceed 32 entries because two Oracle parameters need to be increased along with stmt_cache_entries to prevent system failure.

Managing Database Usage

Performance of your BRM system is affected by the number of events in the database. You can limit which types of events are recorded in the database, which saves space and improves performance.

It is essential that all events with a balance impact be recorded in the database. You do this by including them in the pin_event_map file (BRM_home/sys/data/pricing/example) when you set up your product offerings. This is enforced by the Price List Facilities Module (FM) opcodes.

On the other hand, many events without balance impacts may not need to be recorded. For example, event objects that are recorded during account creation, bundle purchase, and charge offer purchase require no further updating.

BRM provides a utility and file for excluding events from being recorded in the database. The "load_pin_event_record_map" utility loads the pin_event_record_map file (BRM_home/sys/data/config/pin_event_record_map), in which you specify the event types to exclude.

Note:

By default, if an event type is not listed in the pin_event_record_map file, it is recorded.
Event notification can still be performed based on excluded events because it is triggered even by events that are configured not to be recorded. See "Using Event Notification" in BRM Developer's Guide.

Events that are mapped in the pin_event_map file should not be added to the pin_event_record_map file. If you specify an event type, the event record map is ignored when the event occurs.

Note:

Excluding events from being recorded can cause applications that use the event data to return incorrect results. Ensure the events you exclude are not being used by any other application before you load the event record file.

To exclude events from being recorded:

Open the BRM_home/sys/data/config/pin_event_record_map file.
List the events you want to exclude and set their record flag value to 0. For example, to not record folds that have no balance impacts, enter:
```
/event/billing/cycle/fold:   0
```
Note:

The file includes the option to enable recording of listed events. You can use this option under special circumstances to record events that are normally not recorded.
Save and close the file.
Use the "load_pin_event_record_map" utility to load the file.
Verify that the file was loaded by using Object Browser or the robj command in the testnap utility to display the /config/event_record_map object. See "Reading an Object and Writing Its Contents to a File" in BRM Developer's Guide.

Rebuilding Indexes

Indexes can become large and unbalanced, which reduces performance. To increase performance, rebuild the most heavily used indexes regularly. You can quickly rebuild indexes at any time. For example, you might want to rebuild some indexes before running billing. See "Rebuilding Indexes" in BRM Installation Guide.

Removing Unused Indexes

By default, BRM installation creates indexes for all features. However, if you do not use some features, you can delete their associated indexes.

See your database documentation for information about finding unused indexes. For example, on an Oracle database, turn on the Oracle tracing facility while BRM is running. This produces an output trace file, which you use as input to the Oracle TKPROF utility. The TKPROF utility creates a file that lists the access paths for each SQL command. These access paths include indexes.

Note:

You can also use the Oracle tracing facility to find missing indexes.