Tuning Oracle Database 11g for OIM 11.1.1

Version: Oracle Identity Manager 11g R1
Description: I will follow the sample configurations given in the Oracle Administrator's Guide for Oracle Identity Manager. Performance tuning varies from one environment to another so adjust the configuration accordingly. You can use the Oracle Database out of the box utilities to monitor your database performance. You must have the SYS credentials or privileges to make these changes. The database must be restarted after making these changes. Shutdown all WebLogic server instances when making these changes. Only have the Oracle database up.

Database Initialization Parameters
Sample configuration parameters values are used here. The parameters mentioned here are performance-related for Oracle 11g database.

Parameters                          Recommended Initial Settings
memory_target                       3GB
sga_target                          6G
pga_aggregate_target                2G
db_keep_cache                       800M
log_buffer                          15MB
cursor_sharing                      FORCE
open_cursors                        800
session_cached_cursors              800
query_rewrite_integrity             TRUSTED
query_rewrite_enabled               TRUE
db_file_multiblock_read_count       16
db_writer_processes                 2
processes                           Based on connection pool settings

To get all the Oracle Database 11g system variables, execute the following query:

show parameters;

Here is a  query to get only the initialization parameters mentioned above.  Note: "ISSYS_MODIFABLE" determines whether the parameter can be changed by using SYSTEM ALTER.

select * from v$parameter where name IN 
('memory_target', 'sga_target', 'pga_aggregate_target',
'db_keep_cache_size', 'log_buffer', 'cursor_sharing', 'open_cursors',
'session_cached_cursors', 'query_rewrite_integrity', 'query_rewrite_enabled',
'db_file_multiblock_read_count', 'db_writer_processes');

Alter the parameters:

ALTER SYSTEM SET memory_target=3221225472;
ALTER SYSTEM SET sga_target=6G;
ALTER SYSTEM SET pga_aggregate_target=2G;
ALTER SYSTEM SET db_keep_cache_size=800M;
ALTER system SET log_buffer=15728640 scope=spfile;
ALTER SYSTEM SET cursor_sharing=FORCE;
ALTER SYSTEM SET open_cursors=800;
ALTER SYSTEM SET session_cached_cursors=800 scope=spfile;
ALTER SYSTEM SET query_rewrite_integrity=TRUSTED;
ALTER SYSTEM SET query_rewrite_enabled=TRUE;
ALTER SYSTEM SET db_file_multiblock_read_count=16;
ALTER system SET db_writer_processes=2 scope=spfile;

Tablespaces
It is highly recommended to create dedicated tablespaces for Large Objects (LOBs). The Oracle documents provides a list of tables  to create tablespace for. I will create tablespaces for the following segments:

•  ORCHPROCESS.ORCHESTRATION
•  RECON_ACCOUNT_OLDSTATE.RE_OLD_ACCOUNT_DATA
•  RECON_ACCOUNT_OLDSTATE.RE_NEW_ACCOUNT_DATA

To check all your tablespaces in your database:

SELECT tablespace_name FROM dba_tablespaces;

To check all the location of your data files:

SELECT name FROM v$datafile;

Create a new tablespace and move LOB segments to it. Subsitute your values for the items in brackets.

CREATE TABLESPACE "[tablespace name]" DATAFILE '[PATH]' SIZE 
1024M AUTOEXTEND ON MAXSIZE 32767M LOGGING ONLINE PERMANENT
BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL UNIFORM SIZE 2M
DEFAULT NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO;

ALTER TABLE [OIM SCHEMA NAME].ORCHPROCESS MOVE LOB(ORCHESTRATION) STORE AS (TABLESPACE [tablespace name]);
ALTER TABLE [OIM SCHEMA NAME].RECON_ACCOUNT_OLDSTATE MOVE LOB(RE_OLD_ACCOUNT_DATA) STORE AS (TABLESPACE [tablespace name]);
ALTER TABLE [OIM SCHEMA NAME].RECON_ACCOUNT_OLDSTATE MOVE LOB(RE_NEW_ACCOUNT_DATA) STORE AS (TABLESPACE [tablespace name]);

Example:

CREATE TABLESPACE "OIM_RECON_LOB_ENHANCER" DATAFILE 
'/home/oracle/db/app/oracle/oradata/orcl/OIM_RECON_LOB_ENHANCER.dbf' SIZE 
1024M AUTOEXTEND ON MAXSIZE 32767M LOGGING ONLINE PERMANENT
BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL UNIFORM SIZE 2M
DEFAULT NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO;

ALTER TABLE DEV_OIM.ORCHPROCESS MOVE LOB(ORCHESTRATION) STORE AS (TABLESPACE OIM_RECON_LOB_ENHANCER);
ALTER TABLE DEV_OIM.RECON_ACCOUNT_OLDSTATE MOVE LOB(RE_OLD_ACCOUNT_DATA) STORE AS (TABLESPACE OIM_RECON_LOB_ENHANCER);
ALTER TABLE DEV_OIM.RECON_ACCOUNT_OLDSTATE MOVE LOB(RE_NEW_ACCOUNT_DATA) STORE AS (TABLESPACE OIM_RECON_LOB_ENHANCER);

After making these changes, you will need to rebuild your objects and indexes.
To rebuild your indexes for the ORCHPROCESS table execute the following:

ALTER index DEV_OIM.IDX_ORCHPROCESS_PPID rebuild;
ALTER index DEV_OIM.IDX_ORCHPROCESS_STATUS rebuild;
ALTER index DEV_OIM.ORCHPROCESS_PK rebuild;
ALTER index DEV_OIM.IDX_ORCHPROCESS_DEPPROCESSID rebuild;

To check the status of your db objects:

SELECT owner,object_type,object_name,status FROM dba_objects WHERE status = 'INVALID';

Manual approach to recompile db objects:

ALTER PACKAGE [my_package] COMPILE;
ALTER PACKAGE [my_package] COMPILE BODY;
ALTER PACKAGE [my_procedure] COMPILE;
ALTER FUNCTION [my_function] COMPILE;
ALTER TRIGGER [my_trigger] COMPILE;
ALTER VIEW [my_view] COMPILE;
ALTER [object_type] [my_object] COMPILE;

#Examples 
ALTER PROCEDURE DEV_SOAINFRA.PURGE_RUNTIME_WITH_TIMEOUT COMPILE;
ALTER PROCEDURE DEV_SOAINFRA.PURGE_B2B_INSTANCES_DOWN COMPILE;

Or use the out of the box sql script to recompile db objects:

#Login to SQLPLUS* as sysdba
SQL> @?/rdbms/admin/utlrp.sql

Keep Pool Changes
For over 50K users in OIM, change the USR and PCQ tables from "keep pool" to "default buffer pool".

ALTER TABLE DEV_OIM.USR STORAGE(buffer_pool default);
ALTER TABLE DEV_OIM.PCQ STORAGE(buffer_pool default);

Redo Logs
To get information on your redo-logs, execute the following queries:

SELECT * FROM v$log;
SELECT * FROM v$logfile;
SELECT * FROM v$log_history;
SELECT * FROM v$thread;