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:

Sample Instance Configuration Parameters
Physical Data Placement
Pinning Sequences and Stored Procedures in the System Global Area (SGA)
Database Performance Monitoring

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.

Note:

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
`db_block_size`	8192	8192
`sga_target`	4 GB (Enables ASMM)	10 GB (Enables ASMM)
`sga_max_size`	4 GB	10 GB
`pga_aggregate_target`	1.2 GB	1.2 GB
`db_keep_cache_size`	800M	800M
`log_buffer`	15 MB	15 MB
optimizer_mode	CHOOSE	CHOOSE
`optimizer_index_cost_adj`	Between 0 and 20	Between 0 and 20
`cursor_sharing`	FORCE	FORCE
`open_cursors`	600	800
`session_cached_cursors`	600	800
`cursor_space_for_time`	False	False
`query_rewrite_enabled`	TRUE	TRUE
`query_rewrite_integrity`	TRUSTED	TRUSTED
`db_file_multiblock_read_count`	16	16
`db_writer_processes`	2	2
`processes`	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:

Physical tables
Indexes
Large objects (LOBs or CLOBs)

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:

UPA
UPA_FIELDS
UPA_GRP_MEMBERSHIP
UPA_RESOURCE
UPA_USR

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:

/installServer/Xellerate/db/oracle

To pin the sequence objects and stored procedures:

Log in as SYS.
Start SQL*Plus (the Oracle client tool) at a command prompt, by entering the following command:
```
sqlplus /nolog
```
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.
```
Edit the create_db_trigger.sql script in a text editor, and specify your Oracle Identity Manager database account name.

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

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

Monitor real-time performance by using a performance-monitoring tool such as Oracle Enterprise Manager console or Automatic Workload Repository (AWR) in Oracle Database 10g.
Collect complete schema statistics upon implementation of Oracle Identity Manager.

Update schema statistics regularly, so that the Cost-Based Optimizer (CBO) can access the latest statistics. You must consider complete schema or table statistics on mass data change events like a huge reconciliation run from a new target, reconciliation archival, or a task archival.

This helps the CBO determine an efficient query execution plan that is based on the current state of data. The following is a sample SQL command to collect database statistics on a regular basis:
```
DBMS_STATS.GATHER_SCHEMA_STATS(OWNNAME=> schema_owner,
  ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE,
  DEGREE=>8,
  OPTIONS=>'GATHER AUTO',
  NO_INVALIDATE=>FALSE);
```
Look for relevant recommendations provided in advisory sections in the Automatic Database Diagnostic Monitor (ADDM) or Automatic Workload Repository (AWR) report, and adjust the instance configuration parameters according to the recommended settings.