Skip Headers
Oracle® Identity Manager Best Practices Guide

Part Number E14761-02
Go to Documentation Home
Go to Book List
Book List
Go to Table of Contents
Go to Index
Go to Feedback page
Contact Us

Go to previous page
Go to next page
View PDF

2 Tuning Oracle Database for Oracle Identity Manager

As with any enterprise class business application, there is no simple procedure for tuning that works for all systems. This section describes one sample configuration and outlines the principles for tuning Oracle Database.

Oracle Identity Manager has many configuration options. The best way to identify bottlenecks and optimize performance is to monitor key database performance indicators in your production environment and adjust the configuration accordingly. This chapter serves as a guideline to help you choose the initial baseline database configuration.

This chapter discusses the following topics:

2.1 Sample Instance Configuration Parameters

The following sample configuration parameter settings are based on a server with four CPUs (64 bit) and 8 or 16 gigabytes (GB) RAM.


In the following table:

ASMM denotes the Automatic Shared Memory Management feature of Oracle Database 10g. It automatically distributes the memory among various subcomponents to ensure the most effective memory utilization.

You should set the processes parameter to accommodate the following connection pool requirements and few extra connections for external programs:

  • Connection pool size of XA data-source configured in Application Server

  • Connection pool size for non-XA data-source configured in Application Server

  • Direct database connection pool size configured in xlconfig.xml

Table 2-1 Sample Configuration Parameters

Parameter Recommended Initial Settings for Oracle9i Database Recommended Initial Settings for Oracle Database 10g





4 GB (Enables ASMM)

10 GB (Enables ASMM)


4 GB

10 GB


1.2 GB

1.2 GB





15 MB

15 MB





Between 0 and 20

Between 0 and 20


























Based on connection pool settings

Based on connection pool settings

2.2 Physical Data Placement

The basic installation of Oracle Identity Manager uses only one physical tablespace to store database objects. Oracle Identity Manager database objects belong to one of the following categories:

For better performance, create multiple locally managed tablespaces and store each category of database object in a dedicated tablespace. This optimizes storage for efficient data access. Oracle recommends that you place the following User Profile Audit (UPA) component tables and indexes in their own tablespaces:

These tables can store significant amounts of historical data and can be used by historical reports.

The database schema includes the following tables for reconciliation data:

If your environment generates a large amount of reconciliation data, move these tables to a new tablespace. Use the locally managed tablespaces with automatic extent allocation.

You can use performance metrics to identify tables that are accessed frequently (hot tables). To reduce I/O contention, move hot tables to dedicated tablespaces. See "Database Performance Monitoring" for more information about performance metrics.

Redo-Log Files

Depending on the reconciliation processes configured in Oracle Identity Manager, the volume of database transactions and commits during a reconciliation run can be high. Oracle recommends that you use multiple redo-log files. The total allocated redo-log space should be 1 GB to 2 GB.

Keep Pool Changes

By default, Oracle Identity Manager assigns USR and PCQ tables to be cached in the database by using a keep pool buffer (see db_keep_cache_size in Table 2-1). If your installation contains more than 50,000 users, then Oracle recommends that you use the default database buffer for USR and PCQ tables instead of the keep pool buffer. You can use the following commands.

ALTER TABLE USR STORAGE(buffer_pool default);

ALTER TABLE PCQ STORAGE(buffer_pool default);

2.3 Pinning Sequences and Stored Procedures in the System Global Area (SGA)

Oracle Identity Manager uses sequence objects to generate unique record identifiers. Oracle Identity Manager also uses stored procedures to perform specific database operations. To optimize performance during production, pin the sequence objects and stored procedures in the SGA. A script named create_db_trigger.sql is shipped with the Oracle Identity Manager installation for this purpose. The create_db_trigger.sql script is written for the Oracle Identity Manager database account SYSADM. It is a sample Oracle login account.

This script is located in the following installation directory:


To pin the sequence objects and stored procedures:

  1. Log in as SYS.

  2. Start SQL*Plus (the Oracle client tool) at a command prompt, by entering the following command:

    sqlplus /nolog
  3. Connect to the Oracle instance as SYS user with SYSDBA role.

    For example, enter the following command:

    CONNECT SYS/sys_password@db_instance AS SYSDBA

    In this command, sys_password is the password for the SYS user account, and db_instance is the Net 8 service name for connecting to the database instance.

    For example:

    CONNECT SYS/sys@xeltest AS SYSDBAConnected.
  4. Edit the create_db_trigger.sql script in a text editor, and specify your Oracle Identity Manager database account name.

  5. In create_db_trigger.sql, substitute all references to sysadm with the account name that you used.

    For example, if your Oracle Identity Manager database account name is myschema, edit your script as follows:

    create or replace trigger cache_seq after startup on database beginmyschema.pin_obj;-- pin all sysadm's sequences in shared_poolmyschema.pin_sp;-- pin all commonly executed XELL stored procedures or functionsend;/
  6. Run the create_db_trigger.sql script.

    This script creates a database trigger that is fired every time the database starts. Any subsequent database bounces automatically pin the sequences and stored procedures in the SGA.

  7. While connected to the Oracle instance as the SYS user, enter the following commands:

    EXEC database_user.PIN_OBJ;EXEC database_user.PIN_SP;

    In these commands, database_user is the database account.

    Run these commands only once during initial schema creation. Bouncing the Oracle server is not required.

2.4 Database Performance Monitoring

To identify performance bottlenecks, you can monitor real-time performance metrics for the Oracle Identity Manager database.

Perform the following at regular intervals: