Oracle® Identity Manager Best Practices Guide Release 9.1.0.2 Part Number E14761-02 |
|
|
View PDF |
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:
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 |
---|---|---|
|
8192 |
8192 |
|
4 GB (Enables ASMM) |
10 GB (Enables ASMM) |
|
4 GB |
10 GB |
|
1.2 GB |
1.2 GB |
|
800M |
800M |
|
15 MB |
15 MB |
optimizer_mode |
CHOOSE |
CHOOSE |
|
Between 0 and 20 |
Between 0 and 20 |
|
FORCE |
FORCE |
|
600 |
800 |
|
600 |
800 |
|
False |
False |
|
TRUE |
TRUE |
|
TRUSTED |
TRUSTED |
|
16 |
16 |
|
2 |
2 |
|
Based on connection pool settings |
Based on connection pool settings |
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:
RCA
RCB
RCD
RCE
RCH
RCM
RCP
RCU
RPC
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);
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.
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.