3 Tuning Oracle Database for Oracle Identity Manager

As with any enterprise class business application, turning the system for optimum performance depends on many unknown factors. Therefore, there is no simple procedure that will work for all systems. This section describes one sample configuration and outlines principles used to customize your configuration to best meet your needs.

Oracle Identity Manager has many configuration options. The best way to identify bottlenecks and remove them is by monitoring key database performance indicators in your production environment. Adjust the configuration from time to time in order to optimize performance. This chapter serves as a guideline to help you choose the initial baseline database configuration. It includes 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
`compatible`	9.2.0.0.0
`cursor_sharing`	SIMILAR
`db_block_size`	8192
`db_cache_size`	3200M
`db_keep_cache_size`	800M
`log_buffer`	262144
`shared_pool_size`	500M
`sga_max_size`	5500M
`db_file_multiblock_read_count`	8
`db_writer_processes`	2
`hash_join_enabled`	true
`java_pool_size`	150M
`open_cursors`	600
`optimizer_feature_enable`	9.2.0
`optimizer_index_cost_adj`	Between 0 and 20
`pga_aggregate_target`	1000M
`workarea_size_policy`	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)
`query_rewrite_enabled`	true
`query_rewrite_integrity`	trusted
`session_cached_cursors`	300

Physical Data Placement

The out-of-the-box installation of Oracle Identity Manager uses only one physical tablespace to store various database objects. In order to achieve better performance, create multiple locally managed tablespaces, and use a dedicated tablespace to store each category of database objects.

Oracle Identity Manager data is classified into the following categories:

Physical tables
Index
Large objects (LOBS/CLOBS)

Place each category of data in different tablespaces in order to optimize storage for efficient data access. Furthermore, Oracle strongly recommends that you place the User Profile Audit (UPA) component tables (UPA, UPA_FIELDS, UPA_GRP_MEMBERSHIP, UPA_RESOURCE, and UPA_USR) and indexes in their own tablespaces. These tables may need to store significant amounts of historical data and might be used by historical reports.

The database schema includes a set of nine reconciliation tables (RCA, RCB, RCD,RCE, RCH, RCM, RCP, RCU, and RPC) that are used to store reconciliation data. If your environment generates a lot of reconciliation data, move these tables to a different tablespace. Use the locally managed tablespaces with automatic extent allocation.

Also, you can use performance metrics to further 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 in order to generate unique record identifiers. Oracle Identity Manager also uses stored procedures in order to perform certain database operations. To achieve optimal performance in production deployment, pin the sequence objects and stored procedures in SGA. For this purpose, a script create_db_trigger.sql is shipped with the Oracle Identity Manager installation. This script is located in the installation distribution in \installServer\Xellerate\db\oracle. Log in as SYS to run this script and perform the following steps:

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.
```
The create_db_trigger.sql script is written for the Oracle Identity Manager database account SYSADM. This is a sample Oracle login account. Edit the create_db_trigger.sql script, and specify your actual Oracle Identity Manager database account name prior to running the script.
Open the create_db_trigger.sql script in a text editor and 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 so it looks like:

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. This way, any subsequent database bounces pin the sequences and stored procedures in SGA automatically.
Now, 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

Monitor the real-time performance metrics for the Oracle Identity Manager database to help identify any performance bottlenecks. Perform the following steps regularly:

Monitor the real-time performance using performance-monitoring tools (such as Oracle statspack, or any other third party tool).
Collect initial schema statistics upon implementation of Oracle Identity Manager. Then update schema statistics regularly, so that the Cost-Based Optimizer (CBO) has access to the most up-to-date statistics. This helps CBO determine the 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, and adjust the instance configuration parameters according to the advised settings.