3 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 principles for tuning.

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 from time to time. 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 System Global Area (SGA)
Database Performance Monitoring

Sample Instance Configuration Parameters

The following parameter settings are based on a server with 4 CPUs and 8GB RAM

Table 3-1 Sample Configuration Parameters

Parameter	Recommended Initial Settings for Oracle9i	Recommended Initial Settings for Oracle10g
`compatible`	9.2.0.0.0	10.2.0.2.0
`cursor_sharing`	EXACT	EXACT
`db_block_size`	8192	8192
`db_cache_size`	3200M	3200M
`db_keep_cache_size`	800M	800M
`log_buffer`	14289920	14289920
`shared_pool_size`	500M	500M
`db_file_multiblock_read_count`	16	16
`db_writer_processes`	2	2
`hash_join_enabled`	TRUE	Not applicable
`java_pool_size`	300M	300M
`open_cursors`	600	600
`optimizer_feature_enable`	9.2.0	10.2.0.2
`optimizer_index_cost_adj`	Between 0 and 20	Between 0 and 20
`pga_aggregate_target`	1100M	1100M
`workarea_size_policy`	AUTO	AUTO
`processes`	Set the processes parameter to the maximum number of concurrent users + the number of background processes + the number of SQL*PLUS and other user processes + 10 (as an extra cushion)	Set the processes parameter to the maximum number of concurrent users + the number of background processes + the number of SQL*PLUS and other user processes + 10 (as an extra cushion)
`query_rewrite_enabled`	TRUE	TRUE
`query_rewrite_integrity`	TRUSTED	TRUSTED
`session_cached_cursors`	300	300

Physical Data Placement

The out-of-the-box 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
Index
Large objects (LOBS/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, or hot tables. To reduce I/O contention, move hot tables to dedicated tablespaces. See "Database Performance Monitoring" for more on performance metrics.

Pinning Sequences and Stored Procedures in 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 in production, pin the sequence objects and stored procedures in 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 Oracle SQL*Plus (the Oracle client tool), at a command prompt, by typing the following command:
```
sqlplus /nolog
```
Connect to the Oracle instance as SYS user with SYSDBA role.

For example, type the following command:
```
CONNECT SYS/sys_password@db_instance AS SYSDBA
```
Where 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 actual Oracle Identity Manager database account name.

In create_db_trigger.sql, substitute all references to sysadm with the account name you actually 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/functionsend;/

Run the create_db_trigger.sql script.

This script creates a database trigger that is fired every time the database starts up. Any subsequent database bounces automatically pin the sequences and stored procedures in SGA.
While connected to Oracle as the SYS user, run the following commands:
```
EXEC database_user.PIN_OBJ;EXEC database_user.PIN_SP;
```
Where database_user is the database account.

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

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 using a performance-monitoring tool such as Statspack in Oracle9i or Automatic Workload Repository (AWR) in Oracle10g.
Collect initial schema statistics upon implementation of Oracle Identity Manager.

Update schema statistics regularly, so that the Cost-Based Optimizer (CBO) has access to the most up-to-date statistics.

This helps the CBO determine a efficient query execution plan that is based on the current state of data.
Look for relevant recommendations provided in advisory sections in the Oracle Statspack report or AWR report, and adjust the instance configuration parameters according to the advised settings.